Error Handling Evolution

Errors are a fact of life in programming. Errors are unavoidable.  But errors are not the same as bugs.

Errors signal a problem with the program's state.  Bugs indicate a problem with the program itself.

Not all errors are bugs

For example, consider a program that saves files to a network folder.  If the network folder is unavailable, the program will raise an error.  As the programmer, you should expect this.  Your program has no control over whether the network drive is available at any given moment (it's a leaky abstraction after all.)

But so far, all we have is an error.  And there's nothing wrong with errors in your program.  The problem is when you fail to anticipate them and handle them with grace.  That's when errors turn into bugs.  The way to avoid bugs is with proper error handling.

Not all bugs come from errors

Not every error results in a bug.  Similarly, not all bugs arise from errors.  In fact, the most insidious bugs of all having nothing to do with the kinds of errors that a programming language raises.  No, the worst bugs of all are logic bugs.

A logic bug is one where the program appears to work fine, but the output it generates is incorrect.

Think of an online shopping cart.  The retailer charges shipping based on the order total: $5 for orders up $50, $10 for orders up to $200, etc.  The order qualifies for a 20% discount.  The retailer wants to calculate shipping before applying the discount.  Instead, the program applies the discount and then calculates shipping.  This is a classic logic bug.  The best way to avoid logic bugs is with automated testing.

Handling errors

How you handle errors says a lot about you as a programmer. Most people evolve with experience in how they handle errors.  From the most naïve to the most advanced, here is what that evolution looks like.

See no evil

The most naïve approach is to simply pretend errors don't exist.

Sub MyRoutine()
    'Some code
End Sub

You see this most commonly in Excel or Word VBA.  You see it less in Access because of what happens with unhandled errors in a runtime environment:

When Access is running in a runtime environment it will crash hard--exiting the application immediately--as soon as any unhandled error is raised.

Hear no evil

You can safely ignore many errors in VBA.  But by no means should you ignore every error in VBA.  The On Error Resume Next statement allows you to ignore errors.  There are times when this is useful.  There are times when it is necessary.

But if your programs look anything like this, you have gone down a very dark path indeed:

Sub MyRoutine()
    On Error Resume Next
    'Some code 
End Sub 

Function MyFunction() As String
    On Error Resume Next 
    'Some other code
End Function

Speak the evil?

One of the most common approaches to error handling in VBA is to just show a message box to the user.  Here's how this looks:

Sub MyRoutine()
    On Error Goto MyErrorHandler
    
    'Some code

    Exit Sub
MyErrorHandler:
    MsgBox Err.Description
End Sub

The code above is pretty straightforward.  The On Error Goto MyErrorHandler line tells VBA to go to the line labeled MyErrorHandler if the code that follows raises an error.  In the case of an error, the routine shows the user a message box with a description of the error.

While this is infinitely better than the first two approaches, it still has many problems.  (1) It relies on users to pay attention to and understand the error messages.  Users will do neither of these things.  (2) Errors don't bubble up.  A procedure that calls MyRoutine() will not be alerted if it throws an error.  This may or may not be what you want.  It's almost certainly not what you want every single time.  (3) It requires lots of boilerplate code.  There are plugins like MZTools that automate creating that code.  But you're still left with four lines of code per routine that have nothing to do with the software's actual purpose.  This lower's the signal-to-noise ratio and makes the code harder to read.

Most people move past the first two approaches very quickly.  But a surprising number of people get stuck in this approach.  There are better ways.

Select...Case error handling

This one is similar to the previous approach, but with a couple of important distinctions.  Here is what it looks like:

Sub MyRoutine()
    On Error Goto MyErrorHandler
    
    'Some code 

ExitRoutine:
    DoCmd.Hourglass False
    DoCmd.SetWarnings True
    Exit Sub
MyErrorHandler:
    Select Case Err.Number 
    Case 70  'Permission denied
        MsgBox "Can't access file.  Make sure you have permission to make changes." 
    Case 3265  'Item not found in this collection
        Resume Next 
    Case Else
        MsgBox Err.Description
    End Select
    GoTo ExitRoutine 
End Sub

There are some key differences from before:

1. I added an ExitRoutine: label with some cleanup code.  If you are familiar with the Try...Catch...Finally error handling approach of other languages, this section is like the Finally block.  It includes code that should always be called before the routine exits, even if the routine raises an error.
2. I added a Select...Case statement to handle specific types of errors that I anticipate the body of my routine might raise.  The actual Case clauses will vary from one routine to another.  The point is that I'm putting some thought into what errors I might encounter and how my code should handle each.

On the downside, there is even more boilerplate code than before.  That's not ideal, but it's not the end of the world, either.

Dedicated error handling function

One way to cut down on some of the boilerplate code is to use a dedicated error handling function.  Here is what that may look like:

Sub MyRoutine()
    On Error Goto MyErrorHandler

    'Some code 

ExitRoutine:
    Exit Sub 
MyErrorHandler:
    Select Case Err.Number
    Case Else 
        HandleError Err.Number, Err.Description, Errors 
    End Select 
    GoTo ExitRoutine 
End Sub 

Public Sub HandleError(ErrNum As Long, ErrDesc As String, _
                       Optional Errs As DAO.Errors = Nothing) 

    'Restore some default settings in case
    '   they were changed in the calling routine
    Application.Echo True
    DoCmd.SetWarnings True
    DoCmd.Hourglass False 
    SysCmd acSysCmdClearStatus 
    
    'Ignore OpenForm/Report action was canceled error messages 
    If ErrNum = 2501 Then Exit Sub
    
    'Get DAO error message(s), if applicable 
    Dim Msg As String, ErrItem As DAO.Error 
    If Not Errs Is Nothing Then
        If Errs.Count > 1 Then
            For Each ErrItem In Errs 
                Msg = Msg & ErrItem.Description & vbNewline 
            Next ErrItem 
        End If 
    End If 
    If Len(Msg) = 0 Then Msg = ErrDesc 
    
    MsgBox Msg, vbCritical, "Error [" & ErrNum & "]" 
End Sub

Having a dedicated function lets you build some advanced functionality into your default error handling process.  In my example above, I do things like making sure the screen drawing is turned on (Application.Echo True ), the Access warnings are turned on (DoCmd.SetWarnings True), the hourglass icon is turned off (DoCmd.Hourglass False), and any custom status messages are cleared (SysCmd acSysCmdClearStatus).

I ignore an error that gets raised any time I cancel the opening of a report because there is no data (ErrNum 2501).  The DAO Errors object contains a collection of error messages when certain database operations fail.  To access the error messages, one must iterate through the collection.  This is more code that we can relegate to our error handler.

This is just scratching the surface of what you can do with a dedicated error handling function.  Astute readers may already be thinking, "Hey, this would be a great place to log errors!"  And you would be right.  A whole new world of advanced error handling opens up once you have a dedicated function in place.

If this is as far as you go, congratulations.  You are likely in the top 10% of Access developers when it comes to error handling.  But wait...there's more!

Automatic error handling?!?!

I spent years using the previous error handling method.  I tweaked my error handler to log every error to a dedicated error log table in a centralized database server.  I wrote a fallback function to log the error to a local text file if the database was unreachable.

I learned about the Erl function and started saving line numbers with my error logging.  I didn't like seeing the line numbers, though, so I created a script to add the line numbers as part of my automated release process (Joel Test #2).  It was all very advanced, very impressive, and very cumbersome.

My boilerplate code had taken over.  I'm not exaggerating when I say that nearly half of my lines of code were error-handling boilerplate.  In other words, they were lines of code that added no value to the program.  They hindered readability.  I knew there had to be a better way.

And then one day--deep down a stackoverflow/ExpertsExchange rabbit hole--I stumbled upon something amazing.  Like Alice through the looking glass, what I saw challenged my very conception of reality: vbWatchdog.

(I want to be clear that I have zero affiliation with this product.  You know, other than the fact that it completely changed my life as an Access and VBA developer.)

This is a paid product.  At the time of writing, it sells for $160 for a single-developer license.  It is worth all that and more.  If you have sold at least $1,000 of Access software development services and have plans to continue selling more in the future, you should buy this.  Period.  Full stop.

There will be some upfront investment on your part as a developer to set up your global error handling function.  Of course, if you have a dedicated error handling function, then you are most of the way there already.

I will go into detail in future posts about how to incorporate vbWatchdog into your projects.  But for now, I want to leave you with an example of what your typical routine looks like after implementing vbWatchdog:

Sub MyRoutine()
    'Some code
End Sub

That's right.  We have come full circle.  The most advanced VBA error handling looks exactly like the most naïve.  And that is the true beauty of vbWatchdog.