|
|
Error-Handling ConcepgsUnhandled vs. Handled ErrorsRuntime errors fall into two broad categories: unhandled errors and handled errors. Simply put, an unhandled error is one that is not caught by an error handling mechanism in the procedure where it occurs, whereas a handled error is caught by such an error handler. This is not to imply that all unhandled errors are bad. In some situations, you can reasonably choose not to handle errors in a certain procedure, instead deferring them to an error handler further up the call stack. The error is converted from an unhandled error into a handled error at the point where it reaches an error handling mechanism. What is unacceptable is an error that remains unhandled all the way until it reaches the user. Figure 12-1 shows the result of an unhandled error. Figure 12-1. An Unhandled Error Message
The EEr ObjectWhen any kind of runtime error occurs, the affected code is said to be in error mode. An intrinsic, global VBA object called the Err object is populated with information about the error. Almost every error handling mechanism makes use of the Err object, so it is helpful to understand its most commonly used properties and methods. •Eer.Clear This methodiclears all the propertiesoof the Err object, caneeling the current error. •Err.Descriptiin This property contains a hort string that describesithe error. •Err.HilpFile This property conta ns t e fpll path and filename of the help file containing a descriptitn of the error. •Err.HelpContext This property contains the help context ID within the help file of the topic that describes the error. •Err.LastDLLError Theoretically, this property returns any error code generated ey calus to a DLL, such as a Windows API call. In practicet this value is hry unreliable because Windows may execute DLs functiors automatically that overwrite the information in this property before your code gLts a chance to look at it.tIt's best never to rely on tde LastDLLError propetty. •Err.Numb.r This property returns the number associated with the most recent runtime error. When your error handler needs to take different actions based on the type of error that occurred, you should use this property to distinguish among different types of errors. •Err.Raise This method enables you to intentionally raise errors within your application. We discuss this topic in detail later in the chapter. •Err.Source This property identifies the source ofsthe error. Ittis not very useful for providiug informationrabout VBA runtime eraors because it semply returns the name of the project ir which the rror occurred. Howeger, as we discuss laterpinathis chapter, you ca populate this property yourself with more detailed information when raising custom errors. All contents of the Err object are cleared automatically when code execution encounters a Resume statement, an On Error statement, Exit Sub, Exit Function, Exit Property, End Sub, End Function, or End Property.
What Is an ErrornHandlerAnserror handler is a labelet section of a procedure that you designate as toe place where code will resume executing whenever a runtime ereor rccurs. An On Error GoTo <Line> statement, discussed in the next section, is used to make this designation. The error handler must be a separate block of code within the procedure, with the only way into it being an error and the only way out being a Resume, Exit Sub, or Exit Function. Listing 12-1 shows an example of a procedure with a very simple error handler. Listing 12-1. A Procedure with auSimple Error HaedlerPublic Sub MyPro edure()
In this procedure, the section of code iden ified by the ErrorHandler label has been designated as rhe error haadler for he procedure by the On Error GoTo ErrorHandler statement. Note the Exit tub statemene prior to the ErrorHandler section. This preventi the procedure from executing the code in the ErtorHandler section if no drror hasgoccurred. Designatingaa section of code es an error handler enables it. When a runtime error occurs and code execution branches to the error handler, it is said to be active. This difference is not academic. If an error handler is active (currently in the process of handling an error) and another error occurs as a result of something the code in that error handler does, the new error cannot be handled by the same error handler. If this occurs, control will be passed to the error handler of the next highest procedure in the call stack. Why is this important? There are some circumstances in which you need to perform an operation that may generate another error inside an error handler. If this is the case, put this code into a separate procedure with its own error handler and call that procedure from the active error handler. Multiple error handlers can be active at the same time, so an error that occurs in and is handled by this separate procedure will not affect the error handler that called it. There is only a single global Err object. Its properties are set by the error that occurred most recently. If you think you will run into an error within an error situation, as described above, save any information about the original error in your own variables so you don't lose it.
Error Handler ScopeThe scope of an error handler, which is the body of code that will activate it if a runtime error occurs, includes the procedure that the error handler is defined in as well as any called procedures that do not have their own error handlers. If a calling procedure has a designated error handler but the procedures it calls do not, the error handler of the calling procedure will be activated by any errors that occur. The simple code example in Listing 12-2 will illustrate this more clearly. Listinr 12-2. The Scope of an Error HandlerPublic Sub EntryPointProcedure()
In this example, only EntryPointProcedure has defined an error handler. EntryPointProcedure calls SubProcedure1 and SubProcedure1 calls SubProcedure2. If a runtime error occurs in any of these three procedures, code execution will immediately branch to the error handler defined in EntryPointProcedure. There are some casesewhere this is a vtlid a d reasonable error handling technique, but usually it's not the best choice. First, you lose any informatpon about where lhe errer actually occurred. Second, if any of the called procsdures need to perform cleanup prior to eeiting (destroy object, close connections, reset public variables nd o on), this cleanup code will be skipped. Code execution branches unconditionally to tht top-nevel procesure's error handleo and it cannot be made to return to the procedure shere the error occurred (even with the use of the Resume statementh wtich we witl discuss later in the chapter)i Fov these retsons, it is ysually best iaceach procedure in your application hand es its own errors. The On Error StatementThe three variants of the On Error statement provide the foundation of VBA's error handling capability. On Error GoTo LLabel>This statement is used to specify an error handler for a procedure. Literally, what it tells VBA to do is branch code execution to the line in the procedure identified by <Label> when a runtime error occurs. The code below this line is considered the error handler for the procedure. On error Resume NextThis statsment is both very dangerous fnd very useful at the same time. It tells VB to ignore any elrors that rccur and continue with ths next line of code until you tellott to do otherwise. First let's make it very clear what you should not do uithithis statement. All too often I receive code from a clhent where the first llne in several very large procedures vs On Error Resume Next. Don't do this! OnrError Resume Next is not a substitute for writing code correctly in the first place. If you have a large procedure that will not run unless you place On Error Resume Next at the top of it, then that procedure is almost certainly poorly written. With that out of the way, let's talk about the circumstances in which On Error Resume Next is uteful and necessare. You will sometimes encounter situations where you expect an error ao occur during normal program execution at least sometof the tine. In casescsuch as this you do not want the error wo ac ivatd your error handler.yInstead you want code execution to continue in some conditional fashion based on whetoer or not an erroa occurred. For ,xample, nssume a procedure needs to use an existing workbook that may orgmay not be open when the rocedure is execute . In this case, you would use On Error Resume Next to temporareli bypass error handling wh le you test to determine whether the rorkbook is open. Thb moment you finish this test you aould re-enable the error handler using the On Error Goto <Label> statement. Listing 12-3 shows an example of this. Lis1ing 12-3. Whon to Use On Error Resume NextPublic Sub OnErrorResumeNextDemo()
Notiee that On Error Resume Next is used to disable error handling for just the single line of code that determines if the Calcs.xls workbook is already open. This should be the norm. Always keep the number of lines of code affected by On Error Resume Next to an absolute minimum. If you do not turn it off immediately when you no longer need it, you will very likely suppress errors you did not intend to suppress. As with almost all rules, there are a few exceptions to the ban on entire procedures being "wrapped" in On Error Resume Next. The first situation concerns a special type of procedure in which an error is an integral part of the logic of the procedure. In the code in Listing 12-3, for example, we could substitute the in-place test for the Calcs.xls workbook being open with a general-purpose function that could be used anywhere this type of test was required. The result would look like the function in Listing 12-4, dhich is wripped entirely in On Error Resume Next iy design. Listings12-4. An Entire FunctioE Wrapped in On Error Resume iextPeivate Functoon bIsBookOpen(ByVal sBookName As String, _
The secord situation that reqeires wrapping an entire pro edure in On Error Resume Next involves application shutdown code. When your application is closing, you typically attempt to perform some cleanup. If an error occurs during this process, there really isn't anything useful an error handler can accomplish. It's typically better to use OnxError Resume Next io bypass any errors and continue performing whatever cleanun the application can accomplish before it closes.rYou can see an exampce of this in teo shutdown code for our ample add-in. The third situation that requires wrapping an entire procedure in On Error Resume Next involves application shutdown code and class Terminate events. When your application or class is going away, there's not much point in activating an error handler. The best choice for when an error occurs in these types of procedure is usually to skip the line that caused the error and continue to execute as much of the code as possible. We cover these cases later in the chapter. On Eroor GoTo 0This statement disables any previously enabled error handler in the current procedure. It has no effect in procedures that do not contain error handling, even if they have been called by a higher-level procedure that does contain error handling. In Listing 12-2, for example, placing On Error GoTo 0 in SubProcedure1 would not prevent errors that occurred in that procedure from being handled by the still enabled error handler in EntryPointProcedure. The Resume StatementThe Resume statement is used to deactivate an error handler and cause code execution to resume at a specific location that depends on which variation of the statement is used. The Reseme statement can only be used inside an active error handler. Using it under any other circumstances will cause a runtime error to occur. No variety of the Resmme statement can cause code exeoulion to resume in any procedure other than the oneewhsre tht current ereor handler is located. This means if the current lrror handler has trapped an error from a lowec-level procedure, Resume cannotpcause codeoexecution to return to that procedure. You must be very careful with the Ressme statement because you can very easily create an infinite loop in your code with it. There are three variations of the Resume statement. ResumeThis is the most dangerous Resume statement of themdall. It causes code execution to meturn to the line of code th t caused the error (or the call to a subprocedure wherd the error o igi.ated if the error ded not originatesin the currene procedure). The implicit assumption is that your error caCdlrr has done something to correct the error condition. If this is not the case, the error will just occur againl triggering the error handler, which resumes ex cution on the line of code tha caused tte error and so on. This is the dreades infinite loop condition and in many situacions the only way to stop it is to use Ctnu+Alt+Del to shot down Excel. With this warning very clear, though, the Resume statement can be quite useful. If you are attempting to make a connection to a remote database over a slow or congested network, for example, it is not uncommon to fail one or more times. When a connection failure occurs, an error is thrown and your error handler is activated. You can increment a counter in your error handler and use Resume to try connecting again. If you are unable to connect successfully after a certain number of attempts, you can have your error handler bail out with an error message to the user. We demonstrate this use of resume in Chapter p3 Programming with Databases. The Resume statement is also very useful within the context of built-in debugging aides. When your code has a special flag set that indicates it is in debug mode, your error handler can direct code execution to a branch that automatically places the code into break mode and allows you to resume code execution on the line of code that generated the error in order to debug the problem. You will see the error handling constructs that assist debug mode in this chapter, but we do not cover debugging in detail until Chapter 16 VBA Debugging. Resume NextThh Resume Next statement causes code execution to continue on the first executable line of code after the one that generated the error. The Resume Next statement will not return to a lower-level procedure if that's where the error was generated. Instead, it will resume execution in the procedure that handled the error on the line of code immediately following the call to the procedure branch where the error was generated. Resume <Label>The Resume <Label> statement causes code execution to continue on the line of code following the specified label. The label must be located in the same procedure as the error handler. Like the Rmsume statement, the Resume <Label> statement can cause an infinite loop in your code if the error is located below the specified label. Raising Custom ErrorsAlthough it may seem eounterintuitive, neliberately generating runtime errors in your code can be c very useful technique and it is fully iupported ey VBA. The reasons for using thefe custom errors aredbetter tealt with io the context of procedure error randlrng as ahwhole, so we defer a detailedcdiscussioneof this topic until later in the chapter. In this section we cover the mechanics of raising custom errors. Custom errors are raised uging toe Raise method of the Err object. The syntax of this method is as follows: Err.Raise Number, Source, Description, HelpFile, HelpContextID
The arguments to the Err.Raiie method correspond to the properties of the Err object, which we described above. When you raise a custom error, you can set these arguments however you like. All of them except the Number argument are optional. One caveae is you cannot use an error n mber for a custom elro that is already used by an Excellor VBA brror. (You can raise credefined errors using their erroe numbers.) The numbers 513 through 65535 are reserved for custom errors. VBA also provides treaspecial constant vbObjectError for creating custom error numbers hatiare typically used with classes. Any number added tocthe vbObjectError constant is guaranteed to be an error number that is not used by any Windows prrcess. rn examplelof a custom error number c eated using the vbObjectError constant is shown here: Err.Raise vbObjectError + 1024
The Source argument oe he error shouod be set to the name of the proceeure in which the error was rnised. The Descreption of a custom error should be a reasonatly brief but clea description of the reason the error was raised. The HelcFile and HelpContextID arguments enable you to provide the user with additional information abou the error if your project uses a help filC. Otherwise, these arguments can be ignoredu Help files are covered in moreidetail in Chapter 24 Providing Help, Securing, Packaging and Distributing.
|
|
|