論壇 › 程式交易討論區 › 請教VBA裡遇0/0時程式中止的解決方法

MaverickRay 發表於 19-11-14 06:25

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

本帖最後由 MaverickRay 於 19-11-14 06:27 編輯

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

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

pierrebox 發表於 19-12-7 12:16

本帖最後由 pierrebox 於 19-12-7 13:24 編輯

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

Const 分子欄 As String = "$a"
Const 分母欄 As String = "$b"
Const 結果欄 As String = "$c"
Dim a%
On Error Resume Next
With ThisWorkbook.ActiveSheet
    For a = 6 To .Range("A6").End(xlDown).Row
    .Range(結果欄 & a).Value2 = .Range(分子欄 & a).Value2 / .Range(分母欄 & a).Value2
      If Err.Number <> 0 Then
      Err.Clear
      .Range(結果欄 & a).Value2 = 0
      End If
    Next a
End With
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

SecondsElapsed = Round(Timer - StartTime, 2)
MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation
End Sub



'========= 範例 2，使用陣列 ===========Sub testCOCO_array()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Dim StartTime As Double
Dim SecondsElapsed As Double
StartTime = Timer

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

    For a = 1 To UBound(n1)
    q1(a) = n1(a) / n2(a)
      If Err.Number <> 0 Then
      Err.Clear
      q1(a) = 0
      End If
    Next a

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

End With

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

SecondsElapsed = Round(Timer - StartTime, 2)
MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation
End Sub

mick 發表於 19-11-14 16:12

on error resume next

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

MaverickRay 發表於 19-11-15 00:26

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

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

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

abab47036 發表於 19-11-15 09:35

本帖最後由 abab47036 於 19-11-15 09:37 編輯

MaverickRay 發表於 19-11-15 00:26
您好~我有查到這個，可是我不太懂要怎麼寫它的語法，大大若方便的話，可否提點一二？
另20字是什麼意思呢 ...
參考一下 https://blog.gtwang.org/programming/excel-vba-debug-error-handling/2/

MaverickRay 發表於 19-11-26 23:51

本帖最後由 MaverickRay 於 19-11-26 23:55 編輯

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 發表於 19-11-27 16:29

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

pierrebox 發表於 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設定一次就好）

pierrebox 發表於 19-12-7 13:49

本帖最後由 pierrebox 於 19-12-7 13:51 編輯

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


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

abopt 發表於 19-12-7 20:36

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

提升效率非常好用 感謝{:4_113:}

查看完整版本: 請教VBA裡遇0/0時程式中止的解決方法