Background
Sometime I need to repeat some tasks automatically in Excel. I missed the Timer function in Visual Basic. After struggled for some time, I figured out how to do it in Visual Basic for Application (VBA).
Self Repeating Timer in Excel VBA
The code below is the self repeating timer. TimerActive is a global variable. It is tied to a push button so user can turn on or turn off the self repeating timer. The code below repeat every three seconds. When timer hits, it calls the doSomeThingSub() subroutine and accomplish designed tasks.
Detail Code
Dim TimerActive As Boolean
Public Sub Start_Timer()
TimerActive = True
Application.OnTime Now() + TimeValue(“00:00:03”), “Timer”
End Sub
Public Sub Stop_Timer()
TimerActive = False
End Sub
Private Sub Timer()
If TimerActive Then
Call doSomeThingSub()
Application.OnTime Now() + TimeValue(“00:00:03”), “Timer”
End If
End Sub