Solved: macro timer

[vzachin]

hi,

i need to run a macro automatically every 20 minutes within an 8-hour timeframe. is that possible? if yes, then is there a way to add a button to terminate the macro sooner than the 8-hour time frame?

ideally, i would fire the macro at 6 am, and every 20 minutes, the macro "does stuff" then goes to sleep for another 20 minutes..and continues for 8 hours. and if i want to terminate the macro, click a button


thanks
zach

[Bob Phillips]

Public nTime As Double

Public Sub StartTimer()

If Now - Int(Now) < Time(14, 0, 0) Then

nTime = Now + Time(0, 20, 0)
Application.OnTime nTime, "StartTimer"
Call myProc
End If
End Sub

Public Sub StopTimer()

Application.OnTime nTime, "StartTimer", , False
End Sub

[vzachin]

hi bob,

i'm getting a run-time error 13 (type mismatch) here:
If Now - Int(Now) < Time(14, 0, 0) Then

i'm using excel 2003


thanks
zach

[GTO]

Greetings Zach,

A tiny tweak to Bob's. Just substitute "TimeSerial" for the two places "Time" is...

Hope the weather is nice up there in New York (a 'favorite' Aunt lives up there).

Hope this helps,

Mark

Option Explicit
Public nTime As Double

Public Sub StartTimer()
If Now - Int(Now) < TimeSerial(14, 0, 0) Then
nTime = Now + TimeSerial(0, 20, 0)
Application.OnTime nTime, "StartTimer"
Call myProc
End If
End Sub

Public Sub StopTimer()
Application.OnTime nTime, "StartTimer", , False
End Sub

[vzachin]

thanks mark & bob for the coding.
it took me a while to figure out TimeSerial(14, 0, 0) = 2PM.
i also found out about TimeValue("2 PM")

ny is windy & stormy tonight but will be in the balmy 60s tomorrow. great weather for your aunt...

thanks again
zach

[GTO]

Oh no thanks needed for me; Bob's clear/consice coding just astounds me, and I was certainly learning as well.

Thank you for the weather advisement and have a great evening,

Mark