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

MaverickRay

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

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

pierrebox

如果很在意效能，可以考慮用陣列方式來計算比較快。
我的機器是大約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. 
   
8. Const 分子欄 As String = "$a"
   
9. Const 分母欄 As String = "$b"
   
10. Const 結果欄 As String = "$c"
    
11. Dim a%
    
12. On Error Resume Next
    
13. With ThisWorkbook.ActiveSheet
    
14.     For a = 6 To .Range("A6").End(xlDown).Row
    
15.     .Range(結果欄 & a).Value2 = .Range(分子欄 & a).Value2 / .Range(分母欄 & a).Value2
    
16.         If Err.Number <> 0 Then
    
17.         Err.Clear
    
18.         .Range(結果欄 & a).Value2 = 0
    
19.         End If
    
20.     Next a
    
21. End With
    
22. Application.Calculation = xlCalculationAutomatic
    
23. Application.ScreenUpdating = True
    
24. 
    
25. SecondsElapsed = Round(Timer - StartTime, 2)
    
26. MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation
    
27. 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. 
   
8. Const 分子欄 As String = "$a"
   
9. Const 分母欄 As String = "$b"
   
10. Const 結果欄 As String = "$c"
    
11. Dim a%
    
12. Dim n1, n2, q1
    
13. With ThisWorkbook.ActiveSheet
    
14. n1 = Application.Transpose(.Range(分子欄 & "6:" & 分子欄 & .Range("A6").End(xlDown).Row).Value2)
    
15. n2 = Application.Transpose(.Range(分母欄 & "6:" & 分母欄 & .Range("B6").End(xlDown).Row).Value2)
    
16. q1 = n1
    
17. On Error Resume Next
    
18. 
    
19.     For a = 1 To UBound(n1)
    
20.     q1(a) = n1(a) / n2(a)
    
21.         If Err.Number <> 0 Then
    
22.         Err.Clear
    
23.         q1(a) = 0
    
24.         End If
    
25.     Next a
    
26. 
    
27. .Range(結果欄 & "6:" & 結果欄 & .Range("$a6").End(xlDown).Row).Value2 = Application.Transpose(q1)
    
28. 
    
29. End With
    
30. 
    
31. Application.Calculation = xlCalculationAutomatic
    
32. Application.ScreenUpdating = True
    
33. 
    
34. SecondsElapsed = Round(Timer - StartTime, 2)
    
35. MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation
    
36. End Sub

複製代碼

mick

on error resume next

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

MaverickRay

mick 發表於 19-11-14 16:12
on error resume next

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

您好~我有查到這個，可是我不太懂要怎麼寫它的語法，大大若方便的話，可否提點一二？
另20字是什麼意思呢?~
不材了，感謝~

abab47036

MaverickRay 發表於 19-11-15 00:26
您好~我有查到這個，可是我不太懂要怎麼寫它的語法，大大若方便的話，可否提點一二？
另20字是什麼意思呢 ...

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

MaverickRay

abab47036 發表於 19-11-15 09:35
參考一下 https://blog.gtwang.org/programming/excel-vba-debug-error-handling/2/

事情是這樣的，當我將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

abab47036

就以上幾行程式並不會有效能問題 問題應該在它處

pierrebox

先用onerror resume next (在迴圈之前)
然後在計算後，檢查 err.number:

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

== 將結果填0 ==

另外，可以不用val，直接計算內容就好：

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

還有，不考慮用格式化條件嗎，也不慢（用vba設定一次就好）

pierrebox

pierrebox 發表於 19-12-7 10:03
先用onerror resume next (在迴圈之前)
然後在計算後，檢查 err.number:

Sorry, 寫錯了，前面提到"格式化條件"並非本主題，我想成別的目的，例如依值塗色之類的。

abopt

pierrebox 發表於 19-12-7 12:16
如果很在意效能，可以考慮用陣列方式來計算比較快。
我的機器是大約10年前的筆電，只有雙核，以下兩個範例 ...

提升效率非常好用 感謝