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

Complex Project Error Handler Organization

There are two or three complex error handling system designs commonly used in Excel VBA applications and several minor variations on each of those. If designed correctly, all of them will accomplish the same purpose; gracefully handling runtime errors encountered by your application. The complex error handling system we introduce in this section has the following characteristics:

All nontrivial procedures contain error handlers.

All procedure error handlers call a central error handling function. This function tracks and logs each error, decides whether or not to display an error message to the user and tells the calling procedure how to proceed by way of its return value.

All entry point procedures are subroutines. An entry point procedure is any procedure in which code execution begins. This includes subroutines in standard modules called by toolbar buttons and event procedures executed in response to some user action.

All nontrivial lower-level procedures (all procedures that are called by entry point procedures) are Boolean functions whose return value indicates whether the function succeeded or failed.

We cover all of these points in detail as this section progresses, but we wanted to give you a high-level overview of how our error handling system works.

An important point to keep in mind as you read this section is that entry point procedures must only be triggered directly by some user action. One entry point procedure must never call another entry point procedure or the error handling system described here will break down. If two entry point procedures need to run the same code, the common code should be factored out into a lower-level function that can be called by both entry point procedures.

Procedure Error Handlers

The Central Error Handler section later in the chapter.

Listing 12-6. Subroutine and Function Error Handlers

Private Const msMODULE As String = "MMyModule"
Public Sub MyEntryPointSubroutine()
Const sSOURCE As String = "MyEntryPointSubroutine()"
On Error GoTo ErrorHandler
' Call the lower level function.
If Not bMyLowerLevelFunction() Then
Err.Raise glHANDLED_ERROR
End If
ErrorExit:
' Cleanup code here.
Exit Sub
ErrorHandler:
If bCentralErrorHandler(msMODULE, sSOURCE, , True) Then
Stop
Resume
Else
Resume ErrorExit
End If
End Sub
Private Function bMyLowerLevelFunction() As Boolean
Const sSOURCE As String = "bMyLowerLevelFunction()"
Dim bReturn As Boolean  ' The function return value
On Error GoTo ErrorHandler
' Assume success until an error is encountered.
bReturn = True
' Operational code here.
ErrorExit:
' Cleanup code here.
bMyLowerLevelFunction = bReturn
Exit Function
ErrorHandler:
bReturn = False
If bCentralErrorHandler(msMODULE, sSOURCE) Then
Stop
Resume
Else
Resume ErrorExit
End If
End Function

The general layout of the error handlers is very similar in both cases. The only significant difference is the function must return a value indicating success or failure without violating the single exit point principle, so we have added the structure required to accomplish that to the function's error handler.

Listing 12-6 shows examples of very simple error handlers. They don't try to respond to errors other than by invoking the central error handler and exiting. In many situations, you will be aware of errors that might occur but can be corrected in the error handler and allow code execution to continue. A more complex error handler, such as the one shown in Listing 12-7, enables you to accomplish this.

Listing 12-7. A More Complex Error Handler

ErrorHandler:
Select Case Err.Number
Case 58
' File already exists. Resolve the problem and resume.
Resume
Case 71
' Disk not ready. Resolve the problem and resume.
Resume
Case Else
' The error can't be resolved here. Invoke the central
' error handling procedure.
If bCentralErrorHandler(msMODULE, sSOURCE, , True) Then
' If the program is in debug mode, execution
' continues here.
Stop
Resume
Else
Resume ErrorExit
End If
End Select
End Function

A Select Case statement is used to identify error numbers that can be handled within the error handler. If the number of the error trapped is not one of those handled by a specific Case clause, it falls through to the Case Else clause, which invokes the central error handler.

The Central Error Handler section below, we describe how the central error handler determines when an error message should be displayed and how it influences program execution after the error is handled.

Trivial Procedures

At the beginning of this section we stated that all nontrivial procedures contain error handlers. That begs the question of what is a trivial procedure that wouldn't require an error handler. A trivial procedure is either so simple that an error cannot occur within it or is structured such that any errors that do occur are ignored. Listing 12-8 shows examples of both types.

Listing 12-8. Trivial Procedures Don't Require Error Handlers

' This subroutine is so simple that no errors
' will ever be generated within it.
Public Sub ResetAppProperties()
Application.StatusBar = False
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.EnableCancelKey = xlInterrupt
Application.Cursor = xlDefault
End Sub
' Any errors that occur in this function are ignored.
Private Function bIsBookOpen(ByVal sBookName As String, _
ByRef wkbBook As Workbook) As Boolean
On Error Resume Next
Set wkbBook = Application.Workbooks(sBookName)
bIsBookOpen = (Len(wkbBook.Name) > 0)
End Function