Manipulating Objects and Collections

As an Excel programmer, you'll spend a lot of time working with objects and collections. Therefore, you want to know the most efficient ways to write your code to manipulate these objects and collections. VBA offers two important constructs that can simplify working with objects and collections:

• With-End With constructs

• For Each- Next constructs

With-End With constructs

The With-End With instruction construct enables you to perform multiple operations on a single object. To start understanding how the With-End With construct works, examine the following procedure, which modifies five properties of a selection's formatting (the selection is assumed to be a Range object):

 Sub ChangeFont1()     Selection.Font.Name = "Cambria"     Selection.Font.Bold = True     Selection.Font.Italic = True     Selection.Font.Size = 12     Selection.Font.Underline = xlUnderlineStyleSingle     Selection.Font.ThemeColor = xlThemeColorAccent1 End Sub 

This procedure can be rewritten using the With-End With construct. The following procedure performs exactly like the preceding one:

 Sub ChangeFont2()     With Selection.Font         .Name = "Cambria"         .Bold = True         .Italic = True         .Size = 12         .Underline = xlUnderlineStyleSingle         .ThemeColor = xlThemeColorAccent1     End With End Sub 

Some people think that the second incarnation of the procedure is actually more difficult to read. Remember, though, that the objective is increased speed. Although the first version may be more straightforward and easier to understand, a procedure that uses the With-End With construct to change several properties of an object can be faster than the equivalent procedure that explicitly references the object in each statement.

Note  

When you record a VBA macro, Excel uses the With-End With construct every chance it gets. To see a good example of this construct, try recording your actions while you change the page orientation using the Page Layout Page Setup Orientation command.

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

 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.