With ... End With in Excel VBA

 

In a previous lesson, 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:

With object

.property

End With

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:

With ActiveCell.Font

.Bold = True
.Color = vbBlue
.Name = "Arial"
.Size = 22
.Italic = True

End With

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. These ones:

A list of properties of the Font object in Excel VBA

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.

 

OK, that's enough of Conditional Logic. We'll move and take a look at another variable type that's quite common - As String. We'll do that in the next section below.