In this chapter, you'll learn some of the things that can go wrong in your VBA code and what you can do about them. You'll examine the types of errors that can occur, from simple typos to infinite loops to errors that occur only once in a while (intermittent bugs are usually the hardest to locate).
The chapter starts by explaining the basics of debugging. Then you'll work with the debugging tools available in VBA and practice using these tools to get the bugs out of some examples. The chapter concludes with a discussion of various ways to have your program itself respond to errors that happen during runtime.
A bug is an error in hardware or software that causes a program to execute other than as intended. Debugging means removing the bugs from hardware or software.
Your goal when debugging should be to remove all bugs from your code. Your order of business will probably go something like this:
Even if it seems to work, continue testing for a reasonable period with various data from various sample documents before unleashing the procedure on the world (or your colleagues).
And don't make fun of this user. It might seem sensible to users that the procedure should be launched before a file is loaded. Users might expect the procedure to display an input box asking them which document they want to manipulate. And more important, users also expect that you will anticipate and handle unexpected errors without crashing your programming. There are ways to trap unanticipated user behavior or other runtime errors and respond to them gracefully. What does your program do if users attempt to save a file to a disk that's full, for example? Just crash and thereby lose all the information they've spent time typing in?
Debugging a procedure tends to be idiosyncratic. There's no magic wand that you can wave over your code to banish bugs (although the VBA Editor does its best to help you eliminate certain types of errors from your code as you create it). Moreover, such simple things as forgetting to initialize a variable can wreak havoc on your code.
You'll probably develop your own approach to debugging, partly because your programming will inevitably be written in your own style. But when debugging, it helps to focus on understanding what the code is supposed to do. You then correlate this with your observations of what the code actually does. When you reconcile the two, you'll probably have worked out how to debug the procedure.
Also, the longer and more complex your code, the higher the probability that it will contain bugs. Certain kinds of bugs occur because of interactions among the parts of a project. And obviously, the larger the project, the more parts with potential side effects, so keep your code as simple as possible by breaking it into separate procedures and modules, as discussed in Chapter 16, “Building Modular Code and Using Classes.” Small code sections with distinct, small tasks to accomplish are almost always easier to debug than large lumps of code that try to do several things all at once. Remember that most debugging is a matter of locating where in your code the problem occurs. If you're testing a small module of code with a very easily specified objective, locating a bug is that much easier.
You'll encounter four basic kinds of errors in your programming:
The following sections look at these kinds of errors in turn and discuss how to prevent them. After that, you'll examine the tools VBA provides for debugging.
The first type of error is usually the easiest to fix: language errors (also known as syntax errors). When you mistype a word in the Code window, omit a vital piece of punctuation (and in programming, all punctuation is vital), scramble a statement, or leave off the end of a construction, that's a language error. If you've worked your way through the book to this point, you've probably already made dozens of language errors as part of the learning process and through simple typos.
VBA helps you eliminate many language errors as you create them, as you'll see later in this chapter. Those language errors that the VBA Editor doesn't catch as you type them in usually show up as compile errors during runtime testing, so the next section shows you examples of both language errors and compile errors.
Compile errors occur when VBA can't compile a statement correctly—that is, when VBA can't turn a statement that you've entered into viable code.
For example, if your programming tells VBA to use a certain property for an object that doesn't have that property, a compile error results. Compilation is the act of turning your source code (the programming you type into the Editor) into the lower-level commands understandable by the computer. For example, when you press F5 to execute your program, VBA starts off by compiling your programming. If it finds a problem during compilation, it displays an error message.
The good news is that the VBA Editor detects many language errors and some compile errors as soon as you move the insertion point from the offending line. You don't even have to press F5 in many cases. For example, try typing the following statement in the Code window and pressing Enter to create a new line (or pressing ↑ or ↓ to move to another line, or clicking the mouse in another line in the macro):
If X > Y
The VBA Editor displays the compile error “Expected: Then or GoTo” (see Figure 17.1) to tell you that the statement is missing a vital element: it should say If X > Y Then
or If X > Y GoTo
. (If you don't see the error message, there are two possibilities: Either you have turned off the Auto Syntax Check option [Tools ➢ Options] or you didn't actually type it in by hand and press Enter.)
Every time you enter a line of code, the Editor examines that line for completeness and accuracy. In this example, VBA knows that when the code contains an If
command, there must be a subsequent Then
or GoTo
command. And so the Editor rejects the line and informs you what the problem is.
This vigilance on the part of the VBA Editor prevents you from running into this type of error deep in the execution of your code.
The VBA Editor notices blunders like the previous If X > Y
problem easily enough, but you can also make language errors that the VBA Editor cannot identify when you move the insertion point from the line in which the blunder resides. Instead, VBA identifies these errors as compile errors later when you press F5 and it compiles the code. For example, if you enter the following statement in the Code window when working with Word, the VBA Editor won't detect anything wrong. But when you run the procedure by pressing F5, VBA will compile the code, discover the error, and object to it (see Figure 17.2):
ActiveDocument.SaveAs FileMame:="My File.docm"
This error is a straightforward typo—FileMame
instead of FileName
—but VBA won't see this particular kind of problem until it runs the code and fails to find any FileMame
property.
The VBA Editor sometimes indirectly helps you to notice errors of this kind while you're writing code. Say you're trying to enter a Documents.Close
statement in Word and mistype Documents
as Docments
. In this case, the VBA Editor won't display the Properties/Methods list (Auto List Members) as it normally does if you have this feature turned on. You haven't entered a valid object. VBA doesn't, therefore, have a members list to display.
Not seeing the Properties/Methods list should alert you that something is wrong. If you continue anyway and enter the Docments.Close
statement, the VBA Editor won't spot the mistake—it will show up as a “Run-time error 424: Object required” message (if you don't have Option Explicit
on) when you try to run the procedure. (If you do have Option Explicit
on, you will get a “Variable not defined” compile error instead.)
The Editor gives you yet another clue that Docments.Close
is an error. When you press Enter to leave this line of code, you see this:
docments.Close
Does anything here look odd to you? VBA will automatically capitalize valid object names. But docments
is not capitalized.
Another kind of problem is caused if you specify a property or method for an object to which that property or method doesn't apply. In this situation, VBA displays a compile error. For example, say you forget that the proper method here is Add
and you enter Documents.Create
instead. VBA highlights the offending word and gives the compile error “Method or data member not found” (see Figure 17.3), which tells you there's no Create
method for the Documents
collection. This message is displayed only during runtime, not design time (design time means when you're typing in code lines).
The third type of error is the runtime error, which occurs while code is executing. You will cause a runtime error if you write code that forces VBA to try to perform an impossible operation, such as opening a document that doesn't exist, closing a file when no file is open, or performing something mathematically impossible, such as dividing by zero.
A runtime error can occur when the diction, punctuation, and syntax of your code is error-free, but you're asking VBA to do something that can't be done. An unhandled runtime error results in a crash that manifests itself as a Microsoft Visual Basic dialog box displaying a runtime error number, such as the one shown in Figure 17.4.
As an example of an impossible operation, consider the archetypal division by zero. The following statements give a “Run-time error ‘11’: Division by zero” message:
Dim x As Integer
x = 1 / 0
You're unlikely to enter anything as obviously wrong as this in your code (you're not nuts). A line of code like this will inevitably produce a division-by-zero error because the divisor is zero. But it's easy to enter a valid equation, such as MonthlyPay = Salary/Months
, and forget to assign any value to Months
(if a numeric variable is empty, it counts as a zero value) or to produce a zero value for Months
by addition or some other math. Or the user can type zero into a dialog box, and your code later tries to use that as a divisor. And so on.
One way to check for runtime errors is to track the values of your variables by using VBA's Watch window (discussed later in this chapter). To avoid possible user-input errors, have your code check users’ input after they close a dialog box. You can, for example, display a message explaining that zero isn't an acceptable input for their age, and then display the dialog box again, expecting valid input this time around.
The fourth type of error is the program logic error. The code is valid, but it nonetheless produces incorrect results. With program logic errors, the code is technically fine. VBA is able to compile and run it without noticing any errors—but you get a different result than you intended. These errors are usually the toughest to fix. The problem is where do things go wrong?
Program logic errors can range in scope from the relatively obvious (such as performing manipulations on the wrong workbook in Excel because your code doesn't check which window is active) to the subtle (such as extending a range to the wrong character or cell).
In the first example, the procedure is likely to run perfectly, but the resulting workbook will bear little resemblance to what you were trying to accomplish. In the second example, you might get a result that is almost correct—or the error might cause you to get perfect results sometimes and slightly wrong results at other times. Or, worst of all, get an error only once in 10,000 executions.
To nail down logic error, you need to trace the execution of your code and pinpoint where things start to go wrong. And to do that, you almost always need to employ the debugging tools discussed in the next section.
A friend of mine wrote a very nice program to format and print forms. But while he was testing it he noticed that after working fine about five times, it suddenly sent only one-third of the form to the printer. Sadly, he simply could not get the program to repeat the error after dozens of tries.
So he surrounded the code with a loop and let it run continuously (dumping the sample form repeatedly into a log file rather than wasting paper by printing hardcopy over and over). He then discovered that the error only occurred once every 256 times the program ran. That's a clue because 256 is a special number in binary computation (1000000002). Early on, programmers decided to group bits into 8-bit units called bytes. A byte is capable of representing numbers from 0 to 255, or 2 to the power of 8.
However, he never could locate this bug. When he gave the program to other people, he just told them that it worked fine pretty much always.
VBA provides a solid assortment of debugging tools to help you remove the bugs from your procedures. The main windows you'll employ for debugging are the Immediate window, the Locals window, and the Watch window. You can access these tools in various ways, one of which is by using the Debug toolbar (shown in Figure 17.5). Four of the buttons—Design Mode, Run Sub/UserForm(F5)/Continue(F5), Break, and Reset—are shared with the Standard toolbar. You'll learn about most of the others later in this chapter.
Break mode is a vital tool for debugging your procedures because it lets you watch your code execute step by step—line by line—in the Code window (by repeatedly pressing F8). This technique is called single-stepping.
For example, if an If…Then…ElseIf…Else
statement appears to be executing incorrectly, you can step through it in Break mode and watch exactly which statements are executing, and which are being skipped, to produce the bad result.
These are the easiest ways to enter Break mode:
You can set any number of breakpoints. They're especially useful when you need to track down a bug that you suspect is located in a particular procedure because a breakpoint allows you to run the parts of a procedure that have no problems at full speed and then stop the procedure where you think there might be problems. From there, you can step through the suspicious statements and watch closely how they execute.
You can also enter Break mode in a couple of other ways:
Normally, the only reason to enter Break mode this way is if your code gets stuck in an endless loop (which you'll typically recognize when the code appears to be doing nothing for a long time or is repeating itself when you think it shouldn't be). VBA highlights the statement that was executing when you pressed Ctrl+Break, but (depending on your timing) it's unlikely to be the statement that's causing the problem in your code—it'll just be one of the statements in the offending loop. You'll then need to step through the loop to identify the aberrant statement.
In Chapter 3, “Editing Recorded Macros,” you learned how to step through a procedure by repeatedly pressing the F8 key to issue the Step Into command, going down the lines one at a time. (You can also issue this command by clicking the Step Into button on the Debug toolbar or choosing Debug ➢ Step Into, but F8 is ever so much more efficient.)
Stepping into lets you see exactly what each statement in your code does, but you'll often find that you need to get past sections of code that you're sure are working fine so that you can step through a section that seems perhaps suspicious. This situation is particularly true of loop structures, which can have you going round and round—a real time-waster if you know the bug you're tracking down isn't within the loop. So you want to leap past the loop.
Break mode offers three features to speed up stepping through your code: the Step Over command, the Step Out command, and the Run To Cursor command. The Step Over and Step Out commands aren't available until you enter Break mode (for example, by using the Step Into command).
The Step Over command (which you can trigger by pressing Shift+F8, clicking the Step Over button on the Debug toolbar, or choosing Debug ➢ Step Over) executes the whole Sub or function called from the current procedure instead of stepping through the called procedure statement by statement as the Step Into command would do. (It “steps over” that procedure or function.) Use the Step Over command when you're debugging a procedure that calls another procedure or function that you know to be error-free and that you don't need to test step by step.
The Step Out command (which you can issue by pressing Ctrl+Shift+F8, clicking the Step Out button on the Debug toolbar, or choosing Debug ➢ Step Out) runs the rest of the current procedure at full speed. Use the Step Out command to quickly execute the rest of a procedure once you've gotten through the part that you needed to watch step by step.
The Run To Cursor command (which you can issue by pressing Ctrl+F8 or choosing Debug ➢ Run To Cursor) runs the code at full speed until it reaches the statement where the blinking cursor currently is in the Code window, whereupon it enters Break mode. Click to position the cursor in the appropriate statement before invoking this command.
The Locals window provides a quick readout of the values and types of all variables or expressions in the currently active procedure. It displays a collapsible tree view (see Figure 17.8).
An expression is a combination of keywords, operators, variables, and/or constants. Variables are one kind of expression; but more complex expressions involve more than a single variable: x > y
, for example, is an expression stating that x
is greater than y
. This expression might be True
or False
, depending on what's happening during runtime.
The Expression column displays the name of each expression, listed under the name of the procedure in which it appears. The Value column displays the current value of the expression (including Empty
if the expression is empty, or Null
or Nothing
as appropriate). And the Type column displays the data type of the expression, with Variants listed as “Variant” along with their assigned data type (for example, “Variant/String” for a Variant assigned the String data type).
To display the Locals window, click the Locals Window button on the Debug toolbar or choose View ➢ Locals Window. To hide the Locals window, click its Close button.
From the Locals window, you can also click the button marked with an ellipsis (…) to display the Call Stack dialog box, discussed later in this chapter. This button is also available only in Break mode.
The Watch window (identified as Watches in Figure 17.9) is a separate window that you use to track the values of variables and expressions as your code executes. To display the Watch window, click the Watch Window button on the Debug toolbar or choose View ➢ Watch Window in the VBA Editor. To hide the Watch window again, click its Close button (clicking the Watch Window button or choosing View ➢ Watch Window again doesn't hide it).
The Watch window displays watch expressions—expressions in your code that you specify ahead of time. You want to view a dynamic display of the values in these variables or expressions.
Watch-expression information can help you to pinpoint where an unexpected value for a variable or an expression occurs as your code executes. The Watch window lists the names of the watched expressions or variables in the Expression column, their values in the Value column, their type (Integer, Byte, String, Long, and so on) in the Type column, and their context (the module and procedure in which they're operating) in the Context column. So to track the value of a given variable, you need only look at the Watch window at any given point while in Break mode.
If a variable or expression listed in the Watch window hasn't been initialized, the Watch window displays “< Out of Context >” in the Value column and “Empty” (for a variable other than a Variant) or “Variant/Empty” (for a Variant) in the Type column.
The VBA Editor updates all watch expressions in the Watch window whenever you enter Break mode and whenever you execute a statement in the Immediate window. So if you step through a procedure in the Code window by pressing the F8 key (which keeps you in Break mode), you can watch the value in a variable, or of an expression, as each statement executes. This is a great way to pinpoint where an error or an unexpected value occurs—and is much easier than moving the mouse over each variable or expression in question to check its value by using the Auto Data Tips feature.
Here's a typical debugging scenario. Let's say your code is producing a preposterous result, such as asserting that your annual salary is $2,200,000. As usual with most debugging, you're trying to figure out where in your code this sudden and massive gain in income is being calculated. Just keep an eye on the Watch window while single-stepping through your code to see in which line of code the variable MySalary
goes from 50,000 to 2,200,000. Now you're right there close to where in your source code the bug is, and you can examine the preceding lines of code to see what's impacting the MySalary
variable.
Because watch expressions slow down the execution of your code, the VBA Editor doesn't save them with the code—you need to redo them for each editing session. However, the Editor does store watch expressions during the current editing session, so you can move from procedure to procedure without losing your watch expressions.
Sometimes referred to as conditional breakpoints, watch expressions give you considerable flexibility when debugging. You can ask the VBA Editor to halt execution on most any kind of situation you can think up, such as break on any line that causes a variable to exceed a certain value, go below zero, change to a shorter string length, and so on. In other words, you specify a condition, an expression such as MySalary > 50000
, and the VBA Editor automatically halts execution and displays the line where your salary increases beyond the expected 50,000. As you can imagine, the conditional breakpoint is one of the best tools a debugger has.
To set a watch expression, add it to the list in the Watch window by following these steps:
You can also select the variable or expression you're interested in and choose Debug ➢ Add Watch to display the Add Watch dialog box. If you choose Debug ➢ Add Watch without selecting the variable or expression, you must type it in the Expression text box, which is a waste of time.
True
.MySalary
in the previous example) or when you want to be alerted every time an expression changes.You can also drag a variable or an expression from the Code window to the Watch window; doing so sets a default watch expression in the current context. To set Break When Value Is True or Break When Value Changes, edit the watch expression after dragging it to the Watch window.
To edit a watch expression, right-click it in the Watch window and choose Edit Watch from the context menu, or select it in the Watch window and choose Debug ➢ Edit Watch. Either action will display the Edit Watch dialog box with the watch expression selected in the Expression box, as shown in Figure 17.11. Change the context or watch type for the watch expression by using the settings in the Context group box and the Watch Type group box, and then click the OK button to apply your changes.
To delete a watch expression, right-click it in the Watch window and choose Delete Watch from the context menu. You can also delete the current watch expression by clicking the Delete button in the Edit Watch dialog box.
For those times when you don't need to create a watch expression for an expression or a variable, when you merely want to observe the value, you can use the Quick Watch feature, which displays the Quick Watch dialog box (see Figure 17.12) containing the context and value of the selected expression.
To use Quick Watch, while in Break mode select an expression or variable in the Code window and then click the Quick Watch button on the Debug toolbar, or choose Debug ➢ Quick Watch, or press Shift+F9. (If you're already working in the Quick Watch dialog box, you can click the Add button to add the expression to the Watch window.)
One use for the Immediate window is as a virtual scratchpad. In the Immediate window you enter lines of code that you want to test quickly, without having to enter them in a procedure, and then testing the entire procedure. A second major use of the Immediate window is to display information to help you check the values of variables while a procedure is executing.
In the first case, you type code into the Immediate window, and then press Enter to see the results immediately (get it?). In the second case, you insert in your code Debug.Print
statements that display information in the Immediate window, where you can easily view it. We'll explore both of these techniques in the following sections.
To display the Immediate window, click the Immediate Window button on the Debug toolbar, choose View ➢ Immediate Window, or press Ctrl+G. To hide the Immediate window again, click its Close button. (Clicking the Immediate Window button, choosing View ➢ Immediate Window, or pressing Ctrl+G when the Immediate window is displayed does not hide the Immediate window.)
You can execute code in the Immediate window in both Break mode and Design mode.
There are a number of restrictions on the code you can use in the Immediate window:
Dim
, Private
, Public
, Option Explicit
, Static
, or Type
) or control-flow statements (such as GoTo
, Sub
, or Function
). These statements cause VBA to return an “Invalid in Immediate Pane” error.If
statements or block For…Next
statements) because there's no logical connection between statements on different lines in the Immediate window: Each line is treated in isolation.The Immediate window supports a number of standard Windows editing keystrokes and key combinations, such as Ctrl+X (Cut), Ctrl+C (Copy), Ctrl+V (Paste), Ctrl+Home (move the insertion point to the start of the window), Ctrl+End (move the insertion point to the end of the window), Delete (delete the current selection), and Shift+F10 (display the context menu).
The Immediate window also supports the following VBA Editor keystrokes and key combinations:
Finally, the Immediate window has a couple of commands of its own:
As well as entering statements in the Immediate window for quick testing, you can use this window for a different debugging technique. To include in your procedures statements that print information to the Immediate window, use the Print
method of the Debug
object. Printing like this allows you to create a log during execution, a log you can later examine for errors or strange behavior. You don't single-step or display message boxes containing the value of a variable. Instead you print data for later study.
The syntax for the Print
method is as follows:
Debug.Print [outputlist]
outputlist
is an optional argument specifying the expression or expressions to print. You'll almost always want to include outputlist
—if you don't, the Print
method prints a blank line, which is of little use. Construct your outputlist
using the following syntax:
[Spc(n) | Tab(n)] expression
Here, Spc(n)
inserts space characters and Tab(n)
inserts tab characters, with n
being the number of spaces or tabs to insert. Both are optional arguments, and for simple output, you'll seldom need to use them.
expression
is an optional argument specifying the numeric expression or String expression to print:
True
or False
(as appropriate).outputlist
is Empty
, Print
doesn't print anything. If outputlist
is Null
, Print
prints Null
.outputlist
is an error, Print
prints it as Error
errorcode
, where errorcode
is the code specifying the error.As an example, you could log the contents of the String variables (expressions) CustName
, Address1
, Address2
, City
, State
, and Zip
to the Immediate window in an address format by using the following statements:
Debug.Print CustName
Debug.Print Address1 & "," & Address2
Debug.Print City & "," & State & " " & Zip
As another example, the following procedure prints the names and paths of all open workbooks in Excel to the Immediate window:
Sub See_All_Workbook_Names()
Dim oBook As Workbook
For Each oBook In Workbooks
Debug.Print oBook.FullName
Next
End Sub
In practice, Debug.print
is used by many programmers as a sometimes-quick, efficient alternative to debugging with the Watch windows, message boxes, or breakpoints. You need to see if something is going wrong with a variable (its value is wrong, but where does it go wrong?). So you insert some Debug.Print
statements to display the variable's value while executing a procedure. Then you can see if the value is wrong in that location or somewhere else in the code.
If your program contains multiple procedures, you might also want to debug.print
the name of the procedure. This example identifies both the procedure and variable name within the Debug.Print
statement:
Debug.Print "In the Sub Add_Tax the variable intLocal is: " & intLocal
This results in the following line in the Immediate window:
In Sub Add_Tax the variable intLocal is: 7
When working in Break mode, you can summon the Call Stack dialog box (see Figure 16.1 in Chapter 16) to display a list of the active procedure calls—the outside procedures being triggered by the current procedure. It shows the history of your code's execution path.
When you begin running a procedure, that procedure is added to the call-stack list in the Call Stack dialog box. If that procedure then calls another procedure, the name of the second procedure is added to the call-stack list, but only while the procedure is executing; it's then removed from the list. By using the Call Stack dialog box in Break mode, you can find out what procedures are being called by another procedure; this can help you establish which parts of your code you need to check for errors.
To display the Call Stack dialog box, click the Call Stack button on the Debug toolbar, press Ctrl+L, or select View ➢ Call Stack. To display one of the procedures listed in the Call Stack dialog box, select it in the Project.Module.Function list box and click the Show button.
You'll probably find it easy to tell when a procedure gets stuck in an infinite loop: You'll notice that the procedure simply doesn't stop executing. If you open Windows's Task Manager, it will report that your application has “stopped responding.” To interrupt an infinite loop, press Ctrl+Break. The VBA Editor then displays a Code Execution Has Been Interrupted dialog box. Infinite loops are also known as endless loops.
There are several ways to get stuck in infinite loops, such as using GoTo
statements without If
conditions or Do
loops without While
or Until
constraints. These are easy enough to avoid, but even if you do, it's still possible for infinite loops to occur in your code because of conditions you haven't been able to anticipate.
The best way to approach detecting and eliminating an infinite loop is to use breakpoints or a watch expression to pinpoint where the procedure enters the infinite loop. Once you've reached it, use the Step Into command to step into the procedure. Then use the Watch window or the Locals window to observe the variable and expressions in the loop, which should indicate when something is going wrong and causing the loop to be endless.
If your code contains a loop that should execute only a set number of times but you suspect that it's instead running endlessly, you can insert a counter variable in the loop in an If…Then
structure that triggers either an Exit For
statement or an Exit Do
statement to exit the loop if it runs more than a certain number of times:
For i = 1 To 1500
If i > 1200 Then
MsgBox (i)
Exit For
End If
Next
Despite the help that VBA provides by checking for language errors and compile errors, runtime errors remain an unpleasant fact of life. Sooner or later, you will get runtime errors in your code, but you don't have to take them lying down. The best tactic to deal with them is to add error handlers—pieces of code that trap errors that occur during execution, analyze them, and take action to deal with the problem.
An error handler is a preventative measure, allowing your code to manage problems gracefully rather than crashing in front of a user's alarmed or bemused face.
Consider writing an error handler in the following circumstances:
You'll also run into errors if the user tries to use a printer or other remote device (say, a scanner or a digital camera) that's not present, not connected, turned off, or not configured correctly. Similarly, any procedure that deals with a particular object in a document (for example, a chart in Excel) will run into trouble if that object is not available.
Trapping an error means catching it in your code during runtime, and adding some code to handle the error during runtime.
VBA's On Error
statement triggers when there is a runtime error, allowing you to write code that responds to the error.
Usually, you'll want to prevent an error from stopping your VBA code, but you can also anticipate particular errors and use them to determine a suitable course of action to follow from the point at which they occur.
To trap an error, you use the On Error
statement. The usual syntax for On Error
is as follows:
On Error GoTo line
Here, line
is a label specifying the line to which execution is to branch when a runtime error occurs. For example, to branch to the label named ErrorHandler
, you could use a structure like this:
Sub ErrorDemo()
On Error GoTo ErrorHandler
'ordinary code statements here
Exit Sub
ErrorHandler:
'error-handling statements here
End Sub
The label you use to identify the error handler can be named with any valid label name—you don't have to call it ErrorHandler
or anything similar. Some people find that a descriptive label (perhaps one that identifies the type or types of error expected, such as HandleErrorNoFileOpen
) is clearer in the long run than a generic name; others prefer to go with a generic name such as HandleErr
.
Usually, you'll want to place the error trap early, near the top of a procedure so that it's active and ready to trap errors for all the lines of code below it throughout the whole procedure. If necessary, you can place several different error traps in a procedure by entering multiple On Error
statements where they're needed—but only one can be enabled at a time. (Enabled means that an error trap has been switched on by an On Error
statement. When an error occurs and execution branches to the error handler, that error handler is active.)
Inserting multiple error handlers in a procedure can be useful when you're dealing with statements that can cause different types of errors that may need to be trapped. In the following example, the first On Error
statement directs execution to ErrorHandler1
, and the second On Error
statement directs execution to ErrorHandler2
:
Sub ErrorDemo2()
On Error GoTo ErrorHandler1
'statements here
On Error GoTo ErrorHandler2
'statements here
Exit Sub
ErrorHandler1:
'statements for first error handler here
ErrorHandler2:
'statements for second error handler here
End Sub
Each error handler is limited to the procedure in which it appears, so you can create different error handlers for different procedures and have each enabled in turn as the procedures run.
Because the error handler appears as code in the procedure, you need to make sure that it doesn't run when no error has occurred. You can do this by using either an Exit Sub
statement in the line just above the error-handler statement (this ends execution of the procedure) or a GoTo
statement that directs execution to a label beyond the error-handling code. The Exit Sub
statement is better if you choose to place your error handler at the end of its procedure, which is standard practice and usually makes sense. The GoTo
statement may prove easier to use if you choose to place your error handler elsewhere in the procedure.
For a function, use an Exit Function
statement rather than an Exit Sub
statement. For a property in a class module, use an Exit Property
statement.
The following example uses an Exit Sub
statement to cause execution to end before the error handler if no error occurs:
Sub ErrorDemo3()
On Error GoTo ErrorHandler
'statements that might cause an error
Exit Sub
ErrorHandler:
'statements that handle the error
End Sub
This next example uses a GoTo
statement to skip the error handler—which is placed within the code of the procedure—unless an error occurs. When execution reaches the GoTo SkipErrorHandler
statement, it branches to the SkipErrorHandler
label, thus bypassing the code in the error handler:
Sub ErrorDemo4()
On Error GoTo ErrorHandler
'statements that might cause an error
GoTo SkipErrorHandler
ErrorHandler:
'statements that handle the error
SkipErrorHandler:
'statements
End Sub
You read earlier in this book that some people don't like GoTo
statements for uses such as the second example here. Given that this GoTo
statement makes the flow of the procedure a little harder to follow, you may be inclined to agree with them in this case. (The use of GoTo
in the On Error
statement itself is, however, unavoidable.)
Recall that an error trap works only for the procedure in which it appears, and VBA disables it when the code in the procedure has finished executing. You can also disable an error trap before the end of a procedure in which it appears if you want by using the following statement:
On Error GoTo 0
Why would you do this? You might want to disable an error trap while testing a procedure to enable yourself to pinpoint errors that occur after a certain point while at the same time retaining error trapping for the first part of the procedure.
You use the Resume
statement to resume execution of a procedure after trapping an error or handling an error with an error-handling routine. The Resume
statement takes three forms: Resume
, Resume Next
, and Resume
line
.
The Resume
statement causes execution to resume at the same line that caused the error. Use Resume
with an error-handling routine that detects and fixes the problem that caused the offending statement to fail. For example, look at the error handler in Listing 17.1, which runs when VBA is unable to apply a specified style in Word.
Here's how the StyleError
procedure in Listing 17.1 works:
On Error
statement to enable the imaginatively named error handler, which is identified by the Handler
label in line 12.Exit Sub
statement to end execution of the procedure before the error handler.Selection.Style
statement in line 5 causes an error, execution branches to the Handler
label in line 12, and the error handler is activated.Resume
statement in line 16 causes execution to resume where the error occurred, on line 5. Because the specified style is now available, the Selection.Style
statement runs without an error. Resume Next
causes execution to resume with the next statement after the statement that caused the error. You can use Resume Next
in either of the following circumstances:
On Error Resume Next
statement that causes execution to continue at the next statement after the statement that caused an error, without using an error handler to fix the errorAs an example of the first circumstance, if the style specified in the previous example isn't available, you can use a Resume Next
statement to skip applying it:
Sub StyleError2()
On Error GoTo Handler
Selection.Style = "Executive Summary"
'the rest of the procedure happens here
'exit the procedure once execution gets this far
Exit Sub
Handler:
Resume Next
End Sub
The descriptions of Resume
and Resume Next
apply if the error occurred in the procedure that contains the error handler. But if the error occurred in a different procedure from the procedure that contains the error handler, Resume
causes execution to resume with the last statement that transferred execution (called) out of the procedure where the handler is located; Resume Next
causes execution to resume with the statement after the last statement to call out of the procedure that contains the error handler.
Resume
line
causes execution to resume at the specified line. Use a label to indicate the line, which must be in the same procedure as the error handler.
For example, if a procedure tried to open a particular file, you could create a simple error handler that uses a Resume line
statement, as shown in Listing 17.2. This procedure works with Word. To make it work with other applications, substitute the appropriate error numbers in line 15.
Here's how Listing 17.2 works:
strFName
.StartHere
label, to which execution will return from the Resume
statement in line 16.On Error
statement to enable the error handler ErrorHandler
.strFName
, which line 9 then tries to open.strFName
against an empty string and ends execution if it matches.Exit Sub
statement exits the procedure, ending its execution. Otherwise, an error is generated, and execution branches to the ErrorHandler
label in line 13, where the error handler becomes active.Resume
statement in line 16 then returns execution to the StartHere
label in line 5.You can't use a Resume
statement anywhere other than in an error-handling routine (or an On Error Resume Next
statement). If you do, VBA reports an error.
To see the description of the current error, return the Description
property of the Err
object:
MsgBox Err.Description
In general, operating-system and programming-language error messages tend to be terse, cryptic, and of less help to the end user than to the people who built the OS or language. Think twice before displaying one of these error messages to an end user. The error message shown in Figure 17.7 says “Run-time error ‘5941’: The requested member of the collection does not exist.” As you can imagine, most users would be baffled by this message; some would panic.
Usually, it's more effective, not to mention kinder, to write and display a more verbose error message of your own devising. It should explain in ordinary English what the problem is—and, preferably, what (if anything) the user can do to solve it.
As part of your testing, you may want to deliberately simulate errors so that you can see how well your error handler handles them. (Programming lingo sometimes substitutes the word raise
for cause or trigger. Nobody knows why.)
To cause an error to be triggered, use the Raise
method of the Err
object, specifying only the number
argument. number
is a Long argument giving the number of the error that you want to cause. For example, the following statement “raises” error 5121:
Err.Raise 5121
Many of the procedures you build will use message boxes or dialog boxes to allow the user to choose options for the procedure. In some applications—such as Word, Excel, PowerPoint, and Access—you can use the DisplayAlerts
property of the Application
object to suppress the display of message boxes and errors while a procedure is running:
DisplayAlerts
can be set to wdAlertsNone
(0
) to suppress alerts and message boxes, wdAlertsMessageBox
(-2
) to suppress alerts but display message boxes, or wdAlertsAll
(-1
, the default) to display all alerts and message boxes.
DisplayAlerts
is a sticky setting. You need to set DisplayAlerts
explicitly back to one of four things: to True
or to wdAlertsAll
when you want to see alerts again after setting it to False
, to wdAlertsNone
, or to wdAlertsMessageBox
. VBA resets the default value when you restart Word.
DisplayAlerts
is a read/write Boolean property that can be set to True
to display alerts and False
to suppress them. The setting sticks until you change it or restart Excel, at which point VBA resets it to True
.DisplayAlerts
is a read/write property that can be set to ppAlertsAll
to display all alerts and ppAlertsNone
to suppress all alerts. The setting sticks until you change it or until you restart PowerPoint, at which point VBA resets it to ppAlertsNone
.DoCmd
object's SetWarnings
method, like this:
DoCmd.SetWarnings False
Errors may seem quite enough of a problem, but you also need to decide what will happen if a user tries to interrupt your code by pressing Ctrl+Break during execution. Some VBA hosts, including Word and Excel, offer you three options:
To disable user input while a procedure is executing, disable the Ctrl+Break key combination by setting the EnableCancelKey
property of the Application
object to wdCancelDisabled
(in Word) or xlDisabled
(in Excel):
Application.EnableCancelKey = wdCancelDisabled 'Word
Application.EnableCancelKey = xlDisabled 'Excel
VBA automatically enables user input again when the procedure stops executing. You can also re-enable user input during a procedure by setting the EnableCancelKey
property to wdCancelInterrupt
(in Word) or xlInterrupt
(in Excel):
Application.EnableCancelKey = wdCancelInterrupt 'Word
Application.EnableCancelKey = xlInterrupt 'Excel
Excel offers a third setting, xlErrorHandler
, that traps the Ctrl+Break keystroke as error 18. You can deal with this error as you would any other error. Here's a quick example:
Sub CancelKey_Example()
Dim i As Long
On Error GoTo EH
Application.EnableCancelKey = xlErrorHandler
For i = 1 To 100000000 ' time-consuming loop
Application.StatusBar = i
Next i
EH:
If Err.Number = 18 Then
If MsgBox("Do you want to stop the procedure?" _
& vbCr & vbCr & "If not, stop pressing Ctrl+Break!", _
vbYesNo + vbCritical, "User Interrupt Detected") = vbYes Then End
End If
End Sub
You may want to temporarily disable user input while a procedure is executing a sensitive task that must not be interrupted. Then when the task is complete, you can re-enable user input because at that point it's again safe for the user to stop the procedure.
For example, say you have a procedure in which a section of code moves a number of files from one folder to another. You don't want the user to interrupt the code that moves the files. That could cause problems because if the user stopped the procedure in mid-task, it might leave some files still in the source folder and some in the destination folder.
Here's an example using Word:
'interruptible actions up to this point
Application.EnableCancelKey = wdCancelDisabled
For i = 1 to LastFile
SourceFile = Source & "Section" & i
DestFile = Destination & "Section" & i
Name SourceFile As DestFile
Next i
Application.EnableCancelKey = wdCancelInterrupt
'interruptible actions after this point
Some musicians can read a symphonic score and more or less “hear” the music. Likewise, some programmers can read raw code and visualize what it does. But most programmers need comments to help them understand what code is doing, particularly if they wrote the code months before or if it was written by another programmer.
Many programmers also find it easier to debug their procedures if they've previously documented their code. The best way to document your code is to add comments to it, either as you create the code or after you've finished creating it: This procedure does this. It expects this data as input and provides this as its output. This line does this. And so on.
Some experts advise that you document your code as you create it in any procedure in which you're exploring your way and trying different methods to reach your goal. Add comments to explain what action each group of statements is trying to achieve. But once you've gotten the procedure to work, go through the code and delete the code that didn't work and the comments describing those failed tactics. Leave only the comments that are relevant to how the code successfully functions.
Also consider adding comments when you're modifying an existing procedure so that you don't lose track of your changes. Once you have the procedure working to your liking, remove any unnecessary comments and reword any verbose or unclear comments.
Other experts suggest documenting your code when you've finished writing it. This allows you to enter only the comment lines that you want to be there permanently. This is the way to go when you're fairly sure of the direction of your code when you start writing the procedure and the procedure needs only a few pointers to make its code clear once it's complete.
To document your code, use comments prefaced by either the single quote ('
) or the Rem
keyword (short for remark).
Few programmers use Rem
anymore. When you're trying to comment out only a part of a line, the apostrophe is usually the better choice anyway. If you do choose to use the Rem
keyword, you'll need to add a colon before it to make it work consistently (some statements accept a Rem
without a colon at their end; others generate a compile error):
Rem This is a comment line.
Documents.Add: Rem create a document based on Normal.dotm
Generally, apostrophe-commented remarks are separated by a few spaces or tabs from any statement the line contains (as in the second line here). This makes the code and comments easier to read than comments using Rem
:
'This is a comment line
Documents.Add 'create a document based on Normal.dotm
It's tempting to think that you don't need to document your code because you'll be able to recall what it does. But once you've written a lot of code, you probably won't be able to remember. Coming back to a procedure six months after writing it, you'll find it as unfamiliar as if someone else had written it. And if you've become a VBA whiz, you may even find it hard to visualize the clumsy techniques you were using at that time.
Most programmers have a distinct aversion to documenting their code; for some, the dislike of documenting is almost pathological. You can see why: When you're writing the code, documenting what each line does slows you down and distracts you from your larger purpose. And documenting after the code is finished and tested is tedious work. Besides, anyone who's competent should be able to read the code and see what it does, shouldn't they?
Maybe so, but consider this: It's likely that you won't always be the person working with your code—at times, others may work with it too, and they'll appreciate all the help they can get in understanding its purposes and behaviors. Likewise, the code on which you work won't always be your own—you may at times have to debug code that others have written, and in such cases, you'll be the one grateful for comments.