Closing all unmatched bets In Play after 5 seconds using VBA

[technician]

Hi all

I'm having issues getting the attached script to work

I'm using an Excel spreadsheet to scalp the market when not in play. Now and then some offset bets will go in play even though I have bets set to cancel at the off. I guess they're being matched a split second before the off and the offset goes in play when the market is unsuspended

Anyway, I would like any unmatched bets to stay In Play for FIVE seconds ONLY and then CANCEL-ALL

I have tried to initiate this in my Trigger column cell formula, but the CANCEL-ALL will only work if there's a bet id in the 'Bet ref' column, and often there isn't..

So I'm trying to do it with VBA and with mixed success.

The script I've written seems very clunky, I especially don't like the fact it goes into a 'not responding' loop for five seconds during which time the Excel spreadsheet freezes and isn't updated with the in-running prices.

Furthermore, after the race has finished and Gruss has auto-selected the next market, I need a way to reset my spreadsheet to the original scalping mode, which means repopulating the Trigger column (Q5:Q55) with my original formula. I think I know how to do this but I'm not confident my script is fundamentally sound to start with. I'm hoping one of you guys will look at it and tell me that's not the way to approach this problem, try this solution instead!

I'm not even sure if I have my sub routine in the correct place which is why I've attached the screen shot

Any assistance or advice would be much appreciated

cheers

[GaryRussell]

Here is the code I would use. I always suggest to put the code in the Worksheet_Change event and also only run directly after odds columns changed because this is when the triggers are read (Target.Columns=16). It doesn't wait for the time to elapse in a loop, instead it checks it every refresh so that the user interface is never interrupted. It also resets everything when a new market is selected. Also note it disables events while updating the trigger cells to prevent it triggering another change event while updating them. Finally it is better to use CANCEL-ALL-MARKET as it does it one API request.

Code: Select all

Dim startTime As Single
Dim endTime As Single
Dim elapsedTime As Single
Dim inPlayFlag As Boolean
Dim cancelledFlag As Boolean
Dim inPlayStatus As String
Dim currentMarket As String

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Columns.count = 16 Then
        If [A1] <> currentMarket Then
            currentMarket = [A1]
            inPlayFlag = False
            cancelledFlag = False
            startTime = 0
            endTime = 0
        End If
        inPlayStatus = [E2]
        If inPlayStatus = "In Play" And Not inPlayFlag Then
            inPlayFlag = True
            startTime = Timer()
        Else
            If startTime <> 0 And Not cancelledFlag Then
                endTime = Timer()
                elapsedTime = endTime - startTime
                If elapsedTime >= 5 Then
                    cancelledFlag = True
                    Application.EnableEvents = False
                    [T5] = "DUMMY"
                    [Q5] = "CANCEL-ALL-MARKET"
                    Application.EnableEvents = True
                End If
            End If
        End If
    End If
End Sub

[technician]

Hi Gary

Thank you for this, it works perfectly. Not only that, your method has shown me the way on how to clean up and optimise a bunch of other code that I had poorly written

Thanks again, it's very much appreciated