Excel slow to update worksheets

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
leerudd
Posts: 28
Joined: Tue Dec 01, 2020 1:29 am

Hi all,

I created a worksheet and it works pretty good except a few problems.
I need it to do more, its a bit of a long one.
15 mins before a horse race i run a macro that copies all the current back prices to a space. This is to reference against to find drifters. With IF statements ive managed to automate the reference and bet. Brilliant so far.

Anyway my problem is that while in running the prices all change and other bets are paced that i dont want. Also when the sheet updates to the next race if the prices on the new race still match up then new bets are placed (Nightmare).

To get around this i created 40 tabs or workbook pages so that when a race is over is doesnt need to change to a next race, solving some of the problem. This is also seems to many tabs to update in a efficient manner sometimes minutes pass without data refreshing.

Here is what i would love to be able to do.

1.Automate the macro 15 mins before every race meaning i cant forget.
2. Fix the problem with either worksheets not updating in time or stopping the bets when in play starts.

Im basically trying to achieve full automation, but it is trying.

Thanks if anyone can help with a solution or workaround
Anbell
Posts: 2004
Joined: Fri Apr 05, 2019 2:31 am

leerudd wrote:
Wed Apr 21, 2021 10:10 pm
Hi all,

I created a worksheet and it works pretty good except a few problems.
I need it to do more, its a bit of a long one.
15 mins before a horse race i run a macro that copies all the current back prices to a space. This is to reference against to find drifters. With IF statements ive managed to automate the reference and bet. Brilliant so far.

Anyway my problem is that while in running the prices all change and other bets are paced that i dont want. Also when the sheet updates to the next race if the prices on the new race still match up then new bets are placed (Nightmare).

To get around this i created 40 tabs or workbook pages so that when a race is over is doesnt need to change to a next race, solving some of the problem. This is also seems to many tabs to update in a efficient manner sometimes minutes pass without data refreshing.

Here is what i would love to be able to do.

1.Automate the macro 15 mins before every race meaning i cant forget.
2. Fix the problem with either worksheets not updating in time or stopping the bets when in play starts.

Im basically trying to achieve full automation, but it is trying.

Thanks if anyone can help with a solution or workaround
This would all be very easy to do without excel.
leerudd
Posts: 28
Joined: Tue Dec 01, 2020 1:29 am

The problem there is I only have betdaq ba and it doesn't have the features of the Betfair version.
Worst thing is I know it's possible and it's close but so far
User avatar
Silverthorn
Posts: 26
Joined: Sat Mar 13, 2021 2:47 pm
Location: Australia

Something like this should work.

First you need to set a trigger. It's probably easiest is to use a cell. That way you don't have to mess with numeric time.
Just copy the count down timer from F4 from your sheet and paste it to somewhere convenient like G4 so the formatting is the same. Then change G4 to 0:15:00.

VBA Code run your Macro..

Right click the sheet tab that you want the macro to run on and select view code. Put the following in the code for the sheet.

Private Sub Time_Trigger()
If .Range("F4") = .Range("G4") Then Run "MyMacro"
End If
End Sub

You can clear the sheet using similar code but using the suspend notice in cell H1to trigger a macro to clear the required cells.

If .Range("H1") ="Suspended" Then Run "MyMacro"

That should allow you to simplify the workbook and allow it to run faster. The more functionality you put in VBA rather than formulas the faster the workbook will update. However be aware that the workbook does not update when a Macro is running so it's a trade off for performance.
leerudd
Posts: 28
Joined: Tue Dec 01, 2020 1:29 am

Hi,
Tried this and it doesnt Seem to work. Think i need a = or less than ?

Make any sense ?

Thanks
User avatar
ODPaul82
Posts: 683
Joined: Sun May 08, 2011 6:32 am
Location: Digswell Herts

There isn't a Time_Trigger event in Excel hence why it wouldn't work.
You don't need to put Run before a macro name.
Also the statement VBA is faster than formulas is categorically wrong. Formulas within Excel are derived from compiled C++ code whereas VBA is interpreted and is massively slower than formulas.

Try this within the worksheet code

Code: Select all


Private Sub Worksheet_Change(ByVal Target As Range)
    
    Const TIMER_CELL As String = "$F$4"
    
    ' Negative times aren't numeric
    If IsNumeric(Me.Range(TIMER_CELL).Value) Then
    
        ' Ensure not in-play
        If Me.Range("G1").Value <> "In-Play" Then
        
            If TimeValue(CDate(Me.Range(TIMER_CELL).Value)) <= TimeSerial(0, 15, 0) Then
            
                Application.EnableEvents = False
                
                ' ...
                ' TRIGGER YOUR CODE IN HERE
                ' ...
                
                Application.EnableEvents = True
        
        End If
    
    End If
    

End Sub


Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”