How to use error handling for my Excel VBA code

Question

I am trying to add error handling for one of the code. I am assigning worksheet with specific name to the sheet objects, now I am trying to add OnError statements if in case user changes the names of sheets then code should gracefully exit the sub after displaying a message to correct the file names.

But the problem is as I provide the structure as below then the sub exit code always executes even if the file name is ok.

The problem is, as I provide the details in the structure as below then the sub exit code always executes even if the file name is ok.

Private Sub DoSomething()

    On Error GoTo CleanFail

    '...code...

CleanExit:
    'cleanup code here
    Exit Sub

CleanFail:
    If Err.Number = 9 Then 'subscript out of range
        Err.Clear
        Resume Next
    Else
        MsgBox Err.Description
        Resume CleanExit
    End If
End Sub

On Error GoTo ErrorFailLoad
    Set LoadSheet = Application.ActiveWorkbook.Worksheets("Load_Template")
ErrorExit:
  End Sub
ErrorFailLoad:
    MsgBox "Main File name must be Load_Template. Please change and re-run Macro"
    Resume ErrorExit



On Error GoTo ErrorFailEquip
    Set EquipSheet = Application.ActiveWorkbook.Worksheets("EQUIP")
ErrorExit:
  End Sub
ErrorFailEquip:
    MsgBox "Equipment File name must be EQUIP. Please change and re-run Macro"
    Resume ErrorExit

I expect the code to just check if the file name is not available then just show error message and exit. Actual results is code exit even after correct file name.

Error blocks are not "skipped" if the code above triggers no error - execution continues onto the next lines, which is why error handling typically goes at the end of the method (somewhere below `Exit Sub`), not in the middle. For tasks like this using a separate function might be easier to manage - eg https://stackoverflow.com/questions/6688131/test-or-check-if-sheet-exists/6688482#6688482 — Tim Williams, May 08 '19 at 06:18
You might benefit from reading [VBA Error Handling – A Complete Guide](https://excelmacromastery.com/vba-error-handling). — Pᴇʜ, May 08 '19 at 06:26

How to use error handling for my Excel VBA code

0 Answers0