請選擇 進入手機版 | 繼續訪問電腦版

COCO研究院

 找回密碼
 註冊
搜索
查看: 1212|回復: 9

[EXCEL] 請教VBA裡遇0/0時程式中止的解決方法

[複製鏈接]
發表於 19-11-14 06:25|載入全部圖片 | 顯示全部樓層 |閱讀模式
本帖最後由 MaverickRay 於 19-11-14 06:27 編輯

我在VBA裡利用公式名稱的方式來分別定義兩個欄位
然後利用回圈方式讓程式能自動將兩欄做相除的動作..
可是後來我發現當程式遇到兩欄數值皆為0時,=>0/0
則程式便會中斷了,(必須偵錯,但不知要怎麼改)....想請教我要如何才能令其能順利繼續往下做動作呢?而0/0就讓他的結果=0的假設下

我有大約找了下,好像可以用on error...什麼的,但這語法我不太懂..
感謝~




上一篇︰請教群益API新版2.13.19 取國內權益數問題
下一篇︰台股盤前-美中貿易談判不明朗 多方力守月線支撐
發表於 19-12-7 12:16|載入全部圖片 | 顯示全部樓層
本帖最後由 pierrebox 於 19-12-7 13:24 編輯

如果很在意效能,可以考慮用陣列方式來計算比較快。
我的機器是大約10年前的筆電,只有雙核,以下兩個範例,你可以比較一下
第一個是取儲存格內的值來計算,第二個是先把儲存格值複製到陣列,在陣列中相除,再複製到儲存格。
從a6到a22000,超過二萬筆資料相除
b6開始是分母,c6開始是結果
第一個花了3.8秒左右,第二個只用了0.07 秒,效能快很多。
大約是50倍左右。程式最後會顯示計時,你可以在自己的機器上執行看看,並比較結果。
'========= 範例 1,儲存格取值 ===========
  1. Sub testCOCO()
  2. Application.Calculation = xlCalculationManual
  3. Application.ScreenUpdating = False
  4. Dim StartTime As Double
  5. Dim SecondsElapsed As Double
  6. StartTime = Timer

  7. Const 分子欄 As String = "$a"
  8. Const 分母欄 As String = "$b"
  9. Const 結果欄 As String = "$c"
  10. Dim a%
  11. On Error Resume Next
  12. With ThisWorkbook.ActiveSheet
  13.     For a = 6 To .Range("A6").End(xlDown).Row
  14.     .Range(結果欄 & a).Value2 = .Range(分子欄 & a).Value2 / .Range(分母欄 & a).Value2
  15.         If Err.Number <> 0 Then
  16.         Err.Clear
  17.         .Range(結果欄 & a).Value2 = 0
  18.         End If
  19.     Next a
  20. End With
  21. Application.Calculation = xlCalculationAutomatic
  22. Application.ScreenUpdating = True

  23. SecondsElapsed = Round(Timer - StartTime, 2)
  24. MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation
  25. End Sub
複製代碼




'========= 範例 2,使用陣列 ===========
  1. Sub testCOCO_array()
  2. Application.Calculation = xlCalculationManual
  3. Application.ScreenUpdating = False
  4. Dim StartTime As Double
  5. Dim SecondsElapsed As Double
  6. StartTime = Timer

  7. Const 分子欄 As String = "$a"
  8. Const 分母欄 As String = "$b"
  9. Const 結果欄 As String = "$c"
  10. Dim a%
  11. Dim n1, n2, q1
  12. With ThisWorkbook.ActiveSheet
  13. n1 = Application.Transpose(.Range(分子欄 & "6:" & 分子欄 & .Range("A6").End(xlDown).Row).Value2)
  14. n2 = Application.Transpose(.Range(分母欄 & "6:" & 分母欄 & .Range("B6").End(xlDown).Row).Value2)
  15. q1 = n1
  16. On Error Resume Next

  17.     For a = 1 To UBound(n1)
  18.     q1(a) = n1(a) / n2(a)
  19.         If Err.Number <> 0 Then
  20.         Err.Clear
  21.         q1(a) = 0
  22.         End If
  23.     Next a

  24. .Range(結果欄 & "6:" & 結果欄 & .Range("$a6").End(xlDown).Row).Value2 = Application.Transpose(q1)

  25. End With

  26. Application.Calculation = xlCalculationAutomatic
  27. Application.ScreenUpdating = True

  28. SecondsElapsed = Round(Timer - StartTime, 2)
  29. MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation
  30. End Sub
複製代碼





回復 支持 1 反對 0

使用道具 舉報

發表於 19-11-14 16:12|載入全部圖片 | 顯示全部樓層
on error resume next

20字...20字...20字...20字...20字...
 樓主| 發表於 19-11-15 00:26|載入全部圖片 | 顯示全部樓層
mick 發表於 19-11-14 16:12
on error resume next

20字...20字...20字...20字...20字...

