The Basics

Before delving into actual programming functions and sub-routine, we must first establish what is an error handler.

An error handler is a bit of code which will do pre-defined actions whenever an error occurs. For instance, generate a message to the user or developer describing the nature of the error. For an error handler to be useful, it must provide a minimum of information in its message to the user.

Microsoft’s Error Handler

If ever you allow Microsoft to generate code block, say by converting a macro to VBA, and include their ‘error handler’ you will get something like:

Private Sub Command3_Click()
On Error GoTo Command3_Click_Err

    'The code will be here

Command3_Click_Exit:
    Exit Sub

Command3_Click_Err:
    MsgBox Error$
    Resume Command3_Click_Exit
End Sub

Sadly, this error handler is dismal! It will result in a message box displaying a description of the error, but nothing more. Below is an example of what you can expect from Microsoft’s error handler.

In most cases, this simply isn’t enough information, certainly not for a developer, to properly isolate an issue to remedy it.

Proper Error Handler

So what would be the minimum data to provide in an error handler? Well, that’s a very good question and the answer can vary depending on the environment you are deploying your solution, but, for me, a bare minimum would include things like:

• Error Number
• Error Description
• Error Source

With this in mind, below is an example of a typical error handler I use for code I post on my website. It is a great starting point if you are new to VBA coding/error handling.

On Error GoTo Error_Handler

    'Your code will go here
    
Error_Handler_Exit:
    On Error Resume Next
    Exit {PROCEDURE_TYPE}
    
Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: {PROCEDURE_NAME}/{MODULE_NAME}" & vbCrLf & _
           "Error Description: " & Err.Description, vbCritical, _
           "An Error has Occurred!"
    Resume Error_Handler_Exit

A Concrete Example

Sub HelloWorld()
On Error GoTo Error_Handler

    MsgBox "Hello Word!"

Error_Handler_Exit:
    On Error Resume Next
    Exit Sub
    
Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: YourModuleName/HelloWorld" & vbCrLf & _
           "Error Description: " & Err.Description, vbCritical, _
           "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

Taking Things Even Further

The basic error handler meets a basic need and is a step forward, but for more complex procedure it still can leave you deep in troubleshooting to determine exactly where in a procedure the error is occurring. This is where adding a Line Number in the Error Handler can greatly help!

Luckily, there is the little-known Erl gem! If we add Line Numbers to our procedures, Erl we in fact return the last executed line number (which should normally be the source of our error).

So with this in mind, we can update the Basic Error Handler to

On Error GoTo Error_Handler

    'Your code will go here
 
Error_Handler_Exit:
    On Error Resume Next
    Exit {PROCEDURE_TYPE}
 
Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: {PROCEDURE_NAME}/{MODULE_NAME}" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit

Notice I use a Switch function so the Error Handler will work with, or without, line numbers being present. The result being that now, when line numbers are added to your procedure you will get something like

Now, we starting to get information that will enable us to quickly identify, isolate and remedy an error! We instantly know the exact source of the error, the error number and description, as well as the line number within the procedure that raised the error. Doesn’t get much better than that.

Centralized Error Function

As any developer knows, it takes time to perfect code. Your Error Handler is no different and will probably go through several iterations until you get it just the way you like it. Now, if you implement the code shown above, then any changes will require you editing each procedure, 1 by 1, to update any changes you wish to implement to your error handler. This, of course, could be very time consuming.

This is why, IMHO, it makes much more sense to create and implement an Error Handling function and using it within your procedure. The end result is simplified procedural code (so we reduce the amount of code in each procedure/module) and set things up so that you can change all error handling in one location with ease.

I first found out about this approach, no this wasn’t my idea by any means, from the great Allen Browne when I read his article: Microsoft Access tips: Error Handling in VBA. One extra advantage of Allen’s approach is he logs the errors to a table enabling you to review them when you wish to and not solely have to rely on a user’s recollection of a popup message.

Over the years, I have used and improved upon Allen’s code, but it is an excellent starting and can easily be used as is in production.

So I mentioned having ‘improved’ upon it and people always ask me how exactly? Everything depends on context, but normally I add to the logging function (not displayed to the user, no need) in things like:

