Ontime problem; variable not passed from Workbook_Open to StartTim
 

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

Ontime problem; variable not passed from Workbook_Open to StartTim
 

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.

--
__________________________________
HTH

Bob

"thomas" wrote in message
...
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

Ontime problem; variable not passed from Workbook_Open to Star
 

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" wrote:

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.

--

Ontime problem; variable not passed from Workbook_Open to Star
 

What about my suggestion?

--
__________________________________
HTH

Bob

"thomas" wrote in message
...
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" wrote:

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.

--

Ontime problem; variable not passed from Workbook_Open to Star
 

Bob,

I suppose you mean to trigger the very first run at 9.15am. I will do that
on Monday as it is office computer. Don't leave me.

"Bob Phillips" wrote:

What about my suggestion?