Quite often I’ve come across Excel Newsgroup posts where the poster stores the range in a variable before doing For Next.
Sub test1()
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
Debug.Print rng.Address
Next
End Sub
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
Debug.Print rng.Address
Next
End Sub
They could have written
For Each rng In Range(Range(“A1”), Cells(Rows.Count, “A”).End(xlUp))
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.
Sub test2()
Dim i As Long, rng As Range
For i = 1 To getval
Debug.Print i
Next
For Each rng In getrange
Debug.Print rng.Address
Next
End Sub
Function getval() As Long
MsgBox “getval called”
getval = 5
End Function
Function getrange() As Range
MsgBox “getrange called”
Set getrange = Range(“A1:A5”)
End Function
Dim i As Long, rng As Range
For i = 1 To getval
Debug.Print i
Next
For Each rng In getrange
Debug.Print rng.Address
Next
End Sub
Function getval() As Long
MsgBox “getval called”
getval = 5
End Function
Function getrange() As Range
MsgBox “getrange called”
Set getrange = Range(“A1:A5”)
End Function
That’ll teach me to make assumptions.
When working with Excel from VB 6.0 or VB.NET/C# there is an advantage of using the “variable”-approach, especially if the information will be used for several purposes (same reason can be applied to VBA).
On a personal note I find the “variable” approach to be much cleaner and make the life easier during maintance which is usually done long time ago after the original code was created.
The only reason that would change my way of coding would be if You or someone else could proof that there will be an advantage of using the “non-variable” approach in respect to speed and performance.
Kind regards,
Dennis
Hi Dennis,
Thanks for your feedback.
The point of the example was to show that the end part of the for next statement is read just once.
Not only does that apply to end portion, but the start portion too.
Sub test()
Dim i As Long
For i = s To e
Debug.Print i
Next
End Sub
Function s() As Long
s = 1
MsgBox “start”
End Function
Function e() As Long
e = 10
MsgBox “end”
End Function
I do agree that if the calc is reused elsewhere, it makes sense to store it away than recalculate.
However, as in the example in the post, the variable may only be used just once.
In this case I would find the code cleaner and easier to maintain if that variable were not declared at all – no matter VBA / VB / .NET
Cheers,
Rob
Hi Rob
Personally, I use the ‘variable’ approach for anything more than a fairly simple calculation. The reason is that it makes debugging *much* easier. When stepping through the code with the Locals Window visible, I can readily identify the bounds of loop (they’re showing in the variable’s values). That also gives me an opportunity to correct the calculation if anything’s wrong.
The same applies to With blocks, as trying to fix a complex calculation in a With statement requires a re-compile.
It should also be noted that Do…Loop structures *do* evaluate the expression each time round.
Regards
Stephen Bullen
Dennis, Stephen,
I think you’ve convinced me… I’ll stick to what I’ve been doing, even though now it’ll be for the right reasons!
It was the debugging that got me in the end.
Cheers,
Rob