The first solution is to use a Timer component as the driver for your subroutines. You can then start and stop the timer at your leisure.
The second is to have a series of flags determining the state of the subroutine runs, run the subroutines in a loop, and use a DoEvents call so the program is not in a locked state.
For example, lets say I have an Excel spreadsheet that runs a series of functions in a loop. I want to be able to start and stop that with the click of a button. I would use something like the below, where UserForm1 is my containing my buttons, CommandButton1 starts my loop, CommandButton2 will show my the status of the loop in a message box and ask me if I want to stop, and TextBox1 will show if the loop is running. The loop in the example will only increment a counter X by 1, and reset it if it gets over 6,000,000.
Option Explicit
Dim x As Long
Dim continuerun As Boolean
Private Sub CommandButton1_Click()
continuerun = True
While continuerun
x = x + 1
If x > 6000000 Then
x = 0
End If
DoEvents
TextBox1.Text = continuerun
Wend
End Sub
Private Sub CommandButton2_Click()
MsgBox x
continuerun = (MsgBox("Continue running loop?", vbYesNo) = vbYes)
TextBox1.Text = continuerun
End Sub
Of course, the third option, since it would be in a macro, is to keep the DoEvents call in the loop, and just go into the VBEditor and hit escape or the stop execution button.
No comments:
Post a Comment