MS-Excel / General Formatting

---

Working with Multiple Properties or Methods

Because most objects have many different properties and methods, you'll often need to perform multiple actions on a single object. This is accomplished easily with multiple statements that set the appropriate properties or run the necessary methods. However, this can be a pain if you have a long object name.

For example, take a look at the FormatParagraph procedure shown in Listing below. This procedure uses six statements to format a paragraph. Note that the Paragraph object name- ThisDocument.Paragraphs(1)-is quite long and is repeated in all six statements.

A Procedure That Formats a Range

Sub FormatParagraph()
    ThisDocument.Paragraphs(1).Style = "Heading 1"
    ThisDocument.Paragraphs(1).Alignment = wdAlignParagraphCenter
    ThisDocument.Paragraphs(1).Range.Font.Size = 16
    ThisDocument.Paragraphs(1).Range.Font.Bold = True
    ThisDocument.Paragraphs(1).Range.Font.Color = RGB(255, 0, 0) ' Red
    ThisDocument.Paragraphs(1).Range.Font.Name = "Times New Roman"
End Sub

Note: When you want to specify colors in VBA, use the RGB function:

RGB(red, green, blue)

red
An integer value between 0 and 255 that represents the red component of the color.

green
An integer value between 0 and 255 that represents the green component of the color.

blue
An integer value between 0 and 255 that represents the blue component of the color.

To shorten this procedure, VBA provides the With statement. Here's the syntax:

With object
    [statements]
End With

object
The name of the object.

statements
The statements you want to execute on object.

The idea is that you strip out the common object and place it on the With line. Then all the statements between With and End With need only reference a specific method or property of that object. In the FormatParagraph procedure, the common object in all six statements is ThisDocument.Paragraphs(1). Listing below shows the FormatParagraph2 procedure, which uses the With statement to strip out this common object and make the previous macro more efficient.

A More Efficient Version of FormatParagraph()

Sub FormatParagraph2()
    With ThisDocument.Paragraphs(1)
        .Style = "Heading 1"
        .Alignment = wdAlignParagraphCenter
        .Range.Font.Size = 16
        .Range.Font.Bold = True
        .Range.Font.Color = RGB(255, 0, 0) ' Red
        .Range.Font.Name = "Times New Roman"
    End With
End Sub

Note: You can make the FormatParagraph2 procedure even more efficient when you realize that the Font object also is repeated several times. In this case, you can nest another With statement inside the original one.The new With statement would look like this:

With .Range.Font
     .Size = 16
     .Bold = True
     .Color = RGB(255, 0, 0)
     .Name = "Times New Roman"
End With