For Next Behaviour

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
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
    For Each rng In getrange
        Debug.Print rng.Address
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.

Posted in Uncategorized

4 thoughts on “For Next Behaviour

  1. 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,

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


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


    Stephen Bullen

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


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.