Excel VBA With … End With Statement

Basic Description

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:

With object
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. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>



clearPost Comment