[vba] VBA Macro On Timer style to run code every set number of seconds, i.e. 120 seconds

I have a need to run a piece of code every 120 seconds. I am looking for an easy way to do this in VBA. I know that it would be possible to get the timer value from the Auto_Open event to prevent having to use a magic number, but I can't quite get how to fire off a timer to get something to run every 120 seconds.

I don't really want to use an infinite loop with a sleep if I can avoid it.

EDIT:

Cross-post based on an answer provided is at: Excel VBA Application.OnTime. I think its a bad idea to use this... thoughts either way?

(This is paraphrased from the MS Access help files. I'm sure XL has something similar.) Basically, TimerInterval is a form-level property. Once set, use the sub Form_Timer to carry out your intended action.

Sub Form_Load()
    Me.TimerInterval = 1000 '1000 = 1 second
End Sub

Sub Form_Timer()
    'Do Stuff
End Sub

My solution:

Option Explicit
Public datHora As Date

Function Cronometro(action As Integer) As Integer 
'This return the seconds between two >calls
Cronometro = 0
  If action = 1 Then 'Start
    datHora = Now
  End If
  If action = 2 Then 'Time until that moment
    Cronometro = DateDiff("s", datHora, Now)
  End If
End Function

How to use? Easy...

dummy= Cronometro(1) ' This starts the timer

seconds= Cronometro(2) ' This returns the seconds between the first call and this one

In Workbook events:

Private Sub Workbook_Open()
    RunEveryTwoMinutes
End Sub

In a module:

Sub RunEveryTwoMinutes()
    //Add code here for whatever you want to happen
    Application.OnTime Now + TimeValue("00:02:00"), "RunEveryTwoMinutes"
End Sub

If you only want the first piece of code to execute after the workbook opens then just add a delay of 2 minutes into the Workbook_Open event

I've found that using OnTime can be painful, particularly when:

1. You're trying to code and the focus on the window gets interrupted every time the event triggers.
2. You have multiple workbooks open, you close the one that's supposed to use the timer, and it keeps triggering and reopening the workbook (if you forgot to kill the event properly).

This article by Chip Pearson was very illuminating. I prefer to use the Windows Timer now, instead of OnTime.

Yes, you can use Application.OnTime for this and then put it in a loop. It's sort of like an alarm clock where you keep hittig the snooze button for when you want it to ring again. The following updates Cell A1 every three seconds with the time.

Dim TimerActive As Boolean
Sub StartTimer()
    Start_Timer
End Sub

Private Sub Start_Timer()
    TimerActive = True
    Application.OnTime Now() + TimeValue("00:00:03"), "Timer"
End Sub

Private Sub Stop_Timer()
    TimerActive = False
End Sub

Private Sub Timer()
    If TimerActive Then
        ActiveSheet.Cells(1, 1).Value = Time
        Application.OnTime Now() + TimeValue("00:00:03"), "Timer"
    End If
End Sub

You can put the StartTimer procedure in your Auto_Open event and change what is done in the Timer proceedure (right now it is just updating the time in A1 with ActiveSheet.Cells(1, 1).Value = Time).

Note: you'll want the code (besides StartTimer) in a module, not a worksheet module. If you have it in a worksheet module, the code requires slight modification.