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

Question

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

Al Kremer 0

I am developing an Excel macro and am having trouble with error trapping.

Here is the actual code I am using:

'Select Name Tab

   On Error GoTo NEXTA

   Sheets(Name).Select

   GoTo NEXTB

NEXTA:

   Call Add_A_Name(Name, EnvNo)

NEXTB:

   On Error GoTo 0

The first time this code is executed, it works fine. However, the second time through the 'On Error GoTo NEXTA' statement is ignored, despite the fact that it is reset with the 'On Error GoTo 0' code. I have experienced this problem before using other logic but before, I was able to work around the problem. This time I can't. Why?

0 comments

1 answer

Your answer

Answer 1

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