The On Error statement | VBA Jump Statements

Despite best efforts to avoid errors, we must accept the possibility of bugs in our software. We must anticipate errors that could occur during program execution (i.e. runtime). Rather than leave the user frustrated with these bugs, we must handle them via code. That’s where the On Error statement comes in.

The On Error and Resume statements are the cornerstones of error-handling subroutines. Control jumps to these error-handlers when a “handled” runtime error occurs. They are a standard feature of high-quality VBA programs.

Introducing the On – Error and Resume Statements

Transferring control to/from error-handling subroutines is the On Error statement forte. These subroutines are not the same as Sub, Function, or Property procedures. In fact, they can only exist within those procedures.

Moreover, they don’t have opening and closing block declarations. Instead, they are code-lines wrapped by labelled (top) and Resume (bottom) statements.

Also, the On Error statement and its error-handler must be in the same Sub, Function, or Property procedure. Otherwise, a compilation error occurs.

A runtime error can occur inside or outside the procedure containing the On Error statement. That is, it can occur either in a caller procedure or in a called procedure.

Without the On Error statement, any runtime error that occurs is fatal. So, the runtime error causes an error message to pop up to (frustrate!) the user and stops execution.

Also, before a runtime error occurs the presence of an On Error statement enables the error-handler. The error triggers the error-handling subroutine, making it active.

Types of On Error Statements

The header image above shows the On Error and Resume statements’ syntax. You can see that the On Error statement has two primary forms: On Error Resume Next and On Error GoTo.

Also, the On Error GoTo form has two subtypes: On Error GoTo line and On Error GoTo 0. Moreover, the On Error GoTo line subtype has three forms. Those are On Error GoTo line – Resume Next, On Error GoTo line – Resume line, and On Error GoTo line – Resume 0.

Whew! I hear you, one hierarchical chart coming right up!

The On Error Resume Next Statement

The On Error Resume Next statement jumps control to the line right after the line where the error occurred. Program execution continues without fixing the error.

The user is none the wiser as no error message pops up. However, the error’s side-effects (if any) may return for their pound of flesh later on in the program.

The flowchart below illustrates the On Error Resume Next statement’s logic flow.

The sample code below shows the On Error Resume Next statement in action:

