3.9 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:
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. These
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.
Next Lesson: 4.1 Strings in Excel VBA >
<< Excel VBA Course Menu
Lots more free online courses here on our main Home and Learn site
© All course material copyright Ken Carney