none
Infinate loop RRS feed

  • Question

  • Hi All,

    I was wondering if there is a way to send an excel macro code into an infinite loop that will only exit once the user has pressed a button.

    Monday, February 9, 2015 10:51 PM

All replies

  • Create the following code in a VBA module:

    Dim blnStop As Boolean
    Dim lngCounter As Long
    
    Sub StartLoop()
        blnStop = False
        lngCounter = 0
        Do
            lngCounter = lngCounter + 1
            Application.StatusBar = "Processing step " & lngCounter
            ' Your code goes here
            ' ...
            ' Important - give events a chance
            DoEvents
        Loop Until blnStop
        Application.StatusBar = False
    End Sub
    
    Sub StopLoop()
        blnStop = True
    End Sub

    On the Developer tab of the ribbon, select Insert > Button (Form Control).

    Assign the macro StartLoop to this button. Change the caption to Start.

    On the Developer tab of the ribbon, select Insert > Button (Form Control) again.

    Assign the macro StopLoop to this button. Change the caption to Stop.

    When you click the first button, a loop will start. You can see the counter increase in the status bar. When you click the Stop button, the loop ends.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, February 9, 2015 11:04 PM