[ 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.