Wednesday, November 08, 2006

VB: Start and Stop Macro Execution with a Button Click

I recently had someone ask me if there was a way, in VB, to start and stop a series of sub routines from running. The problem with Visual Basic is the lack of any sort of easy to use multi-threading. There are a couple of solutions however, while not exactly the prettiest, will do the job. The topic of multi-threading is a very complex one, so we will keep this simple and stay outside of that realm.

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: