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
Next k
Next j
Next i
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]
[instructions]
[Exit Do]
[instructions]
Loop
or
Do
[instructions]
[Exit Do]
[instructions]
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 differ-
ence 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.
The following example is the ROWOFLARGEST function presented in the previous section, rewritten to use a
Do While loop (using the first syntax):
Function ROWOFLARGEST2(c)
Search JabSto ::




Custom Search