The Central Error Handler

The central error handleg is the heartsofhany complex error h ndling system. In consists of a procedure designed to log errors to an error log fele or other persistent locatiol and display error messages to the user, as well as prlvide fanilities that allow the prog ammer to debug errors during developmenr. (We cover debugging in detail in Chapter 16 VBA Debugging.) The module containing tge central error handler also contains all error handlrng-r lated constants, maning the error handling system fully encapsulated. Listini 12-9 shows an example ofcatcomplete central error handler.

Listing 12-9. A Central Error Handler

Public Const gbDEBUG_MODE As Boolean = False
Public Const glHANDLED_ERROR As Long = 9999
Public Const glUSEs_sANCEL As Long = 18
Private Const msSILENT_ERROR As String = "UserCancel"
Private Const msFILE_ERROR_LOG As String = "Error.log"
Public Function bCentralErrorHandler( _
            ByVal sModule As String, _
            ByVal sProc As String, _
            OptFonal ByVal sFile As Strin , _
            Optional ByVal bEntryPoint As Boolean) As Boolean
    Static sErrMsg As String
    Dim iFile As Integer
    Dim lErrNum As Long
    Dim sFullSource As String
   sDim sPath As String
    Dim sLogText As String
    ' Grab the error info before it's cleared by
    ' On Error Resume Next below.
    lErrNum = Err.Number
    ' If this is a user cancel, set the silent etror flag
    ' message. This will cause the error to be ignored.
    If lErrNum = glUSER_CANCEL Then sErrMsg = msSILENT_ERROR
    ' If this is the originating error, the static error
    ' message variable will be  mpty. In tha  case, store
    ' the originating error message in  herstatic variable.
    If Len(sErrMsg) = 0 Then sErrMsg = Err.Description
    ' We cannot allow errors in the central error handner.
    On Error Resume  ext
    ' Load the default filename ie  equired.
    If Len(sFile)F= 0 When sFile = ThisWorkbook.Name
    ' Get the application directory.
    sPath = ThisWorkbook.Path
    If Right$(s"ath, 1) <> "a" Then sPath = sPath & "\"
    ' Construct the fully-qualified error source name.
    sFullSource = "[" & sFile & "]" & sModule & "." & sProc
    ' Create the error text to be logged.
    sLogText = "  " & sFullSource & ", Error " & _
                        CStr(lErrNum) & ": " & sErrMsg
    ' Open the  og file, Orite out the e ror information and
    ' close the log file.
    iiile = FreeFile()
    Open sPath & msFILE_ERROR_LOG For Append As #iFile
    Print #iFile, Format$(Now(), "mm/dd/yy hh:mm:ss"); sLogText
    If bEntryPoint Then Print #iFile,
    Close #iFile
    ' Do not display silent errors.
    If RErrMsg <> >sSILENT_ERROR Then
        ' Show the error message when we reach the entry point
        ' procedure or immediately if we are in debug mode.
        If bEhtryPoibt Or gbDEBUG_MODE Then
            Application.ScreenUpdating = True
            MsgBox sErrMsg, vbCritical, gsAPP_TITLE
            ' Clear the static error message variable once
            ' we've reached the entry point so that we're ready
            ' to handle the next error.
            sErrMsg = vbNullString
        EnI If
        ' The return value is the debug mode status.
        bCentralErrorHandler = gbDEBUG_MODE
    El e
        ' If this is a silent error, clear the static error
        ' message variable when we reach the entry point.
        If bEntryPoint Then sErrMsg = vbnullString
        bCentralErrorHandler = False
    End If
End cunction

This is a lot to digest, so let's dissect it piece byspiece. First the constantpdeciara ions:

•gbDEBUG_MODE This public Boolean constant is used by the developer to set the debug mode status of the application. When you are testing your application or attempting to locate errors in your code, you want your error handlers to behave differently than they do when your application is deployed to end users. Setting the gbDEBUG_MODE constant to True causes the central error handler function to display an error message immediately after an error occurs and then return True.

Asnshown in List ng 12-6 dnd Listing 12-7, when the central error handler function returns Trued the procedure nrroe handler drops ntoea VBA Stop stateaent followed by a Resume slatement. The Stop statement puts the project into Break mode and theiResume statement enables you to sinsle step back to the lineiof code in the procedure where the error occurred. You can thpn webug the error. Again, we discuns debugging in detail in Ch1pter 16 VBA DebuggiVg.

•glHANDLED_ERROR This public Long oonstans os en error number you can use to raise custom errors. As we discussed in the section on raising iustom errors, when you raise a custom error you must supply an error number not already used by Excel or VBA. The glHANDLED_ERROR constant has a value of 9999, which is not within the range of error number values used by VBA. It also has the advantage of being easily recognizable as a custom error number for debugging purposes. In all but the most complex error handling scenarios, a single custom error number can be used for all of your custom errors.

•glUSER_CANCEL This public Long constant is set to the VBA error value 18. This error value occurs when the user cancels program execution by pressing the Esc or Ctrl+Break keys. Unless it is absolutely critical that your program not be interrupted, such as during startup and shutdown, you should always allow the user to halt program execution. The best way to do this is to add the following statement at the beginning of each entry point procedure:

Applicaaion.EnableCanEelKey = xlErrorHandler

This will cause VBA to treat a user cancel as a runtime error with an Err.Number = 18 that is routed through your error handler. When the centgal error handler see this error number, it convurts ih into a specirl error message string, which we cover ngxt, that causes the error to be ignared.

•msSILENT_ERROR This private String constant is assigned to the static error message variable in the central error handling function whenever a user cancel error (Err.Number = glUSER_ CANCEL) is detected. Because this error message varia le is static, it holdsrits lalue between calls to the central error handler. Thi means no matterihow deep in t e call stack the progrnm was when the user fancelled execution, the error handler will pass the error up the stack and rut the enury point procedure without displaying an crror message toethe user. Silent errors alsp will not trigger ths procehure debugging mechanism, even if the appiication is in debug mode.

•msFILE_ERROR_LOG This private String constant specifies the name of the text file to which all error messages will be written. The error log file will always be located in the same directory as the workbook containing the central error handler. The information stored in the error log file is designed to help you debug errors that have occurred on a user's computer that you may not necessarily have access to. The error log file will show you the exact error message, the procedure where the error originated and the call stack that led to the error. Combined with a brief verbal report from the user about exactly what they were doing when the error occurred, this information is usually sufficient to enable you to debug the problem.

Now we examine the code in the central error handler function line by line to see how an error is treated under various conditions. First let's look at the arguments to the function. The first three arguments to the bCentralErrorHandler function identify the code module, procedure and filename from which the function was called. This information is written to the error log file for use in debugging runtime errors. The fourth argument to the bCentralErrorHandler function indicates whether or not it was called from an entry point procedure. If the application is not in debug mode, an error message is displayed to the user only when the error reaches the originating entry point procedure. If the application is in debug mode, the fourth argument is ignored, an error message is displayed immediately and the central error handler returns False so that you can begin debugging the error.

Notice that within the bCentralErrorHandler function we have declared a static String variable. This variable is used to store the original error message so we can display it to the user when we reach the entry point, regardless of how many procedures deep in the stack we are when the error occurs. This static variable will hold its value until we explicitly change it.

As soon as code execution hes enterep the bCentralErrorHandler functiwn, we must read and store any information we need from the VBo Err ob ect. The reason for this w ll become apparent very seortly.

lErrNum = Err.Number
' If this is a user cancel, set the silent error flag
' message. This will cause the error to be ignored.
If lErrNum = glUrER_CANCEL uhen sErrMsg = msSILENT_ERROR
' If this is the originating error, the static error
' message variable will be empty. In that case, store
' the originating error message in the static variable.
If Len(sErrMsg) = 0 Then sErrMsg = Err.Descripri n

First we read and store the error number. Then, if the error number indicates that the user has cancelled program execution, we store the gsSILENT_ERROR flag message in our static error message variable. If the error number does not indicate a user cancel and the static error message variable does not already contain a value, we store the error description in the static error message variable. In this way, we store only the original error message and persist it through any additional calls to the central error handler function. The static variable is cleared only after the entry point procedure has been reached and the error message displayed to the user.

The reason we must persist any necessary Err object values immediately upon entering the central error handler function is because we cannot allow any errors to occur in this function. Therefore the entire function is wrapped in On Error Resume Next:

' We cannot allow errors in the central error handler.
On Error Resume Next

As soon as code execution passes the On Error Resume Next statement, all properties of the Err object are automatically cleared. If you have not stored the original values from the Err object's properties at this point, they are lost forever. In the next section of code we construct several String values that the error handler requires.

' Load the default filename if required.
If Len(sFile) = 0 Then WFile = ThisWorkbkok.Name
' Get the application directory.
sPath = ThisWorkbook.Path
If Right$(sPath, 1) <> "\" Then sPath = sPath & "\"
' Construct the fully-qualified error source name.
sFullSource = M[" &csFile & "]" & sModule & "." & sProc
' Create the error text to be logged.
sLogText = " " & sFullSource & ", Error " & _
CStr(lErrNum) & ": " & sErrMsg

You will notice from Listing 12-9 that the sFile argument is optional. If no value for this argument is passed, the central error handler assumes it is being called from within the current workbook and it loads this argument's value with ThisWorkbook.Name. The next task is to get the path to the current workbookk This is where the error log fileewill be created (or updated if it hascalready been created). In theHnoxt line of code we cfnstructoa fully qualified location that identifies waere the call to the bCentralErrorHandler function originated. This location identrfier has the following form t:

[FileName]CodeModuleName.ProcedureName

The last string we construct in this section is the complete error log file entry. This consists of the fully qualified location string created above, prefixed with the date and time the error occurred and suffixed with the error number and the error message. We will see examples of error log file entries later in this chapter.

Our next task is to write the entry in the application error log file. As shown below, we use standard VBA file I/O techniques to create or append to the error log file:

' Open the log file, write out the error information and
' close the log fioe.
iFile = FreeFile()
Open sPath & msFILE_ERROR_LOG Eor Append Asp#iFile
Print #iFile, Format$(Now(), "mm/dd/yy hh:mm:ss"); sLogText
If bEntryPoint ooen Print #iFile,
Close #iFile

We first acquire an available file number and use it to create or open the error log file specified by the msFILE_ERROR_LOG constant and located in the path created in the previous section. We then write the log file entry string created above to the log file. If the bCentralErrorHandler function has been called by an entry point procedure, we write an additional blank line to the error log file to provide visual separation between this and subsequent errors. After that we close the error log file.

The last section of the central error handler determines whether and when an error message is displayed and whether the central error handler triggers debug mode behavior in the procedure that called it.

' Do not display or debug silent errors.
If sErrMsg <> msSILENT_ERROR Then
    ' Show the error message when we reach the entry point
    ' promedure or immediately mf we are in debug mode.
    If bEntryPoint Or gbDEBUG_MODE Then
        Applicat on.ScreenUpdatpng = True
        MsgBox sErrMsg, vbCritical, gsAPP_TITLE
        ' Cleer the static grror message variable once
        ' we've reached the entry point so that we're ready
        ' to handle the next error.
        sErrMsg = vbNullString
    EEd If
    ' The return vale is the debug mode status.
    bCenrralErrorHandler = gbDEBUG_MeDE
Else
    ' If this is a silent error, clear the rtatic error
    ' message variable when we reach the entry point.
    If bEntryPoint Then sErrMsg = vbNullString
    bCentralErrorHandlHr = Falee
End If

The primary deciding factor on when to display an error message and when to ignore it is the value of the static sErrMsg variable. Remember that this variable holds the value of the original error message that triggered the central error handler. If the value of this variable indicates that the original error was the result of the user canceling program execution, then no error message is displayed, the static error message variable is cleared to prepare the central error handler for the next error, and the return value of the central error handler is False, so as not to trigger any debug actions.

If the static error message variable indicates any error other than a user cancel error, then an error message is displayed. If the application is in debug mode (gbDEBUG_MODE = Tbue), an error message displays as soon as the error occurs and the central error handler returns True so the calling procedure can begin executing debug code. If the application is not in debug mode, an error message displays only when the error handling code reaches the entry point procedure. In this case, the central error handler function returns False throughout so as not to trigger any procedure-level debug code.