Using Loops
Problem
You need to loop through some code and don't know the VBA syntax for loops.
Solution
Use a For loop like the one shown in Example 2-12.
Example 2-12. For loop
|
For i = 1 To n ' Statements go here Next i |
For loops are sometimes called For Next loops because you use the Next statement to bound the code over which you want to loop. Also, the Next statement increments the counter variable, i in this case, by 1 each time through the loop. In this example, the loop starts with the counter equal to 1 and ends when the counter equals the value stored in the variable n. You can hardcode start and end values or use variables.
Discussion
To increment the counter in a For loop by a something other than 1, use the Step keyword as shown in Example 2-13.
Example 2-13. For loop
|
For i = 1 To n Step 2 ' Statements go here Next i |
In this case, the counter variable gets incremented by 2 each time through the loop. You may also use a negative step value to count down from a start value to an end value.
Sometimes it's necessary to exit a For loop before the counter actually reaches the end value. To exit a For loop, use the Exit For statement. You can put this in a conditional statement to exit only under specific conditions, as shown in Example 2-14.
Example 2-14. Exit For statement
|
For i = 1 To n ' Statements go here ' Test an exit condition If time > 32000 Then Exit For End If ' May have more statements here Next i |
When an Exit For statement is encountered, code execution will jump from the Exit For statement to the first statement after the Next statement of the loop.
For most of the calculations discussed in this book that require looping, plain old For loops will work just fine. In fact, I use For loops probably 90% of the time, but in some special cases Do loops are a better choice.
Do loops are useful when you're not actually iterating a counter but are testing some condition (a logical statement) instead. In VBA there are actually four different ways of writing Do loops, as shown in Example 2-15.
Example 2-15. Four different Do loops
|
Do While (dtime < 32000) ' Statements go here Loop Do ' Statements go here Loop While (dtime < 32000) Do Until (dtime > 32000) ' Statements go here Loop Do ' Statments go here Loop Until (dtime > 32000) |
The first two loops in Example 2-15 are Do While loops, and the last two are Do Until loops.
Do While loops execute the loop as long as the condition following the While keyword is TRue. In this example, the condition is enclosed in parentheses. Note the difference between the two Do While loops. In the first case the condition is tested before the code in the loop is executed, while in the second case the code in the loop is executed once before the condition is tested. This means that in the first case it's possible the code within the loop may not get executed, while in the second case the code within the loop will get executed at least once.
Do Until loops execute the loop as long as the condition following the Until keyword is false (that is, until the condition becomes true). As with Do While loops, there are two ways to write Do Until loops. In the first case shown in Example 2-15 the condition is tested before the code within the loop is executed, while in the second case the code in the loop is executed before the condition is tested.
As with For loops, you can exit a Do loop anytime before the looping condition is satisfied, using an Exit Do statement.
See Also
VBA also has a For Each loop that is used to loop over collections of objects. These loops are useful when interfacing with Excel objects, such as cells. For example, you can use a For Each construct to loop over a range of selected cells in a spreadsheet without actually knowing the number of cells in the range. See the help topic "Using For Each...Next Statements" in the VBA help for more information on these loops. Press F1 in the VBA IDE to access the VBA help guide.