Ontime problem; variable not passed from Workbook_Open to StartTim

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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/community/en-us/default.mspx?dg=microsoft.public.excel.programming&tid=ca430c34-bd6b-411c-bee4-8e4ba3c23259&cat=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


.

Relevant Pages