OnTime Unusual Application

[Peter]

I run a spread sheet with DDE links that are updating continuously,
every 1-3 secs and more often I need to monitor a given criterea and
sample the data every 15 mins if the condition is valid then copy the
data to another sheet.

The OnTime function seems to fit this criterea to run the copy and
paste function, but I can not get the syntax to call the Copy Sub at
the designated times.

This will run 72 calls per day, in theory but how do you set out the
relationship of OnTime syntax and the Subroutine.

Any help appreciated... does anyone have experience of OnTime and DDE
as in relation to if it stops the DDE function as I have tested
Application.Wait functions but this stops the DDE link until it times
out.

 

[Bob Phillips]

Ontime is pretty straightforward. Check out
http://www.cpearson.com/excel/ontime.htm, and post your code if you still
have problems.

 

[Tom Ogilvy]

and to add, Ontime does not stop DDE during the "wait" period like Wait
does.

 

[Peter]

Ontime is pretty straightforward. Check out
http://www.cpearson.com/excel/ontime.htm, and post your code if you still
have problems.

 

[Peter]

Ontime is pretty straightforward. Check out
http://www.cpearson.com/excel/ontime.htm, and post your code if you still
have problems.

Thanks Bob,

I have the Timer running and it functions with DDE the only problem is
is runs 2 Tx for each call I have tried several ideas and I can not see
why it runs 2 tx.

I will attach the code there might be some thing obvious I am
missing....

Public RunWhen As Double
Public Const cRunIntervalSeconds = 90 '15 Minutes
Public Const cRunWhat = "LOGGER"

Sub Timer()
' Keyboard Shortcut: Ctrl+Shift+Z
'MsgBox "Timer Called"
RunWhen = Now() + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
schedule:=True
'MsgBox "Timer Ran"
End Sub

Sub LOGGER()
' LOGG Macro
' Keyboard Shortcut: Ctrl+Shift+L
Sheets("Log").Select
Dim emptyRow
If Sheets("LOG").Range("A1").Value = True Then
'Data
'Calculate the row number of the first free cell in our logging sheet
emptyRow = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Sheets("MR Count").Range("A3:BH3").Copy
Sheets("Log").Select
Cells(emptyRow, 2).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
'Time
'The line below logs the time of the entry in the log in column A
Cells(emptyRow, 1).Value = Now()
End If
Sheets("Log").Select
Range("B1").Select
End If
'Resets Timer for next logg in 15 mins
Timer
End Sub