Deep dive: applying VBA error handling the right way

The second article in the ‘Deep Dive’-series covers the way error handling was implemented in our products. In order to develop robust software consistent use of code error handling is vital. Basic principles will be discussed and the manner they were applied in our add-ins. Visual Basic code examples will be gradually extended to help clarify the concepts. Prerequisite for this article is a basic knowledge of VBA error handling syntax.

Introduction

When something happens during code execution that is not meant to happen you’ll want to control how the code should respond.

Error handling (also called exception handling) shouldn’t be implemented in every routine.  Only add  it to the following types of routines:

  1.  Main routines; routines that are the starting point of code execution. Execution may be triggered by an event, user (inter)action or timer.
  2. Routines that need to process logically anticipated exceptions. It is undesirable to handle all errors centrally in one main routine. That’s why expected exceptions should be dealt with as local as possible.
  3. Routines that require logging / user messaging in case of an (un)expected error
  4. Routines that require clean up actions in case of an (un)expected error

Consider next simple code example. The only thing that happens in case of an error is that a message to the user is displayed in our own format:

Sub Main()

    Dim i As Integer

    On Error GoTo ErrH

    MsgBox "Quotient of 1 / " & i & " = " & 1 / i

    Exit Sub
ErrH:
    MsgBox Err.Description, vbCritical
End Sub

If we want to distinguish between expected and unexpected exceptions the code example should be expanded as follows. Try the sample with these input values: 0, 1 and x.

Sub Main()

    Dim i As Integer

    On Error GoTo ErrH

    i = InputBox(Prompt:="Value:", Default:=0)

    MsgBox "Quotient of 1 / " & i & " = " & 1 / i, vbInformation

    Exit Sub
ErrH:
    Select Case Err.Number

        Case 11
            MsgBox "Enter a value <> 0", vbExclamation
        Case Else
            MsgBox "An unexpected error occurred:" & vbCr & _
                Err.Description, vbCritical
    End Select
End Sub

Let’s extend our example even further by adding a clean up action that should always be carried out. Pay attention to the Exit Sub-call!

Sub Main()

    Dim i As Integer

    On Error GoTo ErrH

    i = InputBox(Prompt:="Value:", Default:="x")

    Application.StatusBar = "Calculating 1 / " & i & "..."

    MsgBox "Quotient of 1 / " & i & " = " & 1 / i, vbInformation

CleanUp:
    Application.StatusBar = False
    Exit Sub
ErrH:
    Select Case Err.Number

        Case 11
            MsgBox "Enter a value <> 0", vbExclamation
        Case Else
            MsgBox "An unexpected error occurred:" & vbCr & _
                Err.Description, vbCritical
    End Select
    Resume CleanUp
End Sub

Expanding the code example to a scenario

As functionality grows, code will obviously be refactored into separate routines. Extended functionality also impacts process flow and thus error handling.  This is covered in the final code expansion.  A separate sub CalculateQuotient is created which expects the input value as a parameter. Because of this the need arises to process exceptions differently; in the child sub the expected errors are raised using our own error numbers (like ERR_INPUT_IS_ZERO). These custom errors are subsequently handled in the main routine.
Other changes: the sub CalculateQuotient is called multiple times and another exception, initialization and clean up action are added.

Private Const ERR_INPUT_IS_ZERO = 666
Private Const ERR_INPUT_IS_TOO_HIGH = 667

Sub Main()

    On Error GoTo ErrH

    Call CalculateQuotient(InputBox(Prompt:="Value:", Default:=500))
    Call CalculateQuotient(InputBox(Prompt:="Value:", Default:=2))

    ActiveSheet.Cells(1).Select
    MsgBox "Proces has ended", vbInformation

CleanUp:
    Application.StatusBar = False
    Application.CutCopyMode = False
    Exit Sub
ErrH:
    Select Case Err.Number

        Case ERR_INPUT_IS_ZERO
            MsgBox "Enter a value <> 0", vbExclamation
        Case ERR_INPUT_IS_TOO_HIGH
            MsgBox "Value is too high!", vbExclamation
        Case Else
            MsgBox "An unexpected error occurred:" & vbCr & _
                Err.Description, vbCritical
    End Select
    Resume CleanUp
End Sub

Sub CalculateQuotient(i As Integer)

    Application.StatusBar = "Calculating 1 / " & i & "..."
    ActiveCell.Copy

    'i = "x" 'Uncomment to test unexpected error in this routine

    If i = 0 Then
        Err.Raise ERR_INPUT_IS_ZERO
    ElseIf i > 5 Then
        Err.Raise ERR_INPUT_IS_TOO_HIGH
    End If

    MsgBox "Quotient of 1 / " & i & " = " & 1 / i, vbInformation

End Sub

Identifying error handling scenario’s

For every exception it needs to be determined whether code execution should be aborted or not when the exception occurs. In addition also should be decided if an error should be handled centrally or locally. Theoretically this results in four different error handling scenario’s. Underneath picture shows all cases schematically:
sftp://delegate.nl@delegate.nl/images/posts/ErrH_Schema1.png

What follows now is a discussion of all four cases. The appropiate code is offered so you can apply it to your specific situation.

Case 1: Handle centrally, abort process

