VBA On Error Goto 0

VBA On Error GoTo 0 is an error handler statement used to disable the enabled error handler in the procedure. It is known as “Error Handler Disabler.”

Error handling in any programming language is a master class that all the coders need to understand. VBA programming language too. We also have error handling techniques in this programming language.“On Error Resume Next” enables the error handler, and “On Error GoTo 0” will disable the enabled error handler.

Both “On Error Resume Next” and “On Error GoTo 0” are pairs that one needs to use in tandem for the efficiency of the code. To handle the error, we need to start with the statement “On Error Resume Next” and end this error handler. Next, we need to use the statement “On Error GoTo 0”.

Any line code written between these statements will ignore any error in the proceedings.

For example, look at the below code.

Code:

Sub On_ErrorExample1()    Worksheets("Sheet1").Select    Range("A1").Value = 100    Worksheets("Sheet2").Select    Range("A1").Value = 100 End Sub

The above code will first select the worksheet named “Sheet1,” and in cell A1 it will insert the value 100.

Code:

Worksheets("Sheet1").Select Range("A1").Value = 100

Then it will select the worksheet named “Sheet2” and insert the same value.

Code:

Worksheets("Sheet2").Select Range("A1").Value = 100

Now, we have the below sheets in my workbook.

There are no sheets called “Sheet1” and “Sheet2”. Therefore, when we run the code, it will encounter an error like the one below.

Since there is no sheet named “Sheet1”, it has encountered a “Subscript out of range” error. We will add an error handler statement “On Error Resume Next” at the top of the macro to handle this error.

Code:

Sub On_ErrorExample1()    On Error Resume Next    Worksheets("Sheet1").Select    Range("A1").Value = 100    Worksheets("Sheet2").Select    Range("A1").Value = 100 End Sub

Now, run the code and see what happens.

It would not give any error messages because the error handler statement On Error Resume Next is enabled.

Imagine the scenario where we need to ignore the error in case of non-availability of the worksheet “Sheet1,” but we need to notify if there is no worksheet called “Sheet2.”

Since we have added On Error Resume Next at the top, it has started to handle the error. But at the same time, we need to specify how many lines we need to ignore this error.

In this example, we need to ignore the error for the first worksheet from the second sheet onwards. That is why we need the error to occur if there is no worksheet “Sheet2”. So after the first worksheet code adds the error disable line On Error GoTo 0.

Code:

Sub On_ErrorExample1()    On Error Resume Next    Worksheets("Sheet1").Select    Range("A1").Value = 100    On Error GoTo 0    Worksheets("Sheet2").Select    Range("A1").Value = 100 End Sub

Now, run the code line by line to see the impact by pressing the F8 key.

Now, if you press the F8 key once code execution jumps to the next line, it will execute the active line task. The active line (yellow-colored line) is the “On Error Resume Next” error handler. It will enable the error handler.

Now, any error occurs. We will ignore it until it executes the error handler and disables code “On Error GoTo 0” statement.

In the previous attempt, we encountered errors but pressed the F8 key repeatedly and saw the magic.

Without giving any error, it has resumed executing the code even though there is no worksheet “Sheet2” to select. Now, press the F8 key again.

Since there was no Sheet1, it cannot insert the value in the A1 cell as 500 but what it does is it will insert the value of 500 to cell A1 whichever worksheet is active. For example, when we executed the code, our active sheet was “Sheet3”, so the value of 100 inserts to the cell A1.

Now, the active line of code is “On Error GoTo 0” by pressing the F8 key. It will execute this line task.

Since “On Error GoTo 0” is executed, it has stopped the error handling process and again starts to show errors if any occur. Press the F8 key and see the error.

In the previous case without On Error GoTo 0, it has also ignored this error. But since we added an error handler disabler, it has started to show the error again.

• We must use both "On Error Resume Next" and "On Error GoTo 0" as "Error Handler Enabler" and "Error Handler Disabler."
• Any line of codes between these two statements encounters an error. We will ignore it.
• If there is an On Error GoTo 0 statement, the sub procedure error handler will be disabled after the exit.