[ Understanding the exact action of the With keyword in VBA - unexpected behavior ]

To refactor and optimize some vba code, I started replacing series of statements that were repeatedly refering to a single object using with.

At first I thought that

Object.operation1()
Object.operation2()

was equivalent to

With Object
    .operation1()
    .operation2()
End With

and that the difference was that the second solution was just faster because we were referring to the object only once.

Then I ran on the following case:

Il have a sheet that is filled line by line, using .End(xlDown) to find the last free line, and the cells are accessed thanks to Offset(params):

Case 1

Sheets("Sheet1").Range("A1").End(xlDown).Offset(1, 0) = aValue
Sheets("Sheet1").Range("A1").End(xlDown).Offset(0, 1) = anOtherValue

Result:

 XXX           XXX           XXX             <- Old line
 aValue        anOtherValue                  <- New line

which is not equivalent to

Case 2

With Sheets("Sheet1").Range("A1").End(xlDown)
    .Offset(1, 0) = aValue
    .Offset(0, 1) = anOtherValue
End With

Result:

 XXX           anOtherValue  XXX
 aValue        

but is equivalent to

Case 3

With Sheets("Sheet1").Range("A1").End(xlDown)
    .Offset(1, 0) = aValue
    .Offset(1, 1) = anOtherValue
End With

Result:

 XXX           XXX           XXX
 aValue        anOtherValue

NOTE: by "equivalent" I mean "with the same result"

I imagine that with With the values are set then the sheet is updated, that's why with the second case the second Offset() considers that no new cells have been filled in the sheet (that's why anOtherValue is at the end not in the right cell, because when Offset(0,1) is called the cell which is supposed to contain aValue is empty). Hoping this is clear ...

So (finally) my question is:

Does anyone knows how With exacty work/behave? because I didn't found any clear description on the web. I understand that it's referring to the object only once, but does it means it doesn't undate the object during the process?

Answer 1

---

With simply obtains and holds a reference to the specified object. That reference doesn't change. (you can think of it as being an implicit variable)

Your end(xlDown) code doesn't refer to the same range the second time, for obvious reasons, so it's not equivalent to your With block.

Assume you have A1 selected, and run this code:

Sub foo()
    With ActiveCell
        ActiveCell.Offset(1).Select
        MsgBox .Address
    End With
End Sub

even though the active cell is now A2, you will get A1 in the message because that was the active cell at the time the With line was run.

Equally, if you select A1 and run this:

Sub bar()
    With ActiveCell
        ActiveCell.Cut ActiveCell.Offset(1)
        MsgBox .Address
    End With
End Sub

you will see A2 in the message even though the active cell is still A1, because the cell referred to by the With block was moved. Note that if you copied it, instead of cutting, the message would show you A1 because that cell was not moved.