Timer Procedures

[FinnBarr]

Hi All

Im developing a spreadsheet that captures live feeds. I want to
capture the data at one second intervals max and record different
markets to a separate worksheet. I've got the basics sorted, I just
need to get a repeating timer to record and its set.

Ive read through some of the posts for the above and came acrss Chip
Pearsons site detailing two alternative timer procedures. I tried them
both by copying and pasting from the web page.

I tried the Windows API call first as it can be set to milliseconds.
It was working nicely for 20 minutes but, as Chip points out, its
unstable and caused my machine to fall over, losing all the recorded
data in the process. But at least I know that the rest of my code
works ok.

I tried the OnTime procedure next and ran it from the VBE editor but
it only makes one call and then stops. I assigned it to a menu item
and that doesnt even make call. I cant understand why it wont work.
Help please.

Below is a copy of the code which, as you can see has been lifted
straight from Chips page.

Public RunWhen As Double
Public Const cRunIntervalSeconds = 1 ' one second
Public Const cRunWhat = "UpdateMarkets" ' the name of the procedure
to run

Sub StartTimer()

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

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

End Sub

Sub StopTimer()

On Error Resume Next

Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat,
Schedule:=False

End Sub

Sub UpdateMarkets()

' Ive omitted a select case structure here (which
determines how many markets require data capture)_
to save space.

Call StartTimer

End Sub

 

[JLGWhiz]

Have you tried this one. It works in tenths of a second or whole seconds.

s = Timer + 0.5
Do While Timer < s
DoEvents
Loop

 

[Joel]

First I think you have over simplifie your prblem based on you previous
posting. You said you have a Betfair API that you are calling and you are
crashing after a period of time.

I think you need to determine if your problem is the the Betfair prgram or
your Ontimer code. I would elimiate the Betfair call and see if your code
still crashes.

I don't know how much data is being returned every time you call the Betfair
program. It would be best if you only return one item each time you call
betfair so you are not returning duplicate data with each call. Minimize the
amount of data you are returning to minimize potential problems you may have
when large amount of data is returned. If you are returning large amount of
data before you start the On Timer code get all the previous data you need
and then hae the On Timer route only get the new data.

 

[FinnBarr]

Joel

Thanks for getting back to me.

The Betfair API is a proprietary software program which comes bundled
with an excel interface and spreadhseet that replicates the main
Betfair site. It can poll their server as often as you like but their
is a throttle limit of 20 calls/sec after which they charge you.
Needless to say I dont exceed this limit! The screen refresh can be
set to various levels. I usually have it at 500 milliseconds. Im only
working with the spreadsheet. This doesnt make the calls to Betfair,
the API program does. The spreadsheet is continually refreshed by the
API. I assume that it just dumps the data into excel rather than excel
calling for the data but I may be wrong. I dont have access to the API
code. Even if I did I probably wouldnt comprehend it!

The OnTime procedure doesnt crash the system, the Windows API timer
does. According to Chip Pearson, "If the code executed by the timer
changes a cell value, and you are presently in edit mode in Excel
(e.g., entering data in a cell), Excel will likely crash completely
and you will lose all unsaved work. Use Windows timers with caution."
As my code changes cell values I had anticipated it failing. However,
it did work for 20 minutes before it failed. Im a complete novice as I
said, but surely that indicates that the rest of the code functions
ok. Doesnt it?

The OnTime just doesnt work properly! Even if I disconnect the
spreadhseet from the API it doesnt work properly. It only makes one
call but doesnt trigger itself again after one second as it should.
Given what I said above, and its isolation from the Betafir API surely
this indicates that the fault is with the OnTime code.

My code sets up a sheet for each runner and then links cells in Row A
back to the main sheet so that they display the latest data. From
there the timer function is set to copy the range and paste it into
the next empty row. This all worked ok with the Windows API timer even
if the screen jumped about a bit despite screenupdating switched off.

 

[Peter T]

"If the code executed by the timer

changes a cell value, and you are presently in edit mode in Excel
(e.g., entering data in a cell), Excel will likely crash completely
and you will lose all unsaved work.

That's not my experience using the AP timer. Also I'm not sure that code
that changes a cell puts it into edit mode, even momentarily. Edit mode to
me means when the cell contents are in the process of being edited, after
say pressing F2 or manually typing into a cell. In the timer loop you can
check the app.Ready state and abort the current loop if false. (I think
app.Ready was introducing in XL2002, it's n/a in XL2000)

What you *absolutely must not do* is edit any VBA code in the project while
the code is running, particularly in the module that contains the timer.
Unless you are sure your users will not do that best not to use it.

The OnTime just doesnt work properly! Even if I disconnect the
spreadhseet from the API it doesnt work properly. It only makes one
call but doesnt trigger itself again after one second as it should.

If one second intervals are OK I would strongly recommend you use the OnTIme
method. It *is* possible to ensure the OnTime works repeatedly with the
schedule method until cleared by sending schedule:=false. In a glance of the
code in your OP are you resetting RunWhen with the value of the next time
you want the OnTime to run, eg
RunNow = Now + 1 / (24& * 60 * 60) ' one second later
If user resets the project (presses the stop button) the RunNow variable
will be destroyed and the OnTIme would of course fail to run.

Regards,
Peter T

 

[Joel]

Change this code

from
Sub StartTimer()

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

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

End Sub


to

Sub StartTimer()

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

Application.OnTime EarliestTime:=RunWhen, Procedure:=StartTimer,
Schedule:=True

call cRunWhat

End Sub

 

[Peter T]

Oh yes, apart from updating the new time I forgot to add that the OnTime
needs to be called each time.

If the called macro could take longer than the interval increment, better to
change the order of things slightly, IOW call the macro first, when all done
and the code has returned then update the new time and call the OnTime.

Be careful of incrementing with 0.5 seconds or less (if that's the
intention), or could end up with no increment after Now at all.

Regards,
Peter T

 

[FinnBarr]

Joel/Peter

Just tried your suggestion but it didnt like it. The OnTime calls
itself, wont even make one pass over the data before it grinds to a
halt and displays an error msg.

My brain hurts!!! Ive tried calling the main procedure first so that
the timer is called after the first update. Theoretically that should
give me two updates but I still get one. Ive tried extending the time
from 1 to 5 seconds but that doesnt work either.

If I cant find a solution or a suitable workaround Im considering
using VB to control excel as it comes with a preinstalled timer
function.

Regards

Finnbarr

 

[Peter T]

Try the following on its own

Private mNextTime As Double
Private mCounter As Long ' for test routine

Sub OnTimer()
mNextTime = Now + (TimeSerial(0, 0, 1))
Application.OnTime mNextTime, "myMacro"
End Sub

Sub StopTimer()
' call StopTimer in the workbook's close event or antime to stop the OnTime

If mNextTime Then Application.OnTime mNextTime, "myMacro",
Schedule:=False
mNextTime = 0

End Sub

Sub myMacro()

''' do stuff
mCounter = mCounter + 1
Cells(mCounter, 1) = Format(mNextTime, "hh:mm:ss")
''' do stuff done

OnTimer

End Sub

Regards,
Peter T

 

[FinnBarr]

Hi guys

Just thought Id let you know that this problem is solved. A post on
another forum managed to sort the problem out for me. Thanks for all
your help though. Much obliged.