ontime with end time

eran3185

Board Regular
Joined
Apr 28, 2007
Messages
135
hi

i want to use "ontime".
i want to run this macro from 18:00 , every 1 minute , until 18:30.
the problem with "ontime" that there is only start point , but there is not end point (18:30)

how can i get also the end point ?
 

Diablo II

Well-known Member
Joined
Sep 28, 2008
Messages
538
like this
Code:
Application.OnTime earliesttime:=Timetorun, _
   procedure:="OnTimeMacro", schedule:=False
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,064
OnTime does have an end point - the LatestTime parameter. Try this, based on http://www.cpearson.com/excel/OnTime.aspx:

Code:
Option Explicit

Public RunWhen As Double
Public Const cRunIntervalSeconds = 60 ' one minute
Public Const cRunWhat = "TheSub"  ' the name of the procedure to run
Public Const cStartTime = "18:00"
Public Const cEndTime = "18:30"

Sub Test()
    StartTimer
End Sub

Sub StartTimer()
    Application.OnTime earliesttime:=TimeValue(cStartTime), Procedure:=cRunWhat, latesttime:=TimeValue(cEndTime), schedule:=True
End Sub

Sub RestartTimer()
    RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
    Application.OnTime earliesttime:=RunWhen, Procedure:=cRunWhat, latesttime:=TimeValue(cEndTime), schedule:=True
End Sub

Sub TheSub()
    ''''''''''''''''''''''''
    ' Your code here
    ''''''''''''''''''''''''
    MsgBox Time
    
    RestartTimer  ' Reschedule the procedure
End Sub
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,007
Untested...

Based on Chip Pearson's code...
Code:
Option Explicit

Public RunWhen As Date
Public Const StartTime As Date = #6:00:00 PM#, EndTime As Date = #6:30:00 PM#, _
    cRunIntervalSeconds = 60 ' two minutes
Public Const cRunWhat = "TheSub"  ' the name of the procedure to run


Sub SchedTimer()
    If RunWhen = 0 Then RunWhen = StartTime _
    Else RunWhen = RunWhen + TimeSerial(0, 0, cRunIntervalSeconds)
    If RunWhen < EndTime Then _
        Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
            Schedule:=True
    End Sub


Sub TheSub()
    ''''''''''''''''''''''''
    ' Your code here
    ''''''''''''''''''''''''
    SchedTimer  ' Reschedule the procedure
    End Sub
hi

i want to use "ontime".
i want to run this macro from 18:00 , every 1 minute , until 18:30.
the problem with "ontime" that there is only start point , but there is not end point (18:30)

how can i get also the end point ?
 

eran3185

Board Regular
Joined
Apr 28, 2007
Messages
135
thank's.
its a great web site :)

i try to use this codes , but it didn't stop ...


i wrote this :

"Option Explicit

Public RunWhen As Double
Public Const cRunIntervalSeconds = 10 ' one minute
Public Const cRunWhat = "TheSub" ' the name of the procedure to run
Public Const cStartTime = "8:10"
Public Const cEndTime = "8:11"

Sub Test()
StartTimer
End Sub

Sub StartTimer()
Application.OnTime earliesttime:=TimeValue(cStartTime), Procedure:=cRunWhat, latesttime:=TimeValue(cEndTime), schedule:=True
End Sub

Sub RestartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, Procedure:=cRunWhat, latesttime:=TimeValue(cEndTime), schedule:=True
End Sub

Sub TheSub()
''''''''''''''''''''''''
' Your code here
''''''''''''''''''''''''
MsgBox Time

RestartTimer ' Reschedule the procedure
End Sub "
 

Jinspet

New Member
Joined
Mar 10, 2009
Messages
13
Code:
Sub yourSubHere()
  'do something here
  callOnTime
End Sub
 
Sub callOnTime()
    If TimeValue(Now) >= TimeValue("18:00") And TimeValue(Now) <= TimeValue("18:30") Then
       Application.OnTime NOW + TimeValue("00:01:00"), yourSubHere
    End If
End Sub
 
Last edited:

eran3185

Board Regular
Joined
Apr 28, 2007
Messages
135
hi
there is a problem.
the macro copy from a web site every 15 minutes.
when the macro is running , its copy the data also on another excel files that running same time on my computer.
 

Forum statistics

Threads
1,085,566
Messages
5,384,476
Members
401,904
Latest member
markschneider89

Some videos you may like

This Week's Hot Topics

Top