• the computer name
• OS Information (version, build, bitness, …)
• Office/Access Information (version, build, bitness, runtime or not, language, …)
• …

and in certain cases I also add things like

• user’s selected language (for my multi-lingual databases)
• I switch the language of the error MsgBox displayed to the user
• …

In such a function, you can (and I have) gotten into taking screenshots so you can see the form/report/… and see the exact data, and/or send the information by e-mail, change the message for certain specific error to make them more understandable!,…. The possibilities here are endless, add anything that may help you in your job of troubleshooting issue!

Automating Insertion of Your Error Handler

If you are going to be doing some serious vba (MS Access, Word, Excel, …) work and not just a little tinkering, you should most probably seriously consider looking into buying a copy of MZ-Tools as enables you to insert your error handler into any procedure at the click of a button. You can even manage multiple versions of your error handler. Another beautiful feature of using MZ-Tools is the fact that it can automatically include things like the function name, module name, … in your error handler at runtime, eliminating another manual task.

Testing Your Error Handler

Sometimes, it would be nice to be able to trigger an error, for instance, to test your error handler. Nothing could be easier in VBA, we can simply use the Raise method to do so. That’s actually how I produced the images for this article. Here’s a simple example:

Public Function ErrorDemo()
10    On Error GoTo Error_Handler

20        Debug.Print "Line 1"
30        Debug.Print "Line 2"
40        Debug.Print "Line 3"
50        Err.Raise 9
60        Debug.Print "Line 4"
70        Debug.Print "Line 5"
          
Error_Handler_Exit:
80        On Error Resume Next
90        Exit Function
       
Error_Handler:
100       MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
                 "Error Number: " & Err.Number & vbCrLf & _
                 "Error Source: ErrorDemo/mod_Helpers_Errors" & vbCrLf & _
                 "Error Description: " & Err.Description & _
                 Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
                 , vbOKOnly + vbCritical, "An Error has Occured!"
110       Resume Error_Handler_Exit
End Function

which then generates

So on Line 50 we Raise error number 9 and as shown in the image above our error handler properly reports that back to us (and much more!).

So with a single line of code, you can easily test your code at any time you would like.

Where & When To Use Error Handling?!

Now this is one point that various developers have different viewpoints on.

We have to ask ourselves, what is the purpose of error handling? And there are a couple answers here:

• Avoid users being able to debug code when an error occurs so they can’t access the raw VBA code of our application
• Present comprehensible messages to the user when an error occurs
• Avoids Global Variables from being reset when an error occurs

I’ve always view it as you should have error handling in all your procedure (with very, very few exceptions). This makes your application bulletproof, even during development and ultimately there is no down side to implementing as such.

That said, as someone like Albert Kallal explains:

Thus, some argue that since we should be deploying database in a compiled state (mde, accde) that there is no need for error handling. (and no I’m not saying Albert is stating that, I’m not going to put words in his mouth, but his comments explain why some developer believe this)

As stated above, while true, it still doesn’t address these issues during development and testing and ultimately why I stand behind recommending implementing proper error handling throughout. I view it as there is no downside to adding error handling throughout. Furthermore, if you do implement some type of error logging system, you will even gain insight into the runnings of your solution making it even more valuable to add error handling throughout!

Alternative and Other Options

I thought I’d also mention a couple other tools well worth mentioning, mainly Great Access Tools – Access Crash Reporter.

I’ve also seen many people refer to UtterAccess’ Global Error Handler code which can be found at Global Error Handling, so that may also be worth your while to check out.

A Few Resources on the Subject

• Microsoft Access tips: Error Handling in VBA
• MZ-Tools – Productivity Tools for Visual Studio .NET (C#, VB.NET), Visual Basic and VBA
• On Error statement (VBA) | Microsoft Docs
• Error function (Visual Basic for Applications) | Microsoft Docs
• Raise method (Visual Basic for Applications) | Microsoft Docs
• Information.Erl Method (Microsoft.VisualBasic) | Microsoft Docs I couldn’t find any VBA documentation, only this .net that references VB Namespace?!