Best macro triggers

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
Luke.B
Posts: 100
Joined: Wed Dec 25, 2019 1:28 pm
Location: Australia

I'm sure we have made many macros that once we test find they go in loops.

What are the key triggers people are using to run macros without looping and then re setting for the next auto bind.

I am mainly after one to record latest back lay 5 sec before the off
And then one to run once an 'actual sp' odds are shown.
User avatar
ODPaul82
Posts: 683
Joined: Sun May 08, 2011 6:32 am
Location: Digswell Herts

I have a private level date variable in each worksheet mdteLastStored As Date
I then call a global function from the worksheet to process if the time on the worksheet is greater than the last stored date.

You could modify the routine below to check to see if F4 is within a certain time range to trigger off a write to the worksheet to store the back/lay values and reset whereever you have the Actual SP stored

In any write action that you do to the worksheet you want to do to Application.EnableEvents = False
If you don't do that then the write action that you perform to the worksheet will immediately trigger off the worksheet_change event when it hasn't reached the end of the routine. Remember to turn it back on at the end

Code: Select all

Option Explicit

Private mdteLastStored As Date

Private Sub Worksheet_Change(ByVal Target As Range)
    If IsNumeric(Me.Range("F4")) Then
        ' toggled through?
        If Me.Range("F4") > mdteLastStored Then
            gPriceUpdate "BA Data - 1", Me.Range("F4"), Me
        End If
        mdteLastStored = Me.Range("F4")
    End If
End Sub
Luke.B
Posts: 100
Joined: Wed Dec 25, 2019 1:28 pm
Location: Australia

Hi OD,

Thanks for the reply. I keep re reading but i dont have the knowledge to work it out. Not sure if you want to try and explain further though.
Ideally i am after
take all the back offers and all the lay offers and all the proposed sp prices and insert 4 rows and transpose that data on them and then when in play paste the actual sp.

I am then after some mechanism that then stops this from looping once done. And then ready for next race.

Sorry again i just dont know enough about vba to transfer that to your example below.
The very least if i can get your layman thoughts on a trigger and how to stop looping i can just record the macro of inserting rows and data.
rain91
Posts: 112
Joined: Tue Feb 07, 2017 7:11 pm

Im amateur, i used for next cycle, and now simple IF methots for the all cells. i have too much rows in vba. I have a 32 tabs excel, because I want to be there on every race that day. If VBA runs, the refresh rate is very slow (4-5 seconds). If the market goes inplay, it is terrible. if I stop the VBA, the refresh rate will be good, so vba will cause the slowdown.
if I delete all the rows from VBA and leave only the "Application.Events = True and Application.Events = False" part, it will still remain slow. could it be caused by it? can this be omitted?
User avatar
ODPaul82
Posts: 683
Joined: Sun May 08, 2011 6:32 am
Location: Digswell Herts

Apologies in delay in response, I was taking a break after US open plus moving house next week.

For both of you I've attached a file that I use in production which shows various ways of storing data onto different worksheets, it stores data at 10 second increments to BA data worksheets. I do various calculations on those and then set a stored value for a BA rule to do further processing. I've removed the key parts that I personally use for the calculations but the code should help for the pair of you.

For you in particular rain91, having 32 tabs open is a bit ridiculous in one workbook, consider having seperate instances of BA with seperate instances of Excel operating for different timeframes. The BA refresh rate for guardian will always limit you if you have that amount of markets wanting less than 1 second refresh rates.

It's more for picking apart the code yourselves to learn rather than me giving you the absolute "do this, do that" as I'd rather people learn themselves
BA - Sample for low latency.xls
You do not have the required permissions to view the files attached to this post.
rain91
Posts: 112
Joined: Tue Feb 07, 2017 7:11 pm

first i just want to resolve the status cell clearing.
The logic is simple. If O9, O11 etc. = "PLACED", clear the cells.

but where and how do I put it?

Direct to Object? Worksheet.Calculate or Worksheet.Change?
Or call a macro from object, and write the code in module?
Between Application.Enable.Events false / true, or Application.Screenupdating false / true?

i have seen each solution in sample files from this forum and i don't know which one is the best, fastest.
Wolf1877
Posts: 367
Joined: Fri Sep 08, 2017 10:59 am

rain91 wrote:
Sat Sep 11, 2021 6:51 am
Im amateur, i used for next cycle, and now simple IF methots for the all cells. i have too much rows in vba. I have a 32 tabs excel, because I want to be there on every race that day. If VBA runs, the refresh rate is very slow (4-5 seconds). If the market goes inplay, it is terrible. if I stop the VBA, the refresh rate will be good, so vba will cause the slowdown.
if I delete all the rows from VBA and leave only the "Application.Events = True and Application.Events = False" part, it will still remain slow. could it be caused by it? can this be omitted?
Only 32 tabs in excel? Dont worry about it I have 80 for bank holidays and and it copes fine on an i5-8400 with plenty fo RAM.
Basically I have 2 BA sessions,
1 with restrict refresh off
1 with restrict refresh on scanning markets under 1200 seconds including inplay

On 32 markets I'd expect to average frame refresh at under 1 second per market with restrict refresh off and about 6 to 8 BA frame refreshes a second with restriuct refresh on (< 1200 seconds)
BA cycles through the Guardian markets refreshing frames. BA also has a bit of a stutter where it refreshes about 40 markets and pauses for half a second or so before resuming the refresh.

In order to keep your VBA as efficient as possible you need to:
(a) write your VBA to run and terminate as quickly as possible. I have observed that BA completely skips excel frame refresh if VBA is still running, so slow inefficient VBA will play havock with your frame refresh rate because frames will be skipped.
(b) under no circumstances have any excel cell formulaes running alongside your VBA. Let BA and your VBA code populate the cells. Cell formulae are the devils work though I understand their attraction to non developers.

The code below calls VBA subroutine LogData only when cells in block C2:C6 are refreshed.
This is because BA refreshes 6 or 7 cell range batches per frame refresh and C2:C6 are the last cell range batch to be refreshed.
So basically having this code means that my main VBA code in my Logdata subroutine runs only once per BA frame refresh instead of 6 or 7 times per frame refresh thus increasing efficiency.
vba1.PNG
You do not have the required permissions to view the files attached to this post.
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”