How to do an error handling block inline like Try/Catch

Question

How would I do an inline error handling routine in VBA? I don't want to put the error handler at the end.

This is from CPearson's Error Handling in VBA

Sub testErrHandling()
    On Error GoTo ErrHandler:

    Debug.print 9 / 0 'divide by zero error

    Worksheets("NewSheet").Activate 'missing worksheet error

    'more code here

    Exit Sub

ErrHandler:
    If Err.Number = 9 Then
        ' sheet does not exist, so create it
        Worksheets.Add.Name = "NewSheet"
        ' go back to the line of code that caused the problem
        Resume
    End If
End Sub

But I'm looking for something more like a Try/Catch block in VB.net

Answer 1

This code will handle the error inline. This is a very cleanly structured pattern for handling an error. The flow moves very cleanly from top to bottom; no spaghetti code here.

VBA is an old language and has limitations. One of the ways to use error handling is to use Goto statements in the form of On Error Goto <Label> and Resume <Label>. This creates an opportunity.

Traditionally the error handler is placed at the bottom. But with the advances made in VB.net, it seems reasonable to leverage ideas to improve code. Try/Catch is a very structured way of handling errors and is very easy to follow. This pattern attempts to reproduce that in a very clean concise way. The flow is very consistent and doesn't jump from place to place.

Sub InLineErrorHandling()

    'code without error handling

BeginTry1:

    'activate inline error handler
    On Error GoTo ErrHandler1

    'code block that may result in an error
    Dim a As String: a = "Abc"
    Dim c As Integer: c = a 'type mismatch

ErrHandler1:

    'handle the error
    If Err.Number <> 0 Then

        'the error handler is now active
        Debug.Print (Err.Description)

    End If

    'disable previous error handler (VERY IMPORTANT)
    On Error GoTo 0
    'exit the error handler
    Resume EndTry1

EndTry1:

    'more code with or without error handling

End Sub

Sources:

• Pearson Error Handling In VBA
• How to: Handle Run-Time Errors in VBA
• Properly Handling Errors in VBA (Excel)

Properly managed this works quite nicely. It is a very clean flowing pattern that is reproducible anywhere it is needed.

Answer 2

You can try assigning your object in a variable and use On Error Resume Next instead.

Dim sh As Worksheet

'This is essentially the "Try" part
On Error Resume Next 'this ignores the error
Set sh = Worksheets("NewSheet")
On Error Goto 0 'this resets the active error handling routine

'Then this is the "Catch" part I guess
If sh Is Nothing Then 'check is something is assigned to sh
    'And I think this is "Finally" part
    Set sh = Worksheets.Add: sh.Name = "NewSheet" 'add otherwise
End If

Not really familiar with the Try/Catch since I've not done some VB.Net but this is the closest inline error correction I can think of for your example. HTH.

Answer 3

After months of insecurity/confusion about some obscure laws of VBA error handling, never fully satisfied by any of the popular documentation pages (Microsoft VBA/VB, C. Pearson and a others), I dedicated some days of trial and error to reconstruct the
complete set of the (written and unwritten/corrected) rules governing VBA error handling:

LEGEND:
1A: On Error Goto 0. 1B: On Error GoTo line/label. 1C: On Error Resume Next
2A: an error occurs. 2B: On Error GoTo -1
3: being in a subprocedure, temporarily, just until coming back
4: Erl
5: Resume, Resume Next, Resume line/label
6: Exit […], End […]

RULES:

• With 1A in effect*, error handling is and stays disabled and inactive. This is the default.
• With 1B in effect*, error handling is initially enabled and inactive; it's disabled while 3 and by 2A or 6, and it's activated on 2A and inactivated while 3 and by 5 (that also reenable it) or 6.
• With 1C in effect*, error handling is initially enabled and inactive; it's disabled only while 3 and by 6 (it's not disabled by 2A!), and it always stays inactive (supposedly activated and inactivated immediately on 2A).
• *= If 1A, 1B, 1C are called while error handling is active, the Err object is cleared immediately but the On Error action change effect is delayed until error handling is inactivated (by 5 or 6).
• 2B (instantaneously) inactivates error handling (if any); error handling returns(1B)/stays(1C) enabled.
• 1A, 1B, 1C, 2B, 5, 6 also instantaneously clears the Err object.
• If an error occurs while in a subprocedure, if unhandled (disabled) in the current subprocedure it's passed to the first calling (parent) procedure where it's enabled and inactive. If no one is found, it stays there (in the subproc.).
• 4 if error handling is active returns the line (if specified as a number label) of last error, otherwise 0.
• 5 (correctly) crashes (err 21: "Resume without error") if called while error handling is inactive.
  
  

With all these rules fresh in mind, and starting from the D_Bester's solution posted here, here's my revised solution correcting a couple of errors/inefficiences in his code:

    Sub InLineErrorHandling()
    
        'code without error handling

        On Error GoTo ErrHandler1  'enable error handler
        'code block that may result in an error
        Dim a As String: a = "Abc"
        Dim c As Integer: c = a 'type mismatch

        'inline error handler routine
    ErrHandler1:
        If Err.Number <> 0 Then
            Debug.Print err.Description
            On Error GoTo -1 ' inactivate error handler
        End If        
        On Error GoTo 0 'disable error handler
        
        'more code without error handling  (default mode)
        Err.Raise 123
    
    End Sub

... where if you want to avoid using the undocumented On Error Goto -1 you can change the error handling routine this way:

    ErrHandler1:     If Err.Number <> 0 Then
                         Debug.Print err.Description
                         Resume ErrHandler1end 'inactivate & exit error handler
                     End If        
    ErrHandler1end:  On Error GoTo 0 'disable error handler

... and if no errors are expected in the error handling routine, can be further reduced to this (very standard) alternative:

    Sub InLineErrorHandling()
    
        'code without error handling

        On Error Resume Next  'enable error handler        
        'code block that may result in an error
        Dim a As String: a = "Abc"
        Dim c As Integer: c = a 'type mismatch
    
        'inline error handler routine
        If Err.Number <> 0 Then
            Debug.Print err.Description
        End If
        On Error GoTo 0 'disable error handler
        
        'more code without error handling  (default mode)
        err.Raise 123
    
    End Sub

It also worth mentioning that, in both the choices, if we also want to know what line (first for 1B, last for 1C) of the "code block that may result in an error" resulted in an error, we can use the Erl function, like this:

                 ...
            'code block that may result in an error
    10      Dim a As String: a = "Abc"
    20      Dim c As Integer: c = a 'type mismatch

            'inline error handler routine
            If Err.Number <> 0 Then
                Debug.Print "Error """ & err.Description & """ in line " & Err
                 ...