How to set macro to run every 15 minutes?

[Eric]

Does anyone have any suggestions on how to set macro to run every 15 minutes?
Thanks in advance for any suggestions
Eric

 

[ozgrid.com]

See:
http://www.ozgrid.com/Excel/run-macro-on-time.htm

 

[JLatham]

Stolen from a very nearby universe, er, answer to a question, and modified to
protect the guilty.

Option Explicit
Dim nextRunTime

Sub startRunning()
'you could put
' Run "MyCode"
'in the Workbook_Open() event
'
MyCode
End Sub

Sub stopRunning()
On Error Resume Next
Application.OnTime nextRunTime, "MyCode", , False
End Sub

Sub MyCode()
nextSecond = Now + TimeValue("00:15:00")
Application.OnTime nextSecond, "MyCode"

'your process to run right in with this stuff
'it has already set itself to run again in 15 minutes
End Sub

 

[Dave Peterson]

Chip Pearson's site explains it:
http://www.cpearson.com/excel/OnTime.aspx

 

[Eric]

For using Application.OnTime, do I need to close the excel application in
order to stop the process? It seems to me when the file is closed without
quiting the excel application, it will keep running the macro again.
Does anyone have any suggestions?
Thank everyone very much for any suggestions
Eric

 

[JLatham]

And it can do that. Have you checked out the link that Dave Peterson
provided below? One way to handle the closing of the workbook is to put the
same code that is in Sub stopRunning into the workbook's _BeforeClose() event
processor, or simply call stopRunning from there, as:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Run "stopRunning"
End Sub

Remember that the Workbook_BeforeClose() code has to be placed into the
ThisWorkbook code module.

 

[Eric]

Thank everyone very much for suggestions
There is an error, does anyone have any suggestions on how to fix it?
Thanks in advance for any suggestions
Eric

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Run "stopRunning" / Error in this line
End Sub

 

[Eric]

When I try following codes, there is error on this line.
Do you have any suggestions on how to fix it?
Thanks in advance for any suggestions
Eric

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Application.OnTime dTime, "MyMacro", , False / Error in this line

End Sub

 

[Dave Peterson]

If you look at Chip's site, you'll see this code:

Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen,Procedure:=cRunWhat, _
Schedule:=False
End Sub


The "on error resume next" line is there in case there isn't a pending ontime
macro for that time.

 

[Eric]

Do I need to insert both StopTimer and Workbook_BeforeClose into worksheet in
order to make it work? but I find nothing calling StopTimer under any module.
Could you please give me more suggestions?
Thanks in advance for any suggestions
Eric

Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen,Procedure:=cRunWhat, _
Schedule:=False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Application.OnTime dTime, "MyMacro", , False / Error in this line

End Sub

 

[Dave Peterson]

You can call the stoptimer routine in your _beforeclose procedure:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call StopTimer
End Sub

Then you don't need to duplicate the code (and take a chance that you don't fix
both when you have to change it).

The StopTimer routine is nice to keep separate -- especially when you're testing
and want to kill the next run.