VBA 错误“冒泡”

VBA Error "Bubble Up"

提问by jtolle

I haven't read much about it, but the author at the link below recommends that I don't use "bubble up" to centralize error handling in VBA.

我对它的了解不多，但下面链接中的作者建议我不要使用“冒泡”来集中 VBA 中的错误处理。

Excel Programming Weekend Crash Course via Google Books

Excel 编程周末速成课程通过 Google Books

But I'm not sure why he recommends that, and he doesn't really explain.

但我不确定他为什么推荐那个，他也没有真正解释。

Can someone tell me why I should put error handling in EVERY procedure instead of using "bubble up"? Or at least, do you know why the author says not to?

有人能告诉我为什么我应该在每个过程中进行错误处理而不是使用“冒泡”吗？或者至少，你知道作者为什么说不要吗？

Thanks.

谢谢。

采纳答案by quamrana

I'm not sure what the default error handling of VBA is, but since its Visual Basic for Applications, and those applications include things like excel and word, I assume just a dialog box will appear which will not be helpful to the user.

我不确定 VBA 的默认错误处理是什么，但由于它的 Visual Basic for Applications，并且这些应用程序包括 excel 和 word 之类的东西，我假设只会出现一个对用户没有帮助的对话框。

I assume that the author has been bitten by code not handling errors so he now recommends all procedures to handle errors.

我假设作者被代码不处理错误所困扰，所以他现在推荐所有程序来处理错误。

The full answer is that you have to be aware of every error that can occur and to have code in place to handle it, whether it is as low as possible (where you may not know what to do), or as high as possible (which means less effort writing error handling code, but not knowing why the error occurred), or strategically (which is just in the right places where you should be able to recover from most common errors) or just everywhere (which may be just too much development effort).

完整的答案是，您必须意识到可能发生的每个错误并有适当的代码来处理它，无论是尽可能低（您可能不知道该怎么做），还是尽可能高（这意味着更少的编写错误处理代码的工作量，但不知道为什么会发生错误），或者战略性地（这只是在您应该能够从最常见错误中恢复的正确位置）或只是无处不在（这可能太多了）开发努力）。

回答by jtolle

The short answer to your first question is "you shouldn'tput an error handler in every procedure".

对您的第一个问题的简短回答是“您不应该在每个过程中都放置一个错误处理程序”。

To say that "every procedure must have an error handler" is in general terrible advice. The flaws with VBA error handling have been much discussed elsewhere. Conceptually, though, it's not all that different from the more standard form of exception handling found in other languages. Most of the best practices from those languages apply. You should handle errors at the lowest level where handling them makes sense. Sometimes this is in the procedure where the error occured, many times not.

说“每个过程都必须有一个错误处理程序”通常是糟糕的建议。VBA 错误处理的缺陷已在别处进行了大量讨论。不过，从概念上讲，它与其他语言中更标准的异常处理形式并没有什么不同。这些语言中的大多数最佳实践都适用。您应该在处理它们有意义的最低级别处理错误。有时这是在发生错误的程序中，很多时候不是。

For example, a VBA UDF called from your worksheet should certainly have an EH that makes sure you return an Excel error value to the calling cell(s) instead of dropping your user into the code editor with an error message. The code you call from that UDF, though, might or might not need any. In fact, often the most meaningful thing an internal routine can do when an error occurs is just let it pass on up the stack so it can reach code that knows what to do with it. It really depends on the routine.

例如，从您的工作表调用的 VBA UDF 肯定应该有一个 EH，以确保您将 Excel 错误值返回到调用单元格，而不是将您的用户放入带有错误消息的代码编辑器中。但是，您从该 UDF 调用的代码可能需要也可能不需要。事实上，当发生错误时，内部例程可以做的最有意义的事情通常是让它向上传递堆栈，以便它可以到达知道如何处理它的代码。这真的取决于常规。

