Run an Excel code every 15 minutes of the clock

[thomas]

Right now I have a routine based on CPearson's OnTime running on my Excel.
But it does not really meet my requirement. It runs every 15 minutes of the
clock only if I start Excel before 8 am. If I start my Excel at 10:07, the
module will run at 10:22 followed by 10:37, etc.

I need my Excel module to run at every 15 minutes of the clock. If I start
my excel at 10:07, I want the module to fire up initially at 10.15 followed
by 10.30, 10.45, etc.

My knowledge of excel programming is rather basic.

 

[Francy]

Open excel
Run this macro every 50 seconds instead of every 15 min.


Dim myres As Integer
myres = Mid(Time, 4, 2) Mod 15
If myres = 0 Then
MsgBox "DO WHAT U NEED HERE!"
End If

Best regards,

Frà

 

[Gary''s Student]

Split your code into two parts:

1. Adjust the first OnTime to take you to the next 15 minute point, So if
NOW shows 10:07, set the first OnTIme to take you to 10:15.

2. Set the remaining OnTimes for even 15 minute intervals

 

[Peter T]

Private mdtNextOnTime as Date

Dim d as Date
d = Now
mdtNextOnTime = Int(d) + TimeSerial(Hour(d), (Minute(d) \ 15 + 1) * 15, 0)

Regards,
Peter T

 

[Peter T]

I guess if next qtr hour is only a few seconds or minutes later that might
be too soon for the first OnTime macro. Following adds another 15 minutes if
the next qtr hour is within 7 minutes

Dim d As Date
Dim m As Long
d = Now
mdtNextOnTime = Int(d) + TimeSerial(Hour(d), (Minute(d) \ 15 + 1) * 15, 0)
m = Minute(mdtNextOnTime ) - Minute(d)
If m < 7 Then
mdtNextOnTime = mdtNextOnTime + TimeSerial(0, 15, 0)
End If

Use the above for the first Ontime macro and for each subsequent OnTime
macro

Regards,
Peter T

 

[thomas]

Peter,

The "Now" got flagged for "Compile Error: Invalid Outside Procedure".

But how do I incorporate your code into mine as follows:

Option Explicit
Private Declare Function GetProcessVersion Lib "kernel32" ( _
ByVal ProcessID As Long) As Long
Private Declare Function GetCurrentProcessId Lib "kernel32" () As Long
Private Declare Function CloseHandle Lib "Kernel32.dll" (ByVal Handle As
Long) As Long
Private Declare Function OpenProcess Lib "Kernel32.dll" (ByVal
dwDesiredAccessas As Long, ByVal bInheritHandle As Long, ByVal dwProcId As
Long) As Long
Private Declare Function EnumProcesses Lib "PSAPI.DLL" (ByRef lpidProcess As
Long, ByVal cb As Long, ByRef cbNeeded As Long) As Long
Private Declare Function GetModuleFileNameExA Lib "PSAPI.DLL" (ByVal
hProcess As Long, ByVal hModule As Long, ByVal ModuleName As String, ByVal
nSize As Long) As Long
Private Declare Function EnumProcessModules Lib "PSAPI.DLL" (ByVal hProcess
As Long, ByRef lphModule As Long, ByVal cb As Long, ByRef cbNeeded As Long)
As Long
Public RunWhen As Double
Public Const cRunIntervalSeconds = 900
Public Const cRunWhat = "Make_SGX_Txt" ' the name of the procedure to run
Dim FirstTime As Boolean
Sub StartTimer()

If FirstTime Then
RunWhen = Date + TimeSerial(8, 45, 0)
Else
If Time > TimeSerial(8, 45, 0) And Time <= TimeSerial(12, 30, 0) Or Time

TimeSerial(13, 59, 0) And Time < TimeSerial(17, 10, 0) Then

RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
End If
End If

Application.OnTime EarliestTime:=RunWhen, _
Procedure:=cRunWhat, Schedule:=True

End Sub

Sub Make_SGX_Txt()

Dim ProcID As Long
Dim Version As Long
Dim LastErr As Long
Dim ThisProcID As Long
ProcID = 1092

{ what I want to do }



Thomas

 

[Peter T]

Indeed the Now function is "Invalid Outside Procedure", you need to put it
inside a procedure. Eg

Sub abc()
dim dt as Date
dt = Now
Msgbox dt
End sub

For your future reference, The top of a module is the "Declarations" area.
This is where API functions are declared, together with module or global
level variables and constants. Everything else belongs in a procedure (Sub,
Function, etc).

It's normally best to store the time the Onime macro scheduled to run in a
module level variable is so it can be cancelled if/as necessary, which you
can do by calling the OnTime as normal but with the argument Schedule:=False

It's not clear when you do / don't want to run the OnTime, gaps (not
lunchtime?) the first time and when to cancel it altogether. Try and
describe.

Regards,
Peter T

 

[thomas]

Peter,


Thumbs up to you.


Thomas