Refreshing prices automatically within the spreadsheet

[smug_punter]

First off what an excellent piece of work that spreadsheet is and a massive thanks to its creator.

Having built my own "bots" over the past few years but nothing for the past five years it's great to have such an easy to use template in place with which to once again start developing an automated system to bet my own price forecasts against.

However, whilst the VBA for Excel book is on it's way to me in the post, can anyone out there help me with a method in which the spreadsheet can automatically refresh the prices using a timer? I'm assuming that it's possible and ideally want a price refresh every ten or so seconds.

Anyway, again massive thanks for the the spreadsheet.

Best wishes
Alex Deacon
www.alexanderdeacon.com

[ChamoneHuHu]

I use the Application.OnTime call to schedule data refresh and all calculation activity on my sheets. Each scheduled call's last action is to schedule the next call.

Have a look at the Excel/VBA help for it, it's pretty self explanatory.

Would love to hear from anyone who uses what they beleive might be a better method.

[smug_punter]

Cheers. Application.OnTime seems to work nicely.

[barroy]

Old thread I know, but I was wondering if someone could explain this to me a bit, being a novice n'all...

Where exactly do you use Application.OnTime to make the prices refresh every 2 secs? I've brought up the code window, I assume that, somewhere between Public Sub RefreshMarket () and End Sub, I need to put:

Application.OnTime Now + TimeValue("00:00:02"), "RefreshMarket"

I've tried a few things but I'm not getting anywhere. The websites I've looked up for help on this are pretty esoteric and don't really help me very much.

Any help would be greatly appreciated, thanks.

[fastforward]

I use Wait as I had a few problems with Application on time. Just me
wait 100 ' milliseconds

Private Declare Function timeGetTime Lib "winmm.dll" () As Long


Public Sub wait(ByVal milliseconds As Long)
Dim startTime, StopTime As Long
startTime = timeGetTime
StopTime = startTime + milliseconds
DoEvents

Do Until timeGetTime > StopTime
DoEvents
Loop
End Sub

[hollywood17]

I use Wait as I had a few problems with Application on time. Just me
wait 100 ' milliseconds

Private Declare Function timeGetTime Lib "winmm.dll" () As Long


Public Sub wait(ByVal milliseconds As Long)
Dim startTime, StopTime As Long
startTime = timeGetTime
StopTime = startTime + milliseconds
DoEvents

Do Until timeGetTime > StopTime
DoEvents
Loop
End Sub

Excuse my bad English and my poor familiarity with Excel. I wanted to ask, but the one variable that you entered in the macro where I enter?
thanks for your attention