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:
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:
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.
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.
- Bubbly Run-Time Errors, by Mathieu Guindon
UPDATE [2023-01-16]: Added "Further Reading" section with link to Mathieu Guindon's related post, "Bubbly Run-Time Errors."