Last week I discussed simple error handling techniques such as no error handling (the default experience), ignoring errors, and quit (gracefully) the first time an error occurs. This week I’m going to explain a more flexible technique that allows different actions to be taken depending on the type of error that occurs.
The key to this technique is using the Err object to gather information about the type of error that occurred and then take an appropriate action. Nearly every type of error that can occur at runtime is assigned a number and description and this information is surfaced through the Err object. The Err object is an object that is automatically created by VBA whenever an error occurs.
As an example, consider Listing 1 below.
Listing 1: Flexible Error Handling
Sub FixErrorStrategy()
Dim x As Integer
Dim z As Integer
' If an error occurs, resume execution
' with the statement following the
' ErrHandler label.
On Error GoTo ErrHandler
z = 0
For x = 1 To 10
Debug.Print x / z
Next
' This line will only be reached
' if no errors occur or if
' recoverable errors occur.
MsgBox "Finished"
Exit Sub
ErrHandler:
If Err.Number = 11 Then
MsgBox "Division by zero error. " & _
"Changing denominator to 1."
z = 1
' Resume execution with the statement that
' caused the error.
Resume
Else
MsgBox "Unrecoverable Error: " & _
Err.Number & " - " & Err.Description
End If
End Sub
Listing 1 loops through the numbers 1 through 10 and divides each number by the number stored in variable z (which is mistakenly set to 0 initially). When an error occurs, execution proceeds to the block of code after the ErrHandler label. This code examines the Err.Number and takes action if the error is error number 11 -– the error number associated with division by zero. For all other errors, the procedure will display information about the error and then exit the procedure.
The ErrHandler code block uses the Resume statement after fixing the condition that caused the error to resume execution at the statement that initially caused the error. When the division by zero error occurs, the procedure sets the value of z to 1, then the Resume statement causes execution to resume at the Debug.Print x/z statement. From that point on, the procedure runs as expected and produces the following output in the Immediate window.
1
2
3
4
5
6
7
8
9
10
This error handling pattern is very typical of VBA procedures that require anything more than the most elementary error handling. Stay tuned next week for a great tip on scaling Excel worksheet numbers programmatically.