Sunday, October 30, 2016

Excel VBA parallel timers

Excel VBA does not support multi-threading or running parallel loops. For this reason, if you want to run stop watches or timers at the same time, then you have to run them in the same loop.

You can run/stop each stopwatch individually by pressing the button below each stopwatch counter as seen in the picture below.



The button "All" is used to run/stop all stopwatches at the same time, so you can see if there is any delay between stopwatches or not.


The following is Excel VBA code for the previous demo of 4 stop watches:

Option Explicit
Private Declare Function GetTickCount Lib "kernel32" () As Long
 
Dim StopWatch1, StopWatch2, StopWatch3, StopWatch4, TimerLoopStatus, AllTimersOn As Boolean
Dim t1start, t2start, t3start, t4start, t5start As Long
 
Private Sub UserForm_Initialize()
 
' The following are the default values at userform start
StopWatch1 = False
StopWatch2 = False
StopWatch3 = False
StopWatch4 = False
AllTimersOn = False
 
TimerLoopStatus = False
 
End Sub
 
Private Sub StopWatchAllBtn_Click()
 
AllTimersOn = True
 
StopWatch1Btn_Click
 
StopWatch2Btn_Click
 
StopWatch3Btn_Click
 
StopWatch4Btn_Click
 
MultipleTimers     'Run timers loop
 
End Sub
 
Private Sub StopWatch1Btn_Click()
 
t1start = GetTickCount
 
If StopWatch1 = False Then      'If stop watch is off, then turn it on
StopWatch1 = True
StopWatch1Btn.Caption = "Stop"
StopWatch1Btn.BackColor = &HFF&
ElseIf StopWatch1 = True Then
StopWatch1 = False
StopWatch1Btn.Caption = "Start"
StopWatch1Btn.BackColor = &HC000&
End If
 
If TimerLoopStatus = False And AllTimersOn = False Then
MultipleTimers     'Run timers loop
End If
 
End Sub
 
Private Sub StopWatch2Btn_Click()
 
t2start = GetTickCount
 
If StopWatch2 = False Then      'If stop watch is off, then turn it on
StopWatch2 = True
StopWatch2Btn.Caption = "Stop"
StopWatch2Btn.BackColor = &HFF&
ElseIf StopWatch2 = True Then
StopWatch2 = False
StopWatch2Btn.Caption = "Start"
StopWatch2Btn.BackColor = &HC000&
End If
 
If TimerLoopStatus = False And AllTimersOn = False Then
MultipleTimers
End If
 
End Sub
 
Private Sub StopWatch3Btn_Click()
 
t3start = GetTickCount
 
If StopWatch3 = False Then      'If stop watch is off, then turn it on
StopWatch3 = True
StopWatch3Btn.Caption = "Stop"
StopWatch3Btn.BackColor = &HFF&
ElseIf StopWatch3 = True Then
StopWatch3 = False
StopWatch3Btn.Caption = "Start"
StopWatch3Btn.BackColor = &HC000&
End If
 
If TimerLoopStatus = False And AllTimersOn = False Then
MultipleTimers
End If
 
End Sub
 
Private Sub StopWatch4Btn_Click()
 
t4start = GetTickCount
 
If StopWatch4 = False Then      'If stop watch is off, then turn it on
StopWatch4 = True
StopWatch4Btn.Caption = "Stop"
StopWatch4Btn.BackColor = &HFF&
ElseIf StopWatch4 = True Then
StopWatch4 = False
StopWatch4Btn.Caption = "Start"
StopWatch4Btn.BackColor = &HC000&
End If
 
If TimerLoopStatus = False And AllTimersOn = False Then
MultipleTimers
End If
 
End Sub
 
 
Private Sub MultipleTimers()
 
Do While StopWatch1 = True Or StopWatch2 = True Or StopWatch3 = True Or StopWatch4 = True
 
If StopWatch1 = True Then
Label1.Caption = Round((GetTickCount - t1start) / 1000, 3)
End If
 
If StopWatch2 = True Then
Label2.Caption = Round((GetTickCount - t2start) / 1000, 3)
End If
 
If StopWatch3 = True Then
Label3.Caption = Round((GetTickCount - t3start) / 1000, 3)
End If
 
If StopWatch4 = True Then
Label4.Caption = Round((GetTickCount - t4start) / 1000, 3)
End If
 
DoEvents
Loop
 
TimerLoopStatus = False     'When the loop finishes, then the multiple timer status will be "False" (off)
 
AllTimersOn = False
 
End Sub
 
 
 

Enjoy...