| Your VBA procedures are, in final analysis, really quite dumb. After all, they can only do what you, the programmer, tell them to do. For example, if you tell a program to copy a file to a nonexistent disk, the dim brute just doesn't have the smarts to pull back from the abyss.  | There's a popular and appealing tale of how the word bug came about. Apparently, an early computer pioneer named Grace Hopper was working on a machine called the Mark II in 1947. While investigating a glitch, she found a moth among the vacuum tubes, so from then on glitches were called bugs. Appealing, yes, but true? Not quite. In fact, engineers had already been referring to mechanical defects as " bugs " for at least 60 years before Ms. Hopper's discovery. As proof, the Oxford English Dictionary offers the following quotation from an 1889 edition of the Pall Mall Gazette : "Mr. Edison, I was informed, had been up the two previous nights discovering 'a bug' in his phonograph ”an expression for solving a difficulty, and implying that some imaginary insect has secreted itself inside and is causing all the trouble." |
Later in this chapter you'll learn quite a few techniques that will prove invaluable for stomping on program bugs, so hopefully you'll be able to ship problem-free applications. However, a good and careful programmer always assumes that something, somewhere, at some time can and will go wrong (think of this as a kind of "Murphy's Law of Coding"). Given this heightened (enlightened?) state of paranoia , you must code your projects to allow for potential errors, no matter how obscure. A properly designed program doesn't leave you or another user out in the cold if an error rears its ugly head. Instead, you need to install code that traps these errors and either fixes the problem (if possible), alerts the user to the error so that he or she can fix it (such as by inserting a disk in a floppy drive), or reports a meaningful explanation of what went wrong so that the user can give you feedback. To that end, this section takes you through VBA's error-trapping techniques. A Basic Error-Trapping Strategy For many programmers, adding error-trapping code to a procedure can usually be found near the bottom of their to-do lists (probably just before adding comments to a procedure!). That's because error-trapping code isn't even remotely glamorous, and the optimistic (some would say foolhardy) programmer assumes it will never be needed. That's a shame, because setting up a bare-bones error trap takes very little time. Even a more sophisticated trap can be reused in other procedures, so you really only have a one-time expenditure of energy. To help you get started down this crucial path toward good program hygiene, this section presents a basic strategy for writing and implementing error-trapping code. This strategy will unfold in four parts : Setting the Trap In the simplest error-trapping case, VBA offers what I call the "never mind" statement: On Error Resume Next When inserted within a procedure, this statement tells VBA to bypass any line in the procedure that generates an error and to resume execution with the line that immediately follows the offending statement. No error message is displayed, so the user remains blissfully unaware that anything untoward has occurred. There are three things to note about implementing this statement: -
The trap applies to any executable statement that occurs after the On Error Resume Next statement. -
The trap also applies to any executable statement within each procedure that is called by the procedure containing the On Error Resume Next statement. -
The trap is disabled when the procedure ends. Because the On Error Resume Next statement does nothing to resolve whatever caused the error, and because skipping the offending statement might cause further errors, this error trap is used only rarely. To set a true error trap, use the On Error GoTo statement instead: On Error GoTo line Here, line is a line label, which is a statement that's used to mark a spot within a procedure (line labels aren't executable). The idea is that, if an error occurs, the procedure containing the On Error GoTo statement will branch immediately to the first statement after the line label. This statement should be the beginning of the error handler code that processes the error in some way (see the next section). Here's the general structure that sets up a procedure to trap and handle an error: Sub Whatever() On Error GoTo ErrorHandler [regular procedure statements go here] ' ' If no error occurs, bypass the error handler ' Exit Sub ' ' If an error occurs, the code will branch here ' ErrorHandler: [error handler code goes here] End Sub Here are some notes about this structure: -
To ensure that all statements are protected, place the On Error GoTo statement at the top of the procedure. -
The last statement before the error handler line label should be Exit Sub (or Exit Function if you're working with a Function procedure). This ensures that the procedure bypasses the error handler if no error occurs. -
The line label is a string ”without spaces or periods ”followed by a colon (:) at the end to tell VBA that it's just a label and should not be executed. Coding the Error Handler The On Error GoTo statement serves as the mechanism by which errors are trapped, but the nitty-gritty of the trap is the error handler. The handler is a group of statements designed to process the error, either by displaying a message to the user or by resolving whatever problem raised the error. | | This chapter's code can be found on my Web site in both Excel and Word versions: http://www.mcfedries.com/ABGVBA/Chapter15.xls http://www.mcfedries.com/ABGVBA/Chapter15.doc |
The simplest error handler just displays a message that tells the user that a problem occurred. Listings 15.1 and 15.2 provide an example. Listing 15.1 uses a couple of InputBox functions to get two numbers from the user: a dividend and a divisor. With these values in hand, the procedure calls the Divide function, as shown in Listing 15.2. Listing 15.1. The GetNumbers Procedure Prompts the User for a Dividend and a Divisor Sub GetNumbers() Dim done As Boolean Dim divisor As Variant Dim dividend As Variant ' ' Prompt user for dividend and divisor. ' done = False Do While Not done dividend = InputBox("Enter the dividend:", "Divider") divisor = InputBox("Enter the divisor:", "Divider") done = Divide(dividend, divisor) Loop End Sub The purpose of the Divide function, shown in Listing 15.2, is to divide the dividend argument by the divisor argument. To trap a "division by zero" error, an On Error GoTo statement tells VBA to branch to the DivByZeroHandler label. (Actually, this statement will trap any error, not just a division by zero error.) The division is performed, and, if all goes well, a MsgBox displays the result. However, if the divisor value is zero, an error will occur, and the code will branch to the DivByZeroHandler label. This error handler displays a message and asks the user if he wants to try again. The function's return value is set according to the user's choice. Listing 15.2. The Divide Function Divides the Dividend by the Divisor and Traps "Division by Zero" Errors Function Divide(dividend, divisor) As Boolean Dim msg As String Dim result As Single ' ' Set the trap ' On Error GoTo DivByZeroHandler ' ' Perform the division ' result = dividend / divisor ' ' If it went okay, display the result ' msg = dividend & _ " divided by " & _ divisor & _ " equals " & _ result MsgBox msg ' ' Set the return value and bypass the error handler ' Divide = True Exit Function ' ' Code branches here if an error occurs ' DivByZeroHandler: ' ' Display the error message ' result = MsgBox("You entered 0 as the divisor! Try again?", _ vbYesNo + vbQuestion, _ "Divider") ' ' Return the user's choice ' If result = vbYes Then Divide = False Else Divide = True End If End Function In this example, setting up the error handler was no problem because the potential error ”division by zero ”was a fairly obvious one. (Also note that in a production application you'd confirm a nonzero divisor as soon as the user entered the value rather than wait for the division to occur.) In practice, however, your error handlers will require a more sophisticated approach that tests for multiple error types. For this you need to know about error numbers. I'll discuss those later in this chapter, in the section "Err Object Properties." Resuming Program Execution In Listing 15.2, the error message displayed to the user asks if he or she wants to input the values again, and an If...Then tests the response and sets the function's return value accordingly . This example is a bit contrived because your errors won't necessarily occur inside a Function procedure or loop. However, you'll often still need to give the user a choice of continuing with the program or bailing out. To do this, you can add one or more Resume statements to your error handlers. VBA defines three varieties of Resume statements: | Resume | Tells VBA to resume program execution at the same statement that caused the error. | | Resume Next | Tells VBA to resume program execution at the first executable statement after the statement that caused the error. | | Resume line | Tells VBA to resume program execution at the label specified by line . | Listing 15.3 shows an example. The BackUpToFloppy procedure is designed to get a drive letter from the user and then save the active workbook to that drive. If a problem occurs (such as having no disk in the drive), the procedure displays an error message and gives the user the option of trying again or quitting. Listing 15.3. This Procedure Backs Up the Active Workbook to a Drive Specified by the User and Traps any Errors (Such As Having No Disk in the Drive) Sub BackUpToFloppy() Dim backupDrive As String Dim backupName As String Dim msg As String Dim done As Boolean Dim result As Integer ' ' Define the location of the error handler ' On Error GoTo ErrorHandler ' ' Initialize some variables and then loop ' Application.DisplayAlerts = False done = False backupDrive = "A:" While Not done ' ' Get the drive to use for the backup ' backupDrive = InputBox( _ Prompt:="Enter the drive letter for the backup:", _ Title:="Backup", _ Default:=backupDrive) ' ' Check to see if OK was selected ' If backupDrive <> "" Then ' ' Make sure the backup drive contains a colon (:) ' If InStr(backupDrive, ":") = 0 Then backupDrive = Left(backupDrive, 1) & ":" End If ' ' First, save the file ' ActiveWorkbook.Save ' ' Assume the backup will be successful, ' so set done to True to exit the loop ' done = True ' ' Concatenate drive letter and workbook name ' backupName = backupDrive & ActiveWorkbook.Name ' ' Make a copy on the specified drive ' ActiveWorkbook.SaveCopyAs FileName:=backupName Else Exit Sub End If Wend ' ' Bypass the error handler ' Exit Sub ' ' Code branches here if an error occurs ' ErrorHandler: msg = "An error has occurred!" & vbCr & vbCr & _ "Select Abort to bail out, Retry to re-enter the drive" & _ vbCr & "letter, or Ignore to attempt the backup again." result = MsgBox(msg, vbExclamation + vbAbortRetryIgnore) Select Case result Case vbAbort done = True Case vbRetry done = False Resume Next Case vbIgnore Resume End Select End Sub The bulk of the procedure asks the user for a drive letter, saves the workbook, concatenates the drive letter and workbook name, and saves a copy of the workbook on the specified drive. The error routine is set up with the following statement at the top of the procedure: On Error GoTo ErrorHandler If an error occurs, the procedure jumps to the ErrorHandler label. The error handler's MsgBox function gives the user three choices (see Figure 15.1), which get processed by the subsequent Select Case structure: Figure 15.1. If an error occurs, the error handler displays this dialog box.  | Abort | Selecting this option ( Case vbAbort ) bails out of the While...Wend loop by setting the done variable to True. | | Retry | Selecting this option ( Case vbRetry ) means the user wants to re-enter the drive letter. The done variable is set to False, and then the Resume Next statement is run. If the error occurs during the SaveCopyAs method, the next statement is Wend , so the procedure just loops back (because we set done to False) and runs the InputBox function again. | | Ignore | Selecting this option ( Case vbIgnore ) means the user wants to attempt the backup again. For example, if the user forgot to insert a disk in the drive, or if the drive door wasn't closed, the user would fix the problem and then select this option. In this case, the error handler runs the Resume statement to retry the SaveCopyAs method (or whatever). | Disabling the Trap Under normal circumstances, an error trap set by the On Error GoTo statement is disabled automatically when the procedure containing the statement is finished executing. However, there might be times when you want to disable an error trap before the end of a procedure. For example, when you're testing a procedure, you might want to enable the trap for only part of the code and let VBA generate its normal runtime errors for the rest of the procedure. To disable an error trap at any time during a procedure, even within an error handler, use the following statement: On Error GoTo 0 Working with the Err Object The problem with the error traps we've set so far is a lack of information. For example, the Divide function (in Listing 15.2) assumes that any error that occurs is a result of an attempted division by zero. However, there are two other runtime error possibilities: It's likely that you'll want your error handler to treat these errors differently. For example, a division by zero error requires only that the divisor be re-entered, but an overflow error requires that both the dividend and the divisor be re-entered. To handle different errors, VBA provides the Err object, which holds information about any runtime errors that occur. You can use the properties of this object to get specific error numbers and descriptions. Err Object Properties The Err object has a number of properties, but the following three are the ones you'll use most often: Err.Description ” Returns the error description. Err.Number ” Returns the error number. Err.Source ” Returns the name of the project in which the error occurred. For example, Listing 15.4 shows a procedure that attempts to divide two numbers. The Err object is used in two places within the error handler: -
The error message displayed to the user contains both Err.Number and Err.Description . -
A Select Case structure examines Err.Number to allow the handler to perform different actions depending on the error. Listing 15.4. This Procedure Divides Two Numbers and Traps Three Specific Errors: Division by Zero, Overflow, and Type Mismatch Sub DivideNumbers() ' ' Set the trap ' On Error GoTo DivByZeroHandler ' ' Declare variables ' Dim divisor As Variant Dim dividend As Variant Dim result As Single Dim msg As String ' ' Prompt user for the dividend ' GetDividendAndDivisor: dividend = InputBox("Enter the dividend:", "Divider") If dividend = "" Then Exit Sub ' ' Prompt user for the divisor ' GetDivisorOnly: divisor = InputBox("Enter the divisor:", "Divider") If divisor = "" Then Exit Sub ' ' Perform the division ' result = dividend / divisor ' ' If it went okay, display the result ' msg = dividend & _ " divided by " & _ divisor & _ " equals " & _ result MsgBox msg ' ' Bypass the error handler ' Exit Sub ' ' Code branches here if an error occurs ' DivByZeroHandler: ' ' Display the error message ' msg = "An error occurred!" & Chr(13) & Chr(13) & _ "Error number: " & Err.Number & Chr(13) & _ "Error message: " & Err.Description MsgBox msg, vbOKOnly + vbCritical ' ' Check the error number ' Select Case Err.Number ' ' Division by zero ' Case 11 Resume GetDivisorOnly ' ' Overflow ' Case 6 Resume GetDividendAndDivisor ' ' Type mismatch ' Case 13 If Not IsNumeric(dividend) Then Resume GetDividendAndDivisor Else Resume GetDivisorOnly End If ' ' Anything else, just quit ' Case Else Exit Sub End Select End Sub | 