VBA Selective Error Handling

Question

How can I trap only particular errors in VBA?

So far I have been raising another error with same number in error handler( the one that I don't want to use for this particualr error) to pass the error to another handler, but that makes my code bloated.

I was wondering if there is a better way?

Example of how I have been doing this thus far:

sub correct()
    On Error GoTo CorrectHandler
    wrong
CorrectHandler:
    'some code to handle out of range error (aka Err.Number 9)
End Sub

Sub wrong()
    Dim BadArray(1 To 1) as Variant
    On Error GoTo WrongHandler
    BadArray(100) = 1
WrongHandler:
    Select Case Err.Number
        Case 9
            Err.Raise 9
        'Code to handle other errors
    End Select
End Sub

Answer 1

Typically you would do this the other way around unless you want to have a Select Case that includes all possible errors you can run into. The typical use for doing something like this is if the error is recoverable. If you want to do it in the context of the error handler (harder to debug if it isn't recoverable because you lose the information from the original error), I'd do something like this:

Sub correct()
    On Error GoTo CorrectHandler
    wrong
    Exit Sub
CorrectHandler:
    Debug.Print Err.Number
End Sub

Sub wrong()
    Dim BadArray(1 To 1) As Variant

    On Error GoTo WrongHandler

    Dim i As Integer
    i = 1 / 0
    BadArray(100) = 1

    Exit Sub
WrongHandler:
    If Err.Number = 11 Then
        Debug.Print "I can divide by 0, no problem."
        Resume Next
    Else
        Err.Raise Err.Number
    End If
End Sub

Generally though, it's better to handle these inline. Turn off the error handler, and then test for an error number:

Sub wrong()
    Dim BadArray(1 To 1) As Variant
    Dim i As Integer

    'I think the next line might fail for some reason...
    On Error Resume Next
    i = 1 / 0
    If Err.Number = 11 Then
        Debug.Print "I can divide by 0, no problem."
    ElseIf Err.Number <> 0 Then
        Debug.Print "What the hell? Didn't expect that."
        Err.Raise Err.Number
    End If
    'Turn error handling back on.
    On Error GoTo 0

    BadArray(100) = 1
End Sub

In this case, the out of bounds assignment throws but it get caught in the calling function's handler.