• The On Error statement (code window line #8) enables the Sub procedure’s error handlers. However, no subroutines become active when a runtime error occurs. That’s because we used the On Error Resume Next form;
• A Double variable, dbl_Number, is declared (line #11) to store the result of the procedure’s computations;
• The result of dividing ‘1’ by ‘0’ is assigned to variable dbl_Number (line #14). As you know, dividing any number by zero produces an error. So, the procedure generates a divide by zero error here;
• Instead of stopping execution and displaying an error message, execution continues on the next line. That’s the effect of the On Error Resume Next statement in line #8;
• In line #16, the procedure prints a message to the Immediate window. It does this using the Debug.Print method of the Debug object.

The On Error Resume Next statement is commented out in the image below. Now execution stops at the erring line and an error message pops up. You should set the VBA editor’s Error Trapping option to “Break on Unhandled Errors” before running this sample code.

The On Error GoTo 0 Statement

The On Error GoTo 0 statement disables all error-handlers in a procedure. The sample code below shows the synergetic use of the On Error Resume Next and On Error GoTo 0 statements. The former being an error-handler enabler and the latter being an error-handler disabler.

Typically, we want to ignore one type of error while handling another type in the same procedure. So the On Error Resume Next and On Error GoTo 0 statements are often used in tandem to turn error-handling on and off.

• The On Error statement (line #8) enables the Sub procedure’s error handling subroutines;
• A Double variable, dbl_Number, is declared (line #11) to store the result of the procedure’s computations;
• The result of dividing ‘1’ by ‘0’ is assigned to variable dbl_Number (line #14). As expected, that division generates a divide by zero error;
• Because of the On Error Resume Next statement in line #8, execution continues on the next line. Program execution doesn’t stop and no error message pops up;
• In line #16, the procedure prints a message to the Immediate window using the Debug.Print method.
• The On Error GoTo 0 statement (line #19) disables the Sub procedure’s error handlers;
• The division in line #27 reproduces the divide by zero error. Now execution stops and an error message pops up.

Although it may seem so, the On Error GoTo 0 statement doesn’t jump control to a line with line number 0. Even if such a line exists in a procedure, the statement still acts as described above. Also, once a procedure goes out of scope (i.e., ends), its error-handlers are automatically disabled.

The On Error GoTo line Statement

The On Error GoTo line statement jumps control to the error-handler identified in its line argument (i.e., line label or number). Moreover, a Resume statement always accompanies the On Error GoTo line statement. It could be a Resume Next, Resume line, or Resume 0 (or Resume) statement.

A Resume statement decides where execution continues after an error-handler has run. It is the last statement at the bottom of the error-handling subroutine.

An error occurs if you use the Resume statement outside an error-handling subroutine. That’s because it must always run as a result of an active On Error GoTo line statement.

The Exit statement is also vital to the proper use of the On Error GoTo line statement. Like the Resume statement, it must always follow the On Error GoTo line statement. However, it sits right before the first error-handler’s labelled statement. There, it stops any subroutine from running, unless an On Error GoTo line statement triggered the subroutine.

Without the Exit statement, error-handlers run even when an error didn’t occur. That is, execution falls through the error-handlers, triggering a runtime error. That’s because Resume statements, at the bottom of each subroutine, run outside active On Error GoTo line statements.

The On Error GoTo line – Resume Next Statement

The Resume Next statement jumps control from the error-handler to the statement right after the erring line. All that takes place within the same procedure.

If the error-handler is in a caller procedure and the erring line is in a called procedure, then execution resumes in the caller procedure. Moreover, execution resumes at the line right after the statement that last called out of the caller procedure.

So, with the On Error GoTo line – Resume Next statement, the called procedure does not run again.

The flowchart below shows the On Error GoTo line – Resume Next statement’s logic flow.

The sample code below shows the On Error GoTo line – Resume Next statement in action. The labelled statements, line1 (line #18), line2 (line #18), and line3 (line #26) identify the start of error-handlers. These subroutines end with Resume Next, Resume Line, and Resume 0 statements, respectively.

The following occurs when this sample code runs:

• The On Error GoTo line statement (line #7) enables the Sub procedure’s error handlers;
• A Double variable, dbl_Number, is declared (line #9) to store the result of the procedure’s computations;
• Labelled statement, lineA (line #12), gets a number from the user via the InputBox function. Then it assigns the result of inverting that number to variable dbl_Number. So, by entering ‘0’ in the InputBox, the user generates a divide by zero error;
• The error activates the On Error GoTo line statement (line #7). So, control jumps to the error-handler at the labelled statement, line1 (line #18);
• The error-handler prints “Divisor can’t be 0! Expect invalid result.” to the Immediate window (line #19). Then its Resume Next statement (line #20) jumps control to the line right after the erring line. That would be the labelled statement, lineB (line #13);
• Labelled statement, lineB (line #13), prints variable dbl_Number‘s value to the Immediate window;
• The labelled statement, lineC (line #16), stops the execution of any error-handler that follows it. It does this using an Exit statement, which instantly ends the Sub procedure’s execution. So, an error-handler only runs if it was triggered by an active On Error GoTo line statement.

The image below shows the result produced by the sample code described above. The Resume Next statement ignores the error, so we get infinity (inf) as the result of inverting ‘0.’

The On Error GoTo line – Resume line Statement

The Resume line jumps control from the error-handler to the labelled statement identified by its line argument (i.e., line label or line number). All that takes place within the same procedure.

If the error-handler is in a caller procedure and the erring line is in a called procedure, then execution resumes in the caller procedure. Also, execution resumes at the labelled statement named in the Resume line statement.

So, with the On Error GoTo line – Resume line statement, the called procedure may or may not run again. It runs again if the labelled statement is before (or at) the line that last called out of the caller procedure. Otherwise, it does not.

The flowchart below shows the On Error GoTo line – Resume line statement’s logic flow.

The sample code below shows the On Error GoTo line – Resume line statement in action. It is identical to the On Error GoTo line – Resume Next statement’s sample code discussed earlier, except for one thing. Here, the line argument of the On Error GoTo line statement (line #7) is line label line2, and not line1.

A divide-by-zero error occurs in lineA (line #12) if the user enters ‘0’ in the InputBox that pops up at runtime. So, the On Error GoTo line statement (line #7) jumps control to the error-handler starting at the labelled statement, line2 (line #22).

As shown in the image below, the error-handler at line2 first prints “Divisor can’t be 0! Procedure exited prematurely.” to the Immediate window (line #23). Next, its Resume line statement (line #24) jumps control to lineC (line #16). Lastly, the Exit statement in the labelled statement, lineC, immediately ends the procedure’s execution.

The On Error GoTo line – Resume 0 Statement

The Resume 0 (or Resume) statement jumps control from the error-handler back to the statement the error occurred in. All that takes place within the same procedure.

If the error-handler is in a caller procedure and the erring line is in a called procedure, then execution resumes in the caller procedure. Also, execution resumes at the statement that last called out of the caller procedure.

So, with the On Error GoTo line – Resume 0 statement, the called procedure runs again. Moreover, it does so until the error-handling subroutine fixes the runtime error.

The flowchart below shows the On Error GoTo line – Resume 0 statement’s logic flow.

The sample code below shows the On Error GoTo line – Resume 0 statement in action. It is identical to the On Error GoTo line – Resume Next statement’s sample code discussed earlier, except for one thing. Here, the line argument of the On Error GoTo line statement (line #7) is line label line3, and not line1.

A divide-by-zero error occurs in lineA (line #12) if the user enters ‘0’ in the InputBox that pops up at runtime. So, the On Error GoTo line statement (line #7) jumps control to the error-handler starting at the labelled statement, line3 (line #26).

As shown in the image below, the error-handler starting at line3 first prints “Divisor can’t be 0! Enter a valid divisor.” to the Immediate window (line #27). Next, its Resume 0 statement (line #28) jumps control back to the erring line. That would be the labelled statement, lineA (line #12).

So, the erring line, lineA, repeats until the error-handler fixes the error (i.e. user enters a non-zero number). When the error is fixed, the Debug.Print method in the labelled statement, lineB (line #13), prints the operation’s result to the Immediate window. Lastly, the Exit statement in lineC (line #16) immediately leaves the procedure.