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.

2 thoughts on “Who’s With me?

  1. Alan Elston says:

    Me. .. With for a short section, you show it is very tidy.
    I think better only to use this over a short section and to minimise the code lines not refering back through a . dot to the last With object.

    I often see a muddle up of this sort of form
    Sub RUWivMe()
    With Sheet1.Range(“A1”)
    .Item(1).Value = “cell 1”
    .Item(2, “B”).Value = “cell 16386”
    .Item(3).Value = “cell 32769”
    .Cells.ClearContents ‘Just clears first cell
    Cells.ClearContents ‘Might not go to the right Worksheet
    End With
    With Sheet1.Cells
    .Item(1).Value = “cell 1”
    .Item(2, “B”).Value = “cell 16386”
    .Item(32769).Value = “cell 32769”
    End With
    End Sub

    In such a code , unless you really need to, you would best do away with the Clear lines where they are and stick them outside like
    End With
    Sheet1.Cells.ClearContents
    With Sheet1.Cells

    Or this would be OK, but might encourage the first mess up on another occasion:
    End With
    With Sheet1.Cells
    .ClearContents

    _….

    With the Creating of a local scope object for a short section can be very tidy also, like
    With CreateObject(“msxml2.xmlhttp”)
    .Open blah
    .send
    Etc.
    End With

    I personally would not go With a larger code section, and only go a nesting With when Obfuskating.. with .. With

    • Alan Elston says:

      I just thought of a good demo With improving the code got by macro recording.
      Run a macro recording whilst doing a bit of simple cell text formatting , ( Font, Color, Bold, Underline, Italic , etc..) , and you end up with something like this:
      Sub Makro1()
      ' Makro1 Makro
          Range("B2").Select
          With Selection.Font
              .Name = "Batang"
              .Size = 12
              .Strikethrough = False
              .Superscript = False
              .Subscript = False
              .OutlineFont = False
              .Shadow = False
              .Underline = xlUnderlineStyleNone
              .ThemeColor = xlThemeColorLight1
              .TintAndShade = 0
              .ThemeFont = xlThemeFontNone
          End With
          Selection.Font.Bold = True
          Selection.Font.Italic = True
          With Selection.Font
              .Color = -16776961
              .TintAndShade = 0
          End With
      End Sub

      Withing all that becomes with With like this very tidy:
      Sub GetWithIt()
          With Worksheets("Sheet1").Range("B2").Font ' Font of cell B2 becomes the referenced through . dot object in the With End With bit
           .Name = "Batang"
           .Size = 12
           .Bold = True
           .Italic = True
           .Color = -16776961
          End With
      End Sub

      Once again I think With a short codes section is good to do it … with … With … is

Leave a Reply

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