Control Visual Basic in Microsoft Office Excel 2007 Visual Basic for Applications
- 5/16/2007
- Using Conditionals
- Creating Loops
- Managing Large Loops
- Key Points
Managing Large Loops
A loop that executes only two or three times isn’t much different from a program without a loop. It runs fast, and it’s easy to step through to watch how each statement works. Once you start repeating a loop hundreds or thousands of times, however, you need some additional techniques to make sure the macro works the way you want it to.
Set a Breakpoint
The Flow text file includes a macro named PrintOrders. You can think of this macro as one that your predecessor wrote just before leaving the company. Or you can think of it as one that you almost finished three months ago. In either event, you have a macro that you don’t completely understand and that doesn’t work quite right.
The PrintOrders macro is supposed to print a copy of the entire Orders workbook, specifically one that is sorted by product Category. You give each Category manager the section of the report that shows orders only for that one category, so you need a new page every time the Category changes. Unfortunately, the macro doesn’t do what it’s supposed to. You need to find and fix the problem. Here’s the macro as you first receive it:
Sub PrintOrders()
Dim myRow As Long
Dim myStop As Long
Workbooks.Open FileName:="orders.xls"
Columns("E:E").Cut
Columns("A:A").Insert Shift:=xlToRight
Range("A1").CurrentRegion.Sort Key1:="Category", _
Order1:=xlAscending, Header:=xlYes
myStop = Range("A1").CurrentRegion.Rows.Count
For myRow = 3 To myStop
If Cells(myRow, 1) <> Cells(myRow + 1, 1) Then
Cells(myRow, 1).Select
ActiveCell.PageBreak = xlPageBreakManual
End If
Next myRow
Cells(myRow, 1).Select
ActiveSheet.PageSetup.PrintTitleRows = "$1:$1"
ActiveSheet.PrintPreview
ActiveWorkbook.Close SaveChanges:=False
End Sub
The best approach is probably to start stepping through the macro.
Copy the PrintOrders macro from the text file, and paste it into a VBA module in the Chapter07 workbook.
Make sure the current folder is the one containing the practice files for this book. (Click the Office Button, click Open, change to the correct folder, and then click Cancel.)
In the Visual Basic editor, click in the PrintOrders macro, and then press F8 three times to jump over the variable declarations and open the Orders workbook.
Press F8 three more times.
These statements move the Category field over to column A and then sort the list by Category.
Press F8 twice to assign a number to myStop and to start the loop. Hold the mouse pointer over myStop and then over myRow to see the values that were assigned.
The value of myStop is 3266, and the value of myRow is 3. Those values appear to be correct. The loop will execute from row 3 to row 3266.
Press F8 several times.
Visual Basic keeps checking whether the cell in the current row matches the cell below it. How many rows are in the Art category? Pressing F8 repeatedly until the macro finds the last row in the category could take a long time. But if you just press F5 to run the rest of the macro, you can’t watch what happens when the condition in the If statement is True. If only there were a way to skip over all the statements until the macro moves into the If block.
Click in the gray area to the left of the statement starting with ActiveCell.
A dark red circle appears in the margin, and the background of the statement changes to dark red. This is a breakpoint. When you set a breakpoint, the macro stops when it reaches the breakpoint statement.
Press F5 to continue the macro.
The macro stops at the breakpoint. When the macro reaches the breakpoint, the active cell is the first one that the If statement determined is different from the cell below it.
Press F8 to execute the statement that assigns a manual page break.
The page break appears above the row, not below the row. This is a problem. The macro shouldn’t set the page break on the last cell of a Category; rather, it should set the break on the first cell of a Category. The If statement should check to see whether the cell is different than the one above it.
Change the plus sign (+) in the If statement to a minus sign (–).
The revised statement should look like this:
If Cells(myRow, 1) <> Cells(myRow - 1, 1) Then
Click the Reset button, press F5, and click Yes to reopen the Orders file. Then press F8 to watch the critical statement work—properly this time—as it assigns the page break after the Art category.
Click the red circle in the margin to turn off the breakpoint.
)
)
)
)
)
)
Setting a breakpoint is an invaluable tool for finding a problem in the middle of a long loop. In the following section, you’ll learn an easy way to set a temporary breakpoint if you need to use it only once.
Set a Temporary Breakpoint
A breakpoint stops the macro each time the macro reaches the statement, and the breakpoint stays around until you remove it. What if you want to create a temporary breakpoint—one that you use only once? For example, suppose you’re stepping through the middle of the PrintOrders macro. The code to assign a page break seems to be working properly. However, there are still some statements at the end of the macro that you’d like to step through.
If you’re not already stepping through the macro, press F8 to start the macro.
Click anywhere in the Cells(myRow, 1).Select statement after the end of the loop to place the insertion point in that statement.
You want a breakpoint on this statement, but one that you need to use only once.
On the Debug menu, click the Run To Cursor command.
The macro runs through all the pages of the report and stops on the statement with the cursor.
Press F8 three times to scroll to the bottom of the list, set the print titles, and preview the report. Once the macro has stopped on a statement, you can continue stepping from there.
Review the report. Click Next Page repeatedly to get to page 10 to see the end of the Art category.
Close Print Preview, and press F8 twice more to finish the macro.
Save the Chapter07 workbook.
Turning off a breakpoint is just as easy as turning one on: just click in the left margin of the Visual Basic editor window. But if turning a breakpoint on and off is still too much work, you can create a temporary one by running to the cursor.
Show Progress in a Loop
Even if the loop in a macro is working perfectly, you might get nervous about whether something has gone wrong if the macro takes a long time to execute. The best way to feel comfortable when a long loop is running (particularly if you’re wondering whether you have time to get a cup of coffee) is to show the progress of the loop.
You can show progress with any kind of loop. But a For loop lends itself particularly well to showing progress because at any point in the loop, your macro can determine both the current value of the loop counter and also what its final value will be.
In the PrintOrders macro, immediately following the For statement, insert this statement:
Application.StatusBar = "Processing row " & myRow & " of " & myStop
The status bar at the bottom of the Excel window usually says “Ready.” The StatusBar property of the Application object allows you to make the status bar say whatever you want. The best message is one that shows progress and also gives you an idea of how long the task will take.
The statement you added creates this message when it enters the loop the first time: “Processing row 3 of 3300.” By using an ampersand (&) to join together message text with the numbers in the myRow and myStop variables, you can create a useful message. Just be careful to include an extra space before and after the numbers.
Press F5 to run the macro. Watch the status bar to see how the macro is progressing.
Close the Print Preview screen to let the macro finish.
The status bar indicates that the macro is still running. The status bar doesn’t automatically reset when your macro ends. To return control of the status bar to Excel, you must assign it a value of False.
After the Next statement, insert the statement:
Application.StatusBar = False
Run the macro again, close the Print Preview screen at the appropriate time, and then look at the status bar.
It’s back to normal.
Save the Chapter07 workbook.
Visual Basic provides extremely powerful tools for repeating statements in a loop. Coupled with the decisions that you can make using If blocks, these tools let you create macros that are smart and very powerful.