-
Ontime question
Hi,
I would like to use ontime to begin a process at specified time (say 13:00:00), re-run the process at fixed intervals (say every 00:00:15) and then end the whole thing at a specified time (say 15:00:00).
I can do each operation separately (i.e. I can get a macro to run at a particular time or can get it to run every x seconds) but am not sure how to combine them. Im new to vba so your help for this amateur question would be much appreciated.
Feliche
-
Re: Ontime question
Windows Task Scheduler can do that. You can set it up by hand, or use the AT command to run a BATCH/CMD file.
-
Re: Ontime question
If what you want to start is an external process, dglieanna's proposed solution is definitely one to consider.
If it's a VBA macro, however, you'd need to stick with OnTime. It looks like you've already found out that you can't do that with a single call to OnTime. But you can kick off the process once and then have the macro called from OnTime schedule the next call itself, something like this:
Code:
Option Explicit
Dim SavedEndTime As Date, SavedInterval As Date
Sub StuffToDo()
MsgBox "Hi, it's " & Now
If Now + SavedInterval <= SavedEndTime Then Application.OnTime Now + SavedInterval, "StuffToDo"
End Sub
Sub StartDoingStuff(StartTime As Date, EndTime As Date, Interval As Date)
SavedEndTime = EndTime
SavedInterval = Interval
Application.OnTime StartTime, "StuffToDo"
End Sub
Sub Test()
StartDoingStuff TimeValue("13:00:00"), TimeValue("15:00:00"), TimeValue("00:00:15")
End Sub
(Not tested in this version, would simply take too long... :D)
Ah, and... Welcome to CodeGuru! :)
-
Re: Ontime question
Thanks for the reply. this is what i wanted. only one question though: does this all go in a worksheet? Does any of this go in a module?
-
Re: Ontime question
It's a macro, so you can put it just about anywhere
-
Re: Ontime question
DoStuff() needs to be in a module in order for the internal timer event to find it. StartDoingStuff() and the global variables need to be in the same module so that both Subs have access to the variables. But StartDoingStuff() can be called from anywhere, i.e. Test() does not need to be in the same module or any module at all.
At least that's what my tests resulted in on Excel 2003.