In our third and final installment on VBA error handling we’ll dive into the Err object, some of its properties, and how to use it to raise errors. We’ll also take a brief look at how errors can cascade through subs. If you are new to VBA error handling, start with our tutorial on error handling blocks with GoTo then continue to our Resume error statements tutorial.

The Err Object Properties

Every error instance in VBA populates the Err object with some useful properties designed to help handle the error. The Err object also has two useful methods, .Clear and .Raise, which become invaluable when debugging or providing more error details to your users.

The .Description Property

As you might expect, this property provides a description of your errors. If the error thrown is part of the common set of system errors (divide by zero, overflow, subscript ranges, type mismatches, etc.), a pre-determined description will already be set by the system.

These descriptions are probably familiar to you, because they are the same ones that appear when you are testing in the VBA editor (VBE). If you run code that divides by zero, you’ll know because of the dreaded run-time error popup:

Runtime error message box
The dreaded runtime error message box

The Division by zero string is the pre-determined .Description property of the Err object.

.Description is read/write, so you can set your own description if you prefer. Generally this is done to help users fix the problem, and it is especially practical if you’ve written your own VBA classes and are raising errors (more on that later).

The .Number Property

The description is a string and human-readable, but computers operate with numbers. Using numbers makes it much easier to set up Select Case and IF-statement tests. It’s also more succinct, though most users will not know which numbers correspond to which errors. It’s still valuable to know the number when you’re Googling how to fix a run-time error.

In the above screenshot, the Run-time error ‘11’ part shows the error number. As a little trick, the number property is the “default property” of the Error object, which means you can print the number in two ways:

Debug.Print Err.Number
Debug.Print Err

Combining the .Number and .Description properties, you might tell your users not to divide by zero, overwriting the system default:

Sub div_by_zero_from_input_error()
Dim x, y As Integer

On Error GoTo myHandler
    x = InputBox("Enter numerator")
    y = InputBox("Enter denominator")
    MsgBox "Your ratio is " & x / y
On Error GoTo 0

Exit Sub

If Err.Number = 11 Then
    Err.Description = "You can't divide by zero, dummy"
    MsgBox Err.Description
End If

End Sub

If you don’t understand why we have On Error GoTo 0 or how to reach myHandler, see our first VBA Error Handling article.

The Source and Help Properties

The Error object has three more properties.

First is .Source, which can help you pinpoint what triggered the error. Again, this is a read/write property, so you can write whatever you want to it. You can set it to the name of the triggering subroutine or code block to help you find issues.

The other two are related to help files. When a system default error occurs, like Subscript out of range, the .HelpFile will (ideally) point you to a help file on the system where you can find information about the error you encountered. Since some help files can be very large, .HelpContext will place you in the correct spot in the file.

For example, when I have a Type mismatch error, I will get the following information from the Immediate window:


Type mismatch

C:\Program Files\Common Files\Microsoft Shared\VBA\VBA7.1\1033\VbLR6.chm


You can create your own help files to guide your users, but it’s not as simple as just pointing to a plain text file. If you’ve written any custom classes or your code is being shipped to thousands of users, consider going through this extra effort, though, especially if you are a one man or one woman show. This can help you avoid answering 500 emails. There are plenty of resources on the internet to help you write these files, which take the .chm extension. With a valid one, when the user taps Help in the error message box, they’ll go straight to your file.

Make powerful macros with our free VBA Developer Kit

It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.

I'll take a free VBA Developer Kit

The Err Object Methods

The Error object methods, .Raise and .Clear, can greatly help you in debugging and telling users how to fix issues.

Raising Errors

Note: If you’re a long-time reader of our site, you might recall we published a detailed tutorial about raising custom errors with the VBA Err.Raise method. We’re going to touch on that again here.

A system often automatically raises run-time errors, like Subscript out of range. If you have two sheets in a workbook, Inputs and Outputs, and you ask for a sheet named Intermediates, the system cannot conjure up an Intermediates sheet out of thin air.

You can also raise errors yourself. In our Divide by zero code block above, instead of letting the system raise the error, you could’ve done it yourself. The .Raise method accepts all five properties we talked about earlier, so you can set the description, source, and help information in one go:

Sub div_by_zero_from_input_error2()
Dim x, y As Integer

