Schedule time function VBA

[Muhammad_Usman]

Hello Every One,

I have following code which is working perfectly.

i want some changes

1 = Code start on 10 am
2 = Run Every 15 minutes
3 = and close on 7 pm

on dialy basis'

Sub RunEveryFive()
Windows("Book1.xlsx.xlsm").Activate
ThisWorkbook.FollowHyperlink Range("H2").Value
Application.OnTime Now + TimeValue("00:15:00"), "RunEveryFive"
Windows("Book1.xlsx.xlsm").Activate
End Sub

 

[bobsan42]

two versions below:

'# SUB version
Sub RunEveryFive()

    Windows("Book1.xlsx.xlsm").Activate
    ThisWorkbook.FollowHyperlink Range("H2").Value
    Call schedule_next_run("RunEveryFive")
    Windows("Book1.xlsx.xlsm").Activate
End Sub

Sub schedule_next_run(proc_name As String)
    Static start_time, end_time, run_interval, next_run_time
    If IsNull(start_time) Then start_time = TimeValue("10:00:00")
    If IsNull(end_time) Then end_time = Date + TimeValue("19:00:00")
    If IsNull(run_interval) Then run_interval = TimeValue("00:15:00")

    If Time > end_time Then
        next_run_time = (Date + 1) + start_time
    ElseIf Time + run_interval > end_time Then
        next_run_time = Date + end_time
    Else
        next_run_time = Now + run_interval
    End If
    Application.OnTime next_run_time, proc_name
    next_run_time = Null
End Sub

'# FUNCTION version
Sub RunEveryFive()

    Windows("Book1.xlsx.xlsm").Activate
    ThisWorkbook.FollowHyperlink Range("H2").Value
    Application.OnTime next_run_time, "RunEveryFive"
    Windows("Book1.xlsx.xlsm").Activate
End Sub

Function next_run_time()
    Dim start_time, end_time, run_interval
    start_time = TimeValue("10:00:00")
    end_time = Date + TimeValue("19:00:00")
    run_interval = TimeValue("00:15:00")

    If Time > end_time Then
        next_run_time = (Date + 1) + start_time
    ElseIf Time + run_interval > end_time Then
        next_run_time = Date + end_time
    Else
        next_run_time = Now + run_interval
    End If

End Function