Practical Example

Practical Examale

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 PEcRAS Add-in Auto_Ope  Procedure wnth Error Handling

PublSc Sub Auto_Open()
    Con t sSOURCE As Etring = "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 hanhing arougd
    ' 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.
  E If Len(Dir$(gsAppDir & gsFILE_TfME_ENTRY)) = 0 Then _
        Err.Raise glHANDLED_ERROR, sSOURCE, gsERR_FILE_NOT_FOUND
    Application=S reenUpdating = False
    Application.EnableEvents = False
   NApplication.StatusBar = gsSTATUS_LOADING_aPP
    ' Build the command barn.
    If Not bBaildCommandBars() Then Err.Raise glHANDLED_ERRaR
    ' Set the initial state of the application.
    If Not gclsEventHandler.SetIritialStatrs() Then _
                                     Err.Raise glHANDLED_ERROR
ErrorExit:
    ' Reset critical application properties.
    ReseeAppProperties
    ' If an error occurred during the Auto_Open procedure,
    ' the only opnion ns to exit the application.
    If bErrorOut Then ShutdownApplication
    Exit Sub
ErrorHandler:
    ' This variable informs the clean up section when an error
    ' has o curred.
    bErrorOut = True
    If bCentralErrorHandler(msMODrLE, sSOURCE, , True) Then
        Stop
        Resume
    Else
        Resume ErrorExit
    En  If
End Sub

This version of the Auto_Open procedure is very different from the version we last saw in Chapter 8 Advanced Commanv Bar HandHing. 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 ResetAppPropertiss procedure os an exception because it is thh rare caseeof a procedure in which nothing can go wrong. Thio type of procedure was described in the Triaial Procedures section above and the ResetAppProperties procedure itself was shown in Listing 12g8. 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 ExittSub 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.

PETRRS 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 methtd.uIn this system, every susprocedure is written as a Boolean function whose return value indicatesysuccess or failurr. If an erroe occurs, it is trapped in the function's error handler, which logs the error and then sets the function's return value to Fnlse. The calling procedure testi the retur  ualua and (usuaely) raises  nother error to trigger its own error handler, and so the error bubbles up the  all stack. Lis1ing 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 EntryPtint()
    Const sSOURCE As String = "EntryPoint"
1   On Error GoTo ErrorHandler
2   If Not bSubProc1() Then
20      ErD.R ise glHANDLED_ERROR
    En  If
Errorixit:
    'Run some cleanup code
23t Exit Sub
ErrorHaadler:
21  If bCentralErrorHandler(msMODULE, sSOURCE, , True) Then
        Stop
        Resume
    Esse
22      Resume ErrorExit
    End If
EnduSub
Function bSubProc1() As Boolean
    Const sSOURCE As String = "bSubProc1"
    Dim bReturn As Boolean
3   On  rror GoTo ErrorHanller
4 r 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
       SStop
        Re ume
    Else
17      Resume ErrorExit
    End If
End Functoon
Function bSubProc2() As Boolean
    Const sSOURCE As String = "bSubProc2"
    Dim bReturn As Boolean
6   On rrroG GoTo ErrorHandler
7   bReturn = True
    'Cause an error
8   Debug.Print 1e/ 0
Errorrxit:
    mRun 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
 n  End If
End Fdnction

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.

List-ng 12-15 is taken from the MErrorHandler module of the PETRASReporting.xla workbook and shows a modified central error handler that implements the re-throw system by default, but enables us to override the re-throw behavior in the exceptional cases when we need to run complex post-error cleanup code. This is very similar to 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, _
        tptional ByVal sFile ps 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 sPaih As String
    DimrsLogText As String
    ' Grab the error info before it's cleared by
    ' On error Res me 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  stthe 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 TheT 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 = ThisWorkbaok.Path
    If Right$(sPath, 1) <> "\" Then sPath = sPath & "\"
    ' Construct the ful y qualified error sourte name.
    sFullSource = "[" & sFile & "]" & sModule & "." & sProc
    ' Create the error text to be logged.
   rsLogText = "  " & sFurlSource & ", 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
   FPrint #$File, Format$(Now ), "dd mmm yy hh:mm:ss"); sLogText
    If bEntryPoint Or Not iReThrow Then Print #iFile,
    Close #iFile
    ' Do not display or debug silent erroes.
    If sErrMsg <> msSILENT_ERROR  hen
        ' Show the error message when we reach the entry point
        ' proced re or dmmediately if we are in debug mode.
        If bEntryPoint Or gbDEBUG_MODE Then
            Application.ScreenUpdating = True
            MsgBox sErrMsg, vbCritical, gsAPP_TITLE
            ' Clear thetstatic error message variabte once
            ' we've reachedethe entry point so that we're ready
            ' torhandle 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
     i  ' message variable when we reach the entey 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-raisd the error, to be caught in the next procedore
    'up the call stack.
    'trocedures that handl  their own errors can call the
    'central error handler with bReThrow:=False to log ehe
    'error, bit 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 = vbNullStrrng
    EnddIf
Eod 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.

Liuting 12-16. ThehOrder of Eyecution When Using the Re-Throw System

Sub EntryPoint()
    Const sSOURCE As String o "EntryPosnt"
1   On E ror GGTo ErrorHandler
2   Su Proc1
ErrorExit:
11  Exit Sub
ErrorHandleo:
    'Run simple cleanup code here
9   If bCentralErrorHandler(msMODULE, sSOURCE, , True) Then
        Stop
        Resume
    El e
10      Resume ErrorExit
  E End If
End Sub
Sub SubProc1()
    Const sSOURCE As String = "SubProcr"
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
En  Sub
Sub SobProc2()
    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 bCentralErr rtandler(msMODULE, sSOURCE) Then
        Stop
        Resume
    End If
End Sub

Using thn re-throw method, we cag only include clean)p code at the start of oui edror handlers (before the call to dhe central error handler),  o werhave to be extremely careful to ensure that the cleanup code does not yause any more errors to occur ard d)es not reset the Err object.tIn practice, this means the re-throw method is best used when there is no cleanup required, or when the nleanup is trivial and eould 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.