x = InputBox("Enter numerator")
y = InputBox("Enter denominator")
If y = 0 Then Err.Raise 11, "output ratio sub", "Denominator is zero", "C:\Help With Ratios.chm"
outputRatio = x / y
End Sub

Now while debugging, checking the Err.Source property will tell you the error was encountered in the "output ratio sub". You can put whatever description you want in here.

The user will be able to see the description of “Denominator is zero” if a runtime error window pops up and it’ll even link to a custom help file Help with Ratios.chm, if one actually existed.

If the error is unique for your project, you can even set your own types of errors with custom numbers. The range 0 to 512 is reserved for system errors and valid numbers above 512 can be used for custom error numbers.

The .Clear Method

The other Error object method is .Clear, and, as its name suggests, it clears the current error. Recall that only one error can be active at any time. Further errors will be fatal.

One way to reset the error handler is to use a Resume statement or exit the subroutine (or function). Technically this automatically calls the .Clear method in the background without any interaction from the programmer.

To explicitly clear an error, use Err.Clear. This is quite useful when deferring errors with On Error Resume Next, especially if there’s an iteration during deferment coupled with an IF-statement, like this:

x = 5

On Error Resume Next
    Do While x > 0
        y = z / (x - 4)
        x = x - 1
        If Err.Number <> 0 Then MsgBox ("Oops. There was a problem")
On Error GoTo 0

Here, on the first iteration, no problem. On the second, however, we get 0 in the denominator. This raises an error, which never clears during the loop. Hence, the message box appears every time, even though the error is only pertinent to the second run. On run 3, x = 3, so the operation is valid again, but the uncleared error still triggers the IF-statement. Use .Clear to solve this (illustrative) problem:

x = 5

On Error Resume Next
    Do While x > 0
        y = z / (x - 4)
        x = x - 1
        If Err.Number <> 0 Then MsgBox ("Oops. There's a problem")
On Error GoTo 0

Cascading Errors

Error handling must be activated with an On Error statement. If this is missing and the current subroutine is top-level (i.e., it was not called from another sub), then there will be a fatal error and the dreaded runtime error message appears.

However, if the current sub was called from another sub, and the other sub has an active error handler, the error will “cascade backwards” to the calling sub’s error handler. This can help centralize all your error handling into a single sub for easier maintenance, especially when you have been zealous in modularization and each sub does a very simple task.

In this example, you can easily write a single Select Case in your centralHandler to take care of potential issues in called subs rather than copying code to each sub. This illustrates the traditional tradeoff between code bloat (copying) and the readability of modularized code. If you rely on cascades, make sure to document well!

Sub calling_cascade_back()

On Error GoTo centralHandler
    'revenues = called_cascade_1
    'some code
    'currCapital = called_cascade_2
    magicNumber = called_cascade_3
    '200 more lines of code
On Error GoTo 0

Exit Sub

'error handling code for whole project
Select Case Err.Number
    Case 6
        'handle Overflow
    Case 11
        'handle Divide by zero
    Case 13
        'handle mismatches
End Select

Resume 'use RESUME to return to the errored-out function or sub!
'Resume Next ← GOES TO NEXT LINE IN **calling_cascade_back**!!

End Sub

Function called_cascade_3()
'calculates user-defined ratio
'all errors handled centrally from calling sub

userInput = InputBox("Enter your magic number for stock valuation")
called_cascade_3 = 500 / userInput

End Function

Even though an InputBox was called in the function called_cascade_3, if a user inputs 0 for userInput, then control will move to centralHandler in the main subroutine. The Resume statement will cause the InputBox to keep appearing until a valid number is entered.

This concludes our 3-part VBA Error Handling tutorial. Now you know how to use the Error object, how to check and set properties, and how to use its methods for debugging. You also saw how to centralize error handling through cascades.

The system has plenty of defaults to guide programmers and users, and programmers should use them liberally. It’s very useful to set your own errors using .Raise, especially in large programs, so you can find error-triggering sections more easily. In production code, you can even avoid many customer service calls if you handle errors well and guide the users with clear descriptions or even full help files.

If you found our series on VBA error handling helpful and you’re serious about learning VBA, please subscribe using the form below for more free lessons.