Bubbly Run-Time Errors

300 feet below the surface, in a sunken wreck from another age, a rotting wooden deck silently collapses under the weight of heavy cast iron canons. As the sea floor becomes a thick cloud of millennial dust, the weaponry cracks a cask of over-aged priceless wine, and a tiny amount of air, trapped centuries ago, is freed. Under the tremendous, crushing pressure of the oceanic bottom, the bubbles are minuscule at first. As the ancestral oxygen makes its final journey from the bottom of the ocean up to the surface, the bubbles grow in size with the decreasing pressure – and when it finally reaches its destination to blend with the contemporary atmosphere, it erupts with a bubbly “plop” as it releases itself from the water that held it quietly imprisoned all these years.

Uh, so how does this relate to code in any way?

Bubbles want to explode: the same applies to most run-time errors.

When an error is raised 300 feet down the call stack, it bubbles up to its caller, then to the caller of that caller, and so on until it reaches the entry point – the surface – and blows everything up. When the error is unhandled at least.

And so they told you to handle errors. That every procedure must have an event handler.

Truth is, this is utter cargo-cultist BS. Not every procedure must handle every error. Say you have an object that’s responsible for setting up an ADODB Connection, parameterizing some SQL Command on the fly, and returning a Recordset. You could handle all errors inside that class, trap all the bubbles, and return Nothing instead of a result when something goes wrong. Neat huh? Yeah. Until the caller wants to know why their code isn’t working. That SqlCommand class cannot handle everything: errors need to bubble up to the calling code, for the calling code to handle.

The calling code might be another class module, with a function responsible for – I don’t know – pulling a list of products from a database and returning an array of strings that this function’s own caller uses to populate a ComboBox control, in a UserForm’s Initialize handler. So the data service class lets SqlCommand errors bubble up to its own caller; the UserForm’s Initialize handler receives the error, understands that it won’t be able to populate its ComboBox, and in response decides to go up in flames by bubbling up the error to its own caller – some parameterless procedure in a Macros module, that was called when the user clicked a nicely formatted shape on a dedicated worksheet.

That’s the entry pointThat is where the bubbling stops. That procedure was responsible for bringing up a form for the user to enter some data, but something happened (the detailed information is in the Err object) and we can’t do that now – so we abort the form and display a nice user-friendly message in a MsgBox instead, and we can even send the actual error details into a new Outlook email to helpdesk@contoso.com.

Getting a grip on the handle

Most errors aren’t handled where they’re raised. Well, some are, obviously. But to say that every procedure should have its error handler is just as blatantly wrong as saying no procedure should ever have any error handler: “only a Sith deals in absolutes”.

So which errors should be killed on-the-spot, and which errors should be allowed to bubble up?

Avoidable errors

The vast majority of run-time errors occur due to lack of proper input validation code: we take a value and assume it’s of a type we’re expecting, or at least one we can work with. We assume its format, we assume its location, we assume …lots of things. The more assumptions code makes, the more error-prone it is. Problem is, we don’t always realize all the assumptions we make – and that’s when run-time errors come and bite us. These are completely avoidable errors: they shouldn’t be handled at all, for they are bugs. And we want bugs to blow things up. So if you have code making assumptions – for example a row number is never going to be zero – then you have bugs that are easy to fix (and that a good unit test coverage should prevent, BTW)… and it boils down, mostly, to proper input validation. Avoiding avoidable errors is the #1 best bug-preventing thing you can do.

Of course this supposes the assumptions we make are conscious ones – sometimes, code makes assumptions we don’t realize we’re making. For example, VBA code that implicitly refers to the active workshseet, often assumes that the active sheet is one specific sheet:

foo = Sheet1.Range(Cells(i, j), Cells(i, j)).Value

The above code assumes Sheet1 is active, because the two unqualified Cells calls implicitly refer to the active worksheet. Avoidable. If foo is declared as a String and Sheet1 is active, that same code will still blow up if the cell contains a #VALUE! error. Assumptions are very easy to make! Fortunately they’re also easy to avoid.

Errors you know how to handle

Sometimes you’ll run code that can raise an error even if you’ve validated all inputs – if the SQL server is down, trying to connect to it will blow up your code if you don’t handle that situation. Or the user might not be authorized to run the SQL command, or whatever. The decision on whether to handle in on-the-spot or bubbling it up to the caller, depends on how well you’ve split the responsibilities among your modules and procedures: a utility function usually has no business handling/swallowing its own errors. And unless you’re running the current [not yet released] 2.0.14.x Rubberduck build, your unit tests can’t mock up /fake a MsgBox call, so you have code paths that cannot be cleanly tested.

Looking at it from the client code’s perspective is how you’re going to know what kind of errors and “bad result” outputs you want to be dealing with. And if that client code is a unit test, then you’re probably doing the right thing, whatever that is.