The answer to your second question is that the author doesn't seem to understand exception handling very well. He admits that error handling is context specific, but then seems to suggest that every procedure should locally decide between "correct the problem right here and resume execution" and "terminate the program". He leaves out the usually correct option, which is "clean up locally and kick the problem upstairs". So routines with no need to clean up locally should just let errors "bubble up".

你的第二个问题的答案是作者似乎不太了解异常处理。他承认错误处理是特定于上下文的，但随后似乎建议每个程序都应该在本地决定“在此处纠正问题并恢复执行”和“终止程序”。他遗漏了通常正确的选项，即“在本地清理并将问题踢到楼上”。所以不需要在本地清理的例程应该让错误“冒泡”。

回答by Oorang

My 2 cents: You should put error handlers on all Public Procedures and Events. This means that the procedure at the bottom of the call stack will always have an error handler. Then add error handlers in your other procedures as it makes sense. If an error occurs in a procedure that does not have an error handler, it will "bubble up" to the top level error handler where it be logged/displayed in a professional fashion. A scenario where you might want to add an error handler to a private (lower level) procedure is this: The code needs to be fast. You have a rare condition that can be avoided, but will force you to perform an expensive logical test inside of a loop (or worse a nested loop). You might perform the logical test in the error handler, and if it's said "rare occurrence" make the correction and resume. As the condition is rare, you will see performance gains for most conditions. If the error handler can't figure out and correct the problem then re-raise the error to bubble it on up the stack.

我的 2 美分：您应该在所有公共过程和事件上放置错误处理程序。这意味着调用堆栈底部的过程将始终具有错误处理程序。然后在其他过程中添加错误处理程序，因为它是有意义的。如果在没有错误处理程序的过程中发生错误，它将“冒泡”到顶级错误处理程序，在那里以专业的方式记录/显示。您可能希望向私有（较低级别）过程添加错误处理程序的场景是：代码需要快速。您有一种可以避免的罕见情况，但会迫使您在循环（或更糟的嵌套循环）内执行昂贵的逻辑测试。您可以在错误处理程序中执行逻辑测试，如果说“罕见”，则进行更正并恢复。由于这种情况很少见，您将看到大多数情况下的性能提升。如果错误处理程序无法找出并纠正问题，则重新引发错误以将其冒泡到堆栈中。

Obviously this is just one scenario.

显然这只是一种情况。

回答by Patrick Honorez

I see at least one reason in his explanation: because doing so deprives you from the benefit of Resume (next).
Plus you won't know in which module the error happened.

我在他的解释中至少看到了一个原因：因为这样做会使您无法享受 Resume（下一页）的好处。
另外，您不会知道错误发生在哪个模块中。

回答by shahkalpesh

It is better not to use "bubble-up" part of error handling because errors should be handled & if it is known as to what to do, if such an error occurs - is better known to procedure as to what to do thanthe calling procedure.

最好不要使用错误处理的“冒泡”部分，因为应该处理错误，如果知道该怎么做，如果发生这样的错误 - 程序比调用更了解要做什么程序。

Sub test()
  On Error GoTo e
  Dim c As Integer
  Dim d As Integer
  c = add(5, 0)
  d = divideWhichManagedItsOwnErrorHandling(5, 0)
  d = divide(5, 0)

  Exit Sub

e:
  MsgBox "error occurred somewhere for which I don't know what to do: " + Err.Description
End Sub

Function add(a As Integer, b As Integer) As Integer
   add = a + b
End Function

Function divide(a As Integer, b As Integer) As Integer
   divide = a / b 'if error occurs, it will "bubble-up" to the caller.
End Function

Function divideWhichManagedItsOwnErrorHandling(a As Integer, b As Integer) As Integer
  On Error Resume Next
  Dim result As Integer
  result = a / b
  If Err.Number = 11 Then 'if divide by zero occurred, user must have passed 0 for b
    result = 0 ' return 0 if the divide by zero occurs. 
  End If
  divideWhichManagedItsOwnErrorHandling = result
End Function