This sounds like BASIC, so why doesn't it look familiar?

Using debugging tools to figure out recorded code

The following sections introduce some awesome debugging tools that are available in the VB Editor. These tools are excellent for helping you see what a recorded macro code is doing.

Stepping through code

Generally, a macro runs quickly: You start it, and less than a second later, it’s done. If something goes wrong, you don’t have an opportunity to figure out what the macro is doing. However, using Excel’s Step Into feature makes it possible to run one line of code at a time.

To use this feature, make sure your cursor is in the procedure you want to run, such as the ImportInvoice procedure, and then from the menu, select Debug | Step Into, as shown in Figure 2-7. Alternatively, you can press F8.

FIGURE 2.7 You can use the Step Into feature to run a single line of code at a time.

The VB Editor is now in Break mode. The line about to be executed is highlighted in yellow, with a yellow arrow in the margin before the code (see Figure 2-8).

FIGURE 2.8 The first line of the macro is about to run.

In this case, the next line to be executed is the Sub ImportInvoice() line. This basically says, “You are about to start running this procedure.” Press the F8 key to execute the line in yellow and move to the next line of code. The long code for OpenText is then highlighted. Press F8 to run this line of code. When you see that Selection.End(xlDown).Select is highlighted, you know that Visual Basic has finished running the OpenText command. At this point, you can press Alt+Tab to switch to Excel and see that the Invoice.txt file has been parsed into Excel. Note that A1 is selected.

Switch back to the VB Editor by pressing Alt+Tab. The next line about to be executed is Selection.End(xlDown).Select. Press F8 to run this code. Switch to Excel to see that the last cell in your data set is selected.

Press F8 again to run the Range("A11").Select line. If you switch to Excel by pressing Alt+Tab, you see that this is where the macro starts to have problems. Instead of moving to the first blank row, the program moves to the wrong row.

Now that you have identified the problem area, you can stop the code execution by using the Reset command. You can start the Reset command either by selecting Run | Reset or by clicking the Reset button on the toolbar (the small blue square next to the Run and Pause icons). After clicking Reset, you should return to Excel and undo anything done by the partially completed macro. In this case, you need to close the Invoice.txt file without saving.

More debugging options: Breakpoints

If you have hundreds of lines of code, you might not want to step through each line one at a time. If you have a general idea that a problem is happening in one particular section of the program, you can set a breakpoint. You can then have the code start to run, but the macro pauses just before it executes the breakpoint line of code.

To set a breakpoint, click in the gray margin area to the left of the line of code on which you want to break. A large maroon dot appears next to this code, and the line of code is highlighted in brown (see Figure 2-9). (If you don’t see the margin area, go to Tools | Options | Editor Format and select the Margin Indicator Bar checkbox.) Or select a line of code and press F9 to toggle a breakpoint on or off.

FIGURE 2.9 The large maroon dot signifies a breakpoint.

Next, from the Visual Basic menu, select Run | Run Sub/UserForm or press F5. The program executes but stops just before running the line in the breakpoint. The VB Editor shows the breakpoint line highlighted in yellow. You can now press F8 to begin stepping through the code.

After you have finished debugging your code, remove the breakpoints by clicking the dark brown dot in the margin next to each breakpoint to toggle it off. Alternatively, you can select Debug | Clear All Breakpoints or press Ctrl+Shift+F9 to clear all breakpoints that you set in the project.

Backing up or moving forward in code

When you are stepping through code, you might want to jump over some lines of code, or you might have corrected some lines of code that you want to run again. This is easy to do when you are working in Break mode. One favorite method is to use the mouse to grab the yellow arrow. The cursor changes to a three-arrow icon, which enables you to move the next line up or down. Drag the yellow line to whichever line you want to execute next. The other option is to right-click the line to which you want to jump and then select Set Next Statement.

Not stepping through each line of code

When you are stepping through code, you might want to run a section of code without stepping through each line, such as when you get to a loop. You might want VBA to run through the loop 100 times so you can step through the lines after the loop. It is particularly monotonous to press the F8 key hundreds of times to step through a loop. Instead, click the cursor on the line you want to step to and then press Ctrl+F8 or select Debug | Run To Cursor. This command is also available in the right-click menu.

Querying anything while stepping through code

Even though variables have not yet been discussed, you can query the value of anything while in Break mode. However, keep in mind that the macro recorder never records a variable.

Using the Immediate window

Press Ctrl+G to display the Immediate window in the VB Editor. While the macro is in Break mode, you can ask the VB Editor to tell you the currently selected cell, the name of the active sheet, or the value of any variable. Figure 2-10 shows several examples of queries typed into the Immediate window.

FIGURE 2.10 Queries that can be typed into the Immediate window while a macro is in Break mode, shown along with their answers.