Other times you’ll run into an error, but you know you can simply, gracefully and usefully recover from that error, and resume normal execution – these errors, if they can’t be avoided, should be the kind to handle on-the-spot.

Everything else

For everything else, you’ll want bubbles. Not all the way up though – you’ll want to catch them before they surface and pop in the user’s face! But if your code validates all inputs and makes little or no assumptions, and handles the specific errors you know could happen because roses are red and violets are blue… at the top of every call stack there should be a catch-all handler – an ultimate bubble catcher, that gracefully handles everything other code had to let through.


Rubberduck is never going to tell you to sprinkle error-handling code everywhere. But I think we could have an inspection that warns you if you have a [possible] entry point that lets run-time errors bubble up unhandled.

What do you think? What else can Rubberduck do for you? Should Rubberduck treat any object-returning method as potentially returning Nothing, and suggest that you validate the method’s return value? You would right-click any Range.Find call, and if the returned reference is never compared against Nothing then Rubberduck could introduce an If block that does just that, making the rest of the code path safe to execute in the case of a failing call. Just thinking out loud here…




7 thoughts on “Bubbly Run-Time Errors”

  1. Thanks for the helpful advice with errorhandling. Sounds great to have an inspection and fix for object returning methods not validated for a Nothing object.


  2. Thanks for the article. I see your point, but the counter-argument I would make is this:
    1) VBA does not tell you what module & line caused an error. Error handling everywhere gives you finer granularity, and narrows down the amount of code you have to inspect.
    2) The goal of almost all error handlers should be to: a) Package the error, b) collect the call chain in a string, and c) re-raise the error up to the caller
    3) The remainder of the error handlers would be ‘originating callers’: routines with nothing above them to raise an error to. Examples: a) Event handlers, b) Code run by Access Macros, c) Utility code called from the Immediate window, and d) Any place where an unhandled error could terminate the program
    4) Originating callers should: a) catch the error, b) display and log it, and c) take appropriate clean-up actions
    5) I do use On Error Resume Next in certain situations: a) one-liner functions where a default can be returned when accessing the real data throws an error, and b) cases where VBA’s ‘error-happy’ nature throws errors when you don’t want them
    6) There are good reasons to ignore errors. Example: an Access Report canceling on NoData will throw a (typically unwanted) error. Calling code should just swallow this specific error, as propagating it serves no purpose
    So, that is my case for putting _some kind_ of error handing in every routine. Comments?


    1. Hey Ken, thanks for commenting; the main point is “it’s all about nuances” – do things because you have a reason to do them, not because “that’s how it’s done”.

      You seem to be using OERN the right way; make sure you reset handling with On Error GoTo 0 and you’ll do great! As for VBA not telling you much about where an error came from – that alone is a good reason to make sure you populate the Source parameter whenever you raise an error, or rethrow an error that doesn’t have the Source parameter populated.

      What you call “originating callers” is what I dubbed “entry points” in my post, and I agree: it’s that level of abstraction that should be responsible for catching and reporting anything that wasn’t handled in lower-abstraction code.

      Thing is, if you validate your inputs you’ll find that the vast majority of errors thrown are your own custom ones, which do include a clear source and, often, even an enum value to identify the error code.


  3. Ken hit the nail on the head. The one clarification I would add is that catching an error & re-throwing (raising) it just to provide the stack trace I do not really consider error “handling”. At least, not in the sense of this article. Catch & re-throw in VB6 is just a workaround for a missing language feature. Equivalent code we’ve ported to C# requires no error “handling” in any form in most of the same places.

    Liked by 1 person

  4. I highly recommend vbWatchdog as an alternative to traditional VBA error handling. I’ve implemented automated error-logging to my bug tracking database. I use FogBugz, but the concept applies to other systems that accept email or web service based bug reporting. I get full stack traces with access to all local variables, line numbers and the actual erroring code (except for compiled Access projects, which provides line num but not code).
    All this without requiring *ANY* boilerplate code in individual routines. While I still wish VBA had better native support for structured error handling, I actually think that vbWatchdog allows me to provide better error reporting than I can get with .NET. It’s not free, but it’s by far the best value add-on for VBA (yes, present company included 😉 … it’s that good).
    * NOTE: I have no financial interest in vbWatchdog. Just a very satisfied user.


    1. Yep, I knew about vbWatchDog. Ot actually hides its boilerplate…. in a way that currently breaks our parser (known issue, known solution – just need the time to work on it!). It’s indeed a fabulous tool (never tried it though), be it just for its ability to tap into the VBA runtime and locate the darn call stacks, and expose them for programmatic access. There’s some serious black magic hackery involved there!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s