Select a Category

How to add a Self Repeating Timer in Excel Visual Basic for Application?

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

 

Comments are closed.