Error Handling

Why "Add an Error Handler to Every Routine" is Bad Advice

For every complex problem, there is an answer that is clear, simple, and wrong. (Shocking, I know.)

Mike Wolfe

Nov 10, 2021 • 6 min read

Many VBA sites on the internet tell you to add error handling to every routine.

There are even utilities (like MZTools) that allow you to automate adding error handlers to all your routines. These boilerplate error handlers often look something like this:

Sub MyRoutine()
    On Error Goto MyErrorHandler
    
    'Some code

    Exit Sub
MyErrorHandler:
    MsgBox Err.Description
End Sub

Unhandled Errors are Bad

The advice to add error handlers to every routine comes with the best of intentions.

After all, you do not want to have unhandled errors in your code. An unhandled error is one that results in an ugly message box like the following:

If your Access application is running in Runtime Mode (PRO TIP: all end users should be running your application in Runtime Mode), then the consequences of an unhandled runtime error are even more severe. In that situation, the user is booted unceremoniously from the application with an utterly useless error message:

The same unhandled "Division by zero" error from above when executed in runtime mode.

One-Size-Fits-All Solutions are Bad, Too

You can avoid the above situations by adding an error handler to every routine.

For example, if we added the basic error handler from the beginning of this article to the error we generated above, we get this much nicer looking message box:

This is a brute-force solution to a problem that deserves more nuance, though.

Sometimes, we don't want to handle errors within the routine where they get raised.

Understanding Call Stack Error Bubbling

So, what happens if you don't handle an error within the routine where it got raised?

The unhandled error bubbles up the call stack until it finds an error handler.

Here's an example that I adapted from my #Under100 article, What is a Call Stack?

We start by calling the AskMike routine in the immediate window. AskMike calls AskChloe which calls AskMia which calls AskCooper which calls AskLuke. The error is generated in the AskLuke routine.

Since there is no error handler in AskLuke, the error bubbles up the call stack to AskCooper.

Since there is no error handler in AskCooper, the error continues to bubble up the call stack to AskMia.

Since there is no error handler in AskMia, the error continues to bubble up the call stack to AskChloe.

The AskChloe routine has an error handler (On Error Goto Handle_Err). The code jumps to the Handle_Err line label in accordance with the error handler. Since the error is now handled, it stops bubbling up the call stack. The error never reaches the AskMike routine because the AskChloe routine handles it first.

Why This Matters

Let's consider an example of how handling errors in every routine can lead to bad outcomes.

In the example below, we have a routine named SetAmtDue. This routine calls a function, CalcTotalDue, that applies an adjustment percentage (a penalty, a coupon, etc.) to a base amount. The routine takes the function's return value and then runs an update query to set the Invoice.AmtDue field to that returned value.

I tried to keep this code as straightforward as possible to illustrate the concept. Notice how both routines have error handlers. This code appears to follow best practices.

But watch what happens when we introduce a bug.

Let's say that the SetAmtDue routine gets called with a Null value as the AdjPct argument. When the AdjPct argument gets passed through to the CalcTotalDue routine, it raises an "Invalid use of Null" error (a very common error in Access).

The error is "handled," but in a very simplistic way. Since CalcTotalDue never explicitly assigns a return value (due to the error), it returns the default value of the Currency type: 0.

As a result of this chain of events, the UPDATE SQL statement is going to set the AmtDue column to zero for this Invoice. This is a worse outcome than if the error was completely unhandled.

By "handling" the error in the CalcTotalDue function, we have effectively turned a runtime error into a logic error.

That's a Very Bad Thing™, because logic errors are the most expensive errors to fix:

Syntax errors
Compile errors
Misunderstood requirements (before you start writing code)
Automated test errors (i.e., failing tests)
Runtime errors
Misunderstood requirements (after you've written the code)
Logic errors

What's a Programmer To Do?

Unfortunately, there is no safe one-size-fits-all approach.

One of my mentor's (Bruce Grandjean) favorite quotes was this gem from H.L. Mencken:

"For every complex problem there is an answer that is clear, simple, and wrong."

The key, then, is to understand the logic of how errors bubble up through the call stack until they encounter an error handler. Armed with this knowledge, you can construct an error handling approach that fits the situation at hand.