For...Next

When you don't know how many times you need to run the statements in a loop, use a Do loop. When you know that you must run the statements a specific number of times, use a For...Next loop. Unlike a Do loop, a For...Next loop uses a counter variable that increases or decreases in value during each repetition of the loop. Whereas a Do loop ends when a test condition becomes True or False, a For...Next loop ends when the counter variable reaches a specified value.

For example, the following Sub procedure sounds a tone however many times you specify.

Sub BeepSeveral()
    numBeeps = InputBox("How many beeps?")
    For counter = 1 To numBeeps
        Beep
    Next counter
End Sub

Because you didn't specify otherwise, the counter variable in the preceding example increases by 1 each time the loop repeats. You can use the Step keyword to specify a different increment for the counter variable (if you specify a negative number, the counter variable decreases by the specified value each time through the loop). In the following Sub procedure, which replaces every other value in an array with 0 (zero), the counter variable increases by 2 each time the loop repeats.

Sub ClearArray(ByRef ArrayToClear())
    For i = LBound(ArrayToClear) To UBound(ArrayToClear) Step 2
        ArrayToClear(i) = 0
    Next i
End Sub

Note

The variable name after the Next statement is optional, but it can make your code easier to read, especially if you have several nested For loops.