Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timer Procedures
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, Procedu=cRunWhat, Schedule:=True End Sub Sub StopTimer() On Error Resume Next Application.OnTime EarliestTime:=RunWhen, Procedu=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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timer Procedures
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 "FinnBarr" wrote in message ... 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, Procedu=cRunWhat, Schedule:=True End Sub Sub StopTimer() On Error Resume Next Application.OnTime EarliestTime:=RunWhen, Procedu=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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timer Procedures
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" wrote: 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, Procedu=cRunWhat, Schedule:=True End Sub Sub StopTimer() On Error Resume Next Application.OnTime EarliestTime:=RunWhen, Procedu=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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timer Procedures
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timer Procedures
"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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timer Procedures
Change this code
from Sub StartTimer() RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds) Application.OnTime EarliestTime:=RunWhen, Procedu=cRunWhat, Schedule:=True End Sub to Sub StartTimer() RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds) Application.OnTime EarliestTime:=RunWhen, Procedu=StartTimer, Schedule:=True call cRunWhat End Sub "Peter T" wrote: "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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timer Procedures
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 "Joel" wrote in message ... Change this code from Sub StartTimer() RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds) Application.OnTime EarliestTime:=RunWhen, Procedu=cRunWhat, Schedule:=True End Sub to Sub StartTimer() RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds) Application.OnTime EarliestTime:=RunWhen, Procedu=StartTimer, Schedule:=True call cRunWhat End Sub "Peter T" wrote: "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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timer Procedures
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timer Procedures
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" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timer Procedures
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Get VBA procedures from DB | Excel Programming | |||
Calling procedures from within VBA | Excel Programming | |||
Stopping a Timer / Running a timer simultaneously on Excel | Excel Discussion (Misc queries) | |||
VBA Sub procedures | Excel Programming | |||
Using Sub-procedures w/in a function | Excel Programming |