What are some good patterns for VBA error handling?

What are some good patterns for error handling in VBA?

In particular, what should I do in this situation:

... some code ...
... some code where an error might occur ...
... some code ...
... some other code where a different error might occur ...
... some other code ...
... some code that must always be run (like a finally block) ...

I want to handle both errors, and resume execution after the code where the error may occur. Also, the finally code at the end must always run - no matter what exceptions are thrown earlier. How can I achieve this outcome?

Solution 1:

Error Handling in VBA

• On Error Goto ErrorHandlerLabel
• Resume (Next | ErrorHandlerLabel)
• On Error Goto 0 (disables current error handler)
• Err object

The Err object's properties are normally reset to a zero or a zero-length string in the error handling routine, but it can also be done explicitly with Err.Clear.

Errors in the error handling routine are terminating.

The range 513-65535 is available for user errors. For custom class errors, you add vbObjectError to the error number. See the Microsoft documentation about Err.Raise and the list of error numbers.

For not implemented interface members in a derived class, you should use the constant E_NOTIMPL = &H80004001.

Option Explicit

Sub HandleError()
  Dim a As Integer
  On Error GoTo errMyErrorHandler
    a = 7 / 0
  On Error GoTo 0
  
  Debug.Print "This line won't be executed."
  
DoCleanUp:
  a = 0
Exit Sub
errMyErrorHandler:
  MsgBox Err.Description, _
    vbExclamation + vbOKCancel, _
    "Error: " & CStr(Err.Number)
Resume DoCleanUp
End Sub

Sub RaiseAndHandleError()
  On Error GoTo errMyErrorHandler
    ' The range 513-65535 is available for user errors.
    ' For class errors, you add vbObjectError to the error number.
    Err.Raise vbObjectError + 513, "Module1::Test()", "My custom error."
  On Error GoTo 0
  
  Debug.Print "This line will be executed."

Exit Sub
errMyErrorHandler:
  MsgBox Err.Description, _
    vbExclamation + vbOKCancel, _
    "Error: " & CStr(Err.Number)
  Err.Clear
Resume Next
End Sub

Sub FailInErrorHandler()
  Dim a As Integer
  On Error GoTo errMyErrorHandler
    a = 7 / 0
  On Error GoTo 0
  
  Debug.Print "This line won't be executed."
  
DoCleanUp:
  a = 0
Exit Sub
errMyErrorHandler:
  a = 7 / 0 ' <== Terminating error!
  MsgBox Err.Description, _
    vbExclamation + vbOKCancel, _
    "Error: " & CStr(Err.Number)
Resume DoCleanUp
End Sub

Sub DontDoThis()
  
  ' Any error will go unnoticed!
  On Error Resume Next
  ' Some complex code that fails here.
End Sub

Sub DoThisIfYouMust()
  
  On Error Resume Next
  ' Some code that can fail but you don't care.
  On Error GoTo 0
  
  ' More code here
End Sub

Solution 2:

I would also add:

• The global Err object is the closest you have to an exception object
• You can effectively "throw an exception" with Err.Raise

And just for fun:

• On Error Resume Next is the devil incarnate and to be avoided, as it silently hides errors