在 VBA 中使用 IFERROR

Using IFERROR in VBA

提问by LynseyAbbott

I hope someone can help me please? I've done a lot of googling and can't figure out what the issue is. I only dabble in vba, so I'm certainly no expert...

我希望有人可以帮助我好吗？我已经做了很多谷歌搜索，无法弄清楚问题是什么。我只涉足 vba，所以我当然不是专家......

I am trying to automate some calculations in a huge spreadsheet at work, and I think I'm probably missing something really silly. Basically, one of the calculations is a simple divide of one cell by another cell. When it hits an error, I want it to return a 0. Here is the code that keeps tripping over:

我正在尝试在工作时在一个巨大的电子表格中自动执行一些计算，我想我可能错过了一些非常愚蠢的东西。基本上，其中一种计算是将一个单元格除以另一个单元格。当它遇到错误时，我希望它返回 0。这是不断绊倒的代码：

Sheets("Bridge").Range("W" & SumIfInt) = Application.WorksheetFunction.IfError(Sheets("Bridge").Range("AA" & SumIfInt) / Sheets("Bridge").Range("D" & SumIfInt), 0)

I get a Run-time error 6 Overflow

我收到运行时错误 6 溢出

Thanks in advance

提前致谢

回答by Absinthe

When programming it's always best to avoid an error by checking for it's possibility first, rather than triggering one then dealing with it. Check if the cell is empty or zero before performing the calculation:

在编程时，最好先检查错误的可能性来避免错误，而不是先触发错误，然后再处理错误。在执行计算之前检查单元格是否为空或为零：

If IsEmpty(Sheets("Bridge").Range("D" & SumIfInt)) Or Sheets("Bridge").Range("D" & SumIfInt) = 0 Then
    Sheets("Bridge").Range("W" & SumIfInt) = 0
Else
    Sheets("Bridge").Range("W" & SumIfInt) = Sheets("Bridge").Range("AA" & SumIfInt) / Sheets("Bridge").Range("D" & SumIfInt)
End If

On Error Resume Next basically says "If you encounter an error just ignore it". This can cause all sorts of unexpected problems and should only be used as a last resort.

On Error Resume Next 基本上是说“如果您遇到错误，请忽略它”。这可能会导致各种意外问题，只能作为最后的手段。

Also check out the IsErrorfunction in VBA.

另请查看VBA 中的IsError函数。

回答by Middle

Try this without If error function. As mentioned below, you should use On error resume next very carefully.

在没有 If 错误函数的情况下试试这个。如下所述，您应该非常小心地使用 On error resume next。

Warning: On error resume nextwill skip ALL cases of error for rest of the code unless On error goto 0is present. Also, if you would like to catch a specific error, you can use if error = 'number' thento handle them accordingly. Application.worksheetfunction.iserror is better used in excel, than in excel vba.

警告：On error resume next除非On error goto 0存在，否则将跳过其余代码的所有错误情况。此外，如果您想捕获特定错误，您可以使用相应if error = 'number' then的处理它们。Application.worksheetfunction.iserror 在excel中使用比在excel vba中更好。

    On error resume next
    Sheets("Bridge").Range("W" & SumIfInt) = Sheets("Bridge").Range("AA" & 
    SumIfInt) / Sheets("Bridge").Range("D" & SumIfInt)

    if err <>0 then
       Sheets("Bridge").Range("W" & SumIfInt) = 0
    end if
    On error goto 0

回答by Mathieu Guindon

What is the declared type of SumIfInt? If that's Integer, its maximum legal value is 32,767, because Integeris a 16-bit type - and using a 16-bit signed integer type to represent row numbers in an Excel worksheet is a good way to achieve run-time error 6 "Overflow".

的声明类型是SumIfInt什么？如果是Integer，则其最大合法值为 32,767，因为Integer是 16 位类型 - 并且使用 16 位有符号整数类型来表示 Excel 工作表中的行号是实现运行时错误 6“溢出”的好方法。

Use a 32-bit integer type instead:

改用 32 位整数类型：

Dim SumIfInt As Long

If Sheets("Bridge").Range("D" & SumIfInt)is a very small value, it's possible that using it as a denominator makes the result overflow an Integer, or possibly even a Long- in that case you should probably use a Doublefloating-point type instead.

IfSheets("Bridge").Range("D" & SumIfInt)是一个非常小的值，使用它作为分母可能会使结果溢出 an Integer，甚至可能是 a Long- 在这种情况下，您可能应该改用Double浮点类型。

