Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA®

Practical Example

PETRAS Timesheet

In the Practical Example section of this chapter, we retrofit our time-entry add-in with a complete centralized error handling system. This is the best example to examine if you want to see how a real-world error handling system is constructed.

The process of retrofitting our add-in with error handling is tedious but uncomplicated. All entry point procedures are outfitted with the entry point version of the error handling code and all subprocedures are converted into Boolean functions and outfitted with the function version of the error handling code.

The only code example from the new version of the PETRAS add-in that we show here is the Auto_Open procedure, in Listing 12-13. This is the entry point procedure that makes the most calls to lower-level procedures. It also has the unique requirement to shut down the application if an error occurs. This makes it the most interesting example of error handling in the add-in. You are encouraged to examine the complete revised code for the PETRAS add-in, located on the CD in the Application folder for this chapter, for a complete view of the error handling system.

Listing 12-13. The PETRAS Add-in Auto_Open Procedure with Error Handling

Public Sub Auto_Open()
Const sSOURCE As String = "Auto_Open"
Dim bErrorOut As Boolean
Dim wkbBook As Workbook
' The very first thing your application should do upon
' startup is attempt to delete any copies of its
' command bars that may have been left hanging around
' by an Excel crash or other incomplete exit.
On Error Resume Next
Application.CommandBars(gsBAR_TOOLBAR).Delete
On Error GoTo ErrorHandler
' Initialize global variables.
If Not bInitGlobals() Then Err.Raise glHANDLED_ERROR
' Assume False until an error is encountered.
bErrorOut = False
' Make sure we can locate our time entry workbook before we
' do anything else.
If Len(Dir$(gsAppDir & gsFILE_TIME_ENTRY)) = 0 Then _
Err.Raise glHANDLED_ERROR, sSOURCE, gsERR_FILE_NOT_FOUND
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.StatusBar = gsSTATUS_LOADING_APP
' Build the command bars.
If Not bBuildCommandBars() Then Err.Raise glHANDLED_ERROR
' Set the initial state of the application.
If Not gclsEventHandler.SetInitialStatus() Then _
Err.Raise glHANDLED_ERROR
ErrorExit:
' Reset critical application properties.
ResetAppProperties
' If an error occurred during the Auto_Open procedure,
' the only option is to exit the application.
If bErrorOut Then ShutdownApplication
Exit Sub
ErrorHandler:
' This variable informs the clean up section when an error
' has occurred.
bErrorOut = True
If bCentralErrorHandler(msMODULE, sSOURCE, , True) Then
Stop
Resume
Else
Resume ErrorExit
End If
End Sub

This version of the Auto_Open procedure is very different from the version we last saw in Chapter 8 Advanced Command Bar Handling. You will notice that with the exception of the ResetAppProperties procedure and the ShutdownApplication procedure, every procedure called by Auto_Open is now a Boolean function whose return value indicates success or failure.

The ResetAppProperties procedure is an exception because it is the rare case of a procedure in which nothing can go wrong. This type of procedure was described in the Trivial Procedures section above and the ResetAppProperties procedure itself was shown in Listing 12-8. The ShutdownApplication procedure is an exception because it is the last procedure run before the application closes. Similar to the bCentralErrorHandler function we examined in Listing 12-9, it doesn't make any sense to try and handle errors that occur in this procedure, so the entire ShutdownApplication procedure is wrapped in On Error Resume Next.

We've also added a new bErrorOut flag variable. This is because the cleanup section for the Auto_Open procedure (the section of code between the ErrorExit label and the Exit Sub statement) needs to know whether an error has occurred when it is executed. The error handler for the Auto_Open procedure sets the bErrorOut variable to True when an error occurs. It then calls the central error handler and, after the central error handler returns, it redirects code execution to the cleanup section, starting directly below the ErrorExit label. If the bErrorOut variable indicates to the cleanup section that an error has occurred, the cleanup section initiates application shutdown by calling the ShutdownApplication procedure.

PETRAS Reporting

As mentioned in the section Complex Project Error Handler Organization earlier in this chapter, there are two or three complex error handling system designs commonly used in Excel VBA applications and several minor variations on each of those. Throughout this chapter, we've demonstrated the concepts of error handling using a system known as the function return value method. In this system, every subprocedure is written as a Boolean function whose return value indicates success or failure. If an error occurs, it is trapped in the function's error handler, which logs the error and then sets the function's return value to False. The calling procedure tests the return value and (usually) raises another error to trigger its own error handler, and so the error bubbles up the call stack. Listing 12-14 shows the order in which lines are executed in a nested set of procedures.

Listing 12-14. The Order of Execution When Using the Function Return Value System

