Ontime problem; variable not passed from Workbook_Open to StartTim

[thomas]

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/co...=en-us-excel&lang=en&cr=US&sloc=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, _
Procedure:=cRunWhat, Schedule:=True

End Sub

 

[Bob Phillips]

How about adding a test that if it is opened between 9:00 and 9:15, run the
macro and schedule the next at 9:15.

 

[thomas]

Bob,

Based on the current code, it will run at 9.15. The 15 minute interval is
working beautifully. 'MsgBox "FirstTimer is " & FirstTime' placed in the
StartTimer before "If FirstTime Then" always return false regardless of the
time.

 

[Bob Phillips]

What about my suggestion?

 

[thomas]

Bob,

I suppose you mean to trigger the very first run at 9.15am. I will do that