Setting breakpoints in your code

Earlier in this chapter, I discuss using MsgBox functions in your code to monitor the values of certain variables. Displaying a message box essentially halts your code in midexecution, and clicking the OK button resumes execution.

Wouldn't it be nice if you could halt a routine's execution, take a look at any of your variables, and then continue execution? Well, that's exactly what you can do by setting a breakpoint.

You can set a breakpoint in your VBA code in several ways:

1 Move the cursor to the statement at which you want execution to stop; then press F9.

1 Click in the gray margin to the left of the statement at which you want execution to stop.

1 Position the insertion point in the statement at which you want execution to stop. Then use the DebugOToggle Breakpoint command.

1 Right-click a statement and choose ToggleOBreakpoint from the shortcut menu

The results of setting a breakpoint are shown in Figure 13-3. Excel highlights the line to remind you that you set a breakpoint there and also inserts a large dot in the gray margin.

Figure 13-3:

The highlighted statement marks a breakpoint in this procedure.

Figure 13-3:

The highlighted statement marks a breakpoint in this procedure.

Title Bar Microsoft Word

When you execute the procedure, Excel goes into Break mode when the line with the breakpoint is executed. In Break mode, the word [break] is displayed in the VBE title bar. To get out of Break mode and continue execution, press F5 or click the Run Sub/UserForm button in the VBE toolbar. Refer to "Stepping through your code" later in this chapter to find out more.

To quickly remove a breakpoint, click the large dot in the gray margin or move the cursor to the highlighted line and press F9. To remove all breakpoints in the module, press Ctrl+Shift+F9.

What is Break mode? You can think of it as a state of suspended animation. Your VBA code stops running and the current statement is highlighted in bright yellow. In Break mode, you can

1 Type VBA statements in the Immediate window. (See the next section for details.)

1 Step through your code one line at a time to check various things while the program is paused.

In Break mode, you can move the mouse pointer over a variable to displays its value in a small pop-up window. Figure 13-4 shows an example.

Figure 13-4:

In Break mode, move the mouse pointer over a variable to display its current value. In this example, the cell has a value of 43.

Figure 13-4:

In Break mode, move the mouse pointer over a variable to display its current value. In this example, the cell has a value of 43.

Using the Immediate window

The Immediate window may not be visible in the VBE. You can display the VBE's Immediate window at any time by pressing Ctrl+G.

In Break mode, the Immediate window (see Figure 13-5) is particularly useful for finding the current value of any variable in your program. For example, if you want to know the current value of a variable named CellCount, enter the following in the Immediate window and press Enter:

Print CellCount

You can save a few milliseconds by using a question mark in place of the word Print, like this:

? CellCount

Figure 13-5:

The Immediate window in action. The cell's current value is 43.

The Immediate window lets you do other things besides checking variable values. For example, you can change the value of a variable, activate a different sheet, or even open a new workbook. Just make sure that the command you enter is a valid VBA statement.

You can also use the Immediate window when Excel is not in Break mode. I often use the Immediate window to test small code snippets (whatever you can cram on a single line) before incorporating them into my procedures.

Stepping through your code

While in Break mode, you can also step through your code line by line. One statement is executed for each time you press F8. Throughout this line-byline execution of your code, you can activate the Immediate window at any time to check the status of your variables.

Using the Watch window

In some cases, you may want to know whether a certain variable or expression takes on a particular value. For example, suppose that a procedure loops through 1,000 cells. You notice that a problem occurs during the 900th iteration of the loop. Well, you could insert a breakpoint in the loop, but that would mean responding to 899 prompts before the code finally gets to the iteration you want to see (and that gets boring real fast). A more efficient solution involves setting a watch expression.

For example, you can create a watch expression that puts the procedure into Break mode whenever a certain variable takes on a specific value — for example, Counter=900. To create a watch expression, choose DebugOAdd Watch to display the Add Watch dialog box. See Figure 13-6.

Figure 13-5:

The Immediate window in action. The cell's current value is 43.

Figure 13-6:

The Add Watch dialog box lets you specify a condition that causes a break.

Figure 13-6:

The Add Watch dialog box lets you specify a condition that causes a break.

The Add Watch dialog has three parts:

1 Expression: Enter a valid VBA expression or a variable here. For example, Counter=900 or just Counter.

1 Context: Select the procedure and the module you want to watch. Note that you can select All Procedures and All Modules.

1 Watch Type: Select the type of watch by clicking an option button. Your choice here depends on the expression you enter. The first choice, Watch Expression, does not cause a break; it simply displays the expression's value when a break occurs.

Execute your procedure after setting up your watch expression(s). Things run normally until your watch expression is satisfied (based on the Watch Type you specified). When that happens, Excel enters Break mode (unless the Watch Type is set to Watch Expression). From there, you can step through the code or use the Immediate pane to debug your code.

When you create a watch, VBE displays the Watches window shown in Figure 13-7. This window displays the value of all watches that you've defined.

Figure 13-7:

The Watches window displays all watches.

Figure 13-7:

The Watches window displays all watches.

The best way to understand how this Watch business works is to use it and try various options. Before long, you realize what a useful tool it is.

0 0

Post a comment