This tutorial will demonstrate how to use the VBA On Error Resume Next and On Error Goto 0.
Read our Error Handling Tutorial for more information about VBA error handling.
On Error Resume Next
On Error Resume Next tells VBA to skip lines of code containing errors and proceed to the next line. It works like this:
Sub OnErrorResumeNextDemo() On Error Resume Next MsgBox 5 / 0 MsgBox "Done" End Sub
The line MsgBox 5 / 0 would throw an error (you can’t divide by zero). By adding On Error Resume Next, VBA will skip that line and run the rest of the procedure. You can test this on your own by copying + pasting the code and commenting out On Error Resume Next.
On Error GoTo 0
On Error GoTo 0 is VBA’s default setting. When an error occurs with On Error GoTo 0, VBA will stop executing code and display its standard error message box, for example:
There’s no need to declare On Error GoTo 0 unless you’ve declared some other error handling earlier in your procedure.
If you have added an error trap like On Error Resume Next in your code, you can insert On Error Goto 0 to restore VBA’s default error handling.
Sub OnErrorResumeNextDemo() On Error Resume Next MsgBox 5 / 0 On Error GoTo 0 MsgBox "Done" End Sub
This is important to do! On Error Resume Next should be used with caution and only when absolutely necessary.
On Error Resume Next Example
Sub HideAllSheets() Dim ws As Worksheet For Each ws In ActiveWorkbook.Sheets ws.Visible = False Next ws End Sub
If we run this code, we will get an error as we cannot hide all the worksheets in a workbook – at least one workbook has to remain visible.
However, if we add On Error Resume Next to the procedure as shown below, the code will continue past the error and leave the last sheet in the workbook visible.
Sub HideAllSheets() On Error Resume Next Dim ws As Worksheet For Each ws In ActiveWorkbook.Sheets ws.Visible = False Next ws End Sub
Now let’s say we want to add some more code to our procedure. Next let’s add On Error GoTo 0 to restore VBA’s default error handling.
Sub ErrorGoTo0() On Error Resume Next Dim ws As Worksheet For Each ws In ActiveWorkbook.Sheets ws.Visible = False Next ws On Error GoTo 0 'Run More Code here for example: ActiveSheet.Name = "Sheet1" End Sub
Now if an error occurs after the On Error Goto 0 line, the default error message for Excel would occur. In this example, we already have a sheet named Sheet1 in the ActiveWorkbook as the code above the On Error Goto 0 hides the sheet, but does not delete it. The default Excel error message indicating that the sheet name has already been taken would then show.
On Error Goto To Line
We can also force our code to move to a different line of code by using the On Error Goto Line (Learn More) which tells VBA to go to a specific line of code if an error occurs.
Sub ErrorGoToLine() On Error Resume Next Dim ws As Worksheet For Each ws In ActiveWorkbook.Sheets ws.Visible = False Next ws On Error GoTo errhandler ActiveSheet.Name = "Sheet1" Exit Sub errhandler: MsgBox("There is already a sheet called sheet1!", vbCritical) End Sub
In the above example, when the code finds the sheet “Sheet1”, it moves to the line of code below the error handler label – in this case it calls a custom message box informing the users that the sheet already exits. The error handler label has to have a colon after it to show VBA that it is a label.
The code will then jump down to the line below the label and return the custom message box.
This is useful when you do not want your user being able to click Debug to go into your code as the standard Excel message always gives an option to Debug the code.
We also need an Exit Sub in the procedure. If there is NOT a sheet called Sheet1, then that line of code would run and rename the active sheet to Sheet1. We then want the code to end – we do not want it carrying on to the error handler and showing the message box. The Exit Sub line then exits the procedure and stops the code.
VBA Coding Made EasyStop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!