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

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:

  1. Syntax errors
  2. Compile errors
  3. Misunderstood requirements (before you start writing code)
  4. Automated test errors (i.e., failing tests)
  5. Runtime errors
  6. Misunderstood requirements (after you've written the code)
  7. 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.


Further reading

Referenced articles

Runtime Mode in Microsoft Access
There are two ways to force a full version of Access to run a front-end file in runtime mode: the /runtime switch and changing the extension to .accdr.
What is a Call Stack?
The concept of a call stack explained in under 100 words. #Under100
Runtime Errors
It’s impossible to predict every possible scenario that could befall our applications in the wild. But we need to at least try.
Logic Errors
The logic error is the most dangerous and insidious of all software errors.
Some Bugs are Better than Others
Not all bugs are created equal. Avoid the expensive ones by making more of the ones that are easy to find and fix.
MZ-Tools - Productivity Tools for Visual Studio .NET (C#, VB.NET), Visual Basic and VBA
MZ-Tools has a single goal: To make your everyday programming life easier. As an add-in to several Integrated Development Environment (IDEs) from Microsoft, MZ-Tools adds new menus and toolbars to them that provide many new productivity features.
H. L. Mencken Quotes
“For every complex problem there is an answer...” - H. L. Mencken quotes from BrainyQuote.com

Image by (Joenomias) Menno de Jong from Pixabay

UPDATE [2023-01-16]: Added "Further Reading" section with link to Mathieu Guindon's related post, "Bubbly Run-Time Errors."