Excel VBA Macro Programming

If you know where the statement is that is causing an error, a single breakpoint will locate the problem. However, it's more likely that you will only have a rough idea of the area of code causing the problem.

You can insert a breakpoint where you want to start checking the code and then single step the procedure to see what each statement is doing. You can also skip a statement or start execution from a different place.

Single Stepping

Single stepping allows you to execute one statement at a time. You can place your cursor on variables anywhere in the code to see the state of variables, and you can also use the Debug window to view values of variables.

You can single step by using Debug Step Into from the menu or pressing F8 . You can also run the code to the position of the cursor. Click the mouse on a line of code and then press Ctrl-F8 . The code will only execute as far as where you have clicked the mouse. Note the cursor must be on an executable line of code, not a blank line. You can also step between individual statements if they are on the same line but separated by the : character.

temp = 4: If temp = 3 Then Exit Sub

Procedure Stepping

If you have a subroutine or function that is being called, you may not wish to step through the whole procedure line by line. You may have already tested it and be satisfied with the performance of it. If you use F8 to step through (Single Step), you will be taken all through the subroutine's code one step at a time. This could be extremely time consuming for something that you know already works. If you use Shift-F8 (Step Over), then your subroutine will be treated as a single statement but without stepping through it.

Call Stack Dialog

The Call Stack dialog, shown in the following illustration, displays a list of active procedure calls ‚ that is, calls that have been started but not completed. You can display this dialog by using Ctrl-L . It can help you trace the operation of calls to procedures especially if they are nested where one procedure then calls another procedure.

The earliest active procedure call is shown at the top of the list, and any subsequent procedure calls are added to the top, as shown in Figure 7-2. By highlighting the procedure in the figure and clicking Show, the statement calling that procedure will be displayed. This is highlighted with a green arrow.

Figure 7-2: How to call a subroutine and show which procedure called it