Recall from the preceding chapter that a collection is a group of related objects. For example, the Workbooks collection is a collection of all open Workbook objects. There are many other collections that you can work with. You don't have to know how many elements are in a collection to use the For Each-Next construct.
Suppose that you want to perform some action on all objects in a collection. Or suppose that you want to evaluate all objects in a collection and take action under certain conditions. These are perfect occasions for the For Each-Next construct. The syntax of the For Each-Next construct is
For Each element In group [instructions] [Exit For] [instructions] Next [element]
The following procedure uses the For Each-Next construct to refer to each of the six members of a fixed-length array one at a time:
For Each n In MyArray
The next procedure uses the For Each-Next construct with the Sheets collection in the active workbook. When you execute the procedure, the MsgBox function displays each worksheet's Name property. (If there are five worksheets in the active workbook, the MsgBox function is called five times.)
Dim Item as WorkSheet
For Each Item In ActiveWorkbook.WorkSheets
'f/T In the preceding example, Item is an object variable (more specifically, a s|v Worksheet object).There's nothing special about the name Item; you can \y use any valid variable name in its place.
The next example uses For Each-Next to cycle through all objects in the Windows collection:
Dim AllVisible As Boolean Dim Item As Window AllVisible = True For Each Item In Windows
If Item.Visible = False Then AllVisible = False Exit For End If Next Item MsgBox AllVisible
If a window is hidden, the value of AllVisible is changed to False, and the For Each-Next loop is exited. The message box displays True if all windows are visible and False if at least one window is hidden. The Exit For statement is optional. It provides a way to exit the For Each-Next loop early. This is generally used in conjunction with an If-Then statement (which I describe later in this chapter).
Here's an example that closes all workbooks except the active workbook. This procedure uses the If-Then construct to evaluate each workbook in the Workbooks collection.
Dim Book as Workbook For Each Book In Workbooks
If Book.Name <> ActiveWorkbook.Name Then Book.Close Next Book
My final example of For Each-Next is designed to be executed after the user selects a range of cells. Here, the Selection object acts as a collection that consists of Range objects because each cell in the selection is a Range object. The procedure evaluates each cell and uses the VBA UCase function to convert its contents to uppercase. (Numeric cells are not affected.)
Sub MakeUpperCase() Dim Cell as Range For Each Cell In Selection
Cell.Value = UCase(Cell.Value) Next Cell End Sub
Was this article helpful?