ontime with end time

[eran3185]

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]

like this

Application.OnTime earliesttime:=Timetorun, _
   procedure:="OnTimeMacro", schedule:=False

 

[John_w]

OnTime does have an end point - the LatestTime parameter. Try this, based on http://www.cpearson.com/excel/OnTime.aspx:

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]

Untested...

Based on Chip Pearson's 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]

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]

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

 

[eran3185]

thank's , its work now

 

[eran3185]

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.