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

Putting It All Together

Although we've described all the pieces of an error handling system, it may not be clear how all those pieces fit together. In this section we show a small but complete program that demonstrates the basic error handling techniques. This program is admittedly contrived, but the idea behind it is to have a complete program with as little distraction from non- error- handling-related code as possible. The complete program can be found in the Concepts folder of the CD in the workbook named ErrorHandlingDemo.xls.

The error handling demo program consists of a single entry point procedure that displays a userform and then calls a function that intentionally generates an error depending on whether the user clicks the OK or Cancel button on the userform. Figure 12-2 shows the userform for our error handling demo and Listing 12-10 shows the code behind this userform.

Listing 12-10. The Code Behind the Error Handling Demo Userform

Private Const msMODULE As String = "FDemo"
Private bUserCancel As Boolean
Public Property Get UserCancel() As Boolean
UserCancel = bUserCancel
End Property
Private Sub cmdOK_Click()
bUserCancel = False
Me.Hide
End Sub
Private Sub cmdCancel_Click()
bUserCancel = True
Me.Hide
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
' Route any X-close button calls through
' the cmdCancel_Click procedure.
If CloseMode = vbFormControlMenu Then
Cancel = True
cmdCancel_Click
End If
End Sub
Public Function Initialize() As Boolean
Const sSOURCE As String = "Initialize()"
Dim bReturn As Boolean  ' The function return value
On Error GoTo ErrorHandler
' Assume success until an error is encountered.
bReturn = True
' Set the UserForm caption.
Me.Caption = gsAPP_TITLE
ErrorExit:
Initialize = bReturn
Exit Function
ErrorHandler:
bReturn = False
If bCentralErrorHandler(msMODULE, sSOURCE) Then
Stop
Resume
Else
Resume ErrorExit
End If
End Function

Figure 12-2. The Error Handling Demo Userform

The first thing to notice is the userform has a read-only UserCancel property. The value of this property is determined by which button the user clicks. If the OK button is clicked, the UserCancel property will return False (meaning the user did not cancel the userform). If the Cancel button is clicked, the UserCancel property will return True. In the code for the calling procedure we will demonstrate how to raise a custom user cancel error in response to the UserCancel method returning True that will cause the error handler to exit silently rather than displaying an error.

The second thing to notice is we are trapping clicks to the X-close button on the userform with the UserForm_QueryClose event procedure and rerouting them to the cmdCancel_Click event procedure. This makes a click on the X-close button behave exactly like a click on the Cancel button.

The last thing to notice is the userform contains a custom Initialize method. This method is a Boolean function that returns True if initialization succeeds and False if an error occurred during initialization. This method is called prior to showing the userform. The calling function then examines the return value of the method and does not attempt to show the userform if initialization failed.

Listing 12-11 shows the function that will purposely cause an error.

Listing 12-11. The bCauseAnError Function

Public Function bCauseAnError() As Boolean
Const sSOURCE As String = "bCauseAnError()"
Dim bReturn As Boolean  ' The function return value
Dim lTest As Long
On Error GoTo ErrorHandler
' Assume success until an error is encountered.
bReturn = True
' Cause a divide by zero error.
lTest = 1 / 0
ErrorExit:
bCauseAnError = bReturn
Exit Function
ErrorHandler:
bReturn = False
If bCentralErrorHandler(msMODULE, sSOURCE) Then
Stop
Resume
Else
Resume ErrorExit
End If
End Function

This function is exactly the same as the one we showed in Listing 12-6 with some code added that causes it to throw a divide by zero error. Now we can tie things together with the entry point procedure that runs the application. The code for this procedure is shown in Listing 12-12.

Listing 12-12. The EntryPoint Subroutine

Public Sub EntryPoint()
Const sSOURCE As String = "EntryPoint"
Dim bUserCancel As Boolean
Dim frmDemo As FDemo
On Error GoTo ErrorHandler
Set frmDemo = New FDemo
Load frmDemo
' If UserForm initialization failed, raise a custom error.
If Not frmDemo.Initialize() Then Err.Raise glHANDLED_ERROR
frmDemo.Show
' If the user pressed the Cancel button, raise a custom
' user cancel error. This will cause the central error
' handler to exit the program without displaying an
' error message.
If frmDemo.UserCancel Then Err.Raise glUSER_CANCEL
' If the user pressed the OK button, run the function that
' is designed to cause an error.
If Not bCauseAnError() Then Err.Raise glHANDLED_ERROR
ErrorExit:
' Clean up the UserForm
Unload frmDemo
Set frmDemo = Nothing
Exit Sub
ErrorHandler:
If bCentralErrorHandler(msMODULE, sSOURCE, , True) Then
Stop
Resume
Else
Resume ErrorExit
End If
End Sub

