How to Use the With Statement in VBA
Contents
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
- Block of code/statements between “With” and “End With” statements are completely optional.
<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") &amp;gt; 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.