Tag Archives: exception

Proper VBA error handling

1 Star2 Stars3 Stars4 Stars5 Stars (7 votes, average: 3.71 out of 5)

Writing VBA code is hard, but properly debugging code is even harder. Sounds like non-sense? Well I dare say developers spend more time debugging code than writing it. Looking for errors is what developers do most of the time! A critical part of debugging is proper error handling (VBA error handling in our case).

Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it.

— Brian W. Kernighan

However, today I don’t want to expand on debugging VBA. That I covered in this post. No – today let’s learn how to properly handle errors

The Mouse Trap Analogy

What is error handling? Take this analogy: Say you have a mouse (an error) in the house which turns up every now and then in the least expected moment as slips from your hands (an uncaught exception if you prefer). Without knowing where the mouse is and when it (the exception/error) will appear (in which line of code) you would need to search entire house to catch it (run through the entire code in our case). Obviously a better approach is setting mouse traps in several critical places in the house (corridors etc.) and waiting for the mouse to fall into your trap.
So what is our mouse trap when speaking about VBA error handling? The On Error do this statement!

Using VBA On Error

The VBA On Error statementtells VBA what it should do from now on, within the vicinity of the current block of code (Function or Sub), when an error/exception is raised. It is like setting a mouse trap – with the difference that you can tell it to drop the mouse off the dumpster or put it in your hands to manage.

Let’s remind the full syntax of the On Error statement in VBA first:

Directly from MSDN we learn the different VBA error handling options we have with the On Error statement:

On Error

  • Goto – or if preffered. The Goto instruction in VBA let’s you do a jump to a specific VBA code line number to follow through with error handling or simply to let code execution move on. It comes in three flavors:
    • lineLabel – will jump to a specific line number label
    • 0 – will disable any previously set error handling within the current procedure
  • Resume Next – Simply ignore any error raised and move code execution to the next line. VBA error handling for the lazy, although beware in case of recurring errors (error overflow) – an error will still be raised

On Error examples

With the above synax in mind let’s look at some On Error VBA error handling examples:

On Error Resume Next
Skip any raised errors

On Error Goto 0
Disable any previous VBA error handling

On Error Goto Label
On error raised jump to a specific line label

The VBA Err Object

Whenever a VBA error is raised the Err object is updated with relevant information needed to diagnose the error. Let look at this object for a second.

Err Object functions and properties

The Err object facilitates the following functions and properties:

  • Number – the most important property of the Err Object, the error number raised. On MSDN you can find the full list of VBA errors. The available range for custom user errors is 513-65535.
  • Clear – clear the current Error. Useful when using the Resume Next statement.
  • Raise(Number, [Source], [Description], [HelpFile], [HelpContext]) – raises an error. You need to provide an error Number. See here for a whole list of VBA error Numbers. Want to raise a custom error? The available range for custom user errors is 513-65535.
  • Source – the source of the error – usually your VBAProject.
  • Description – the description of the error.

This is the expected output:

VBA Error: Out of Memory
VBA Error: Out of Memory

VBA Error Handling Best Practices

Now that we know our way around VBA error handling let’s learn some good practices on VBA error handling. The best practice for error handling is putting all handlers (jump labels) at the end of your code block – whether its a VBA Sub or Function. The below example shows how it is done:

Single VBA error handler

If you want to handle all errors in a single section see example below:

Notice the Exit Sub statement just before the ErrorHandler label. This statement is important to make sure the ErrorHandler is accessed only when an error is raised.

Multiple VBA error handler

If you want to handle multiple errors e.g. based on the Error number see example below:

The ErrorHandler block in this case redirects to the designated error handler based on the Err.Number. Unrecognized errors are redirected to the OtherError block. This makes VBA error handling neat and tidy. I definitely prefer the multiple VBA error handler as it gives you and the user more insights and control over the errors raised by VBA.

Custom VBA errors

In some cases you will want to raise a custom error. The best way to do it is using the Err.Raise procedure.

When creating custom errors make sure to keep them well documented. I recommend creating an Enum object and listing all custom errors like below:

Now we can use our Custom Error numbers in our VBA error handler:

Neat and tidy right! Ok… but what about cleaning up those Error msgs? Clearing I don’t want to have duplicate error message descriptions lying around the place. A simple Get function can help:

Let’s collect what we know into a consolidated code sample:
VBA Error Handling

The Resume Next statement

The Resume Next statement is great when you want on a error being raised you want execution to move on to the next statement and continue code execution. You can use the VBA Resume Next statement to resume code execution directly from your VBA error handler:

VBA Error Log

Logging VBA errors is very useful in the VBA error handling concepts. This allows you to make sure you don’t lose track of any errors that might have occurred in your Workbooks although you might not want to handle these errors directly. Read here if you want to learn more about writing to text files.

The LogError procedure will append the error message prefixed with the current time (date and time):