Slow excel with 30 tabs: replace vba codes with excel function

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

I have experienced if i use a lot of VBA code, the refresh rate in excel will slow down a lot. In Guardian i set it for 20ms, but if vba is running, it will update every 2-3 seconds. This is a big problem, when the race goes inplay.
I have a pre race / inplay excel for horse racing with 30 tabs. So, i need to use more excel functions and minimal vba code (vba only for clearing status cells).

But:
in this case, i get more double bets. The condition for the back bet:
- unmatched back bets = 0.
- status <> "PLACING" / "PLACED"

The system place a back bet. The status is "PLACED", VBA clear it. But the unmatched / matched cells update minimally delayed, my excel place the second back bet, because he sees that I have no bet placed yet.

how can i avoid this by using excel functions? I solved with vba that the next bet can only go if the value of the matched / unmatched cells has changed. But like i said, i want to avoid using vba code.
CallumPerry
Posts: 575
Joined: Wed Apr 19, 2017 5:12 pm
Location: Wolverhampton

I'm confused?? VBA is much quicker than using functions?
User avatar
ODPaul82
Posts: 683
Joined: Sun May 08, 2011 6:32 am
Location: Digswell Herts

Functions are quicker than VBA as they're compiled down to machine code (C++ based for the vast majority of the actual language used for it, whether they've gone to C# for bits of it I don't know)
VBA is interpreted so compiled internal functions will always be quicker

Slowness is experienced where users are
  • Looping where they don't need to
    Not understanding how event cascading works
    Recursion
From everything you have posted rain90 you are more than likely constantly triggering off bits of code that will recursively call back on themselves
When an event triggers in Excel into BA it is not triggering once, it is triggering multiple times across multiple cells.
See my attached file in the other thread you've posted in for a sample of best practice of handling events
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”