My ontime program is suppose to run the macro every 15 minutes of the clock
starting from 9.00am to 12.30pm and then from 2.00pm to 4.45pm. In between
the two periods, I may quit Excel.
Right now, my macro does not actually run the first time at 9.00; it runs at
9.00 only because I open the workbook early and wait for the 15 minutes
interval to hit 9.00.
My method is mainly cobbled from answer give by Dave in
https://www.microsoft.com/office/com...=en-us&m=1&p=1
I've found that by way of msgboxes that the FirsTime "true" is not passed to
the module. The firstime in the StartTime module is "false".
Please take a look at my code. Because the FirstTime in StartTimer is always
false, it can't execute the runwhen timeserial.
Private Sub Workbook_Open()
FirstTime = True
If Time = TimeSerial(9, 0, 0) And Time <= TimeSerial(12, 30, 0) Or Time =
TimeSerial(14, 0, 0) Then
FirstTime = False
End If
Call StartTimer
FirstTime = False
End Sub
Option Explicit
Private mdtNextOnTime As Date
Public RunWhen As Double
Public Const cRunWhat = "Make_SGX_Txt" ' the name of the procedure to run
Dim FirstTime As Boolean
Sub ShellAndWait(ByVal PathName As String, Optional WindowState)
Dim hProg As Long
Dim hProcess As Long, ExitCode As Long
{shellandwait lines}
End Sub
Sub StartTimer()
Dim d As Date, m As Long
d = Now
mdtNextOnTime = Int(d) + TimeSerial(Hour(d), (Minute(d) \ 15 + 1) * 15, 0)
m = Minute(mdtNextOnTime) - Minute(d)
If m < 3 Then
mdtNextOnTime = mdtNextOnTime + TimeSerial(0, 15, 0)
End If
If FirstTime Then
If Time <= TimeSerial(12, 30, 0) Then
RunWhen = Date + TimeSerial(9, 0, 0)
Else
RunWhen = date + TimeSerial(14, 0, 0)
End If
Else
If Time TimeSerial(8, 44, 0) And Time <= TimeSerial(12, 30, 0) Or Time
TimeSerial(13, 44, 0) Then
RunWhen = mdtNextOnTime
End If
End If
Application.OnTime EarliestTime:=RunWhen, _
Procedu=cRunWhat, Schedule:=True
End Sub