Microsoft Office Tutorials and References
In Depth Information
Manipulating Objects and Collections
Selection.Font.Italic = True
Selection.Font.Size = 12
Selection.Font.Underline = xlUnderlineStyleSingle
Selection.Font.ThemeColor = xlThemeColorAccent1
You can rewrite this procedure using the With-End With construct. The following procedure
performs exactly like the preceding one:
.Name = “Cambria”
.Bold = True
.Italic = True
.Size = 12
.Underline = xlUnderlineStyleSingle
.ThemeColor = xlThemeColorAccent1
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.
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
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
occasions are perfect 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.