Problems with error trapping using Excel macros (Visual Basic coding)

Hi Kermer,

Thank you for raising this question.

Based on the behavior shown in your sample, the issue appears to be related to how VBA manages an active error handler rather than a problem with On Error GoTo 0.

When Sheets(Name).Select raises an error, execution transfers to NEXTA, making that handler active. The code then falls through to NEXTB without executing a Resume, Exit Sub, Exit Function, or Exit Property statement. As a result, the procedure remains within the original error-handling context, which can prevent subsequent On Error GoTo NEXTA statements from behaving as expected.

A minimal change would be to explicitly exit the active handler by using:

NEXTA:
    Call Add_A_Name(Name, EnvNo)
    Resume NEXTB

As an alternative approach, you could follow the pattern suggested by Bruno Caetano de Brito and check whether the worksheet exists before attempting Sheets(Name).Select. This avoids using error handling as part of the program's execution flow and can help prevent similar issues when the code runs multiple times.

I hope this helps clarify why the behavior occurs and why On Error GoTo 0 alone does not resolve it in this scenario.

If you found my response helpful or informative, I would greatly appreciate it if you could follow this guide for your confirmation. Thank you.

Hello Al Kremer,

This is expected VBA behavior, and the cause is in your error-handling flow, not in On Error GoTo 0.

Why it happens

When Sheets(Name).Select fails, VBA jumps to NEXTA, and that error handler becomes active. A handler stays active until VBA executes a Resume, Exit Sub, Exit Function or Exit Property statement. Your code never runs any of these. It just falls through from NEXTA into NEXTB, so the procedure is still “inside” the first error.

On Error GoTo 0 does not end the active handler. It only disables the enabled one. Because of that, the next time through, the new On Error GoTo NEXTA can’t trap anything. The official On Error statement documentation states that if an error occurs while a handler is active, the current procedure’s handler can’t handle it. Control then goes back to the calling procedure, or the error becomes fatal if no caller handles it.

Fix 1: minimal change (end the handler with Resume)

vb

    On Error GoTo NEXTA
    Sheets(Name).Select
    GoTo NEXTB

NEXTA:
    Call Add_A_Name(Name, EnvNo)
    Resume NEXTB          ' ends the active handler and clears the error

NEXTB:
    On Error GoTo 0

Fix 2 (recommended): don’t use an error handler for control flow

Test whether the sheet exists instead of jumping to labels:

vb

    Dim sh As Object
    Set sh = Nothing                 ' important inside a loop
    On Error Resume Next
    Set sh = Sheets(Name)
    On Error GoTo 0

    If sh Is Nothing Then
        Call Add_A_Name(Name, EnvNo)
    Else
        sh.Select
    End If

This version keeps the same behavior as your original code. It needs no labels and works on every pass through a loop. The Set sh = Nothing line matters: without it, a sheet found on an earlier pass would still be stored in sh.

Hope this helps. If it does, please consider accepting the answer.

Fonte: On Error statement – Microsoft Learn