Menu
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?
301
Use the above and type your code after the code StartingTime = Timer, but before the code MsgBox Timer – StartingTime, i.e., in a green area, you need to enter your code.
If you need Excel to run some VBA at a specific time, or repeatedly at set intervals, you can use the Application. OnTime method. A basic call to Ontime requires that you supply a time when you want the code to run, and the name of the macro you want to run.
Select either the Timed delay in seconds or Timed delay in milliseconds option and then enter the number of seconds or milliseconds in the Delay Time edit box. üNote: This command suspends Macro Express for the amount of time specified.
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
