The first type of error is a language error (also known as a syntax error). 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, 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 you tell 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 about language errors and compile errors 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. 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):

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

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.

Figure 17.1. The VBA Editor helps debug your code by identifying many compile errors as it checks the statements you enter.

Decide for Yourself If You Like the Auto Syntax Check Feature

This chapter assumes that you're keeping VBA's Auto Syntax Check feature and other features switched on. If you have Auto Syntax Check turned off (Tools

Some developers choose to turn off Auto Syntax Checking because they don't want to be nagged as they type in their code—it can interfere with their focus on the larger goals of the program they're writing. Working without automatic, immediate syntax checking can prove a cure worse than the disease for some programmers. But others find error message interruptions about typos annoying.

Ultimately, whether you use the Auto Syntax Check feature is a matter of personal taste. For example, some people like to be told right away if they make a spelling error in a Word document; others consider spelling errors rather tedious issues best left for later during an editing phase. They write, focusing on the main points they're trying to make, then at some later time they turn on the spell checker and worry about issues like typos and punctuation. You find a similar decision when you cook: Should you keep washing bowls and cleaning knives and measuring cups while cooking, or is it better to just let things pile up, washing them all at once after the pie is in the oven?

The VBA Editor picks up the previous If X > Y problem easily enough, but you can also make language errors that the VBA Editor does not identify when you move the insertion point from the line in which you've inserted them. Instead, VBA identifies these errors as compile errors when it compiles the code. For example, if you enter the following statement in the Code window when working with Word, the VBA Editor doesn't detect anything wrong. But when you run the procedure by pressing F5, VBA compiles the code, discovers the error, and objects 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 the FileName property.

Figure 17.2. Other errors appear only when you try to run the code.

The VBA Editor also helps you pick up some errors of this kind while writing your code. Say you're trying to enter a Documents.Close statement in Word and mistype Documents as Docments. In this case, the VBA Editor doesn't display the Properties/Methods list because you haven't entered a valid object (unless you've created a custom collection or object named Docments that has a Close method; choosing those names would be a poor idea).

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 doesn't spot the mistake—it shows 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 get a "Variable not defined" compile error instead.)

Similarly, if you specify a property or method for an object to which that property or method doesn't apply, VBA gives a compile error. For example, say you forget the Add method and 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.

Figure 17.3. The "Method or data member not found" error tells you that you've used a method or property that isn't available for the object in question.

The third type of error is the runtime error, which occurs while code is executing. You create a runtime error when you write a statement that causes 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. The diction, punctuation, and syntax of your statement are 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 DZ
DZ = 1 / 0

You're unlikely to enter anything as obviously wrong as this demonstration line in your code; this line 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 a 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 subtraction. Or the user can type zero into a dialog box, then your code later tries to use that as a divisor.

Figure 17.4. An unhandled runtime error causes VBA to display a message box such as this one.

To avoid runtime errors, track the values of your variables by using the Watch window (discussed later in this chapter). To avoid possible user errors, check their input after they close a dialog box. If necessary, display a message explaining that zero isn't an acceptable input for their age, then display the dialog box again expecting valid input this time around.

The fourth type of error is the program logic error, which is an error that produces incorrect results. With program logic errors, the code is technically fine. VBA is able to compile and run it without generating any errors—but you get a different result than you intended. Program logic errors 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 manipulation procedure is likely to run perfectly, but the resulting workbook will bear little resemblance to what you were trying to produce. 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.

Program logic errors tend to be the hardest errors to catch. To nail them down, you need to trace the execution of your code and pinpoint where things start to go wrong. To do so, you 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 during a trial run. He couldn't get it to repeat this behavior. 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 printing it over and over). He discovered that the error only occurred once every 256 times the program ran. He never did locate the bug, but when he gave the program to other people, he just told them that it worked "almost always."

Break mode is a vital tool for debugging your procedures because it lets you watch your code execute step by step 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 to produce the result.

These are the easiest ways to enter Break mode:

You can also enter Break mode in a couple of other ways:

Figure 17.6. You can enter Break mode by pressing Ctrl+Break and then clicking the Debug button in this dialog box.

Figure 17.7. Entering Break mode from a runtime error dialog box like this one takes you straight to the offending statement in your code. The problem code will be highlighted in yellow.

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

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 are working fine so that you can step through a section that may be problematic. This is particularly true of loop structures, which can have you going round and round—a real time-waster if you know the problems isn't within 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

