Ending a loop naturally

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • qtr8
    New Member
    • Dec 2011
    • 1

    #1

    Ending a loop naturally

    Hi guys, I am pretty new to VBA.. I have a loop running in VBA and it runs perfectly as I want it too, HOWEVER, after it runs it seems to still hog the processor and I cannot do anything in excel for a few seconds, (like 5 secs). I think that this may be because there is no instruction for the VBA to end and to hand back to Excel. The End Loop code I have seen on the web seems to be for code to interrupt the loop, whereas I want the loop to run in it's entirety and then stop.
    Code:
    Sub Entry()
    for ii =12  to 39
    If cells (3,ii)=1 And cells(6,ii)=1 Then Cells (4,ii)=1
    If cells (4,ii) = 1 Then cells(6,ii)=0
    next ii
    End Sub
  • kadghar
    Recognized Expert Top Contributor
    • Apr 2007
    • 1302

    #2
    use EXIT FOR inside the IF

    Comment

    • Killer42
      Recognized Expert Expert
      • Oct 2006
      • 8429

      #3
      Given that this loop only executes 28 times, it certainly should not tie up the processor for a noticeable amount of time. Most likely there's something else going on.

      For instance, how is the Entry() sub invoked? Perhaps another process is calling this lots of times, or continuing on with lots of other processing after this. Or, there could be a huge amount of calculation or conditional formatting triggered by the apparently small changes made to the values by this code. I've worked with a large spreadsheet that took a few minutes to recalculate, every time I changed a value. (Had to turn off automatic calculation to get anywhere). Debugging this really requires more knowledge of the context in which it's used.

      The Exit For would be appropriate only if you want to stop immediately after performing one of these actions, rather than continuing to look for more occurrences.

      Comment

      • kadghar
        Recognized Expert Top Contributor
        • Apr 2007
        • 1302

        #4
        Yeap, that's right Killer,

        And one other thing: When you work with VBA in Excel is always better to read all the cells you're using into a matrix, work with it and when finished, return it to the worksheet. This way you'll only interact with Excel twice.

        In your code you're doing it 5 times per loop. Believe it or not, this makes the code run over 200 times slower.

        Comment

        • Killer42
          Recognized Expert Expert
          • Oct 2006
          • 8429

          #5
          Oh, one other performance tip.

          First, let me state that (a) this is based on very early versions of VB and may no longer apply, and (b) it was only a tiny difference even then. Anyway...

          Leaving the loop counter off the Next statement used to improve performance very slightly. That is,
          Next
          instead of
          Next ii

          Oh, and I meant to ask (forgive me if this sounds a silly question, but you'd be amazed how often people miss the simplest glitches in program logic). Are you aware that making the first change (Cells (4,ii) = 1) will trigger your second test (If cells (4,ii) = 1)?

          Comment

          Working...