What are some good patterns for VBA error handling?

Question

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?

Answer 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