When it hits an error, I want it to return a 0.

当它遇到错误时，我希望它返回 0。

Better code wouldn't hit an error at all. The only error you shouldbe worried about here, is "Division by Zero" - actually also "Type Mismatch" if any of the involved cells may contain an error value - in any case, shoving the error under the carpet with On Error Resume Nextisn't showing you anything about how to avoid that situation in the future.

更好的代码根本不会出错。您应该在这里担心的唯一错误是“除以零” - 实际上也是“类型不匹配”，如果任何涉及的单元格可能包含错误值 - 在任何情况下，将错误推到地毯下并On Error Resume Next没有显示你任何关于如何在未来避免这种情况的信息。

The best error-handling code is code that avoidsraising avoidable errors in the first place.

最好的错误处理代码是首先避免引发可避免错误的代码。

Dim divisorValue As Variant 
divisorValue = Sheets("Bridge").Range("D" & SumIfInt)

If IsError(divisorValue) Or IsEmpty(divisorValue) Then
    ' return 0 and bail out
    Exit Function
End If

Dim divisor As Double
divisor = CDbl(divisorValue)

Now use the known-to-be-ok divisorvalue instead.

现在改为使用 known-to-be-okdivisor值。

A note about your object management: you're dereferencing the exact same object reference multiple times - that's inefficient.

关于您的对象管理的说明：您多次取消引用完全相同的对象引用 - 这是低效的。

If Sheets("Bridge")exists at compile-time in ThisWorkbook(the workbook that's running the code), then you neverneed to dereference it that way - VBA creates a global-scope object variable for every single worksheet in the workbook - select it in the VBE's Project Explorer(Ctrl+R), bring up its Properties(F4), then set its (Name)to something meaningful, e.g. BridgeSheet. Then you can do this:

如果Sheets("Bridge")在编译时存在ThisWorkbook（运行代码的工作簿），那么您永远不需要以这种方式取消引用它 - VBA 为工作簿中的每个工作表创建一个全局范围的对象变量 - 在 VBE 的项目资源管理器中选择它（ Ctrl+R)，调出它的属性(F4)，然后将其设置(Name)为有意义的东西，例如BridgeSheet. 然后你可以这样做：

BridgeSheet.Range("D" & SumIfInt)

Another tip: the more you cram into a single instruction, the harder debugging becomes, because the more possible failing points there are.

另一个提示：你塞进一条指令的次数越多，调试就越困难，因为可能的失败点越多。

Consider pulling the invidividual values involved into their own variables, then validating them, thenperforming the division once you knowdoing that isn't going to blow up.

考虑将涉及的单个值提取到它们自己的变量中，然后验证它们，然后在您知道这样做不会爆炸时执行除法。

回答by Darren Bartrup-Cook

Try to avoid creating errors in the first place - On Errorshould generally be used to handle unexpected errors.
The reasoning behind this is that if a genuine error occurred after the On Error Resume Nextit would be ignored and your sum would be 0.
E.g.If someone wrote Five hundredinstead of a 500you'll just get a 0 (far fetched, but ... actually, not far fetched - never under estimate a sufficiently talented idiot).

首先尽量避免产生错误——On Error通常应该用于处理意外错误。
这背后的原因是，如果以后出现了真正的错误On Error Resume Next会被忽略，你的总和将是0。
例如如果有人写了五百年，而不是的500，你会只得到0（牵强，但实际上... ，并不牵强——永远不要低估一个足够有才华的白痴）。

Anyway....

反正....

If Sheets("Bridge").Range("D" & SumIfInt)is zero or empty you'll get a division by 0 error.
Providing everything else is correct (no text, all other numbers present) then this is the only figure you need to check:

如果Sheets("Bridge").Range("D" & SumIfInt)为零或为空，您将得到除以 0 的错误。
如果其他所有内容都正确（没有文本，所有其他数字都存在），那么这是您需要检查的唯一数字：

Sub Test()

    Dim SumIfInt As Long
    SumIfInt = 2

    With ThisWorkbook.Sheets("Bridge")
        If .Range("D" & SumIfInt) = 0 Then
            .Range("W" & SumIfInt) = 0
        Else
            .Range("W" & SumIfInt) = .Range("AA" & SumIfInt) / .Range("D" & SumIfInt)
        End If
    End With

End Sub