In this post I will explain why we need to use On Error GoTo 0 in error handling. I will explain the use of On Error GoTo 0 using a simple example.
Assume we need to make changes in different workbook. So first we need to declare the variable and set the workbook as follows.
|
Dim WB_Example As Workbook Set WB_Example = Workbooks("Example File.xlsx") |
So if we run above two lines variable WB_Example will be declared and workbook "Example File.xlsx" will be assigned to that variable. But this will be successful only if "Example File.xlsx" is in open state. Otherwise it will give below error.
If you click the debug button second line will be highlighted as follows
However if this workbook is not in open state, we can open it automatically and continue the rest of the code. For that we need to avoid that error message. Error message can be avoided and resume code using On Error Resume Next statement. We can do it as follows.
|
Dim WB_Example As Workbook On Error Resume Next Set WB_Example = Workbooks("Example File.xlsx") If Err <> 0 Then If Err.Number = 9 Then Workbooks.Open ("D:\VBA Blog\On Error Goto Zero\Example File.xlsx") Else MsgBox "Unknown Error.", vbCritical, "warning!" Exit Sub End If End If |
So if there is an error, program will check whether error number is equal to 9. If it equals to 9, Then program will automatically opens the workbook. Because we know that this error occurs at this point due to workbook is not in open state. But if the error number is different to 9, error occurred due to some other reason. So program will notify it to the user and terminated at that point.
Think we run above code and didn't get any other Err.Number different to 9. So then our program will continue from above code. So this is where we need On Error GoTo 0. Consider the following code. If you look at the last line you will see that we are trying to put a string to a cell A1 of sheet2. But what will happen if we don't have worksheet with name "Sheet2". Progrma should give us an error.
|
Dim WB_Example As Workbook On Error Resume Next Set WB_Example = Workbooks("Example File.xlsx") If Err <> 0 Then If Err.Number = 9 Then Workbooks.Open ("D:\VBA Blog\On Error Goto Zero\Example File.xlsx") Else MsgBox "Unknown Error.", vbCritical, "warning!" Exit Sub End If End If WB_Example.Worksheets("Sheet2").Range("A1").Value = "This is an example" |
But if you run the code, you will realized that it is not giving such error if there is no Sheet2 available. So this happen due to effect of On Error Resume Next statement. So this On Error Resume Next statement was helpful up to particular point of code. But after that we are getting unexpected problem due to it. So now we need to end effect of On Error Resume Next statement in a earliest possible point of our code. This can be done by using On Error GoTo 0 statement.
|
Dim WB_Example As Workbook On Error Resume Next Set WB_Example = Workbooks("Example File.xlsx") If Err <> 0 Then If Err.Number = 9 Then Workbooks.Open ("D:\VBA Blog\On Error Goto Zero\Example File.xlsx") Else MsgBox "Unknown Error.", vbCritical, "warning!" Exit Sub End If End If On Error GoTo 0 WB_Example.Worksheets("Sheet2").Range("A1").Value = "This is an example" |
So now if we run the macro again and if there is no Sheet2 in our workbook, then we will get this error message.
So if you click the Debug button, last line will highlighted as follows.
So you can see that effect of On Error Resume Next statement is avoided by On Error GoTo 0.
