Refresh rate and VBA

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
rain91
Posts: 112
Joined: Tue Feb 07, 2017 7:11 pm

Hi,

i set 20ms refresh rate, but if run a VBA macro, the refresh rate is ~500ms-1sec, its not good for me. If i stop VBA macro, its ok. (But i dont know without VBA run my strategy: if i use IF function in excel, i get multiple BETS in first sec, but with VBA it is ok).
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

Sounds like your macro could be written more efficiently. What code are you using to look for cells that change? The std Worksheet_Change proceedure is terrible for consuming cpu time.
rain91
Posts: 112
Joined: Tue Feb 07, 2017 7:11 pm

This is my macro, but if i dont use FOR-NEXT, only "IF", the refresh rate is as slow.

Private Sub Worksheet_Calculate()

Dim i As Integer
Application.EnableEvents = False
For i = 9 To 67 Step 2

'odds
Cells(i, 13) = "200"

'stake
Cells(i, 14) = "0.1"

'lay parancs
If Cells(i, 43) = "LAY" Then
Cells(i, 12) = "LAY"
End If

'clear cells
If Range("H" & 1).Value = "Suspended" Then
Cells(i, 12).ClearContents
Cells(i, 15).ClearContents
Cells(6, 15).ClearContents
Cells(i + 1, 32).ClearContents
End If

Next i
Application.EnableEvents = True
End Sub
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

The Worksheet_Calculate event is triggered everytime the worksheet is Recalcuated, basically after any cell anywhere has changed. It's being called 1000s of times unnecessarily. You'de be better off having your own timed event routine and checking just the cells you are interested in.

This may require 2 'buttons' to start and stop the process but that's quite useful anyway. Here's a bit of a code that might help....DONT just copy it and expect it to work, it's just for guidance. And pls excuse the untidy, uncommented & slightly old fashioned code. Only I usually see it ;)

The VBA Timer function has a resultion of 1/256s.

I use 2 cells to control it....
Sheets("Preferences").Range("RecordStatus") = "No" '(or "Yes")
Sheets("Preferences").Range("RecordInterval") ' Set pause duration.

Code: Select all


Sub StartProc()

    Sheets( "Preferences").Range("RecordStatus") = "Yes"
        
    While Sheets( "Preferences").Range("RecordStatus") = "Yes" And _
         Sheets(DestSheet).Range("RecordInterval") > 0
         DoEvents
         Call ProcessChangedCells
         Call WaitForNext
    Wend
End Sub

Sub StopProc()

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

Sub WaitForNext()
    
    Dim PauseTime As Single, Start As Single
    
    PauseTime = Sheets("Preferences").Range("RecordInterval")    ' Set duration.
    Start = Timer    ' Set start time.
    Do While Timer < Start + PauseTime And _
         Sheets("Preferences").Range("RecordStatus") = "Yes" And +
         Sheets("Preferences").Range("RecordInterval") > 0
         DoEvents    ' Yield to other processes.
    Loop
End Sub

Sub ProcessChangedCells()

	'Main code goes here
	'Compare cell values with what they were the last time this routine was called.
	
End Sub
   

rain91
Posts: 112
Joined: Tue Feb 07, 2017 7:11 pm

Big thanks!

It work. If i write to RecordInterval cell "2", than its 2/256 sec=0,078?
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

Glad it helped. Not sure about the settings it's a long time since I used it. I think 2 is 2s but you might need to have a play.

I used a 'Single' as the timer variable type so I think it must have decimals. So 2s would be 2/256?

I should know this but for now I can't remember, and I'm not on my machine now to check. I hate posting code, I just want to keep making changes because I just did a quick edit from something slightly different.
dmbusa
Posts: 274
Joined: Wed Jan 09, 2013 8:33 am

And thank you from me as you mentioning the the worksheet_calculate event I have been able to fix an annoying issue with the cursor constantly flashing.
spreadbetting
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

The problem with your code is that it's doing so many unnecessary writes to the sheet, at each refresh it'd be sticking in the stakes and odds in columns m & n for 60 odds rows.

If Shaun's code has sorted it for you that's good otherwise look at turning calculations to manual when the code runs and be more efficient in only writing to cells when needed. Generally you'd stick your cell area to memory ,write any changes to the cells in memory then write the area back to the sheet in one go rather than each cell individually. Plus look to exit the routine early if there's no need to run it fully or even once it's fired once per race.
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

I'll second all of that.

Reading and writing cells is the slow part so you need to do it as little as possible, even if that sometimes means having what seems like a lot more lines of code to execute.
rain91
Posts: 112
Joined: Tue Feb 07, 2017 7:11 pm

Hi ShaunWhite,

it works good, but:

i will now run it in multiple Bet Angel sheets, and it doesnt work.

Only work in the last started active sheet. So i connect my spreadsheet to Bet Angel, i start macro in "Bet Angel", start macro in "Bet Angel (2)", start macro in "Bet Angel (3)". Macro work only in "Bet Angel (3)" :/
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

rain91 wrote:
Mon Apr 23, 2018 5:53 pm
Hi ShaunWhite,

it works good, but:

i will now run it in multiple Bet Angel sheets, and it doesnt work.

Only work in the last started active sheet. So i connect my spreadsheet to Bet Angel, i start macro in "Bet Angel", start macro in "Bet Angel (2)", start macro in "Bet Angel (3)". Macro work only in "Bet Angel (3)" :/
Maybe have one routine that looks at all sheets.
I have one start & stop button on a dashboard page ...and the code finds the sheets I'm interested in.
rain91
Posts: 112
Joined: Tue Feb 07, 2017 7:11 pm

Yes, i placed for all 3 worksheet Start/Stop button. If i connect to BA, i start it on all 3 worksheet, but VBA run only on active (what i am seeing).

With worksheet calculate is good in multiple spreadhseets, but too slow the refresh rate (about 1sec), the computer isnt strong. (i5 3470 cpu).
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”