Microsoft Office Tutorials and References
In Depth Information
8.2.1 Exit For
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
8.2.1 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
Search JabSto ::

Custom Search