This tutorial focuses on the meaning and implementation of
GoTo 0, and
GoTo -1. These critical components add structure to the VBA
On Error statement and any explicit handlers using
Resume Next goes to the next executable line,
GoTo 0 turns off the currently enabled error handler, and
GoTo -1 turns off the current exception. Of course, these things need a bit more explanation, otherwise we wouldn’t have written an entire tutorial about them!
- Basic Error Handling Overview
- How to Use
Basic Error Handling Overview
For a more thorough exploration of basic error handling techniques, read our article dedicated to the On Error GoTo statement.
Error handling in VBA is disabled by default, so it must be turned on with the
On Error statement. If you want to explicitly handle errors, label a section of code and direct the execution flow to that section:
On Error GoTo fixErrors 'some code that might trigger an error 'On Error GoTo 0 'uncomment to disable fixErrors and enclose the section Exit Sub fixErrors: 'code to fix the error 'Resume Next 'uncomment this to return to original code End Sub
The first line in the snippet turns on (enables) the
To shut off (disable) the active handler, use
On Error GoTo 0. Doing so will close off the code block that uses that handler. Alternatively, exit the subroutine using
Exit Sub, which automatically turns off the handler.
Calling another subroutine does not exit the current subroutine so any active handlers will be stacked in the new subroutine!
Error handling code must be placed before the
End Sub statement, so in order to avoid it running with the regular code, it should come after an
Exit Sub statement.
It’s wise to check inputs for the correct data types, formatting, and common issues like a divisor of zero. This reduces the prevalence of runtime errors before they can even arise.
How to Use
Resume Next plays a dual role:
- as a stand-in for a custom error handler that is marked by a label
- to return execution flow to the main body of the code after a custom handler completes
We’re going to talk about each of these roles in the next two sections.
You can defer handling errors by using
On Error Resume Next, like in this snippet:
On Error GoTo fixErrors 'turns on fixErrors handler On Error Resume Next 'Defers error handling 'code that might error
If you replace
GoTo [label] with
Resume Next, you can defer error handling. If an error is raised, execution simply skips that line of code and goes to the next one. If the next line raises an error, execution just skips that line, too. This definitely has some benefits, and we use it quite a lot on this site, like when checking if a file exists, but you need to be careful when applying it. If used to ignore a specific deficiency you know exists, you could mistakenly skip over an entire section of code without realizing it.
Resume Next to defer handling has its purposes, such as eventually running code that forcibly rectifies the offending variables and objects. But I’d strongly recommend either explicitly handling errors with
GoTo [label] or avoiding errors by checking inputs with IF statements, where appropriate.
The main problem with the “ignore the errors” method is that users will not notice that the code is broken. It will run as long as deferment is active. However, some expected result will simply be incorrect or not be displayed at all. This leads to frustrated users trying to figure out what they did wrong when it was really a runtime problem with your code. Even worse, they may just accept the erroneous output, no matter how outlandish.
Resume Next to defer errors has its place, but use it wisely. A good habit is to warn others (including your future self) that you’re using it. Clearly state this in the comments at the top module and describe why you’re doing it. This habit at least makes others aware that unnoticed errors may be lurking in the code.
Return Execution to the Main Block
Resume Next is not, however, without a positive side. The same statement can return control to the main code block after entering a custom handler. This means you don’t have to end a subroutine every time a custom handler is employed.
On Error GoTo fixErrors students_per_class = num_students / num_rooms MsgBox ("You'll have " & students_per_class & " students per class") On Error GoTo 0 Exit Sub fixErrors: If num_rooms = 0 Then num_rooms = InputBox("Re-enter number of rooms") students_per_class = num_students / num_rooms End If Resume Next
This code snippet predicts that a divide by zero error may be triggered and fixes it with some user interaction. Once the input is received and the new result calculated, the
Resume Next statement returns execution to the line immediately following the line that triggered the error.
If you’d like to return to the same line that triggered the error, you can simply use
Resume, like this:
On Error GoTo fixErrors students_per_class = num_students / num_rooms MsgBox ("You'll have " & students_per_class & " students per class") On Error GoTo 0 Exit Sub fixErrors: If num_rooms = 0 Then num_rooms = InputBox("Re-enter number of rooms") End If Resume
Resume instead of
Resume Next has the added benefit of naturally looping until the user inputs a valid divisor for
Resume Next actually play a third role when in the error handling block: once executed, they reset the current exception to
Nothing, meaning the active error handler can be used again.
Think of the error handler as a single-use tool, which must be reset before it can be used again. We know that ending and exiting the subroutine resets the handler. What this hidden role means is that
Resume Next also resets the error handler.
GoTo 0 and
With VBA, only one error handler can be active at a time. This makes sense, as an error will immediately trigger the active handler, and VBA cannot guess between multiple handlers. To VBA, an error is an error is an error. You can distinguish errors using the
Err object and a
Select Case statement, but that’s for our next tutorial.
Make powerful macros with our free VBA Developer Kit 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 Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.
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 Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.
Enable and Disable Handlers
On Error GoTo 0 to completely turn off error handling in a subroutine. This effectively resets the error handling in that sub to the default, which is no handling at all. You may decide to do this when you only have one handler, like
Note that you do not need to disable a handler before enabling another. The new one simply takes over. However, for clarity, I like to turn off a handler when its section of code ends. In this code snippet, it’s very clear which handlers go with which parts of the code.
On Error GoTo colorPicker Range("A1:A30").Font.Color = colorChoice1 Range("B1:B30").Font.Color = colorChoice2 On Error GoTo 0 On Error GoTo fixErrors students_per_class = num_students / num_rooms MsgBox ("You'll have " & students_per_class & " students per class") On Error GoTo 0
A particular error instance is called an exception. In the following code, there is one type of error but two exceptions, because each instance of the error throws an exception:
Range("A1:A30").Text.Color = vbRed Range("B1:B30").Text.Color = vbBlue 'Range("B1:B30").Font.Color = vbBlue 'this is the correct object property
When the first error is raised, VBA directs flow immediately to the active error handler.
The single error handler rule applies even in the error code! Once an error has been raised, the error handler is completely “full”. In order to handle another error, the handler must be emptied first. Normally this is done by exiting the handler with
End Sub or
Resume Next. However, there is another method, which uses
On Error GoTo -1.
Let’s return to our divide by zero handler
fixErrors: If num_rooms = 0 Then num_rooms = InputBox("Re-enter number of rooms") students_per_class = num_students / num_rooms End If Resume Next
But what happens if the user inputs zero again? The full error handler cannot take another exception and leads to a fatal error, killing the program.
You can reset the handler - that is, remove the current exception - by using
On Error GoTo -1:
fixErrors: On Error GoTo -1 'clears triggering divide by zero error If num_rooms = 0 Then num_rooms = InputBox("Re-enter number of rooms") students_per_class = num_students / num_rooms End If Resume Next
However, there is a flaw here! The
GoTo -1 clears the current exception, and the
Resume Next statement sets off an infinite loop. Execution does NOT return to the original code section.
GoTo -1 can be extremely confusing, I’d recommend not using it at all. If you are adamant about
GoTo -1, use it as a last resort and finish the error handling code with
Now you can use
On Error GoTo 0 and
Resume Next effectively. Unless absolutely necessary, I’d recommend steering clear of
On Error GoTo -1, as it can greatly complicate the logic of your program. I’d also recommend judiciously employing
On Error Resume Next deferment, because it can mean segments of your code just don’t do anything at all.