The For Loop

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
   ' block of code goes here . . .

Next counter

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.ColorIndex = 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):

For i = 0 To 10
    iArray(i) = 0
Next i

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.)

Exit For

VBA provides the Exit For statement to exit a For loop prematurely. For instance, the code in Example 8-1 finds the first nonempty cell in the first row of the active worksheet. If none exists, a message is displayed. Note the use of a Boolean variable to keep track of the existence question.

Example 8-1. Finding the First Nonempty Cell

Sub FindFirstNonEmpty()

Dim oCell As Range
Dim bNone As Boolean

bNone = True
For Each oCell In ActiveSheet.Rows(1).Cells
  If Not IsEmpty(oCell) Then
    oCell.Select
    bNone = False
    Exit For
  End If
Next
   
If bNone Then MsgBox "No nonempty cells in row 1", vbInformation

End Sub

We can also control the step size and direction for the counter in a For loop using the Step keyword. For instance, in the following code, the counter i is incremented by 2 each time the block of code is executed:

For i = 1 to 10 Step 2
   ' code block goes here
Next i

The following loop counts down from 10 to 1 in increments of -1. This can be useful when we want to examine a collection (such as the cells in a row or column) from the bottom up:

For i = 10 to 1 Step -1
   ' code block goes here
Next i