Schedule a Macro to run every 5 minutes

[Atheobody]

Hello,

Can anyone tell me (in real simple terms) how to run a macro every 5 minutes.

I'd like to hit my hot button "Ctr-Q" and then have it run every 5 minutes
for ever (or until I close the spreadsheet).

I already have the macro done, I just need to add the part I'm asking. I
would ask that you "idiot proof" it for me as I've tried to read Chip
Pearson's page but it was too difficult for me to follow.
Thanks,
atheobody

 

[Don Guillett]

look in the vba help for ONTIME

 

[JMB]

Additional examples

http://www.cpearson.com/excel/ontime.htm

 

[Atheobody]

Hello,

I read that page but it doesn't make sense to me. I just don't undestand.
Isn't there an example you could copy and paste for me that I could copy and
paste into my macro?

 

[Atheobody]

Thanks! Where speficially is the VBA help for ONTIME. You mean in Excel
help on my Excel program? Or on this forum? I'm still very confused.

 

[Don Guillett]

alt f11>f1>index>type in ONTIME

 

[JMB]

Sorry - I must have read over the part where you mentioned you had already
been to Chip's website. I can't give any examples better than what Chip has
already done, so I can only repost his code and offer a little explanation.

These are the variable declarations that go at the top of your vba module.
You will need to change "The_Sub" to the name of your macro that you want to
run every 5 minutes.

Public RunWhen As Double
Public Const cRunIntervalSeconds = 300 ' five minutes
Public Const cRunWhat = "The_Sub" '<<<<< CHANGE


This procedure is called from "The_Sub". This instructs VBA to run
"The_Sub" again in 5 minutes.

Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
schedule:=True
End Sub

This is the macro you want to run every 5 minutes and should be linked to
your hotkey, Ctrl-q. Since your macro is already done, you just need to add
StartTimer to the end of it to get it to run again in 5 minutes.

Sub The_Sub()
'
' your code here
'
StartTimer '<<< Put at the end of your macro
End Sub

As Chip pointed out, even after you close the workbook, Excel will open it
and run your macro in 5 minutes unless you stop the timer. This is a macro
you could assign to a button to stop the timer.

Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, _
procedure:=cRunWhat, schedule:=False
End Sub


Alternatively, you could have excel stop the timer process automatically by
putting the stop timer procedures in a workbook close event handler. Instead
of putting it in a regular vba code module, you have to put it in the
ThisWorkbook module (in the vba editor, you have to have the Project Explorer
open (Ctrl-R) to see the various modules of your project.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, _
procedure:=cRunWhat, schedule:=False
End Sub


If you need more info on how to navigate the vba editor, you should visit
David McRitchies web site. He has a few tutorials that will likely help.
Here is one on getting started w/macros
http://www.mvps.org/dmcritchie/excel/getstarted.htm