Quite often I’ve come across Excel Newsgroup posts where the poster stores the range in a variable before doing For Next.
Dim rng As Range, rngFull As Range
Set rngFull = Range(Range(“A1”), Cells(Rows.Count, “A”).End(xlUp)) ‘Store this
For Each rng In rngFull
They could have written
but they didn’t… and that got me wondering.
I thought “hey, maybe it gets calculated every loop”
I thought “for a column containing 1,000 entries, that would be an enormous performance drain!”
Well… call me Henny Penny… I started storing range calcs in variables before loops too and I have been for ages – until today.
It turns out that VB is well behaved – it’ll only run the calc once. This block of code confirms it.
Dim i As Long, rng As Range
For i = 1 To getval
For Each rng In getrange
Function getval() As Long
MsgBox “getval called”
getval = 5
Function getrange() As Range
MsgBox “getrange called”
Set getrange = Range(“A1:A5”)
That’ll teach me to make assumptions.