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

Question

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?

Phonon

Answer 1

When the workbook first opens, execute this code:

alertTime = Now + TimeValue("00:02:00")
Application.OnTime alertTime, "EventMacro"

Then just have a macro in the workbook called "EventMacro" that will repeat it.

Public Sub EventMacro()
    '... Execute your actions here'
    alertTime = Now + TimeValue("00:02:00")
    Application.OnTime alertTime, "EventMacro"
End Sub

Glantz

thanks for this code :) needed this for work asap and this was my first page to goto :) YAY – Rosemaria

Hi, will it give an error when the Excel sheet id minimized or another excel sheet is opened? – Wycoff

@ArunCheriyan I see no reason why it would. Why, are you seeing an error? If so, try asking a new question and referencing this one. – Glantz

Isn't it an infinite recursion? Wouldn't it cause a stack overflow after few thousand/million/billion function calls? – Trinitrophenol

@BerryTsakala - Application.OnTime calls the Sub "from scratch" - at the top of a new stack. It's not like a normal recursive method call. Since each invocation gets its own stack - no stack overflow is possible. – Glantz

Answer 2

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.

Faltboat

sub Timer looks like an infinite recursive function; Unless the handle for OnTime is kept outside the sub, the sub will keep reference to its caller ad infinitum – Trinitrophenol

Answer 3

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

Signally

Thank you man, I searched for the reason of my error. Haven't put the Macro in a Module. Now my code is working as well. Thanks for the explanation. :) – Artina

Answer 4

(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

Spanishamerican

Thanks for the advice, however I don't want to do this as part of a form. – Phonon

For future reference, Excel unfortunately does not have the TimerInterval property. As far as I've been able to determine, only MS Access has that form property, and none of the other MS products that use VBA have an equivalent. – Epiphenomenalism

Answer 5

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.

Specht

Per the CPearson article, you simply need to stop the timer (cancel the pending OnTime event). This will eliminate the 'event trigger when workbook is closed' issue you described. Also, the article draws warning to using the Windows Timer and changing a cell value which can cause Excel to crash. OnTime requires less code, just handle correctly when in use. – Detritus

Answer 6

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

Ovate