Error Handling in Excel VBA: What, Why and How

Would you embark an airplane without safety doors, slides and oxygen masks? I won’t * While not a matter of life and death, I won’t deliver an Excel application without proper Error Handling. Here’s why, and a hands-on video on how to do it right

What is error handling?

Ever received an error message popping up from an application you had no idea what it means, what to do and what’s going to happen to your data you just entered? Maybe something like this:

How does it make you feel? Do you know what caused this error? Do you know how to avoid it next time? Do you know which button you should pound to feel safe again?

Errors in computer programs happen, and “someone” in the little box will raise a red flag and shout out. It could be the application you wrote in Excel, Excel itself, Windows running Excel, or, god forbid, the lower-level kernel of Windows (ever seen a “Blue Screen of Death?”).

Whoever raised that flag, handled the error. How? By sending you a message, by stopping the application or by crashing Windows.

Why handle errors?

The higher-up the chain the error is handled – the friendlier experience the user has.

For that reason alone, you should make sure it is YOUR application that is handling the error.

When you handle errors, you have the most control over the situation, what can be done and how to better engage the user in order to resolve the situation, and not less important, to transform a potential situation of resentment, to an opportunity for making friends.

How would you FEEL, if instead of the above message, you would receive something like that:

Exactly my point!

How to handle errors in Excel VBA?

Every decent programming language offers error handling mechanism to the programmer. The basic approach is:

1. Enclosing a code segment to be on alert for potential errors
2. Capturing any error raised in that code segment during run-time
3. Routing the code flow to another segment of code in order to handle the error
4. Routing the code flow to another segment of code to finalize tasks – whether an error was raised and handled, or not.

So, the basic concept is that of errors that are being RAISED and code you write to HANDLE the error.  In the error handler, you can control the situation, rollback any data activities, notify the user, or do nothing and just let it pass. In the FINALIZE section you perform tasks you must do anyway before you return control to the user (or to the calling function), such as closing any open files, hiding helper Worksheets, clearing the status bar, re-enabling events, etc.

Watch the video here as I demonstrate how to implement error handling in Excel VBA – a sample lesson from my  Computer Programming with Excel VBA course.