Results 1 to 13 of 13
  1. #1
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    GoTo out of a For Next loop (VBA/All/2003 )

    Hi Loungers
    I am inclined to use code along the following lines

    For Counter = 0 to 100
    some code
    If StateA then
    GoTo LabelX
    End if
    Some more code
    Next Counter
    Still more code
    LabelX:
    The rest of the code

    Although I cannot recall this failing, I have always been concerned that I was leaving unclosed code and building a stack(?) that might overflow in a process that involved many iterations.

    Are my concerns valid? or can I create code of this type with impunity?

    T.I.A.
    Regards
    Don

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: GoTo out of a For Next loop (VBA/All/2003 )

    I've never tried to inspect the stack in VBA. But if you are concerned, you could use Exit For to exit the loop.

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: GoTo out of a For Next loop (VBA/All/2003 )

    Thanks Jefferson
    That requires more care and housekeeping if we are dealing with nested loops and the GoTo target is external to the nest. I think that I will run an iterative test of some humungeous size overnight.
    Will advise of the results.
    Regards
    Don

  4. #4
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: GoTo out of a For Next loop (VBA/All/2003 )

    Even if your test shows that it works, this could be an implementation detail that changes with a future upgrade or bug fix. I would recommend using Exit For and testing for the condition when you exit the loop - if you can't do that then move all the code to a separate sub that you can call, as you can assume that all local storage is destroyed when you return from a sub.

    StuartR

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: GoTo out of a For Next loop (VBA/All/2003 )

    Thank you Stuart
    That sounds like excelent advice.
    Regards
    Don

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: GoTo out of a For Next loop (VBA/All/2003 )

    It is generally considered bad practice to use GoTo statements for anything other than On Error statements these days. Exit For would be my preference for sure.
    Rory
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GoTo out of a For Next loop (VBA/All/2003 )

    I second Stuart and Rory, Go To out of a For Next is a very bad practice.
    Legare Coleman

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GoTo out of a For Next loop (VBA/All/2003 )

    Stuart's advice on using Exit Sub is a very useful technique for dumping out of a complex process. You might also consider using a function, which returns true/false to indicate if the process completed, or the value of "Counter" to indicate where it stopped etc.

    Alan

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GoTo out of a For Next loop (VBA/All/2003 )

    (late arrival)
    I'm inclined to avoid both GoTo and Exit For.
    Consider using a While loop, with a condition along the lines of<pre>(Counter <100) AND (Not StateA)</pre>

    That way control of the loop is held in one place, and is visible.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: GoTo out of a For Next loop (VBA/All/2003 )

    Do While ... Loop tests at the start of the loop, and Do ... Loop Until at the end. Sometimes it is convenient to test and exit somewhere in the middle, and Exit Do / Exit For are provided for that purpose.

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GoTo out of a For Next loop (VBA/All/2003 )

    >Sometimes it is convenient to test and exit somewhere in the middle
    I beg to differ; it is rarely as readable as having all the loop control in one place.

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: GoTo out of a For Next loop (VBA/All/2003 )

    And I beg to differ: if your code is well written and commented properly, it should be eminently clear what is going on! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: GoTo out of a For Next loop (VBA/All/2003 )

    Don, everyone has jumped in and I feel like I'm piling on after the whistle, but ... a couple of other simple techniques to consider when you are using nested loops: when appropriate you can test for a condition in order to start the inner loops (it also speeds processing up by skipping unneeded loops), and you can set a Boolean value to tell you to get all the way out of the loops, here's a hypothetical example of both:

    Dim boolExit as Boolean
    Dim lngA as Long, lngB as Long, lngC as Long

    For lngA = n To somethingA.Count
    If conditionB Then
    For lngB = n To somethingB.Count
    If conditionC Then
    For lngC = n To somethingC.Count
    processing code
    If conditionX then
    boolExit = True
    Exit For
    End If
    Next lngC
    End If ' conditionC
    If boolExit then Exit For
    Next lngB
    End If ' conditionB
    If boolExit then Exit For
    Next lngA

    HTH at some point in your work.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •