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 = "Times New Roman" Selection.Font.FontStyle = "Bold Italic" Selection.Font.Size = l2

Selection.Font.Underline = xlUnderlineStyleSingle Selection.Font.Colorlndex = 5 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 = "Times New Roman" .FontStyle = "Bold Italic"

.Underline = xlUnderlineStyleSingle .Colorlndex = 5 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 when changing several properties of an object can be significantly 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 setup by choosing the File ^ Page Setup command.

Was this article helpful?

0 0

Post a comment