Time Comparision

naishpatel

New Member
Joined
Apr 24, 2002
Messages
6
Hi all,

I am trying to start a macro at specific time and will keep running at specific interval. But i am not able to compare time value to get it work.
Here is my code----

--I need to compare Runwhen with system time and if it is less then 8:28 AM then set RunWhen variable to 8:28 AM and then execute macro every 30 minutes and stops macro at 2:58 PM.

RunWhen = Now + TimeSerial(0, 0, cInterVal)
Application.OnTime earliestTime:=RunWhen, procedure:=RunWhat, Schedule:=True

Can someone help me how to solve this.

Thank you in Advance,
Naish
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi Naish,

First, you must use the Time function rather than the Now function to return just the time rather than the date+time. Here is how the code should look to do what you describe:

Sub ScheduleRunWhat()
Dim tStart As Date
Dim tEnd As Date
tStart = TimeSerial(8, 28, 0)
tEnd = TimeSerial(14, 58, 30)
If Time < tStart Then
Application.OnTime tStart, "ScheduleRunWhat"
ElseIf Time < tEnd Then
're-schedule to run again in 30 minutes
Application.OnTime Time + TimeSerial(0, 30, 0), "ScheduleRunWhat"
RunWhat
Else
RunWhat
End If
End Sub

Sub RunWhat()
Beep
MsgBox "The time is now " & Time
End Sub

My RunWhat macro is just for checkout purposes. Note that ScheduleRunWhat schedules itself 30 minutes in the future, and calls RunWhat if the time is between 8:28 a.m. and 2:58 p.m. Note also that the code runs RunWhat even if the time is past 2:58, but doesn't reschedule itself in this case. This is because some "slippage" will generally occur because ScheduleRunWhat schedules itself 30 minutes from the current time rather than from the last time it was run, and this time can be delayed a bit by Windows process startup time and run time. Thus the last run will generally be slightly after 2:58 (probably less than 2 seconds).
 

Forum statistics

Threads
1,144,437
Messages
5,724,356
Members
422,546
Latest member
KevinOnTheDaily

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top