Close Topic Options

Introduction
While it is quite common to declare many variables and use them liberally throughout code, this is not always the best practice. It is always much more efficient to eliminate as many variables as possible and to refer directly to an object instead of creating some intermediate variable that was created simply as a means of referring to an object, this is where the With statement can be of great assistance.


The With Statement
When used correctly, the With can be used in place of a variable and used to drill straight down to the underlying property that's being called on. As an added bonus, With statements also make your procedures run faster and help you avoid repetitive typing.

Before proceeding further, note that the With statement always has to have a beginning and an end, so the statement begins with With and must be paired with an End With to end the statement.

The underlying principle is that the initial With statement establishes an association with the object, property, or properties that are listed immediately after the With (i.e. any on the same line as the With) and this association is then used to "connect the dots" and drill down to further properties listed on other lines.

The properties to be associated with the initial statement must be placed on subsequent lines with a "dot" preceding them (note that other code lines that are not associated with the With statement can also be written between the With and End With - if they are not preceded with dots they are just not part of the association) - and when the End With is reached, the association that has been established then ends.

This is best illustrated with some basic examples...


Using With_End With Statements
The first step is to decide just where we can insert With statements in our code, but this step is really very simple - it's all done by inspection - here's an example, look carefully at the first draft of the code for the procedure FormatRange1 below.

First draft of code:

By inspection, we can see a lot of repetition, the statement Worksheets("Sheet1").Range("A1:C10") occurs on each and every line of this procedure i.e. on the first line we start with the Worksheets property, then the dot drills down to the Range property... on each subsequent line we do all this over and over again (six times, for a total of twelve property calls) before we even get to the dots for the subsequent properties. This repetition is all totally unnecessary, it just makes your code long, repetitious, hard to read, and slo-o-w-w-w...

This is a case where With is best used to drill down once through the Worksheets and Range properties and we then use the subsequent properties to perform all the actions that are related to the Worksheets and Range properties - as in this next example...

Second draft of code:

{Note that the modification to reach this stage is really quite easy and takes very little time, starting with the original code, just type With as the first word on the first line, then insert the cursor before the dot preceding Value and hit Enter (or Return). Now delete all further instances of Worksheets("Sheet1").Range("A1:C10") and write a new line with End With just before End Sub and you're done}.

Now, when we inspect this revised code (the second draft above) we should see that this can be improved even further, as it now becomes quite obvious that we have another property (Font) that is drilled through three times. So for the next draft we can eliminate some more property calls by using nested Withs inside the first With statement to drill down through the Font property once only, as in this following example.

Final draft of code:

We now have code that's been stripped down to the absolute minimum, it is the bare essentials with no repetition, no delays, and is quite easy to read through and alter i.e. There is no need to search or scroll right back through the preceding code to see what some variable is actually referring to, you only need to go back to the line containing the With statement, and if you later want to change (say) the sheet or the range being referred to, you only need to change one instance of it (these advantages become more obvious as you become more familiar with these type of statements).


Discussion
When presented with the code like that given for FormatRange1 and asked to improve on it, lots of coders will create a variable to refer to Worksheets("Sheet1").Range("A1:C10") and the first piece of code would then be written in the following fashion...

Version of code using variable only:

However, we can see that this really does little to improve matters, simply because the variable MyRange is being used six times. Even with this "improvement" we still need to use With to eliminate unnecessary repetition and property calls, as in this next example.

Version of code using variable and With:

But we should see that while FormatRange5 may be a slight improvement on the piece of code immediately preceding it (FormatRange4), three lines of code had to be added and this is now actually starting to look like a spaghetti code version of FormatRange2. i.e. We've created a variable that's really only going to be used once, and once only.

The point here is that it is quite wasteful of resources and more than a little ridiculous to go to all the trouble of creating, 'setting', and 'destroying' a variable that's only going to be used once - you may as well just refer directly to the object or property.

If we carry through with this 'adding unnecessary variables' concept through to the max, we then start to see how ridiculous the idea of creating unneeded variables becomes, e.g. if we were to now introduce a further variable for the Font property, we need to add another three lines of code to get...

Bloated version of code:

When we compare this with the previous simpler version (copied directly below), it becomes quite obvious that the code above is looking more and more like spaghetti code and is making a very simple task appear to be quite elaborate and difficult...

Simplified version of code:

A Caution
Of course - as with anything - a little knowledge can sometimes be a dangerous thing and can be easily misused, as shown in the example below, where the With statement is misused - the code shown below's not necessarily "wrong" as such, it's simply an inappropriate usage of the statement...

Misuse of With statement:

Which is absolute rubbish, as this can be written as the simple one-liner...

Corrected code:

Using Variables with With
Of course there are limitations and it is not always possible to use With for absolutely everything, so it may sometimes be necessary to create a variable that's going to be used within a With statement. However, it is your call as to what object you're going to use the With statement with, and what object you're going to create a variable for.

The general rule is to use With in conjunction with the object that occurs most frequently in the code so as to cut down on the number of property calls, and to use the variable for the object that occurs less frequently, as shown in the rather artificial example that follows, where properties of Sheet1 are called on six times and properties of Sheet2 are called on nine times.

Correct use of a variable inside a With statement:

Note that using With in all these examples above only eliminated one variable, and its use may thus seem to be rather limited. However, we need to remember that when each With statement ends we are quite free to begin another With statement, and in a large body of code these With statements can account for quite a large number of variables.


Summary
In summary, it's always difficult to define cut and dried coding rules to suit every conceivable situation, but with experience it can become immediately obvious how things can be improved and you should always look at your first error-free working code as only being the first draft and then spend a few seconds looking for ways to simplify it and eliminate all unnecessary code. In particular, the first things to look for are:

Unnecessary variables: If a variable is to be used several times, and if it's also to be used to drill down to other properties, you should certainly consider eliminating it and replacing it with a With statement(s) that refers directly to the object or property. On the other hand, if a variable is unused or is only to be used in the code once, it's usually best to get rid of the variable altogether.

Unnecessary With statements: In a similar vein, if a With statement is only going to be used once to drill down to a subsequent property or properties, it's best not to use a With statement.

For other ways to optimize your code, have a read through this article.