Excel VBA With … End With Statement
Suppose, you had code like this:
ActiveCell(1, 2).Value = “F”
ActiveCell(1, 2).HorizontalAlignment = xlCenter
ActiveCell(1, 3).Value = “Terrible – needs attention”
We were accessing various properties of the ActiveCell. In the code above, we have ActiveCell three times. However, you can speed up your subroutines by using the repeated object only once, on the first line. You then type the property you need after a dot.
The syntax is this:
As an example, suppose we want to change various aspects of the ActiveCell. We want to change the font name, the font size, the boldness, the italics, etc. We could do it like this:
ActiveCell.Font.Bold = True
ActiveCell.Font.Color = vbBlue
ActiveCell.Font.Name = “Arial”
ActiveCell.Font.Size = 22
ActiveCell.Font.Italic = True
But notice the repetition here. We’ve used ActiveCell.Font five times. By using a With Statement, we can just type the ActiveCell.Font once. Like this:
.Bold = True
.Color = vbBlue
.Name = “Arial”
.Size = 22
.Italic = True
So you start with the word With. After a space, you type what it is you’re trying to manipulate. We want to manipulate the Font property of ActiveCell. The Font property has lots of properties of its own. Type a dot and then the name of the Font property in the above list that you want to change. The equal sign and the value are used in the normal way. The whole thing end with the words End With.
With Statements are quite intuitive, so we don’t really need to say too much about them. But just remember: if you’re typing the same object over and over, you might do better to use a With … End With statement.