VBA and Error Handling

I almost put this in the post on "Understanding VBA" because it is really weird. Anyone familiar with classic BASIC or QBASIC will remember the GOTO function. I actually thought they had eliminated this in most modern programming languages due to the code structure you could end up with when using too many GOTO statements (spaghetti code was what I remember it being called). Even though I really wish VBA would adopt the Try… Catch… Error… model, it hasn't and we're stuck with using the GOTO statement.

There are several things to remember when handling errors in VBA. These should be familiar to anyone that programs on a regular basis:

First, never, ever let your code have a section that could error out unhandled. VBA is not forgiving. It will dump your user right into the code AND ALLOW THEM TO EDIT IT. You can put a password on your code if you have to and I would recommend it, but you should still never allow any user to even see the VBA window asking for a password. Always error out gracefully

Second, make sure everything has a general error handler around it. I've seen users do weird things so it is always better to have more error catching than needed.

Third, try and make the errors specific to certain code parts. Imagine that you create an automation tool for Excel that gets sent to 20 users and you are going to be the recipient of each and every error that is raised from each user. You don't want to have them emailing a screenshot of your generic error window with "Sorry, the program experienced an error. Please try again." Think about what information you will need to resolve an error. Where could your code error later that it's not erring now? Database connection time outs are a good example. Give yourself as much information as possible in the event your program does error out.

Fourth, refer to the first thing. This will be crucial to your tools adoption. The user needs to feel like they are getting a good resolution rate on any bugs they run across. Good error handling is critical. Just think of any error window you have seen that didn't make any sense to you. Now imagine you are the guy on the other end trying to resolve it from a screenshot.

Fifth, this was also part of the first item, but the first item was important to I am going to reiterate both points again. Once you have tested your code and you think it's working great protect your code (in the VBA menu click "Tools" then click "Project Properties" then the "Protection" tab). This is a quick and simple step that will prevent you from having someone else create "version b" while you were on vacation (thanks to Google everyone thinks they can become a programmer).

Now for the error handling example:

On Error GoTo <Label>

'Code

Exit Sub

<Label>:

'Error Code

Typically I'll label my general error catch code as "errHandler" so the code will look like this:

On Error GoTo errHandler

'Code

Exit Sub

errHandler:

'Error Code

Note the "Exit Sub" in the above code. Make sure you put this statement right before your error label. VBA doesn't care if a label is used for error handling. It will continue processing your code until it reaches the end, including any code that you have in your error handling section. It's very disturbing see error messages pop up and not find any errors in your programs.

You can have multiple On Error statements in the code, but the thing to remember that if an error is raised it will call the last On Error statement so if you have a database connection that you want to have different error output you could do something like this:

On Error GoTo errHandler

'Code

On Error GoTo DBerrHandler

'DB Code

On Error GoTo errHandler

'More Code

Exit Sub

errHandler:

'Error Code

Exit Sub

DBerrHandler:

'Error Code

Notice the "Exit Sub" in the errHandler code? I call this the "Nice try, but try again" error style. Setting up your error coding like this force the program to exit whatever it was doing and requires the user to retry the same thing they just did. This may not be a big deal if you don't have many steps or the code doesn't take long to execute, but in most cases you only want to use this if the error is critical and you want to quit the program (i.e. the program was unable to connect to a DB and pull data). This goes back to providing the user with a good experience. If the user didn't enter something in a form that causes an error, then maybe you'll want to do some form validation before you begin the next bit of code.

There are other options for error handling in VBA, "On Error Resume Next" is a good example. I haven't used this unless I know an error could be raised and I want to completely ignore it.

What I have showed here are extremely simple examples of error handling code. When you are building something that needs error handling you will want to make sure that anything you load is unloaded prior to you exiting the code. You may end up eating a lot of your computer's resources and not realize it, especially if you use Excel all day long. I once built an application that opened excel doc but didn't make it visible. However, when it erred out it wouldn't close the excel program and I had to go into the task manager to close the excel instances. During one intense testing session I managed to open over 10 instances of Excel and had to close them all manually. Handle the errors as gracefully as possible and unload the resources you have opened, it will save your project.