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:
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 ErrCombining 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
myHandler:
If Err.Number = 11 Then
Err.Description = "You can't divide by zero, dummy"
MsgBox Err.Description
End If
End SubIf you don’t understand why we have On Error GoTo 0 or how to reach
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:
?err.Number
13
?err.Description
Type mismatch
?err.HelpFile
C:\Program Files\Common Files\Microsoft Shared\VBA\VBA7.1\1033\VbLR6.chm
?err.HelpContext
1000013You 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.
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,
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 SubNow while debugging, checking the Err.Source property will tell you the error was encountered in the
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
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")
Loop
On Error GoTo 0Here, 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")
Err.Clear
Loop
On Error GoTo 0Cascading 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
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
centralHandler:
'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 FunctionEven though an InputBox was called in the function 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.
