Status cells sometimes getting stuck at placing

[ShaunWhite]

This isn't necessarily the absolute best way, but it works for me.

I have 2 buttons on my sheet, one runs the StartRecording sub, and the other runs the StopRecording sub

Code: Select all

Sub StartRecording()

    'Set Running indicator to Yes
    Sheets("Preferences").Range("RecordStatus") = "Yes"
            
    'Loop until not recording
    While Sheets("Data").Range("RecordStatus") = "Yes"
       Call RecordData
       Call WaitForNext
    Wend

End Sub

Sub StopRecording()

    'Set Running indicator to No
    Sheets("Preferences").Range("RecordStatus") = "No"
    
End Sub

Sub WaitForNext()
    Dim Start As Single
    
    PauseTime = Sheets("Preferences").Range("RecordInterval")    ' Set pause duration.
    Start = Timer    ' Set start time.
    'Loop until time has passed or Recording has been stopped
    While Timer < Start + PauseTime And Sheets("Data").Range("RecordStatus") = "Yes"
        DoEvents    ' Yield to other processes.
    Wend
    
End Sub

Sub RecordData()

    'Process and store data
End Sub

[sa7med]

This isn't necessarily the absolute best way, but it works for me.

I have 2 buttons on my sheet, one runs the StartRecording sub, and the other runs the StopRecording sub

Code: Select all

Sub StartRecording()

    'Set Running indicator to Yes
    Sheets("Preferences").Range("RecordStatus") = "Yes"
            
    'Loop until not recording
    While Sheets("Data").Range("RecordStatus") = "Yes"
       Call RecordData
       Call WaitForNext
    Wend

End Sub

Sub StopRecording()

    'Set Running indicator to No
    Sheets("Preferences").Range("RecordStatus") = "No"
    
End Sub

Sub WaitForNext()
    Dim Start As Single
    
    PauseTime = Sheets("Preferences").Range("RecordInterval")    ' Set pause duration.
    Start = Timer    ' Set start time.
    'Loop until time has passed or Recording has been stopped
    While Timer < Start + PauseTime And Sheets("Data").Range("RecordStatus") = "Yes"
        DoEvents    ' Yield to other processes.
    Wend
    
End Sub

Sub RecordData()

    'Process and store data
End Sub

Thanks Shaun, I've been trying to optimize things a bit (my sheets are taking 3-5) secs to refresh and this may help. Cheers.

[Wolf1877]

Thanks Shaun, I've been trying to optimize things a bit (my sheets are taking 3-5) secs to refresh and this may help. Cheers.

Shaun's code is a really interesting idea to improve performance by running a single macro continuously in a loop, but regarding your comment about your sheets taking 3-5 seconds to refresh that is not an issue I have and I use worksheets change to launch my code. I get up to about 8 refreshes per second mainly depending on how I have set Guardian up.

I use 2 instances of BA/Guardian/Excel each with all of the days UK/Ireland Race win markets - so 43 races today and 43 connected worksheets per BA/Guardian instance. The first instance of BA/Guardian/Excel is connected to all races from when I set it up at the start of a day through til each race is suspended - this handles my processing until 20 minutes before the off in each market and Guardian cycles through all 43 worksheets in about 3 or 4 seconds and gets faster as more races are finished.

The second instance of BA/Guardian/Excel is set up almost exactly the same except that I use the Restrict Refresh option in Guardian so a race only connects 1200 seconds / 20 mins until 15 mins after the scheduled start time. The second instance handles processing from 20 minutes out through the inplay period until the end of the race. The advantage of the 2nd instance is that only a few races are connected in Guardian hence the refresh cycle from BA/Guardian is faster as fewer sheets are connected which is more critical in the period just before and during a race. I get about 8 worksheet updates a second processed by my second instance with a 100ms connection rate.

If your stuff is taking 3-4 seconds to refresh you need to check if that is that due to he speed that Guardian is refreshing the sheets because it updates all 43 race worksheets in a cycle. If so you may be able to speed that up with Restrict Refresh. If the 3-4 seconds is really because your code is taking 3-4 seconds per sheet then then you need to optimise your code and excel calculations. Shaun's solution might help you to improve performance but it is unlikely to save you 3-4 seconds per sheet.

[ShaunWhite]

+1 Wolfy

I only used my way because i'm usually just grabbing key info at various intervals rather than needing every tiny tic. It's got it's own issues, mainly me not remembering to press start & stop

[sa7med]

If your stuff is taking 3-4 seconds to refresh you need to check if that is that due to he speed that Guardian is refreshing the sheets because it updates all 43 race worksheets in a cycle. If so you may be able to speed that up with Restrict Refresh. If the 3-4 seconds is really because your code is taking 3-4 seconds per sheet then then you need to optimise your code and excel calculations. Shaun's solution might help you to improve performance but it is unlikely to save you 3-4 seconds per sheet.

Thanks for your post Wolf. I meant that it takes about 3-5 secs to cycle through all the sheets (all the racing for the day), not just a single sheet (that would be a disaster.) It used to be better, around 2-3, but the sheets got a little bigger and more complex - mainly that i started recording more selections (15 instead of 10). Ive been looking for ways to optimize but im no excel guru and only recently learned how to code.

[Wolf1877]

Thanks for your post Wolf. I meant that it takes about 3-5 secs to cycle through all the sheets (all the racing for the day), not just a single sheet (that would be a disaster.) It used to be better, around 2-3, but the sheets got a little bigger and more complex - mainly that i started recording more selections (15 instead of 10). Ive been looking for ways to optimize but im no excel guru and only recently learned how to code.

OK good luck with that then. I guess you have got a few ideas for potential optimisation now.