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 Chapter07 workbook 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 database, 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.dbf" 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. Open  -
Make sure the current folder is the one containing the practice files for this book. (On the File menu, 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 database file. (The two variables are declared as Long, which means that they can hold whole numbers but aren't limited to the four-digit numbers of type Integer.)  -
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 3300, and the value of myRow is 3. Those values appear to be correct. The loop will execute from row 3 to row 3300.  -
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 Books category? Pressing F8 repeatedly until the macro finds the last row in the category could take a very 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 structure. -
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 starts stepping just before it would execute the breakpoint statement.  -
Press F5 to continue the macro. The macro stops at 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 Reset  -
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 Books category. 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 Suppose you're now stepping through the middle of the PrintOrders macro. The code to assign a page break seems to be working properly. There are still some statements at the end of the macro, however, that you'd like to step through. -
Click the red circle in the margin to turn off the breakpoint. -
Click anywhere in the Cells(myRow, 1).Select statement after the end of the loop. 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.  -
Press F8 three times to scroll to the bottom of the list, set the print titles, and preview the report. -
Review the report. Click Next repeatedly to get to page 17 to see the end of the Books category. | Important | If you don't see the end of the Books category on page 17, simply click Next or Previous to locate the correct page. Your current printer driver might have placed the end of the Books category on a different page. |  -
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 go 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 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 is the gray strip at the bottom of the Excel window that 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 the value False to it. -
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 structures, these tools let you create macros that are smart and very powerful. |
