The With End With Structure

VBA includes a programming structure designed to reduce the number of object qualifiers required in your code. Although the With/End With structure discussed in this section is not required under any circumstances, its use is often recommended because it makes your programs more readable. Also you will often see the With/End With structure in recorded macros. Consider the following code:

Range("A1:D1").Select With Selection.Font .Bold = True .Name = "Arial" .Size = 18 End With

With Selection

.HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter End With

When executed, this code selects the range A1:D1 of the active worksheet using the Select() method of the Range object. The Select() method applies to several objects including the Worksheet and Chart objects. You will notice that using the Select() method with the Range object will cause the selected range to be highlighted in the worksheet, just as if the user used the mouse to make the selection.

Immediately after invoking the Select() method, the With/End With structure appears. The With statement requires an object qualifier to immediately follow. In this case, the Selection property of the Window object is used to return a Range object from which the Font property returns a Font object associated with the selected range. The statement could have just as easily been written without the Select() method and Selection property and entered using the Range property to return the desired Range object (for example, With Range("A1:D1").Font).

Once inside the structure, any property of the object can be set without having to qualify the object in each line of code. Subordinate objects and their properties can also be accessed. Each line within the structure must begin with the dot operator followed by the property or object name, then the method or assignment.

After all desired properties and/or methods have been invoked for the given object, the structure closes with End With.

You will note that a second With/End With structure is used to set the horizontal and vertical alignment of the selected range. This is because I recorded this code and cleaned it up by deleting lines of code created by the macro recorder for default assignments. The example can be compressed further as shown below:

With Range("A1:D1")

.HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Font.Bold = True .Font.Name = "Arial" .Font.Size = 18 End With

The With/End With structure is straightforward and particularly useful when a large number of properties or methods of one object are to be addressed sequentially in a program.

Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook

Post a comment