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

User · Answer

My solution   Option Explicit Public datHora As Date  Function Cronometro action As Integer  As Integer   This return the seconds between two  gt 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

User · Answer

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

User · Answer

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

User · Answer

I ve found that using OnTime can be painful  particularly when    You re trying to code and the focus on the window gets interrupted every time the event triggers  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

User · Answer

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

User · Answer

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

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

Examples related to vba

Examples related to excel

Examples related to timer

Examples related to scheduling