Looping on a Collection

There are several different ways you can loop on the elements of a collection. However, the recommended method for looping on a collection is to use the For Each...Next loop. In this structure, Visual Basic automatically sets an object variable to return every object in the collection. The following illustration shows the essential components of the For Each...Next loop.

The following procedure shows how you could use a For Each...Next loop to close every workbook except the workbook that contains the running procedure.

Sub CloseWorkbooks()
    Dim wb As Workbook
    For Each wb In Application.Workbooks
        If wb.Name <> ThisWorkbook.Name Then
            wb.Close
        End If
    Next wb
End Sub

The For Each...Next loop is also the recommended method for looping on a range of cells; for more information, see "Looping on a Range of Cells" later in this chapter. For more information about looping in Visual Basic, see Chapter 3, "Controlling Program Flow."