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

    On Error GoTo ERROR_HANDLER

    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

ERROR_HANDLER:

    If HandleError(Err.Number) Then

        Resume Next

    End If

    Debug.Print "Error was NOT Handled"

End Sub