For Each Next constructs

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:

Sub Macro1()

For Each n In MyArray

Debug.Print n Next n End Sub

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

Sub CountSheets()

Dim Item as WorkSheet

For Each Item In ActiveWorkbook.WorkSheets

MsgBox Item.Name Next Item End Sub

'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:

Sub HiddenWindows()

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.

Sub CloseInActive()

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

End Sub

End Sub

Sub MakeUpperCase() Dim Cell as Range For Each Cell In Selection

Cell.Value = UCase(Cell.Value) Next Cell End Sub

Was this article helpful?

0 -1

Post a comment