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