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

vba excel timer scheduling
FinancialRadDeveloper picture FinancialRadDeveloper · Feb 23, 2010 · Viewed 257.3k times · Source

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?

Answer

Alain picture Alain · Dec 3, 2010

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

Related questions

VBA: How do I really stop Application.onTime()?

I have already read some questions/threads (e.g. this and this) about how to stop the VBA Application.OnTime procedure, but I just can't get it to stop! I am using it to pull some data every x seconds. …

Read More
Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
How do I declare a global variable in VBA?

I wrote the following code: Function find_results_idle() Public iRaw As Integer Public iColumn As Integer iRaw = 1 iColumn = 1 And I get the error message: "invalid attribute in Sub or Function" Do you know what I did wrong? I tried …

Read More