vba nested error handling

Exit Sub
ErrorHandlerA:
...
Resume
End Sub

Resume here, resumes normal execution (clears "error handling mode") and jumps back to the instruction that caused the error. Presumably (hopefully?) the ... contains code that makes the previously-failing call, succeed.

cleanUp:
con.close
rs.close

Exit Sub
ErrorHandlerB:
GoTo cleanup

End Sub

Here you're GoTo-jumping outside the error-handling subroutine, without resuming to normal execution, so the instructions under the cleanUp line label execute in an error-handling context. That should be Resume cleanUp, not GoTo.

The Exit Sub statement leaves the scope, which clears the error state and implicitly resumes to normal execution. Here's a little MCVE:

Public Sub Test()
On Error GoTo A
    DoSomething
    Debug.Print "After DoSomething: " & Err.Number
    Exit Sub
A:
    Debug.Print "Inside Test[A]: " & Err.Number
End Sub

Private Sub DoSomething()
On Error GoTo A

    Err.Raise 5

B:
    Debug.Print "Inside DoSomething[B]: " & Err.Number
    Exit Sub
A:
    Debug.Print "Inside DoSomething[A]: " & Err.Number
    Resume B
End Sub

Running the Test procedure produces this output:

Inside DoSomething[A]: 5
Inside DoSomething[B]: 0
After DoSomething: 0

Changing the Resume B instruction for a GoTo B produces this output:

Inside DoSomething[A]: 5
Inside DoSomething[B]: 5
After DoSomething: 0

As you can see, as the error was handled in DoSomething, error state is reset when execution returns to Test, and the handler in the Test procedure is never triggered.

If you want to propagate the error to the caller, you have a number of options:

• Don't handle errors in the called procedure. In the above example this means DoSomething wouldn't have an On Error statement; any run-time errors would "bubble up" the call stack, and the output would look like this now:

  Inside Test[A]: 5
  

  You would typically do this when it's the calling code that has the knowledge of what the best course of action would be (e.g. display a MsgBox to the user, or log the error, etc.).

• Handle errors, and then re-raise them. In your example this means moving (or copying) the cleanup code into the ErrorHandlerB subroutine, and calling Err.Raise Err.Number instead of Resume. Or you keep the GoTo and then the cleanup subroutine can do If Err.Number <> 0 Then Err.Raise Err.Number to effectively re-throw the error for the caller to also handle. In other words:

  Public Sub Test()
  On Error GoTo A
      DoSomething
      Debug.Print "After DoSomething: " & Err.Number
      Exit Sub
  A:
      Debug.Print "Inside Test[A]: " & Err.Number
  End Sub
  
  Private Sub DoSomething()
  On Error GoTo A
  
      Err.Raise 5
  
  B:
      Debug.Print "Inside DoSomething[B]: " & Err.Number
      If Err.Number <> 0 Then Err.Raise Err.Number
      Exit Sub
  A:
      Debug.Print "Inside DoSomething[A]: " & Err.Number
      GoTo B 'resuming would clear the error and prevent rethrow
  End Sub
  

  This outputs:

  Inside DoSomething[A]: 5
  Inside DoSomething[B]: 5
  Inside Test[A]: 5
  

Depending on the exact reason why you would want to handle the same error in two different places, you could also consider making B a Function that returns a Boolean indicating success or failure - in that case A no longer handles an error per se, but rather uses normal flow control to determine what to do:

Public Sub Test()
On Error GoTo A
    If Not DoSomething Then
        Debug.Print "After DoSomething failed: " & Err.Number
        Exit Sub
    End If
    Exit Sub
A:
    Debug.Print "Inside Test[A]: " & Err.Number
End Sub

Private Function DoSomething() As Boolean
'Dim success As Boolean
On Error GoTo A

    Err.Raise 5
    'success = True

B:
    Debug.Print "Inside DoSomething[B]: " & Err.Number
    DoSomething = (Err.Number = 0) 'DoSomething = success
    Exit Function
A:
    Debug.Print "Inside DoSomething[A]: " & Err.Number
    'success = False
    GoTo B 'Resume B
End Function

Which produces this output:

Inside DoSomething[A]: 5
Inside DoSomething[B]: 5
After DoSomething failed: 0

Notice Test knows DoSomething failed, but Err.Number is 0. This is usually preferable to using runtime errors for flow control, again depending on your actual scenario. Also notice, the GoTo jump is avoidable in that situation, using a simple Boolean local variable to track your return value (the commented-out code).