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 ChangeFontl()

Selection.Font.Name = "Cambria" Selection.Font.Bold = True Selection.Font.Italic = True Selection.Font.Size = l2

Selection.Font.Underline = xlUnderlineStyleSingle Selection.Font.ThemeColor = xlThemeColorAccentl 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 = l2

.Underline = xlUnderlineStyleSingle .ThemeColor = xlThemeColorAccentl 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.

0 0

Post a comment