1.8 Resize
Home and Learn - Free Excel VBA Course
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:
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:
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 >