The EntryPoint subroutine is run from a button located on Sheet1 of the ErrorHandlingDemo.xls workbook. This application has only two possible execution paths. Clicking the OK button on the userform triggers the first and clicking the Cancel button on the userform triggers the second. Let's examine what happens in each case and see the resulting error log entries.

The EntryPoint subroutine first creates a new instance of the FDemo UserForm, loads it and calls the userform's custom Initialize method. In this sample application the userform will never fail to initialize. We have provided this custom Initialize method to demonstrate how you would initialize a userform in a way that is linked into the error handling system.

Next, the EntryPoint subroutine shows the FDemo userform. As you can see in Figure 12-2, the only actions available to the user are clicking the OK or Cancel buttons. Clicking the OK button sets the FDemo userform's UserCancel property to False, meaning the user did not cancel. Clicking the Cancel button sets the UserCancel property to True, meaning the user did cancel. Clicking either button also hides the userform, allowing the EntryPoint subroutine to continue executing.

Because the FDemo userform is hidden rather than unloaded, when code execution returns to the EntryPoint subroutine the userform is still in memory. This allows the EntryPoint subroutine to check the value of the FDemo UserCancel property to determine what the user has asked it to do.

If the UserCancel property is True, the EntryPoint subroutine needs to exit without displaying an error message but still running its cleanup code. It accomplishes this by raising a custom user cancel error. If you recall from the discussion of the central error handler, VBA uses the error number 18 to indicate the user has cancelled program execution, we have defined a public constant that holds this value, and when the central error handler sees a user cancel error it exits silently. Therefore, to exit as a result of the user clicking Cancel in the FDemo userform, the EntryPoint subroutine raises a custom error with the error number glUSER_ CANCEL. The line of code used to accomplish this is shown here:

If frmDemo.UserCancel Then Err.Raise glUSER_CANCEL

This notifies the central error handler of the error. The central error handler logs the error and returns control to the EntryPoint procedure so it can complete its cleanup activities prior to exiting.

The central error handler records all errors, including user cancel errors, in the error log. The error.log file will be located in the same directory as the ErrorHandlingDemo.xls workbook. The entry made in response to the user clicking the FDemo Cancel button will be similar to the entry shown below except it will be written to a single line in the error log file:

03/30/04 20:23:37  [ErrorHandlingDemo.xls]
MEntryPoints.EntryPoint, Error 18: UserCancel

If the user did not cancel program execution, the EntryPoint subroutine continues with the next line of code. This line is a call to the function that is designed to intentionally throw a divide by zero error. As you can see in Listing 12-11, this function's error handler will first call the central error handler to notify it of the error, then cause the function to return False in order to notify the calling procedure that an error has occurred. In this case, the error is catastrophic, so the calling procedure must terminate the program. It does this by raising a custom handled error, as shown below:

If Not bCauseAnError() Then Err.Raise glHANDLED_ERROR

Because this error was raised from an entry point procedure, the original error message stored by the central error handler will be displayed to the user, as shown in Figure 12-3.

Figure 12-3. The Error Message Displayed to the User

In this case the central error handler will log two entries: one from the function where the error originated and one from the entry point procedure.

03/30/04 20:44:20  [ErrorHandlingDemo.xls]
MSystemCode.bCauseAnError(), Error 11: Division by zero
03/30/04 20:44:20  [ErrorHandlingDemo.xls]
MEntryPoints.EntryPoint, Error 9999: Division by zero

Note that the first error number recorded is the original VBA error number, while the second error number (and any subsequent error numbers) is the value of our predefined glHANDLED_ERROR constant. If there are multiple procedures in the call stack when an error occurs, the central error handler will create a log entry for each one. This provides helpful information when debugging an error because it provides a record of the call stack at the time the error occurred.

After the error has been logged and the error message displayed, the central error handler returns control to the EntryPoint subroutine so it can complete its cleanup prior to exiting.