Page 196 - Excel Data Analysis

10 537547 Ch09.qxd 3/4/03 12:13 PM Page 182

EXCEL DATA ANALYSIS

EXECUTE A TASK A SPECIFIC

NUMBER OF TIMES

t is quite common to perform the same data analysis executes until the counter variable reaches the specified
tasks repeatedly within the same worksheet. For maximum value then control moves to the next statement
I example, you may want to summarize each column of outside the loop.
data values. Instead of manually rerunning a macro, you can When the For Next loop starts, it checks that the value of
set up a macro to repeat the same tasks a specific number the counter variable has not met the maximum value. The
of times. You can use the For Next loop to execute a counter variable is a numeric value that is incremented by
statement or a series of statements a specific number of 1 each time the loop executes. If the variable is less than the
times in your macro. For example, using a For Next loop maximum, the loop executes. If the minimum value is
enables you to add the values in a specific number of cells.
initially greater than the maximum value, the body of the
loop never executes.
The For Next loop consists of four basic parts. The For
statement initiates the loop. You specify a counter variable VBA provides several other statements that you can use to
with an initial and a maximum value, such as A = 1 To 5. control the flow of a macro, such as the Do While
The inside of the body of the loop consists of a series of statement, which repeats the statements as long as a
statements that performs until the counter meets the condition is true. See Appendix C for additional program
maximum value of the loop. Finally, you mark the end of flow statements.
the loop with the Next statement. The For Next loop
EXECUTE A TASK A SPECIFIC NUMBER OF TIMES

⁄ Create a new subroutine ¤ Declare the loop variable ‹ Type For N = 1 To Max,
for the macro. and any other variables replacing N with the variable
needed for the subroutine. declared for the For Next
Note: See the section "Create a loop and Max with the
Macro Using the Visual Basic Note: See the section "Declare a maximum value of the loop.
Editor" for more information. Variable" for more information.

182