sa7med wrote: ↑Mon Apr 23, 2018 1:38 pm
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.