Page 109 - Excel Progamming Weekend Crash Course

h540629 ch06.qxd 9/2/03 9:33 AM Page 84

84 Saturday Morning

Optionally, For...Next can use an increment value other than 1. The syntax for this is:

For index = start To stop Step step
...
Next index
In the preceding example, step specifies the increment value. Note the following:
If step is negative, start must be greater than stop.
If step is a floating-point value, index must be declared as a floating-point type.

This example counts down from 10 to 5 as follows: 10, 9.9, 9.8 ... 5.1, 5:
Dim index As Single
For index = 10 To 5 Step -0.1
...
Next index

Never: Change the value of the loop counter inside the loop. You can use the value, but
you should let the For...Next statement make changes as the loop counts down.
Use the Exit For statement to exit from a For...Next loop early. The following exam-
ple loops through all the elements in an array and exits if an element equal to zero is
found:
Dim found As Boolean
Dim index As Integer
found = False
For index = LBound(MyArray) To UBound(MyArray)
If MyArray(index) = 0 Then
found = True
Exit For
End If
Next index

The For Each...Next Statement
You use the For Each...Next statement to iterate through all the members of a collection
or array. The syntax for collections is:

For Each item in collection
...
Next

Item is a variable that has been declared as an appropriate type to reference the objects
in the collection (type Object, type Variant, or the specific type of the collection). The
loop starts by setting item to reference the first object in the collection and then executing
the statements between the For and the Next. Item is then set to reference the next object
in the collection, and the process repeats for all elements of the collection. If the collection
is empty, the statements in the loop are not executed at all.