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.
- Cascading Errors
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,
.Raise, which become invaluable when debugging or providing more error details to your users.
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
.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 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
.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 Sub
If 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.
.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 1000013
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 spreadsheets with our free VBA Developer's Guide 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 Guide and a bunch of pre-built macros so you can master file I/O, arrays, strings and more.
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 Guide and a bunch of pre-built macros so you can master file I/O, arrays, strings and more.
Err Object Methods
The Error object methods,
.Clear, can greatly help you in debugging and telling users how to fix issues.
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 Sub
Now 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 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 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") Err.Clear Loop On Error GoTo 0
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 Function
Even 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.