Programming Excel with VBA and .NET

Start and Stop

I’ve already touched on how to run code from Excel or Visual Basic a little bit, and Table 1-1 lists the handy keys and key combinations that you can use to start and stop code in Excel.

Table 1-1. Useful keys to start and stop code

Press	To
Alt-F8	Run or edit a Visual Basic procedure from an Excel workbook.
Esc or Ctrl-Break	Stop code that is running out of control.
F8	Run one line at a time in Visual Basic.
Shift-F8	Run one line as a single statement (without stepping in to another procedure) in Visual Basic.
Shift-Ctrl-F8	Finish running the current procedure and return to the procedure that called the current one. In other words, step out of the current procedure and go up one level.
Ctrl-F8	Run all the code from the beginning of a procedure to the current cursor position in Visual Basic.
F5	Run a procedure from beginning to end in Visual Basic.
F9	Set or remove a stopping point (called a breakpoint) in code.
Ctrl-Shift-F9	Remove all breakpoints from all classes and modules.

Of these, F9 to add a breakpoint combined with F5 and F8 are perhaps the most useful combinations to help solve runtime errors or just to help figure out how the code works. When you set a breakpoint in code, Visual Basic highlights the whole line by making its background red (Figure 1-17).

Now if you run the code, it will stop if the active sheet is not a worksheet or a chart (for instance, it might be an old-style dialog sheet). Breakpoints change the focus from Excel to Visual Basic, so they are a great way to step in to a procedure that is triggered by Excel in some way (for example, through an event).

Basically, any time you have a question about what code is doing, set a breakpoint somewhere before the point that you have a question about, then run the code. When Visual Basic hits the breakpoint, it will stop and you can press F8 to step through the code one line at a time.

Running to a breakpoint puts the code in context by filling in variables with live data from Excel. Looking at the values Excel fills in is what I cover next.

Figure 1-17. You can also set/clear a breakpoint by clicking to the left of the line of code (where the dot is)