Excel VBA On Error Goto 0
Error handling in any of the programming languages is a master class that all the coders need to understand. VBA programming language too, and we also have error handling techniques in this programming language. in VBA, “On Error Resume Next” enables the error handler and “On Error GoTo 0” will disable the enabled error handler.
Both “On Error Resume Next” & “On Error GoTo 0” are pairs that need to be used in tandem for the efficiency of the code. To handle error we need to start with the statement “On Error Resume Next” and to end this error handler we need to use the statement “VBA On Error GoTo 0”.
Any line code written between these statements will ignore any kind of error that occurred in the proceedings.
How to Use “On Error GoTo 0” in VBA?
Let’s discuss how to use excel VBA On Error GoTo 0 with example.
For an example look at the below code.
Sub On_ErrorExample1() Worksheets("Sheet1").Select Range("A1").Value = 100 Worksheets("Sheet2").Select Range("A1").Value = 100 End Sub
What the above code does is it will first select the worksheet named “Sheet1” and in cell A1 it will insert the value 100.
Worksheets("Sheet1").Select Range("A1").Value = 100
Then it will select the worksheet named “Sheet2” and insert the same value.
Worksheets("Sheet2").Select Range("A1").Value = 100
Now I have below sheets in my workbook.
There are no sheets called “Sheet1” & “Sheet2”, when we run the code it will encounter an error like the below.
Since there is no sheet named as “Sheet1” it has encountered “Subscript out of range” error. To handle this error I will add an error handler statement “On Error Resume Next” at the top of the macro.
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 won’t 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 for how many lines we need to ignore this error.
In this example, we just need to ignore the error for the first worksheet but for the second sheet onwards 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.
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 will jump to the next line and active line task will be executed. Now the active line (yellow-colored line) is “On Error Resume Next” error handler and the error handler will be enabled.
Now any error occurs it will be ignored until it executes the error handler disables code “On Error GoTo 0” statement.
In the previous attempt, we have encountered errors but press the F8 key one more time and see the magic.
Without giving any kind of error it has resumed execution of the code even though there is not worksheet “Sheet2” to select. Now press F8 again.
Since there was no worksheet 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. My active sheet when I am executing the code was “Sheet3”, so value of 100 is inserted to the cell A1.
Now the active line of code is “On Error GoTo 0”, by pressing the F8 key this line task will be executed.
Since “On Error GoTo 0” is executed it has stopped the process of error handling and again starts to show errors if any occurs. Press the F8 key and see the error.
In the previous case without error disabler statement “On Error GoTo 0,” it has ignored this error as well, but since we have added error handler disabler it has started to show the error again.
Things to Remember here
- Both On Error Resume Next and On Error GoTo 0 needs to be used as “Error Handler Enabler” and “Error Handler Disabler”.
- Any line of codes between these two statements encounters an error it will be ignored.
- If there is On Error GoTo 0 statement then after the exit of the subprocedure error handler will be disabled.
This has been a guide to VBA On Error Goto 0. Here we discuss how to use on error goto 0 statement in excel VBA to handle any sort of errors along with a practical example and a downloadable template. Below you can find some useful excel VBA articles –
- How to Convert VBA String to Date?
- Get Data from Web Pages
- Excel VBA Sqr Function
- Excel VBA SendKeys
- On Error GoTo Types in VBA
- Fix OverFlow Error in VBA
- How to Handle “On Error” in VBA?
- 1004 Runtime Error in VBA