Using Breakpoints

If you know more or less where you can expect a problem in the code of your procedure, you should suspend code execution at that location (on a given line). Setting a breakpoint boils down to pressing F9 when the cursor is on the desired line of code. When VBA gets to that line while running your procedure, it will immediately display the Code window. At this point, you can step through the code of your procedure line by line by pressing F8 or choosing Debug | Step Into.

To see how this works, let's look at the following scenario. Assume that during the execution of the ChangeCode procedure the following line of code could get you in trouble:

ActiveCell.Formula = _

"=VLookup(RC[1],Codes.xls!R1C1:R6C2,2)"

1. Prepare the spreadsheets shown in Figures 13-2 and 13-3. Save the data shown in Figure 13-2 as Chap13.xls. Save the data in Figure 13-3 as Codes.xls. Close the Codes.xls file.

0 Microsoft Excel - Chapl3

-l-fxt

S] File Edit View Insert Format Tools Data Ql Arial - IC ' B I U

Window Hein - S1 X

□ 13 » f*

A

B

C

D

E

F

±

1

Teacher

Position

Amoiim

Godel

2

Ann Marie Smith

A

6500

66

pi

Barbara Kaufman

A

6500

62

I

John Frederick

A

6500

73

5

Katharine Stein

B

630Ü

73

6

Christine Martin

B

6300

73

7

Mark O'Brian

B

6300

65

6

Jorge Rodriguez

S

63IXJ

67

9

111

Figure 13-2: The codes entered in column D of this spreadsheet will be replaced by the ChangeCode procedure with the codes illustrated in Figure 13-3.

t^] Microsoft 1 Hrcl - Codes

^iBi.

»

ffl File Edit View Insert Format lools Data Window Help - S1

□ J Arial -10 - B I U __ » <5> - A -

A1

fx 62

A

B

c I

D

-

621

1227.163-14-100

■ 2

65

211.163-23-220

3

_66 227.163-23-220 _

s

67

227.163-11-100

5

73

211 163-23-330

6

73

21 i 163-28-330

_

7

Figure 13-3: The ChangeCode procedure uses this code table for lookup purposes.

Figure 13-3: The ChangeCode procedure uses this code table for lookup purposes.

2. With Chap13.xls active, switch to the Visual Basic Editor window.

3. Use the Properties window to rename VBAProject (Chap13.xls) to Debugging.

4. Insert a module into the Debugging (Chap13.xls) project, and change its Name property to Breaks.

5. Enter the code of the ChangeCode procedure, as shown below:

Sub ChangeCode()

Workbooks.Open FileName:="C:\Codes.xls"

Windows("Chap13.xls").Activate

Columns("D:D").Select

Selection.Insert Shift:=xlToRight

Range("D1").Select

ActiveCell.Formula = "Code"

Columns("D:D").Select

Selection.SpecialCells(xlBlanks).Select

ActiveCell.Formula = "=VLookup(RC[1],Codes.xls!R1C1:R6C2,2)"

Selection.FillDown

With Columns("D:D")

.EntireColumn.AutoFit .Select End With Selection.Copy

Selection.PasteSpecial Paste:=xlValues Rows("1:1").Select With Selection

.HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Orientation = xlHorizontal End With Workbooks("Codes.xls").Close End Sub

6. In the ChangeCode procedure, click anywhere on the line containing the following statement:

ActiveCell.Formula = "=VLookup(RC[1],Codes.xls!R1C1:R6C2,2)"

7. Press F9 (or choose Debug | Toggle Breakpoint) to set a breakpoint on the line where the cursor is located. Another way to set a breakpoint is to click in the margin indicator to the left of the line on which you want to pause the procedure. When you set the breakpoint, Visual Basic displays a red circle in the margin. At the same time, the line that has the breakpoint is indicated as white text on a red background (Figure 13-4). The color of the breakpoint can be changed on the Editor Format tab in the Options dialog box (Tools menu).

8. Run the ChangeCode procedure. When you run the procedure, Visual Basic will execute all the statements until it encounters the breakpoint. Once the breakpoint is reached, the code is suspended, and the screen displays the Code window (Figure 13-5). Visual Basic displays a yellow arrow in the margin to the left of the statement at which the procedure

I^g Microsoft Visual Basic - Lhapl3jds - [Breaks (Code)]

^HlJEl

Fila £dt View Insert Format Debug Rlti Tods

Add-lrc Window

T /rvi a orf '"r, Fül -|-Ip

T ^ fi DC

m m • a * ^ ® &1 * Í? ► ii ■ H | ¿

ÜSS'/ t*

Ln 1. 0£i 1

i- i- ■ÖSSS + % % t

-

I (General)

