Читать книгу Excel 2019 Power Programming with VBA - Michael Alexander, Dick Kusleika - Страница 186

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 iCount As Integer Dim Win As Window iCount = 0 For Each Win In Windows If Not Win.Visible Then iCount = iCount + 1 Next Win MsgBox iCount & " hidden windows." End Sub

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

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 of the 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's selected, and then the loop ends when the Exit For statement is executed.