The “With” Statement in VBA
Work on the object
As in any other language, situations can arise in VBA where you are constantly working on a particular object.
For example, we can apply conditional formatting to a cell based on its information by changing the font, font color, background color, underline, bold or italic, etc. If this action needs to be coded, we need to use a code block where each line starts with :
Sheets().cells(,
).
with block.
The repeating part of the code, as in the example above, can be used once and made common to all lines in the block with a few with-end with statements.
Syntax
С
[]
At the end of the day.
Where
- The code/header block between the C and Complete with statements is completely optional.
- can be interpreted as
[. ][. ][. ]……
Example
If you take a closer look at our example, above you will find the code to format the cell :
Cell_format()
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.PatternPatternTintAndShade = 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 = xlUnderlineSingle
End Sub.
However, this code can also be written as follows:
Subformat_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 = xlUnderlineSingle
End With
End Sub
alt=example data-original-width=1024 data-original-height=576 data-ez= data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/04/The-With-Statement-in-VBA.png />
alt=Pin it! data-ezsrc=/utilcave_com/social/pin_it.png />
alt=Facebook-ezsrc=/utilcave_com/social/fb_share.png />
Use of nested statements with
Depending on your needs, one or more C-statements can be nested within other C-statements. For example. For example, the expression of an external object with an expression may be part of the entire expression of an internal object with a block object expression.
Here is an example to help you understand:
User formatting of text fields
In this example, we define the properties of a specific text field using a block of VBA code. These strings are entered into the user form’s initialization method so that property values are set when the form is loaded.
alt=VBA code block for setting text field properties. data-orig-width=1024 data-orig-height=576 data-ez= data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/04/1619036392_828_The-With-Statement-in-VBA.png />
alt=Pin it! data-ezsrc=/utilcave_com/social/pin_it.png />
alt=Facebook-ezsrc=/utilcave_com/social/fb_share.png />
alt=user-entered lines form-original-width=1024 data-original-height=576 data-ez= data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/04/1619036392_299_The-With-Statement-in-VBA.png />
alt=Pin it! data-ezsrc=/utilcave_com/social/pin_it.png />
alt=Facebook-ezsrc=/utilcave_com/social/fb_share.png />
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 with the C instruction and at the end :
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
alt=Set single text field properties with data-orig-width=1024 data-orig-height=576 data-ez= data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/04/1619036392_363_The-With-Statement-in-VBA.png />
alt=Pin it! data-ezsrc=/utilcave_com/social/pin_it.png />
alt=Facebook-ezsrc=/utilcave_com/social/fb_share.png />
Now we can write the same code with a nested C block.
In this case, the object expression for the first block with is Textbox1 and the object expression for the second block with is Textbox1.Font. The expression of the purpose of the former is part of the latter. So we can rewrite the code as follows:
Private subgroup UserForm_Initialize()
C TextBox1.BorderStyle = fmBorderStyleSingle.BackColor = yellow.AutoSize = True.BackStyle = fmBackStyleTransparent.CanPaste = True.Enabled = TgC .Font.Bold = True.Italic = True.Size = 5C
Sub-sub final
alt= Record code with nested data-original-width=1024 data-original-height=576 data-ez= data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/04/1619036393_479_The-With-Statement-in-VBA.png />
alt=Pin it! data-ezsrc=/utilcave_com/social/pin_it.png />
alt=Facebook-ezsrc=/utilcave_com/social/fb_share.png />
Scenarioreal time – use with operator when operator
In this example, we will select an orange cell and change the font properties if it contains the word India.
Sub format_cell_with()For i = 1 To 9For j = 1 To 4cellcontent = Cells(i, j).ValueIf InStr(cellcontent, India) > 0 ThenWith Cells(i, j).Internal.Pattern = xlSolid.PatternColorIndex = xlAutomatic.ThemeColor = xlThemeColorAccent2.TintAndShade = 0.399975585192419.PatternTintAndShade = 0
With cells(20, 1).Font
.Bold = True
.Italic = True
.Underline = xlUnderlineSingle
End With
End With
End If
Next
Next
End Sub
alt=excel document with data-original-width=1024 data-original-height=576 data-ez= data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/04/1619036393_178_The-With-Statement-in-VBA.png />
alt=Pin it! data-ezsrc=/utilcave_com/social/pin_it.png />
alt=Facebook-ezsrc=/utilcave_com/social/fb_share.png />
Supplement
This with-end operator allows you to organize and reduce the number of characters in your code, making it easier to maintain. At the same time, debugging problems with this type of code can be difficult if there are too many declarations in a block. This is partly because we can scroll down to read the code, and partly because we have trouble understanding the object expressions that go with the expressions in this block. This can lead to execution errors that waste our time.
In VBA, when we want to check the status or properties of an object at runtime, we just need to select its reference in the code and choose Add to Viewport from the context menu to add it to the viewport. Compared to other languages, such as. B. UFT, which provide us with snooping and object selection functions, which can be a hindrance with an operator because we have to select an entire line of code when debugging.
In short, when using the with statement, assigning a full object reference at runtime can get tedious, although it is possible.
Marked with: Conditional, conditional formatting, end, last statement, Excel, Microsoft Excel, operators, VBA, with statement, with statement at end
Related Tags:
excel vba with statement worksheetsvba with statement multiple objectswith sheets vbaif condition in vba ends with statementvba with end withvba with range end with,People also search for,Feedback,Privacy settings,How Search works,excel vba with statement worksheets,vba with statement multiple objects,with sheets vba,if condition in vba ends with statement,vba with end with,vba with range end with,excel vba with statement range,vba if statement
