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, and there are many other collections that you can work with.

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 because you don't have to know how many elements are in a collection to use the For Each-Next construct.

The syntax of the For Each-Next construct is

For Each element In collection [instructions] [Exit For] [instructions] Next [element]

The following procedure uses the For Each-Next construct with the Worksheets 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

Note In the preceding example, Item is an object variable (more specifically, a Worksheet object). There's nothing special about the name Item; you can use any valid variable name in its place.

The next example uses For Each-Next to cycle through all objects in the Windows collection and count the number of windows that are hidden.

Sub HiddenWindows()

Dim Cnt As Integer Dim Win As Window Cnt = 0

For Each Win In Windows

If Not Win.Visible Then Cnt = Cnt + l Next Win

MsgBox Cnt & " hidden windows." End Sub

For each window, if the window is hidden, the Cnt variable is incremented. When the loop ends, the message box displays the value of Cnt.

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 End Sub

A common use for the For Each-Next construct is to loop through all cells in a range. The next 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

VBA provides a way to exit a For-Next loop before all the elements in the collection are evaluated. Do this with an Exit For statement. The example that follows selects the first negative value in Row 1 of the active sheet.

Sub SelectNegative() Dim Cell As Range For Each Cell In Range("1:1") If Cell.Value < 0 Then Cell.Select Exit For End If Next Cell End Sub

This example uses an If-Then construct to check the value of each cell. If a cell is negative, it is selected, and then the loop ends when the Exit For statement is executed.


0 0

Post a comment