Referencing objects using With End With

Sometimes, it may be necessary to refer to the same object properties or methods several times consecutively within a VBA macro. You can use a With...End With program structure that simplifies object reference.

The basic syntax of With...End With is: With Object

'Statements that use properties & methods of that object' End With

To see how this works, suppose that we wanted to set the three properties with the interior of the object Range ("D34") as follows:

Range ("D34").ColorIndex = 3 'Sets the colour to red

Range ("D34").Pattern = xlSolid 'Makes the interior colour red solid

Range ("D34").PatternColorIndex = xlAutomatic

'This resets to automatic the colour following the selection

Instead of the longhand individual reference to each property or method of the range, we could rewrite the above using With.End With as follows:

Range ("D34").Select With Selection.Interior

.ColorIndex = 3 'this colour is red

.Pattern = xlSolid .PatternColorIndex = xlAutomatic End With

Note how the statements are indented between the With Selection.Interior and End With block.

0 0

Post a comment