The Step Out command (which you can issue by Ctrl+Shift+F8, clicking the Step Out button on the Debug toolbar, or choosing Debug

The Run To Cursor command (which you can issue by pressing Ctrl+F8 or choosing Debug

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

Figure 17.8. Use the Locals window to see at a glance all the expressions in the active procedure.

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

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

Figure 17.9. Use the Watch window to track the values of variables and expressions in your code.

The Watch window displays watch expressions—expressions you set ahead of time to give you a running display of the value of a variable or an expression. (Note that it is common to describe variables as a type of "expression" even though technically an expression involves more than a single variable: x > y for example.)

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 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. Observe the Watch window while single-stepping through your code to see in which line of code the variable MySalary goes from 30,000 to 2,200,000. Now you're right there close to where the bug is and you can examine the preceding lines of code very carefully to see what's impacting the MySalary variable.

Because watch expressions slow down 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.

Setting Watch Expressions

Sometimes referred to as conditional breakpoints, watch expressions give you considerable flexibility when debugging. You can ask the VBA Editor to break on any kind of expression you can think up, such as 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 > 30000, and the VBA Editor automatically halts execution and displays the line where your salary increases beyond the expected 30,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:

1. Select the variable or expression in your code, right-click it, and choose Add Watch from the context menu to display the Add Watch dialog box (see Figure 17.10). The variable or expression in which you right-clicked appears in the Expression text box.

   You can also select the variable or expression in the Code window and choose Debug

   

2. If necessary, adjust the settings in the Context group box. The Procedure drop-down list is set to the current procedure, and the Module drop-down list is set to the current module.

3. In the Watch Type group box, adjust the option button setting if necessary:

   • The default setting—Watch Expression—adds the variable or expression in the Expression text box to the list in the Watch window. However, conditional breakpoints are more useful if you do more than merely observe the status of variables or expressions. The following two list items describe the true benefit of these breakpoints.

   • Break When Value Is True causes VBA to enter Break mode whenever the value of the variable or expression changes to True.

   • Break When Value Changes causes VBA to enter Break mode whenever the value of the watch expression changes. Use this setting when dealing either with a watch expression whose value you don't expect to change but that appears to be changing (such as MySalary in the previous example) or with a watch expression whose every change you need to observe.

4. Click the OK button to add the watch expression to the Watch window.

Use These Two Important Conditional Break Techniques

The Break When Value Is True option button allows you to run your code without stepping through each statement that doesn't change the value of the watch expression to True. This allows you to specify that Break mode should be entered, for example, when your variable exceeds a certain value (such as X > 10000) or equals another variable (such as x = y) and so on. Employing this kind of conditional break can be extremely helpful when tracking down elusive bugs.

The Break When Value Changes option button allows you to run your code and stop at each location in the code where the value changes.

Figure 17.10. In the Add Watch dialog box, specify the watch expression you want to add.

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.

Editing Watch Expressions

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

Figure 17.11. You can edit your watch expressions in the Edit Watch dialog box.

Using the Quick Watch Feature

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, select the expression or variable in the Code window and then click the Quick Watch button on the Debug toolbar, choose Debug

Figure 17.12. Use the Quick Watch dialog box to get quick information on a variable or expression for which you don't want to set a watch expression in the Watch window.

One use for the Immediate window is as a virtual scratchpad. You enter lines of code that you want to test quickly, without having to enter them in a procedure and 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, then press Enter to see the results. In the second case, you insert statements in your code that display information in the Immediate window, where you can easily view it. You explore both of these techniques in the following section.

To display the Immediate window, click the Immediate Window button on the Debug toolbar, choose View

You can execute code in the Immediate window in both Break mode and design mode.

Debug.Print [outputlist]

[Spc(n) | Tab(n)] expression

Debug.Print CustName
Debug.Print Address1 & ", " & Address2
Debug.Print City & ", " & State & " " & Zip

Sub See_All_Workbook_Names()
    Dim oBook As Workbook
    For Each oBook In Workbooks
        Debug.Print oBook.FullName
    Next
End Sub

Debug.Print "In the Sub Add_Tax the variable intLocal is: " & intLocal

In Sub Add_Tax the variable intLocal is: 7

When working in Break mode, you can summon the Call Stack dialog box (see Figure 17.3) to display a list of the active procedure calls—the procedures being called by the current procedure. 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 for as long as the procedure takes to execute; it's then removed from the list. By using the Call Stack dialog box, 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

Consider writing an error handler in the following circumstances:

Trapping an error means catching it so that you can do something about it. 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
    'statements here

Exit Sub
ErrorHandler:
   'error-handler 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) for most or all of their error handlers.

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 document 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 (to end 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, 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 wish 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.

1.  Sub StyleError()
 2.
 3.      On Error GoTo Handler
 4.
 5.      Selection.Style = "Executive Summary"
 6.
 7.      'the rest of the procedure happens here
 8.
 9.      'exit the procedure once execution gets this far
10.      Exit Sub
11.
12.  Handler:
13.
14.      If Err = 5834 Then
15.          ActiveDocument.Styles.Add _
                 Name:="Executive Summary", Type:=wdStyleTypeParagraph
16.          Resume
17.      End If
18.
19.  End Sub

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

1.  Sub Handle_Error_Opening_File()
 2.
 3.      Dim strFName As String
 4.
 5.  StartHere:
 6.
 7.       On Error GoTo ErrorHandler
 8.       strFName = InputBox("Enter the name of the file to open.", _
              "Open File")
 9.       If strFName = "" Then End
10.       Documents.Open strFName
11.       Exit Sub
12.
13.  ErrorHandler:
14.
15.     If Err = 5174 Or Err = 5273 Then MsgBox _
              "The file " & strFName & " does not exist." & vbCr & _
              "Please enter the name again.", _
              vbOKOnly + vbCritical, "File Error"
16.       Resume StartHere
17.
18.  End Sub

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

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 reenable 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 reenable user input because it's safe for the user to stop the procedure again.

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 prevent the code that executes the move operations from being interrupted. 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