Practical ExamplePETRAS TimesheetIn 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 HandlingThis 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 ReportingAs 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 SystemYou'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 SystemListing 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 SystemUsing 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. |