The code sample above implements case 1 when an error occurs in  a child sub. The main routine handles all errors after which the process is terminated; the second quotient is not calculated and the message ‘”Proces has ended”‘ is not displayed. This is the most common scenario of dealing with exception handling.

Case 2: Handle centrally, continue process

In some cases you’ll want the process to continue after an expected exception has arisen. For instance if you only want to log the incident if the error occurs. In that case central error handling is not advised because it would require GoTo constructions that would obscure clear process flow. Conclusion: in such cases exceptions need to be processed in the child sub itself, including clean up actions. This is discussed next.

Case 3: Handle locally, continue process

Consider next sample in which this is realised. Note that error handling in the main routine is limited to unexpected errors only. Also note our custom error numbers have changed scope: from member level constants to routine level constants . Finally pay attention to the cleanup action in the child sub; the CleanUp:..Exit Sub-construction is absent in CalculateQuotient. The reason for this is that this mechanism doesn’t work if an error is raised in the error handling section of a routine; this is done in the child sub in case of an unexpected error.
The sample can easily be adjusted to process expected exceptions locally AND centrally (control question: what changes should be made to the code?).

Sub Main()

    On Error GoTo ErrH

    Call CalculateQuotient(InputBox(Prompt:="Value:", Default:=8))
    Call CalculateQuotient(InputBox(Prompt:="Value:", Default:=2))

    ActiveSheet.Cells(1).Select
    MsgBox "Proces has ended", vbInformation

CleanUp:
    Application.StatusBar = False
    Exit Sub
ErrH:
    MsgBox "An unexpected error occurred:" & vbCr & _
            Err.Description, vbCritical

    Resume CleanUp
End Sub

Sub CalculateQuotient(i As Integer)

    Const ERR_INPUT_IS_ZERO = 666
    Const ERR_INPUT_IS_TOO_HIGH = 667

    On Error GoTo ErrH

    Application.StatusBar = "Calculating 1 / " & i & "..."
    ActiveCell.Copy

    'i = "x" 'Uncomment to test unexpected error in this routine

    If i = 0 Then
        Err.Raise ERR_INPUT_IS_ZERO
    ElseIf i > 5 Then
        Err.Raise ERR_INPUT_IS_TOO_HIGH
    End If

    MsgBox "Quotient of 1 / " & i & " = " & 1 / i, vbInformation

ErrH:
    Application.CutCopyMode = False 'Clean up action!

    Select Case Err.Number

        Case 0
            'No error
        Case ERR_INPUT_IS_ZERO
            MsgBox "Enter a value <> 0", vbExclamation
        Case ERR_INPUT_IS_TOO_HIGH
            MsgBox "Value is too high!", vbExclamation
        Case Else
            Err.Raise Err.Number, Err.Source, Err.Description
    End Select
End Sub

Case 4: Handle locally, abort process

The last scenario also deals with exceptions on a local level. However, code execution should be halted after that. Use the following VBA to accoplish this. Its structure looks a lot like that of Case 3. There are two differences though:

  1. A new custom error number is introduced: ERR_IGNORE. The reason for this is that exception handling should be ignored in the main routine; this was already done in sub CalculateQuotient
  2. In CalculateQuotient ERR_IGNORE is raised in the error handling section to force the process to terminate.

Note that cleanup actions are performed both in the local ánd main routine!

Private Const ERR_IGNORE = 668

Sub Main()

    On Error GoTo ErrH

    Call CalculateQuotient(InputBox(Prompt:="Value:", Default:=8))
    Call CalculateQuotient(InputBox(Prompt:="Value:", Default:=2))

    ActiveSheet.Cells(1).Select
    MsgBox "Proces has ended", vbInformation

CleanUp:
    Application.StatusBar = False
    Exit Sub
ErrH:
    Select Case Err.Number

        Case ERR_IGNORE
            'Error was already handled locally
        Case Else
            MsgBox "An unexpected error occurred:" & vbCr & _
                Err.Description, vbCritical
    End Select

    Resume CleanUp
End Sub

Sub CalculateQuotient(i As Integer)

    Const ERR_INPUT_IS_ZERO = 666
    Const ERR_INPUT_IS_TOO_HIGH = 667

    On Error GoTo ErrH

    Application.StatusBar = "Calculating 1 / " & i & "..."
    ActiveCell.Copy

    'i = "x" 'Uncomment to test unexpected error in this routine

    If i = 0 Then
        Err.Raise ERR_INPUT_IS_ZERO
    ElseIf i > 5 Then
        Err.Raise ERR_INPUT_IS_TOO_HIGH
    End If

    MsgBox "Quotient of 1 / " & i & " = " & 1 / i, vbInformation

ErrH:
    Application.CutCopyMode = False 'Clean up action!

    Select Case Err.Number

        Case 0
            'No error
        Case ERR_INPUT_IS_ZERO
            MsgBox "Enter a value <> 0", vbExclamation
        Case ERR_INPUT_IS_TOO_HIGH
            MsgBox "Value is too high!", vbExclamation
        Case Else
            Err.Raise Err.Number, Err.Source, Err.Description
    End Select

    'Abort in case of expected error
    Err.Raise ERR_IGNORE

End Sub

I wish this article would’ve been around when I started developing in VBA years ago. The next best thing though, is offering these concepts and code so you don’t have to struggle with it as I had to!