Who’s With me?

I frequently see people get confused by, and misuse, the With ... End With construction. The most common misconception seems to be that anything put inside one of these clauses automatically refers back to the object used on the With line – so in other words, the belief is that this works:

With Sheets("Sheet1")
Range("A1").Value = "Hello"
Range("A2").Value = "Magical With block"
End With

and, indeed, while debugging it may seem to work – because usually Sheet1 is active so that you can see what is happening. But in fact the With block is doing absolutely nothing here – the code would do exactly the same thing if it weren’t there at all. (Feel free to remove it and see)

The simplest way I can think of to explain the way this clause should be constructed is like this:

Imagine your code written out in full, repeating the object you want to use in the With block wherever it is necessary. So, for the example above, we would start with this code:

Sheets("Sheet1").Range("A1").Value = "Hello"
Sheets("Sheet1").Range("A2").Value = "Magical With block"

Nothing too tricky there, I hope you’ll agree.

Now to convert this to a with block, we add back the With and End With lines we had before:

With Sheets("Sheet1")
Sheets("Sheet1").Range("A1").Value = "Hello"
Sheets("Sheet1").Range("A2").Value = "Magical With block"
End With

and then remove any occurrence of the object we have on the With line – but do not remove anything else:

With Sheets("Sheet1")
.Range("A1").Value = "Hello"
.Range("A2").Value = "Magical With block"
End With

Note the two differences between this version and the first version that didn’t work? Correct – the dots before the two Range calls. Those dots are what tell the compiler that the properties or methods relate back to the object referred to on the With line. Without them, the With block may as well not exist.

Now, where it can seem a little confusing is if you start to nest With blocks:

With Sheets("Sheet1")
    .Name = "New sheet"
    With .Range("A1")
        .Value = "Hello"
        .Font.Bold = True
    End With
.Range("A2").Value = "Magical With block"
End With

In truth though, all you need to remember is that any property or method that you prefix with a dot refers back to the nearest enclosing With block. So the second line refers back to Sheets(“Sheet1”), as does the third line. The fact that the third line is also a With block only affects the lines that follow, until the next End With line. After that, the next line refers back to Sheets(“Sheet1”) again.

So that’s the basics of writing these clauses, but there is one very important thing to remember about the way they work at run time:

The With line sets and holds a reference to the object in question, so any changes made to that object will be reflected in any code inside the with block. What that means is that this:

With Range("A1")
    .EntireRow.Insert
    MsgBox .Address
End With

is not the same as this:

    Range("A1").EntireRow.Insert
    MsgBox Range("A1").Address

The first version gets a reference to A1, then inserts a row, so the address of the object is now A2. In the second, the address is still A1. This is quite a trivial example purely to demonstrate the point.

The one that used to catch me out regularly was changing the location of a chart inside a With block because the Location method of a Chart actually returns a reference to a new Chart object making the old one (the one held by the With block) invalid.