Error Handling

All but the simplest of macros will need to deal with illegal inputs, files not being found, and other unexpected results. VBA provides the On Error GoTo <line label> construct to trap errors as they arise. Your program will have to effectively deal with these errors so it doesn’t crash and burn, leaving the user wondering what to do next.

Step 1 – Define Your Errors

You will need to determine what kind of errors your program is likely to encounter. Make an enumerated list of these errors.  The numbers you assign in the Enum will be the error numbers you raise when you want to trap the error. You can assign any error numbers you like above the value of 512.

Public Enum My_ERR
    errFolderNotFound = 1001
    errFileNotFound = 1002
    errNumberRequired = 1003
End Enum


Step 2 – Build an Error Handler Function

Next you will need to build your own error handler function to process errors when they are raised. For those errors that can be resolved allowing your program to continue the function should return True, for all other errors the function should return False causing processing to stop.

Public Function HandleError(eNumber As Long) As Boolean
    Dim isHandled As Boolean
    isHandled = True
    Select Case eNumber
        Case My_ERR.errFolderNotFound
        ‘ Insert code to handle the error
        Case My_ERR.errFileNotFound
        ‘ Insert code to handle the error
        Case My_ERR.errNumberRequired
        ‘ Insert code to handle the error
        Case Else
            MsgBox "Error =  " & CStr(eNumber) & " - " _
& Error$(eNumber), vbExclamation, _
"Unhandled  Error"
            isHandled = False
    End Select
    HandleError = isHandled
End Function

Step 3 – Implement the Error Handler

Now that your errors are defined and the HandleError function constructed you can implement them in your macro.

Public Sub MyMacro()
    Dim i As Long
    Dim strValue As String
    strValue = "abc"
    If Not IsNumeric(strValue) Then
        Err.Raise My_ERR.errNumberRequired
        ' Resume Next from ERROR_HANDLER returns the program
        ‘ to the next line of code following the one that
  ‘ raised the error
        Debug.Print "Error was handled"
    End If
   ‘ This line will raise an unhandled error
    i = 1 / 0
   ‘ Exit Sub/Function required prior to ERROR_HANDLER label
    Exit Sub
    If HandleError(Err.Number) Then
        Resume Next
    End If
    Debug.Print "Error was NOT Handled"
End Sub


Since 1996, EnvisionCAD has been a nationally recognized leader in the configuration, customization, implementation, training & support for CAD software solutions. Our individualized approach has benefited private engineering firms and government agencies alike. Basic or advanced, we can help you get the most from your CAD technology.


Leave a Reply

Your email address will not be published. Required fields are marked *