Stepoption.Stepallows you to specify the size of the increment and also the direction by
using the following code:
For n = 3 to 12 Step 3
MsgBox n
Next n
You will get the results 3, 6, 9, and 12, because it works in increments of 3.
To see howStepworks backward, try this example:
For n= 10 to 1 Step -1
MsgBox n
Next n
You will get the results 10, 9, 8, 7, 6, 5, 4, 3, 2, and 1.
For..Next loops can also be nested inside each other. For example, if you want to look at
each value in a spreadsheet, you can use one For..Next to go across a multidimensional array
and a second For..Next to go down the multidimensional array.
Following is an example that loops through values fornandm. Notice the indentation of
the code; it makes the nesting of the For..Next clear. Themloop has been nested inside of the
nloop so it will perform the firstnvalue, then all values ofm, then the nextnvalue, then all
values ofmagain. Indenting helps prevent you from getting lost in your code when you look
at it in a month’s time.
Sub test_loop()
For n = 1 To 4
For m = 1 To 5MsgBox "n= " & n
MsgBox "m= " & m
Next m
Next n
End Sub
For Each Loops
The For Each loop is very similar to a For..Next loop, but it is specifically for use on collections
or arrays. For Each allows you to step through each item within the collection or array. You do
not use an index (such asnin the previous example) because it automatically moves through
each item within the collection. This is very useful if you need to search through a collection
for a certain object and then delete it because the position in the collection after deletion is
maintained in your loop. If you use a For..Next loop with an index and delete the object, the
index will be moved up one and your routine will go through one loop too many, causing an
error message.
Chapter 4: Programming Basics: Decisions and Looping 41