Microsoft Office Tutorials and References
In Depth Information
The For-Next loop checks each cell in the column. When the cell equal to MaxVal is found, the
row number (variable r , the loop counter) is assigned to the function’s name, and the Exit For
statement ends the procedure. Without the Exit For statement, the loop continues to check all
cells in the column — which can take quite a long time!
The previous examples use relatively simple loops. But you can have any number of statements
in the loop, and you can even nest For-Next loops inside other For-Next loops. The following
is VBA code that uses nested For-Next loops to initialize a 10 x 10 x 10 array with the value –1.
When the three loops finish executing, each of the 1,000 elements in MyArray contains –1.
Dim MyArray(1 to 10, 1 to 10, 1 to 10)
For i = 1 To 10
For j = 1 To 10
For k = 1 To 10
MyArray(i, j, k) = –1
Do While loops
A Do While loop is another type of looping structure available in VBA. Unlike a For-Next
loop, a Do While loop executes while a specified condition is met. A Do While loop can have
one of two syntaxes:
Do [While condition]
Loop [While condition]
As you can see, VBA enables you to put the While condition at the beginning or the end of the
loop. The difference between these two syntaxes involves the point in time when the condition is
evaluated. In the first syntax, the contents of the loop may never be executed: That is, if the
condition is met as soon as the Do statement is executed. In the second syntax, the contents of the
loop are always executed at least one time.