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

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

The short answer to your first question is "you shouldn't put an error handler in EVERY procedure". But usually some routines do need them!

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 occurred, many times not.

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 and how it fits with the rest of the program.

Consider these examples:

A handler in a calling procedure will handle all errors raised by the routines it calls. So if a particular routine doesn't need any cleanup, then don't put any error-handler code there:

Sub Caller()
          On Error GoTo HANDLER
          ChildProc
          On Error GoTo 0
          
          Exit Sub
HANDLER:
          Debug.Print Error, "Parent cleanup - something happened in either this procedure or a procedure that it called"
End Sub


Sub ChildProc()
          Debug.Print 10 / 0  ' causes error

          'Don't bother handling errors here since there's nothing this routine can do about them
End Sub

On the other hand, you may need cleanup tasks, in which case you need an error handler.

Sub Caller()
          On Error GoTo HANDLER
          ChildProc
          On Error GoTo 0
          
          Exit Sub
HANDLER:
          Debug.Print Error, "Parent cleanup"
End Sub


Sub ChildProc()
          
          'Pretend this routine gets ahold of some resource that must be cleaned up when it's done
          call get_resources()

          On Error GoTo HANDLER
          Debug.Print 10 / 0  ' causes error
          On Error GoTo 0
          
          'Clean up once we're done
          call release_resources()
          
          Exit Sub
HANDLER:
          Debug.Print Error, "Child cleanup"
          
          'Clean up in case of an error
          call release_resources()

          'Raise another error if necessary to let callers know something went wrong
          Err.Raise 10000, "ChildProc", Error
End Sub

The above examples are just meant to illustrate the point about why you might need or not need an error handler in a given routine. So it's worth noting that in real code the "resource" example is usually handled better with an RAII technique where the error handling is encapsulated with the resource acquisition and release - see https://stackoverflow.com/a/3792280/58845 for a VBA example. And things like whether to re-raise a caught error are also situation-dependent. Sometimes it's possible to handle the error entirely locally and then there is no need to tell callers that anything went wrong.

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