Microsoft Office Tutorials and References
In Depth Information
Manipulating Objects and Collections
The syntax of the For Each-Next construct is
For Each element In collection
[Exit For]
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 five worksheets are 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
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 + 1
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.
Search JabSto ::

Custom Search