Selected Answer
Sunil
You need to three things to do this:
- To use the Workbook-Open event
- A check of the notes from Microsoft on Application.OnTime method (Excel), specifically the bit about running procedures at a specfic time
- Use the MROUND worksheet function to round the time to fixed interval (like 10:05 say)
plus a bit of logic!
In the attached file, the first bit calls two macros from Module 1 and is just:
Private Sub Workbook_Open()
SetStartTime 'initialise
TimerLogic 'set timer to run c.f. start and end times
MsgBox "Timer running; macro next due to run at " & NextTime 'remind user
End Sub
The MROUND function is available in Excel365 and rounds a number to the nearest stated multiple e.g.
=MROUND(10, 3) rounds 10 to the nearest multiple of 3 and would give the result 9. It can be used for time too (as here, but best that the multiple is a factor of 60 minutes so events are at the same time past the hour each time).
In the code below (from Module 1), the results will appear in the Intermediate pane of VB Explorer. The key bit is the TimerLogic module which will set the timer for 09:00hrs today (in you case) if the workbook is opened before then, at 5 minute intervals thereafter but after the StopTime (19:00hrs for you) it will set the start and stop times to the next day (if you leave the file open).
You'll need to remove the lines starting Debug.Print... and with replace with code to do what you want:
Public StartTime As Date, StopTime As Date, NextTime As Date, Interval As Date
Sub TimerLogic()
Select Case Now
Case Is < StartTime 'if before start time, set as start time
NextTime = StartTime
Case Is >= StopTime 'if after stop time
StartTime = StartTime + 1 'set start and stop times for tomorrow tomorrow
StopTime = StopTime + 1 'run timer for tomorrow
NextTime = StartTime
Case Else 'if between times
NextTime = Now + 0.6 * Interval 'add 60% of interval (to be rounded to next later interval)
End Select
'round time to nearest next interval time
NextTime = Application.WorksheetFunction.MRound(NextTime, Interval)
Debug.Print "Next time set at: " & Now '### to demo
Debug.Print "Due (with interval and rounding)= " & NextTime '### to demo
SetTimer (NextTime)
End Sub
Sub SetTimer(NextTime As Date)
Application.OnTime NextTime, "MyMacro"
End Sub
Sub SetStartTime()
StartTime = Date + TimeValue("09:00:00")
StopTime = Date + TimeValue("19:00:00")
Interval = TimeValue("00:05")
End Sub
Sub MyMacro()
Debug.Print "Done: " & Now
TimerLogic
End Sub
Sub StopTimer()
On Error Resume Next
Tstop = Now + TimeValue("00:00:10")
Application.OnTime Earliesttime:=Tstop, Procedure:="MyMacro", Schedule:=False
End Sub
Provided you have Excel 365, this should work well. If you're using an earlier version, use these lines instead of MROUND:
'round time to nearest next interval time
NextTime = Application.WorksheetFunction.RoundUp((NextTime - Int(NextTime)) / TimeValue("00:05:00"), 0) * Interval
the disadvantage being that the divisor has to be written as
TimeValue("00:05:00") rather than
Interval (for some reason!).
When I set the constants to
StartTime = Date + TimeValue("13:50:00")
StopTime = Date + TimeValue("14:15:00")
Interval = TimeValue("00:05")
then the results were (from start to waiting for the macro to run the next day):
Next time set at: 07/09/2021 13:49:18
Due (with interval and rounding)= 07/09/2021 13:50:00
Done: 07/09/2021 13:50:00
Next time set at: 07/09/2021 13:50:00
(etc. until....)
Done: 07/09/2021 14:15:00
Next time set at: 07/09/2021 14:15:00
Due (with interval and rounding)= 08/09/2021 13:50:00
Hope this helps.
