Vba with keyword in Excel
There are some occassions while programming, where one would want to refer an object repeatedly for some purpose.
For example, if there is a need to change the font,color, backcolor and so on for a range object, then the same object has to be repeated for every property setting.
But with "With..End With" statement, the object is referred only once at the beginning and the rest of the code is automatically made to refer that object only.
This feature comes handy while coding with multiple objects, wihout which, the coding will be tedious.
Also note that, When using a structure, you can only read the values of members or invoke methods, and you get an error if you try to assign values to members of a structure used in a "With End with" block.
Syntax
- With
- End With
The following example demonstrates the use of with statement.
- Sub withendeith()
- Dim rng As Range
- Set rng = Sheets("Q238").Range("K12:K22")
- With rng
- .Interior.ColorIndex = 37
- End With
- MsgBox "K12:K22 background changed with \'With\' Statement"
- rng.Select
- With Selection.Interior
- .Pattern = xlSolid
- .PatternColorIndex = xlAutomatic
- .ThemeColor = xlThemeColorDark1
- .TintAndShade = 0
- .PatternTintAndShade = 0
- End With
- End Sub
The above example changes the background color, theme color, patterncolorindex,tint and shade and so on of the range K12:K22.
Note the preceeding .(dot) within the "With" block. Without this, the rng object has to be called at each statement.
The Screenshot of the editor is as shown below