Introduction
Error handling refers to the programming practice of anticipating and coding for error conditions that may arise when your program runs. Errors in general come in three flavors: compiler errors such as undeclared variables that prevent your code from compiling; user data entry error such as a user entering a negative value where only a positive number is acceptable; and run time errors, that occur when VBA cannot correctly execute a program statement. We will concern ourselves here only with run time errors. Typical run time errors include attempting to access a non-existent worksheet or workbook, or attempting to divide by zero. The example code in this article will use the division by zero error (Error 11) when we want to deliberately raise an error.
Your application should make as many checks as possible during initialization to ensure that run time errors do not occur later. In Excel, this includes ensuring that required workbooks and worksheets are present and that required names are defined. The more checking you do before the real work of your application begins, the more stable your application will be. It is far better to detect potential error situations when your application starts up before data is change than to wait until later to encounter an error situation.
If you have no error handling code and a run time error occurs, VBA will display its standard run time error dialog box. While this may be acceptable, even desirable, in a development environment, it is not acceptable to the end user in a production environment. The goal of well designed error handling code is to anticipate potential errors, and correct them at run time or to terminate code execution in a controlled, graceful method. Your goal should be to prevent unhandled errors from arising.
A note on terminology: Throughout this article, the term procedure should be taken to mean a Sub, Function, orProperty procedure, and the term exit statement should be taken to mean Exit Sub, Exit Function, or Exit Property. The term end statement should be taken to mean End Sub , End Function, End Property, or just End.
The On Error Statement
The heart of error handling in VBA is the On Error statement. This statement instructs VBA what to do when an run time error is encountered. The On Error statement takes three forms.
On Error Goto 0
On Error Resume Next
On Error Goto <label>:
The first form, On Error Goto 0, is the default mode in VBA. This indicates that when a run time error occurs VBA should display its standard run time error message box, allowing you to enter the code in debug mode or to terminate the VBA program. When On Error Goto 0 is in effect, it is the same as having no enabled error handler. Any error will cause VBA to display its standard error message box.
The second form, On Error Resume Next , is the most commonly used and misused form. It instructs to VBA to essentially ignore the error and resume execution on the next line of code. It is very important to remember that On Error Resume Next does not in any way "fix" the error. It simply instructs VBA to continue as if no error occured. However, the error may have side effects, such as uninitialized variables or objects set to Nothing. It is the responsibility of your code to test for an error condition and take appropriate action. You do this by testing the value of Err.Number and if it is not zero execute appropriate code. For example,
On Error Resume Next
N = 1 / 0 ' cause an error
If Err.Number <> 0 Then
N = 1
End If
This code attempts to assign the value 1 / 0 to the variable N. This is an illegal operations, so VBA will raise an error 11 -- Division By Zero -- and because we have On Error Resume Next in effect, code continues to the If statement. This statement tests the value of Err.Number and assigns some other number to N.
The third form On Error of is On Error Goto <label>:which tells VBA to transfer execution to the line following the specified line label. Whenever an error occurs, code execution immediately goes to the line following the line label. None of the code between the error and the label is executed, including any loop control statements.
On Error Goto ErrHandler:
N = 1 / 0 ' cause an error
'
' more code
'
Exit Sub
ErrHandler:
' error handling code
Resume Next
End Sub
Enabled And Active Error Handlers
An error handler is said to be enabled when an On Error statement is executed. Only one error handler is enabled at any given time, and VBA will behave according to the enabled error handler. An active error handler is the code that executes when an error occurs and execution is transferred to another location via a On Error Goto <label>: statement.
Error Handling Blocks And On Error Goto
An error handling block, also called an error handler, is a section of code to which execution is tranferred via a On Error Goto <label>: statement. This code should be designed either to fix the problem and resume execution in the main code block or to terminate execution of the procedure. You can't use to the On Error Goto <label>: statement merely skip over lines. For example, the following code will not work properly:
On Error GoTo Err1:
Debug.Print 1 / 0
' more code
Err1:
On Error GoTo Err2:
Debug.Print 1 / 0
' more code
Err2:When the first error is raised, execution transfers to the line following Err1:. The error hander is still active when the second error occurs, and therefore the second error is not trapped by the On Error statement.
The Resume Statement
The Resume statement instructs VBA to resume execution at a specified point in the code. You can use Resume only in an error handling block; any other use will cause an error. Moreover, Resume is the only way, aside from exiting the procedure, to get out of an error handling block. Do not use the Goto statement to direct code execution out of an error handling block. Doing so will cause strange problems with the error handlers.
The Resume statement takes three syntactic form:
Resume
Resume Next
Resume <label>
Used alone, Resume causes execution to resume at the line of code that caused the error. In this case you must ensure that your error handling block fixed the problem that caused the initial error. Otherwise, your code will enter an endless loop, jumping between the line of code that caused the error and the error handling block. The following code attempts to activate a worksheet that does not exist. This causes an error (9 - Subscript Out Of Range), and the code jumps to the error handling block which creates the sheet, correcting the problem, and resumes execution at the line of code that caused the error. On Error GoTo ErrHandler:
Worksheets("NewSheet").Activate
Exit Sub
ErrHandler:
If Err.Number = 9 Then
' sheet does not exist, so create it
Worksheets.Add.Name = "NewSheet"
' go back to the line of code that caused the problem
Resume
End If
The second form of Resume is Resume Next . This causes code execution to resume at the line immediately following the line which caused the error. The following code causes an error (11 - Division By Zero) when attempting to set the value of N. The error handling block assigns 1 to the variable N, and then causes execution to resume at the statement after the statement that caused the error.
On Error GoTo ErrHandler:
N = 1 / 0
Debug.Print N
Exit Sub
ErrHandler:
N = 1
' go back to the line following the error
Resume Next
The third form of Resume is Resume <label>: . This causes code execution to resume at a line label. This allows you to skip a section of code if an error occurs. For example,
On Error GoTo ErrHandler:
N = 1 / 0
'
' code that is skipped if an error occurs
'
Label1:
'
' more code to execute
'
Exit Sub
ErrHandler:
' go back to the line at Label1:
Resume Label1:
All forms of the Resume clear or reset the Err object.
Error Handling With Multiple Procedures
Every procedure need not have a error code. When an error occurs, VBA uses the last On Error statement to direct code execution. If the code causing the error is in a procedure with an On Error statement, error handling is as described in the above section. However, if the procedure in which the error occurs does not have an error handler, VBA looks backwards through the procedure calls which lead to the erroneous code. For example if procedure A calls B and B calls C, and A is the only procedure with an error handler, if an error occurs in procedure C, code execution is immediately transferred to the error handler in procedure A, skipping the remaining code in B.
A Note Of Caution
It is tempting to deal with errors by placing an On Error Resume Next statement at the top of the procedure in order to get the code to run without raising an error. This is very bad coding practice. Remember that using On Error Resume Next does not fix errors. It merely ignores them.
The Future Of Error Handling In VBA
Error handling in VB6 and VBA is based on the On Error statement, which leads to awkward code structure. Languages like C++ provide a code structure call Try/Catch that allows much more granularity and control. At some point, Microsoft will introduce their NET framework in to Office, and when this happens, VBA programmers will have at their disposal the language features of Try/Catch/Finally code structure that VB.NET developers already enjoy.
| 