您好~我有查到這個,可是我不太懂要怎麼寫它的語法,大大若方便的話,可否提點一二?
另20字是什麼意思呢?~
不材了,感謝~
發表於 19-11-15 09:35|載入全部圖片 | 顯示全部樓層
本帖最後由 abab47036 於 19-11-15 09:37 編輯
MaverickRay 發表於 19-11-15 00:26
您好~我有查到這個,可是我不太懂要怎麼寫它的語法,大大若方便的話,可否提點一二?
另20字是什麼意思呢 ...

參考一下 https://blog.gtwang.org/programm ... g-error-handling/2/

評分

參與人數 1金錢 +2 收起 理由
MaverickRay + 2 按一個讚

查看全部評分

 樓主| 發表於 19-11-26 23:51|載入全部圖片 | 顯示全部樓層
本帖最後由 MaverickRay 於 19-11-26 23:55 編輯

事情是這樣的,當我將onError Goto...  加入到我的程序中.....===============================
For a = 6 To Range("A6").End(xlDown).Row   '從第六列起,到自A6格往下共有列數(所有公司數)
    '讓程式忽略任何的錯誤
    '=>將 On Error 的處理方式指定為 Resume Next。但執行後會將分母為0時之儲存格以空白填入
     On Error Resume Next
           '將計算結果丟到目標欄
          Cells(a, [目標欄].Column) = Val(Cells(a, [分子欄].Column)) / Val(Cells(a, [分母欄].Column))   
Next a

=======================
問題:
'1、執行完後凡遇分母為0時,則目標欄的儲存格內之值皆為空白,無法幫忙填入值為0
'2、若改用 網頁範例中的On Error GoTo ErrorHandler 、、、、,則會在加入到自己的程式後,出現 ErrorHandler: 這一塊使用者未定義,還沒研究清楚要怎麼撰寫...雖然我有將範例檔照著使用,單獨時很OK,但加入到自己程式後,卻出現未定義sub、、、等,不知是否為我加入到主要Sub()~~END Sub內所致???
'3、由於無法填入0,使得做總體篩選時不方便,所以我只好先棄on error、、、
改用條件式判斷,程序如下:
=======================
For a = 6 To Range("A6").End(xlDown).Row   '從第六列起,到自A6格往下共有列數(所有公司數)
    '讓程式忽略任何的錯誤
    '=>將 On Error Resume Next 改用 if 判斷條件式
    If Val(Cells(a, [分母欄].Column)) = 0 Then
            Cells(a, [目標欄].Column) = 0
            Else

            Cells(a, [目標欄].Column) = Val(Cells(a, [分子欄].Column)) / Val(Cells(a, [分母欄].Column))   
    End If
Next a
=======================
雖然可以正常執行,但有個問題卻開始困擾了我...
原因出在多了個條件式在迴圈內,而公司共計近2萬筆,換言之,這個不論我是用 if  判斷式 或用 on Error Resume Next,因為都卡在迴圈內,這跑完真的消耗費挺多的時間,故想請教大大,我該如何才能優化...
在此先跟大大們道聲感謝  Orz

發表於 19-11-27 16:29|載入全部圖片 | 顯示全部樓層
就以上幾行程式並不會有效能問題 問題應該在它處
發表於 19-12-7 10:03|載入全部圖片 | 顯示全部樓層
先用onerror resume next (在迴圈之前)
然後在計算後,檢查 err.number:

If Err.Number <> 0 Then
Err.Clear '回復exception 狀態

== 將結果填0 ==

另外,可以不用val,直接計算內容就好:

range(分子欄 & a).value2/ range(分母欄 & a).value2

還有,不考慮用格式化條件嗎,也不慢(用vba設定一次就好)
發表於 19-12-7 13:49|載入全部圖片 | 顯示全部樓層
本帖最後由 pierrebox 於 19-12-7 13:51 編輯
pierrebox 發表於 19-12-7 10:03
先用onerror resume next (在迴圈之前)
然後在計算後,檢查 err.number:

Sorry, 寫錯了,前面提到"格式化條件"並非本主題,我想成別的目的,例如依值塗色之類的。
發表於 19-12-7 20:36|載入全部圖片 | 顯示全部樓層
pierrebox 發表於 19-12-7 12:16
如果很在意效能,可以考慮用陣列方式來計算比較快。
我的機器是大約10年前的筆電,只有雙核,以下兩個範例 ...

提升效率非常好用 感謝
您需要登錄後才可以回帖 登錄 | 註冊

本版積分規則

手機版|Archiver|站長信箱|廣告洽詢|COCO研究院  |網站地圖

GMT+8, 19-12-10 05:22

Powered by Discuz! X3.2

© 2001-2013 Comsenz Inc.

快速回復 返回頂部 返回列表
理財討論網站 | 優質玻尿酸隆鼻 專業皮膚科診所 推薦電波拉皮效果 優質淨膚雷射效果 推薦微晶瓷隆鼻 | 徵信社精選| SEO優化|