[(Declarations)

lOption Explicit

Project - Dcuuov'iQ

Debugging (Chapl3.xls)

F Ö Microsoft Excel Objects ® Sheet 1 {Sheet 1} ® Sheet2{Sheet2) ® Sheet3(Sheet3) Q This'A'otktcok B-<üf Modules víj Breaks

Properties Breaks

I Breaks t'tdde A^beüc J Categorized |

I Breaks t'tdde A^beüc J Categorized |

I (General)

[(Declarations)

lOption Explicit

Sub ChangeCodeQ Workboo ks. O pen F ¡I enarne :=" C: \Cod es. xl s" Win dowsfCha p 13. xls") Actívate Columns f'D D"). Sel ect Selecti o n. Inserí Shift:=kIToRigh1 Range('Dl").Select Active Ce II. Formula = "Code" Columns ("D:D"). Sel ect Selecti on. Sp eci alCel Is (x IB lan ks) Seled

írfueCeí Formula = "=Vl nnkimíBCH 1 CnHps ilsIRI C1:R6C2

Selecti on. Fill Down With Columns("D:D") . E ntireC o lu rrtn. AutoF it .Select End With Selection. Copy

Selection. Past eSpecial Paste:=xlValues Rows("1:1").Select With Selection

HorizonlalAlignmenl = xlCenter .Vertical Alignment = «I Bottom Orientation = kI Horizontal End With Workboo ks("Co des. xls"). Clo se End Sub __

Izff^LJ

Figure 13-4: The line of code where the breakpoint is set is displayed in the color specified on the Editor Format tab in the Options dialog box.

g Microsoft Visual Bask - LhaplSjds [break] - [Breaks (Code)]

Fite £dt V¡ew Insert Format Debug Rlti Tods Add-Ins Window Help

H - H * % e «| *>_f I ► ¡i ■ M, U us" 15 O m n%.% M-n

Project - Debugging

JSJxl

Debugging (Chapl3.xls)

F ä Microsoft Excel Objects ® Sheet 1 {Sheet I} Bp Sheot2{Sheet2) Ép Sheet3{Sheet3) "ThlEWortdDook E-£3 Modules

Properties Breaks

Breaks Module A^beüc I Categorized |

Breaks Module A^beüc I Categorized |

I (General)

[changecorte

Option Explicit

Sub ChangeCodeQ Workboo ks. O pen F il enarne :=" C: \Cod es. xl s" Win dewsf Cha p 13. xls") Activate Columns fD D"). Sel ect Selecti o n. In s erl S hilt :=xlToRig hi Ran ge('Dl"). Select Active Cell. Formula = "Code" Columns ("D:D"). Sel ect Selecti on. Sp eci alCel Is (x IB Ian ks) Select ■ActmiCeH Tormula = =VLookup(RC[1 ]. Codes ,*te!R1 C1R6C2.2)" Selecti on. FillDown With Columns("D:D") . E ntireC o lu mn .AutoF it .Select End With Selection. Copy

Selection. Past eSpecial Paste:=xlValues Rows("1:1").Select With Selection

HorizonlalAlignmenl = xlCenter .Vertical Alignment = «I Bottom Orientation = kI Horizontal End With Workbooks("Co des. xls") .Close End Sub

Figure 13-5: When Visual Basic encounters a breakpoint, it displays the Code window and indicates the current statement.

was suspended. At the same time, the statement appears inside a box with a yellow background. The error and the box indicate the current statement or the statement that is about to be executed. If the current statement also contains a breakpoint, the margin displays both indicators overlapping one another (the circle and the arrow).

9. Press F8, or choose Debug | Step Into.

10. Repeat the instruction in step 9 a few more times.

11. Press F5 (or choose Run Sub/UserForm) to continue running the procedure without stepping through its code.

When you finish running your procedure, Visual Basic does not automatically remove the breakpoint. Notice that the line of code with the VLookup function is still highlighted.

In this example you have set only one breakpoint. Visual Basic allows you to set any number of breakpoints in a procedure. This way, you can suspend and continue the execution of your procedure as you please. You can analyze the code of your procedure and check the values of variables while execution is suspended. You can also perform various tests by typing statements in the Immediate window.

12. Remove the breakpoint by choosing Debug | Clear All Breakpoints, or press Ctrl+Shift+F9.

All the breakpoints are removed. If you had set several breakpoints in a given procedure and would like to remove only one or some of them, click on the line containing the breakpoint that you want to remove and press F9 (or choose Debug | Clear Breakpoint). You should clear the breakpoints when they are no longer needed. The breakpoints are automatically removed when you close the file.

Tip 13-2: When to Use a Breakpoint

Consider setting a breakpoint if you suspect that your procedure never executes a certain block of code.

0 0

Post a comment