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.

Sub TestErrorIgnore()
On Error Resume Next
  ActiveWorkbook.Sheets("Sheet4").select
  ActiveSheet.Delete
'more code here
End Sub

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.

Sub TestErrorIgnore()
  Dim ws As Worksheet
  On Error Resume Next
  Set ws = ActiveWorkbook.Sheets("Sheet4")
  ws.Delete
'more code here
End Sub

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.

Sub DeleteSheets()
 Dim ws As Worksheet 
 For Each ws In ActiveWorkbook.Sheets
  ws.Delete
 Next ws 
End Sub

The code above will stop with an error.

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.

Sub DeleteSheets()
'add error line here
  On Error Resume Next
  Dim ws As Worksheet 
  For Each ws In ActiveWorkbook.Sheets
    ws.Delete
  Next ws 
End Sub

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.

Sub TestErrorIgnore() 
  Dim ws As Worksheet 
  On Error Resume Next 
  Set ws = ActiveWorkbook.Sheets("Sheet4") 
  ws.Delete
  On Error GoTo 0
'more code here 
End Sub