VBA Error "Bubble Up"

Question

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.

http://bit.ly/k2WJW

(↑ Excel Programming Weekend Crash Course via 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.

Answer 1

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.

Answer 2

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 than the 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

Answer 3

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.

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).

Answer 4

The short answer to your first question is "you shouldn't put 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.

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.

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".

Answer 5

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.

Obviously this is just one scenario.

Answer 6

Hi all,

This "bubbling up" - does it work across Excel projects?

I ask because I can trap errors fine if the On Error statement is in the same project as the error, but the error goes un-handled (causing a fatal) if I called the procedure containing the error from outside of the project using Application.Run relying on an On Error statement in the calling project.

Basically, what I'd like to be able to do is test projects for errors by calling macros in projects B, C...Z one at a time from project A. If they have errors but there's error handling code in projects B, C etc. to trap the errors, then they pass the test, and my project A won't crash. I then log the passes somewhere else.

But, if there are errors in projects B, C etc. that go un-handled then I want project A to handle this at the first error, as project A is at the top of the call stack. Project A would then log the fail for any project that didn't handle its own errors.

This all works so far except for the fact that project A's On Error statement misses the errors in project B, C, etc. It would be a pain to have to import the modules from each project into project A to make them more "local", which I'm sure is not the answer.

Thanks a lot!