What is the best way to get some code to run at timed intervals?

I have used the following code.

Application.OnTime Now + TimeValue("00:05:00"), _ "time_set",
TimeValue("22:00:00")

This should run every 5 minutes until 22:00 Hours.

I have also tried:-

Application.OnTime Now + TimeValue("00:05:00"), "time_set", ,False

I get debugging errors with both.

Would a Do_Loop be better?

TIA

To cancel an OnTime timer, you must pass it the EXACT time that was set in
the code that called OnTime. You should create a module level Double
variable, store the time in that variable, and then pass that value when you
cancel the timer.

See www.cpearson.com/excel/ontime.htm for details.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"Saxman" wrote in message
...
What is the best way to get some code to run at timed intervals?

I have used the following code.

Application.OnTime Now + TimeValue("00:05:00"), _ "time_set",
TimeValue("22:00:00")

This should run every 5 minutes until 22:00 Hours.

I have also tried:-

Application.OnTime Now + TimeValue("00:05:00"), "time_set", ,False

I get debugging errors with both.

Would a Do_Loop be better?

TIA

Chip Pearson wrote:
To cancel an OnTime timer, you must pass it the EXACT time that was set in
the code that called OnTime. You should create a module level Double
variable, store the time in that variable, and then pass that value when you
cancel the timer.

I get constant errors when running this. Do I have to alter the values
in the Sub StartTimer() routine?
.................................................. ..........
Public RunWhen As Double
Public Const cRunIntervalSeconds = 30 ' 30 seconds
Public Const cRunWhat = "The_Sub"

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub

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

Sub The_Sub()
Private Sub Worksheet_Calculate()
Application.EnableEvents = False

Worksheets("Data").Range("F5:F10").Copy
Worksheets("Archive").Range("A2").Insert Shift:=xlDown

Application.EnableEvents = True
StartTimer
End Sub
.................................................. ...................

Sorry, but I'm very green at this.

Thanks.