"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


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