Return to VBA Code Examples

VBA Ignore Error

This tutorial will demonstrate how to ensure that VBA Ignores an Error when running your code.

Sometimes when you construct VBA code, you actually want VBA to ignore a line of code when an error occurs. This can actually be quite useful if, for example, you want to delete a sheet if it exists, but ignore that line of code if the sheet doesn’t exist.

(See our Error Handling Guide for more information about VBA Errors)

On Error Resume Next

To ignore a line of code in a procedure, we use the On Error Resume Next statement.

In the example above, an On Error Resume Next statement has been placed at the top of the procedure. The code is then constructed to select Sheet4 and then delete it.

However, due to the On Error Resume Next statement, if the code does not find Sheet4, it will just carry on to the next line, and delete whichever active sheet it finds.

This can be quite dangerous if it was only Sheet 4 you wanted to delete and not just the Sheet that is active. To prevent this error, we can set a variable to sheet 4 and delete only that sheet IF it exists.

Now when the code is run, only Sheet4 will be deleted IF Sheet4 actually exists.  If Sheet 4 does not exist, VBA will ignore the error and carry on going.

Another example of this is if you want to delete all the sheets from your workbook using a loop.   As an Excel workbook has to have at least one sheet, the code will delete all the sheets except one.

The code above will stop with an error.

VBAIgnoreError

Clicking Debug will take us into the code with the offending line highlighted!

However, adding the On Error Resume Next line to the code will prevent the error and the routine will always leave one sheet in the workbook.

On Error GoTo 0

Often if you use On Error Resume Next to ignore an error, you want error trapping later on in the code, or you want your code to stop running if an error occurs in the future.   We can re-set the error handling so that the code will once again break on errors by adding the line On Error GoTo 0.

 

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!