Error handling refers to the programming practice of anticipating and coding for error conditions that may arise when your program runs. In programming VBA, errors generally come in four flavours:
- Syntax errors occur from typographical errors, missing punctuation, or improper use of a language element;
- Compiler errors such as undeclared variables that prevent your code from compiling;
- Logic errors, which are more commonly referred to as bugs, which occur when code executes without causing an error, but does not produce the results intended;
- Run time errors that occur when VBA cannot correctly execute a program statement. Typical run time errors include attempting to access a non-existent worksheet or workbook, or attempting to divide by zero.
I’ll only look at run time errors and the example code in this article will use the Error 11 division by zero error to deliberately raise an error in the examples.
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. Furthermore, if you have any module level or public variables then the values held in those variables will be lost.
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. This also ensures that if you have any module level or public variables then the values held in those variables will be retained. Your goal should be to prevent unhandled errors from arising.
Note: Throughout this article, the term procedure should be taken to mean a Sub, Function, or Property 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 a 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 occurred. However, the error may have side effects, such as uninitialized variables or objects set to Nothing.
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
' error handling code
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 an 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 transferred via an 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 the On Error Goto <label>: statement to 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: label. The error handler 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 as it safely clears the Err object ready for the next error. 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 and the Err object does not get reset.
The Resume statement takes three syntactic form:
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 an 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.
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.