The For...Next statement provides a method for repeatedly looping through a block of code (that is, one or more lines of code). This loop is naturally referred to as a For loop. The basic syntax is:
For counter = start To end
The first time that the block of code is executed, the variable counter (called the loop variable for the For loop) is given the value start. Each subsequent time that the block of code is executed, the loop variable counter is incremented by 1. When counter exceeds the value end, the block of code is no longer executed. Thus, the code block is executed a total of end - start + 1 times, each time with a different value of counter.
Note that we can omit the word counter in the last line of a For loop (replacing Next counter with just Next). This may cause the For loop to execute a bit more quickly, but it also detracts a bit from readability.
To illustrate, the following code loops through the collection of all cells in the current selection. If a cell has a date in it, then the font color is changed to red:
Dim i As Integer Dim oCell As Range
For i = 1 To Selection.Count ' Get the next cell Set oCell = Selection.Cells(i)
' Color it if a date If IsDate(oCell) Then oCell.Font.Colorlndex = 3 End If Next i
For loops are often used to initialize an array. For instance, the following code assigns a value of 0 to each of the 11 variables iArray (0) through iArray (10):
Note that the loop variable counter will usually appear within the block of code, as it does in this array initialization example, but this is not a requirement. However, if it does appear, we need to be very careful not to change its value, since that will certainly mess up the For loop. (VBA automatically increments the loop variable each time through the loop, so we should leave it alone.)
Was this article helpful?