Return to VBA Code Examples

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:

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:

vba runtime error 13

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.

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:

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.

VBA OnError Error

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.

 

VBA OnError ShowSheet

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.

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.

VBA OnError Sheet 1004

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.

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.

VBA OnError CustomMsg

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.

VBA OnError Debug

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 OnError ExitSub

 

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! vba save as


Learn More!