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

Error-Handling Concepts

Unhandled vs. Handled Errors

Runtime 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 Err Object

When 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.

Err.Clear
This method clears all the properties of the Err object, canceling the current error.

Err.Description
This property contains a short string that describes the error.

Err.HelpFile
This property contains the full path and filename of the help file containing a description 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 by calls to a DLL, such as a Windows API call. In practice, this value is very unreliable because Windows may execute DLL functions automatically that overwrite the information in this property before your code gets a chance to look at it. It's best never to rely on the LastDLLError property.

Err.Number
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 of the error. It is not very useful for providing information about VBA runtime errors because it simply returns the name of the project in which the error occurred. However, as we discuss later in this chapter, you can populate this property yourself with more detailed information when raising custom errors.

NOTE

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 Error Handler

An error handler is a labeled section of a procedure that you designate as the place where code will resume executing whenever a runtime error occurs. 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 a Simple Error Handler

Public Sub MyProcedure()
On Error GoTo ErrorHandler
' Lots of code here.
Exit Sub
ErrorHandler:
MsgBox Err.Description, vbCritical, "Error!"
End Sub

In this procedure, the section of code identified by the ErrorHandler label has been designated as the error handler for the procedure by the On Error GoTo ErrorHandler statement. Note the Exit Sub statement prior to the ErrorHandler section. This prevents the procedure from executing the code in the ErrorHandler section if no error has occurred.

Designating a section of code as 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.

NOTE

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 Scope

The 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.

Listing 12-2. The Scope of an Error Handler

Public Sub EntryPointProcedure()
On Error GoTo ErrorHandler
SubProcedure1
Exit Sub
ErrorHandler:
MsgBox Err.Description, vbCritical, "Error!"
End Sub
Private Sub SubProcedure1()
SubProcedure2
End Sub
Private Sub SubProcedure2()
Dim lTest As Long
' This error will activate the error handler
' in the entry point procedure.
lTest = 1 / 0
End Sub

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 cases where this is a valid and reasonable error handling technique, but usually it's not the best choice. First, you lose any information about where the error actually occurred. Second, if any of the called procedures need to perform cleanup prior to exiting (destroy object, close connections, reset public variables and so on), this cleanup code will be skipped. Code execution branches unconditionally to the top-level procedure's error handler and it cannot be made to return to the procedure where the error occurred (even with the use of the Resume statement, which we will discuss later in the chapter). For these reasons, it is usually best if each procedure in your application handles its own errors.

The On Error Statement

The three variants of the On Error statement provide the foundation of VBA's error handling capability.

On Error GoTo <Label>

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 Next

This statement is both very dangerous and very useful at the same time. It tells VBA to ignore any errors that occur and continue with the next line of code until you tell it to do otherwise. First let's make it very clear what you should not do with this statement. All too often I receive code from a client where the first line in several very large procedures is On Error Resume Next. Don't do this! On Error 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 useful and necessary. You will sometimes encounter situations where you expect an error to occur during normal program execution at least some of the time. In cases such as this you do not want the error to activate your error handler. Instead you want code execution to continue in some conditional fashion based on whether or not an error occurred.

For example, assume a procedure needs to use an existing workbook that may or may not be open when the procedure is executed. In this case, you would use On Error Resume Next to temporarily bypass error handling while you test to determine whether the workbook is open. The moment you finish this test you would re-enable the error handler using the On Error Goto <Label> statement. Listing 12-3 shows an example of this.

Listing 12-3. When to Use On Error Resume Next

Public Sub OnErrorResumeNextDemo()
Dim wkbCalcs As Workbook
On Error GoTo ErrorHandler
' Lots of code here.
' Test if the Calcs.xls workbook is open.
Set wkbCalcs = Nothing
On Error Resume Next
Set wkbCalcs = Application.Workbooks("Calcs.xls")
On Error GoTo ErrorHandler
' If the workbook wasn't open we need to open it.
If wkbCalcs Is Nothing Then
Set wkbCalcs = Application.Workbooks.Open( _
ThisWorkbook.Path & "\Calcs.xls")
End If
' Lots more code here.
Exit Sub
ErrorHandler:
MsgBox Err.Description, vbCritical, "Error!"
End Sub

Notice 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, which is wrapped entirely in On Error Resume Next by design.

Listing 12-4. An Entire Function Wrapped in On Error Resume Next

Private Function bIsBookOpen(ByVal sBookName As String, _
ByRef wkbBook As Workbook) As Boolean
' Checks to see if the specified workbook is open. If it is,
' a reference to it is returned in the wkbBook argument.
On Error Resume Next
Set wkbBook = Application.Workbooks(sBookName)
bIsBookOpen = (Len(wkbBook.Name) > 0)
End Function

The second situation that requires wrapping an entire procedure 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 On Error Resume Next to bypass any errors and continue performing whatever cleanup the application can accomplish before it closes. You can see an example of this in the shutdown code for our sample 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 Error GoTo 0

This 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 Statement

The 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 Resume 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 Resume statement can cause code execution to resume in any procedure other than the one where the current error handler is located. This means if the current error handler has trapped an error from a lower-level procedure, Resume cannot cause code execution to return to that procedure.

You must be very careful with the Resume statement because you can very easily create an infinite loop in your code with it. There are three variations of the Resume statement.

Resume

This is the most dangerous Resume statement of them all. It causes code execution to return to the line of code that caused the error (or the call to a subprocedure where the error originated if the error did not originate in the current procedure). The implicit assumption is that your error handler has done something to correct the error condition. If this is not the case, the error will just occur again, triggering the error handler, which resumes execution on the line of code that caused the error and so on. This is the dreaded infinite loop condition and in many situations the only way to stop it is to use Ctrl+Alt+Del to shut 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 13 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 Next

The 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 Resume statement, the Resume <Label> statement can cause an infinite loop in your code if the error is located below the specified label.

Raising Custom Errors

Although it may seem counterintuitive, deliberately generating runtime errors in your code can be a very useful technique and it is fully supported by VBA. The reasons for using these custom errors are better dealt with in the context of procedure error handling as a whole, so we defer a detailed discussion of this topic until later in the chapter. In this section we cover the mechanics of raising custom errors.

Custom errors are raised using the 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.Raise 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 caveat is you cannot use an error number for a custom error that is already used by an Excel or VBA error. (You can raise predefined errors using their error numbers.) The numbers 513 through 65535 are reserved for custom errors. VBA also provides the special constant vbObjectError for creating custom error numbers that are typically used with classes. Any number added to the vbObjectError constant is guaranteed to be an error number that is not used by any Windows process. An example of a custom error number created using the vbObjectError constant is shown here:

Err.Raise vbObjectError + 1024

The Source argument of the error should be set to the name of the procedure in which the error was raised. The Description of a custom error should be a reasonably brief but clear description of the reason the error was raised. The HelpFile and HelpContextID arguments enable you to provide the user with additional information about the error if your project uses a help file. Otherwise, these arguments can be ignored. Help files are covered in more detail in Chapter 24 Providing Help, Securing, Packaging and Distributing.