Mastering VBA For Loop Variations

1. For Loop Variations

2. A Different Kind of For Loop • VBA has two kinds of For loops • The first kind is controlled by a variable that is automatically incremented, and runs between a beginning and ending value • The second kind is a For Each loop: it is executed for every member of a collection

3. Collections • There are many kinds of collections in VBA. For example, all the open workbooks are a collection • Here’s an example from Walkenbach: SubCloseInactive() Dim Book As Workbook For Each Book In Workbooks IfBook.Name <> ActiveWorkBook.NameThen Book.Close End If Next Book End Sub

4. A Range is a Collection Too… • The workbook called ForLoopVariations has an example using a named range. The elements of a range are cells which are also ranges. The scope of the range name is the workbook. SubBigYellow() Dim cellVarAs Range For Each cellVarIn Range("ExampleRange") If cellVar.Value >= 100 Then cellVar.Interior.Color = vbYellow End If NextcellVar End Sub

5. Changing the increment • Our examples so far have used an increment of 1 for the control variable • This is the default and no special code needs to be written for it • You can also use other increments, using the Step keyword • Workbook ForLoopVariations has a simple example (next slide)

6. Make Alternate Cells Blue Const CHANGEROW As Long = 8 Const LASTCOL As Long = 10 SubBlueSkip() Dim j As Long For j = 1 To LASTCOL Step 2 Cells(CHANGEROW, j).Interior.Color = vbBlue Next j End Sub