When invoked with Ctrl+G, the Immediate window usually appears at the bottom of the code window. You can use the resize handle, which is located above the Immediate title bar, to make the window larger or smaller.

There is a scrollbar on the side of the Immediate window that you can use to scroll backward or forward through past entries.

It is not necessary to run queries only at the bottom of the Immediate window. For example, if you have just run a line of code, type ?Selection.Address in the Immediate window to ensure that this line of code worked.

Next, press the F8 key to run the next line of code. Instead of retyping the same query, in the Immediate window, click anywhere in the line that contains the last query and press Enter. The Immediate window runs this query again, displays the results on the next line, and pushes the old results farther down the window.

You also can use this method to change the query by clicking to the right of the word Address in the Immediate window. Press the Backspace key to erase the word Address and instead type Columns.Count. Press Enter, and the Immediate window shows the number of columns in the selection.

This is an excellent technique to use when you are trying to figure out a sticky bit of code. For example, you can query the name of the active sheet (?ActiveSheet.Name), the selection (?Selection.Address), the active cell (?ActiveCell.Address), the formula in the active cell (?ActiveCell.Formula), the value of the active cell (?ActiveCell.Value or ?ActiveCell because Value is the default property of a cell), and so on.

To dismiss the Immediate window, click the X in its upper-right corner.

Querying by hovering

In many instances, you can hover the cursor over an expression in code and then wait a second for a tooltip to show the current value of the expression. This is incredibly helpful when you get to looping in Chapter 5, “Looping and flow control.” It also comes in handy with recorded code. Note that the expression that you hover over does not have to be in the line of code just executed. In Figure 2-11, Visual Basic just selected E11, making E11 the active cell. If you hover the cursor over ActiveCell.FormulaR1C1, you see a tooltip showing that the formula in the active cell is "=SUM(R[-9]C:R[-1]C)".

FIGURE 2.11 Hover the mouse cursor over any expression for a few seconds, and a tooltip shows the current value of the expression.

Sometimes, the VBA window seems to not respond to hovering. Because some expressions are not supposed to show values, it is difficult to tell whether VBA is not displaying a value on purpose or whether you are in the buggy “not responding” mode. Try hovering over something that you know should respond, such as a variable. If you get no response, hover, click into the variable, and continue to hover. This tends to wake Excel from its stupor, and hovering works again.

Are you impressed yet? This chapter started with a complaint that VBA doesn’t seem much like BASIC. However, by now, you have to admit that the Visual Basic environment is great to work in and that the debugging tools are excellent.

Querying by using a Watches window

In Visual Basic, a watch is not something you wear on your wrist; instead, it allows you to watch the value of any expression while you step through code. Let’s say that in the current example, you want to watch to see what is selected as the code runs. You can do this by setting up a watch for Selection.Address.

From the Debug menu, select Add Watch. In the Add Watch dialog, enter Selection.Address in the Expression text box and click OK (see Figure 2-12).

FIGURE 2.12 Setting up a watch to see the address of the current selection.

A Watches window is added to the busy Visual Basic window, usually at the bottom of the code window. When you start stepping through the code, it imports the file and then selects the last row with data. The Watches window confirms that Selection.Address is $A$18 (see Figure 2-13).

FIGURE 2.13 Without having to hover or type in the Immediate window, you can always see the value of watched expressions.

Press the F8 key to run the code to the line after Rows("1:1").Select. The Watches window is updated to show that the current address of the Selection is now $1:$1.

In the Watches window, the value column is read/write (where possible)! You can type a new value here and see it change on the worksheet. For example, if your watch expression is Selection.Value, you can click on the value and enter a new one.

Using a watch to set a breakpoint

Right-click the Selection.Address expression in the Watches window and select Edit Watch. In the Watch Type section of the Edit Watch dialog, select Break When Value Changes. Click OK.

The glasses icon to the left of the expression changes to a hand with a triangle icon. You can now press F5 to run the code. The macro starts running lines of code until something new is selected. This is very powerful. Instead of having to step through each line of code, you can now conveniently have the macro stop only when something important has happened. You also can set up a watch to stop when the value of a particular variable changes.

Using a watch on an object

In the preceding example, you watched a specific property: Selection.Address. It is also possible to watch an object such as Selection. In Figure 2-14, when a watch has been set up on Selection, you get the glasses icon and a + icon.

FIGURE 2.14 Setting a watch on an object gives you a + icon next to the glasses.

By clicking the + icon, you can see all the properties associated with Selection. When you look at Figure 2-15, you can see more than you ever wanted to know about Selection! There are properties you probably never realized are available. You can see that the AddIndent property is set to False, and the AllowEdit property is set to True. There are useful properties further down in the list, such as the Formula of the selection.

In this Watches window, some entries can be expanded. For example, the Borders collection has a + next to it, which means you can click any + icon to see more details.

FIGURE 2.15 Clicking the + icon shows a plethora of properties and their current values.