Using For Next Loops

The most common type of loop is the For...Next loop. Use this loop when you know exactly how many times you want to repeat a group of statements. The structure of a For...Next loop looks like this:

For counter = start To end [Step increment]

[statements] Next [counter]

counter A numeric variable used as a loop counter. The loop counter is a number that counts how many times the procedure has gone through the loop.

start The initial value of counter. This is usually 1, but you can enter any value or you can use a variable.

end The final value of counter. You can also use a variable here, if it's appropriate.

increment This optional value defines an increment for the loop counter. If you leave this out, the default value is 1. Use a negative value to decrement counter.

statements The statements to execute each time through the loop.

The basic idea is simple. When VBA encounters the For...Next statement, it follows this five-step process:

1. Set counter equal to start.

2. Test counter. If it's greater than end, exit the loop (that is, process the first statement after the Next statement). Otherwise, continue. If increment is negative, VBA checks to see whether counter is less than end.

3. Execute each statement between the For and Next statements.

4. Add increment to counter. Add 1 to counter if increment isn't specified.

5. Repeat steps 2 through 4 until done.

Listing 6.12 shows a simple Sub procedure—LoopTest—that uses a For...Next statement. Each time through the loop, the procedure uses the Application object's StatusBar property to display the value of counter (the loop counter) in the status bar. When you run this procedure, counter gets incremented by 1 each time through the loop, and the new value gets displayed in the status bar.

0 0

Post a comment