VBA On Error Resume Next or Goto 0

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

Let’s walk through an example to show how you might use On Error Resume Next in practice. This code will hide each worksheet in the workbook:

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.

errhandler:

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 Easy

Stop 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!


Learn More!