Working On an Object

As in any other language, in VBA too there may be situations where we continuously work on a specific object. 

For example, we may like to apply conditional formatting to a cell based on its information by changing the font face, font color, background color, underline, bold or italics, etc. When this action needs to be coded, we will have to use a block of code in which each line starts with:

Sheets(“<sheet name>”).cells( <row num> , <col num> ).

The “With–End With” Block

The repetitive part of the code as seen in the above example can be used once and made common to all the lines in the block using the With–End With pair of statements.

Syntax

With <object expression>

[<block of code where each line starts with a period .>]

End With

Where

1. Block of code/statements between “With” and “End With” statements are completely optional.
2. <object expression> can be elaborated as 

<object name > [. <property name>][. <property name>][. <property name>]……

Example

To elaborate on our example above, the code to format a cell would be:

Sub format_cell()
    Cells(20, 1).Interior.Pattern = xlSolid
    Cells(20, 1).Interior.PatternColorIndex = xlAutomatic
    Cells(20, 1).Interior.Color = 65535
    Cells(20, 1).Interior.TintAndShade = 0
    Cells(20, 1).Interior.PatternTintAndShade = 0
    Cells(20, 1).Font.ThemeColor = xlThemeColorAccent6
    Cells(20, 1).Font.TintAndShade = 0
    Cells(20, 1).Font.Bold = True
    Cells(20, 1).Font.Italic = True
    Cells(20, 1).Font.Underline = xlUnderlineStyleSingle
End Sub

But this code could also be written as:

Sub format_cell_with()
With Cells(20, 1).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Cells(20, 1).Font
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0
.Bold = True
.Italic = True
.Underline = xlUnderlineStyleSingle
End With
End Sub

Using Nested “With–End” With Statements

One or more “With” statements can be nested within other “With” statements depending on your requirements. For example, the object expression of the outer “with” statement should be a part of the whole object expression of the inner “with” block object expression.

Here’s an example to help you understand:

Userform – Textbox Formatting

In this example, we set the properties of a specific text box using the VBA code module. These lines are typed in the Userform’s “initialize” method so that the property values are set when the form loads.

Private Sub UserForm_Initialize()
    TextBox1.BorderStyle = fmBorderStyleSingle
    TextBox1.BackColor = yellow
    TextBox1.AutoSize = True
    TextBox1.BackStyle = fmBackStyleTransparent
    TextBox1.CanPaste = True
    TextBox1.Enabled = True
    TextBox1.Font.Bold = True
    TextBox1.Font.Italic = True
    TextBox1.Font.Size = 5
End Sub

This can also be done easily using the “With and End With” statement:

Private Sub UserForm_Initialize()
 With TextBox1
    .BorderStyle = fmBorderStyleSingle
    .BackColor = yellow
    .AutoSize = True
    .BackStyle = fmBackStyleTransparent
    .CanPaste = True
    .Enabled = True
 End With
 With TextBox1.Font
    .Bold = True
    .Italic = True
    .Size = 5
    End With
End Sub

Now, we can also write the same code using a nested “With” block.

In this case, the object expression for the first with block is Textbox1 and the object expression for the second with block is Textbox1.Font. The first one’s object expression is a part of the second one. So, we can rewrite the code as:

Private Sub UserForm_Initialize()

 With TextBox1
    .BorderStyle = fmBorderStyleSingle
    .BackColor = yellow
    .AutoSize = True
    .BackStyle = fmBackStyleTransparent
    .CanPaste = True
    .Enabled = True
    With .Font
        .Bold = True
        .Italic = True
        .Size = 5
    End With
 End With
 
End Sub

Real Time Scenario – Use “With” Statement With “If” Statement

In this example, we will highlight a cell in orange color and change the font properties if it has the word “India” in it.

Sub format_cell_with()
For i = 1 To 9
    For j = 1 To 4
    cellcontent = Cells(i, j).Value
    If InStr(cellcontent, "India") > 0 Then
    With Cells(i, j).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
        
        With Cells(20, 1).Font
            .Bold = True
            .Italic = True
            .Underline = xlUnderlineStyleSingle
        End With
    End With
    End If
    Next
Next
End Sub

Conclusion

This “With–End With” statement helps in organizing and reducing the number of characters in your code, thereby making it easy to maintain. At the same time, it might be difficult to debug the issues with such code when there are too many statements within the block. This is because we might scroll through the page to read the code and find it difficult to understand the object expressions connected with the statements in that block. This can lead to runtime errors that waste our time. 

In VBA, during the runtime, if we want to check the state or properties of an object, we just highlight its reference from the code and select “add to watch window” from the context menu to get them added to the watch window. With reference to other languages like UFT that provide us with object spy and highlight object features, this “With” statement can be a hindrance as we need to highlight the whole line of code while debugging. 

In short, when the “With” statement is used, highlighting the full reference to the object might become cumbersome during runtime, though it is possible.