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.