Loop Statements
The most frequently used looping structure in Visual Basic is undoubtedly the
For...Next loop:
For counter = startvalue To endvalue [Step increment]
' Statements to be executed in the loop...
Next
You need to specify the
Step clause only if
increment is not equal to 1. You can exit the loop using an
Exit For statement, but unfortunately Visual Basic doesn't provide any sort of "Repeat" command that lets you skip the remaining part of the current iteration and restart the loop. The best you can do is use (nested)
If statements or, if you don't want to make the logic too complex, use a plain
GoTo keyword that points to the end of the loop. In fact, this is one of the few occasions when a single
GoTo statement can make your code
more readable and maintainable:
For counter = 1 To 100
' Do your stuff here ...
' if you want to skip over what follows, just GoTo NextLoop.
If Err Then Goto NextLoop
' more code that you don't want to enclose within nested IF blocks
' ...
NextLoop:
Next
TIP Always use an Integer or Long variable as the controlling variable of a For...Next loop because they're faster than a Single or a Double controlling variable, by a factor of 10 or more. If you need to increment a floating-point quantity, the most efficient technique is explained in the next example.
CAUTION A compelling reason to stay clear of floating-point variables as controlling variables in For...Next loops is that, because of rounding errors, you can't be completely sure that a floating-point variable is incremented correctly when the increment is a fractional quantity, and you might end up with fewer or more iterations than expected: Dim d As Single, count As Long
For d = 0 To 1 Step 0.1
count = count + 1
Next
Print count ' Displays "10" but should be "11"
When you want to be absolutely sure that a loop is executed a given number of times, use an integer controlling variable and explicitly increment the floating-point variable within the loop:
Dim d As Single, count As Long
' Scale start and end values by a factor of 10
' so that you can use integers to control the loop.
For count = 0 To 10
' Do what you want with the D variable, and then increment it
' to be ready for the next iteration of the loop.
d = d + 0.1
Next
I covered the
For Each...Next loop already in
Chapter 4, and I won't repeat its description here. I just want to show you a neat trick that's based on this type of loop and the
Array function. This technique permits you to execute a block of statements with different values for a controlling variable, which don't need to be in sequence:
' Test if Number can be divided by any of the first 10 prime numbers.
Dim var As Variant, NotPrime As Boolean
For Each var In Array(2, 3, 5, 7, 11, 13, 17, 19, 23, 29)
If (Number Mod var) = 0 Then NotPrime = True: Exit For
Next
The values don't even have to be numeric:
' Test if SourceString contains the strings "one", "two", "three", etc.
Dim var2 As Variant, MatchFound As Boolean
For Each var2 In Array("one", "two", "three", "four", "five")
If InStr(1, SourceString, var2, vbTextCompare) Then
MatchFound = True: Exit For
End If
Next
The
Do...Loop structure is more flexible than the
For...Next loop in that you can place the termination test either at the beginning or the end of the loop. (In the latter case, the loop is always executed at least once.) You can use either the
While clause (repeat while the test condition is True) or the
Until clause (repeat while the test condition is False). You can exit a
Do loop at any moment by executing an
Exit Do statement, but?as with
For...Next loops?VBA doesn't offer a keyword that skips over the remaining statements in the loop and immediately restarts the loop.
' Example of a Do loop with test condition at the top.
' This loop is never executed if x <= 0.
Do While x > 0
y = y + 1
x = x \ 2
Loop
' Example of a Do loop with test condition at the bottom.
' This loop is always executed at least once, even if x <= 0.
Do
y = y + 1
x = x \ 2
Loop Until x <= 0
' Endless loop: requires an Exit Do statement to get out.
Do
...
Loop
The
While...Wend loop is conceptually similar to the
Do While...Loop. But you can test the condition only at the beginning of the loop, you don't have an
Until clause, and you don't even have an
Exit While command. For these reasons, most programmers prefer the more flexible
Do...Loop structure, and in fact you won't see a single
While...Wend loop in this entire book.