Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA® نسخه متنی

The Central Error Handler

The central error handler is the heart of any complex error handling system. It consists of a procedure designed to log errors to an error log file or other persistent location and display error messages to the user, as well as provide facilities that allow the programmer to debug errors during development. (We cover debugging in detail in Chapter 16 VBA Debugging.) The module containing the central error handler also contains all error handling-related constants, making the error handling system fully encapsulated. Listing 12-9 shows an example of a complete 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 glUSER_CANCEL 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, _
Optional ByVal sFile As String, _
Optional ByVal bEntryPoint As Boolean) As Boolean
Static sErrMsg As String
Dim iFile As Integer
Dim lErrNum As Long
Dim sFullSource As String
Dim 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 error 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 empty. In that case, store
' the originating error message in the static variable.
If Len(sErrMsg) = 0 Then sErrMsg = Err.Description
' We cannot allow errors in the central error handler.
On Error Resume Next
' Load the default filename if required.
If Len(sFile) = 0 Then sFile = ThisWorkbook.Name
' Get the application directory.
sPath = ThisWorkbook.Path
If Right$(sPath, 1) <> "\" 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 log file, write out the error information and
' close the log file.
iFile = 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 sErrMsg <> msSILENT_ERROR Then
' Show the error message when we reach the entry point
' procedure or immediately if we are in debug mode.
If bEntryPoint 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
End If
' The return value is the debug mode status.
bCentralErrorHandler = gbDEBUG_MODE
Else
' 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 Function

This is a lot to digest, so let's dissect it piece by piece. First the constant declarations:

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.Listing 12-6 and Listing 12-7, when the central error handler function returns True, the procedure error handler drops into a VBA Stop statement followed by a Resume statement. The Stop statement puts the project into Break mode and the Resume statement enables you to single step back to the line of code in the procedure where the error occurred. You can then debug the error. Again, we discuss debugging in detail in Chapter 16 VBA Debugging.

glHANDLED_ERROR
This public Long constant is an error number you can use to raise custom errors. As we discussed in the section on raising custom 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:

Application.EnableCancelKey = 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 central error handler sees this error number, it converts it into a special error message string, which we cover next, that causes the error to be ignored.

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 variable is static, it holds its value between calls to the central error handler. This means no matter how deep in the call stack the program was when the user cancelled execution, the error handler will pass the error up the stack and out the entry point procedure without displaying an error message to the user. Silent errors also will not trigger the procedure debugging mechanism, even if the application 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 has entered the bCentralErrorHandler function, we must read and store any information we need from the VBA Err object. The reason for this will become apparent very shortly.

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 = glUSER_CANCEL Then 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.Description

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 sFile = ThisWorkbook.Name
' Get the application directory.
sPath = ThisWorkbook.Path
If Right$(sPath, 1) <> "\" 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

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 workbook. This is where the error log file will be created (or updated if it has already been created). In the next line of code we construct a fully qualified location that identifies where the call to the bCentralErrorHandler function originated. This location identifier has the following format:

[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 file.
iFile = 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

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
' procedure or immediately if we are in debug mode.
If bEntryPoint 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
End If
' The return vale is the debug mode status.
bCentralErrorHandler = gbDEBUG_MODE
Else
' 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

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 = True), 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.