[VBA] How to Retry the Original Operation After Error Handling (Resume)

Introduction

When handling errors in VBA, you may need more advanced control than simply notifying the user and ending the macro. Sometimes, you want to “programmatically fix the cause of the error and try the failed operation again.”

For example, if a “sheet does not exist” error occurs, you might want the macro to automatically create the sheet and then attempt to write to it again.

The Resume statement allows you to achieve this kind of “error recovery and retry.”

In this article, I will explain a practical error handling technique using On Error GoTo and Resume to automatically fix problems and re-execute the original process when an error occurs.

Sample Code: Error Recovery and Retry Using Resume

This macro attempts to write a value to cell B2 of a sheet named “Report.” If the Report sheet does not exist, the error handler activates, creates the sheet, and then uses Resume to retry the original writing process.

Complete Code

' Automatically create the sheet and retry if it does not exist
Sub RetryAfterCreatingSheet()
    Const TARGET_SHEET_NAME As String = "Report"

    '--- Set Error Handler ---
    On Error GoTo CreateSheetHandler
    
    '--- Normal Processing ---
    ' This line may cause "Subscript out of range" (Error 9)
    Worksheets(TARGET_SHEET_NAME).Range("B2").Value = "Process Completed."
    
    MsgBox "Writing to the report sheet is complete."
    
    Exit Sub

'--- Error Handling Block ---
CreateSheetHandler:
    ' Check if the error is "Subscript out of range" (Sheet not found)
    If Err.Number = 9 Then
        ' Fix the cause of the error (missing sheet)
        MsgBox "The sheet [" & TARGET_SHEET_NAME & "] does not exist. Creating a new one.", vbInformation
        Worksheets.Add(Before:=ThisWorkbook.Worksheets(1)).Name = TARGET_SHEET_NAME
        
        ' Resume statement restarts processing from the line where the error occurred
        Resume
    Else
        ' For other unexpected errors
        MsgBox "An unexpected error occurred. Processing stopped." & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Description: " & Err.Description, vbCritical, "Error"
        ' End without Resuming
    End If

End Sub

Explanation of the Code

The Resume Statement

This is the core part that allows the process to retry.

• Resume: When you place this statement at the end of an error handling block, the program jumps back to the line that caused the error and executes that line again. Resume 0 behaves exactly the same as Resume.

In this example, if an error occurs at the line Worksheets(TARGET_SHEET_NAME).Range("B2").Value = ..., the error handler creates the sheet. Then, Resume causes the program to retry the Worksheets(...) line. Since the sheet now exists, the operation succeeds.

Difference from “Resume Next”

There is a similar statement called Resume Next.

• Resume: Restarts from the same line where the error occurred.
• Resume Next: Restarts from the line following the one where the error occurred.

You use Resume Next when you want to achieve behavior similar to On Error Resume Next (ignoring the error and moving on) but within the structure of an On Error GoTo handler.

Summary

In this article, I explained advanced error handling using the Resume statement to retry the original operation after an error.

• Sometimes, the cause of an error can be resolved programmatically within the error handler.
• Use Resume to return to the line that caused the error and retry it.
• Use Resume Next to resume processing from the line immediately following the error.

By mastering Resume, you can build very robust and user-friendly macros that possess self-repairing capabilities, rather than just stopping whenever an error occurs.