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.


  1. 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.)

  2. 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.)

    click to expand

  3. Press F8 three more times.

    These statements move the Category field over to column A and then sort the list by Category.

    click to expand

  4. 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.

    click to expand

  5. 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.

  6. 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.

    click to expand

  7. 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.

    click to expand

  8. Press F8 to execute the statement that assigns a manual page break.

    click to expand

    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.

  9. 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


  10. 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.

  1. Click the red circle in the margin to turn off the breakpoint.

  2. 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.

    click to expand

  3. On the Debug menu, click the Run To Cursor command.

    click to expand

  4. Press F8 three times to scroll to the bottom of the list, set the print titles, and preview the report.

  5. Review the report. Click Next repeatedly to get to page 17 to see the end of the Books category.


    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.

    click to expand

  6. Close Print Preview, and press F8 twice more to finish the macro.

  7. 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.

  1. 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.

  2. Press F5 to run the macro. Watch the status bar to see how the macro is progressing.

    click to expand

  3. 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.

  4. After the Next statement, insert the statement:

    Application.StatusBar = False
  5. 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.

    click to expand

  6. 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.

Microsoft Excel 2002 Visual Basic for Applications Step by Step
FrameMaker 6: Beyond the Basics
ISBN: 735613591
EAN: 2147483647
Year: 2005
Pages: 122
Authors: Lisa Jahred

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net