Sub EntryPoint()
Const sSOURCE As String = "EntryPoint"
1   On Error GoTo ErrorHandler
2   If Not bSubProc1() Then
20      Err.Raise glHANDLED_ERROR
End If
ErrorExit:
'Run some cleanup code
23  Exit Sub
ErrorHandler:
21  If bCentralErrorHandler(msMODULE, sSOURCE, , True) Then
Stop
Resume
Else
22      Resume ErrorExit
End If
End Sub
Function bSubProc1() As Boolean
Const sSOURCE As String = "bSubProc1"
Dim bReturn As Boolean
3   On Error GoTo ErrorHandler
4   bReturn = True
5   If Not bSubProc2() Then
14      Err.Raise glHANDLED_ERROR
End If
ErrorExit:
'Run some cleanup code
18  bSubProc1 = bReturn
19  Exit Function
ErrorHandler:
15  bReturn = False
16  If bCentralErrorHandler(msMODULE, sSOURCE) Then
Stop
Resume
Else
17      Resume ErrorExit
End If
End Function
Function bSubProc2() As Boolean
Const sSOURCE As String = "bSubProc2"
Dim bReturn As Boolean
6   On Error GoTo ErrorHandler
7   bReturn = True
'Cause an error
8   Debug.Print 1 / 0
ErrorExit:
'Run some cleanup code
12  bSubProc2 = bReturn
13  Exit Function
ErrorHandler:
9   bReturn = False
10  If bCentralErrorHandler(msMODULE, sSOURCE) Then
Stop
Resume
Else
11      Resume ErrorExit
End If
End Function

You'll notice that in the vast majority of cases, the calling procedure handles a False return value just by raising another error to trigger its own error handler.

Error handling in VBA is designed such that any unhandled errors and any errors raised within an error handler automatically fire the error handler of the calling procedure. So if we raise an error within the subprocedure's error handler, it will automatically trigger the calling procedure's error handler, without the calling procedure having to test for a False return value and trigger the error handler itself. The same will happen if we raise an error at the end of the central error handler. This is known as the re-throw system of error handling and has been implemented in the PETRAS reporting application. The main advantages of the re-throw system are that we can use it within Sub, Property and Function procedures, and our functions' return values can be used for their results instead of success/failure indicators. The main disadvantage is that it becomes slightly harder for us to include complex cleanup code if an error occurs.Listing 12-9, with the extra code to implement the re-throw method highlighted.

Listing 12-15. A Central Error Handler Implementing the Re-Throw System

Public Function bCentralErrorHandler( _
ByVal sModule As String, _
ByVal sProc As String, _
Optional ByVal sFile As String, _
Optional ByVal bEntryPoint As Boolean = False, _
Optional ByVal bReThrow As Boolean = True) 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(), "dd mmm yy hh:mm:ss"); sLogText
If bEntryPoint Or Not bReThrow Then Print #iFile,
Close #iFile
' 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
'If we're using re-throw error handling,
'this is not the entry point and we're not debugging,
're-raise the error, to be caught in the next procedure
'up the call stack.
'Procedures that handle their own errors can call the
'central error handler with bReThrow:=False to log the
'error, but not re-raise it.
If bReThrow Then
If Not bEntryPoint And Not gbDEBUG_MODE Then
On Error GoTo 0
Err.Raise lErrNum, sFullSource, sErrMsg
End If
Else
'Error is being logged and handled,
'so clear the static error message variable
sErrMsg = vbNullString
End If
End Function

Listing 12-16 shows the order in which lines are executed in a nested set of procedures when the re-throw system is implemented.

Listing 12-16. The Order of Execution When Using the Re-Throw System

Sub EntryPoint()
Const sSOURCE As String = "EntryPoint"
1   On Error GoTo ErrorHandler
2   SubProc1
ErrorExit:
11  Exit Sub
ErrorHandler:
'Run simple cleanup code here
9   If bCentralErrorHandler(msMODULE, sSOURCE, , True) Then
Stop
Resume
Else
10      Resume ErrorExit
End If
End Sub
Sub SubProc1()
Const sSOURCE As String = "SubProc1"
3   On Error GoTo ErrorHandler
4   SubProc2
Exit Sub
ErrorHandler:
'Run simple cleanup code here
8   If bCentralErrorHandler(msMODULE, sSOURCE) Then
Stop
Resume
End If
End Sub
Sub SubProc2()
Const sSOURCE As String = "bSubProc2"
5   On Error GoTo ErrorHandler
'Cause an error
6   Debug.Print 1 / 0
Exit Sub
ErrorHandler:
'Run simple cleanup code here
7   If bCentralErrorHandler(msMODULE, sSOURCE) Then
Stop
Resume
End If
End Sub

Using the re-throw method, we can only include cleanup code at the start of our error handlers (before the call to the central error handler), so we have to be extremely careful to ensure that the cleanup code does not cause any more errors to occur and does not reset the Err object. In practice, this means the re-throw method is best used when there is no cleanup required, or when the cleanup is trivial and could not cause an error.

In Listing 12-15, we added an optional parameter to the central error handler that enables us to stop the error being re-raised. This results in exactly the same behavior as the function return value system, thereby allowing us to use that method in the exceptional cases that require complex cleanup code. This parameter is used in the ConsolidateWorkbooks procedure to handle errors that occur while extracting the data from a timesheet workbook. In that case, we call the central error handler to log the error, then close the problem timesheet workbook and continue with the next one.

Whether to use the function return value or re-throw system of error handling is largely a philosophical decision. Both have their advantages and disadvantages and will be more or less appropriate for different situations. Either system is better than having no error handling at all.