VBA Error Handling

Question

I am relatively new to VBA (Java/Python are more my thing, along with Haskell). One thing that has shocked and surprised me about VBA is the lack of any "obvious" way to do proper structured 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.

Ideas?

Answer 1

I think VBA currently only offers the On Error ... instruction for error handling. Detailled article with examples: Error Handling in VBA

Answer 2

So you could do something like this

Function Errorthingy(pParam)
On Error GoTo HandleErr

 ' your code here

    ExitHere:
    ' your finally code
    Exit Function

    HandleErr:
        Select Case Err.Number
        ' different error handling here'
        Case Else
            MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, ErrorThingy" 
        End Select


   Resume ExitHere

End Function

---

If you want to bake in custom exceptions. (e.g. ones that violate business rules) use the example above but use the goto to alter the flow of the method as necessary.

Answer 3

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

Answer 4

Professional Excel Development has a pretty good error handling scheme. If you're going to spend any time in VBA, it's probably worth getting the book. There are a number of areas where VBA is lacking and this book has good suggestions for managing those areas.

Answer 5

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 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.

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