Getting over "Suspended" as goes "In-play"

[Fixador]

Probably been asked a thousand times - but - cant find anything in 'Search' !

Something to do between races on a cool afternoon ....

I decided I would like 'see' the winner on my Excel Sheet a little sooner than waiting for the official results, ie, nap it off the screen - assuming the next race hasn't started already !

Ok, I know I have to wait for the market to go 'suspended', but I see, there plenty of occasions when the market goes 'suspended' for a few seconds - but the race isn't over, or, it is flicking over from 'not in play' to 'inplay'.

In vba, do I need to start a timer when i get 'Suspended' and say - wait 10 secs, before testing for the winner ? and if goes back 'inplay' cancel the timer.

I thought I had it cracked yesterday, but today, I see my worksheet_change doesn't fire off once the market has 'suspended' finally

Please no high flying code - as I am still on Excel 2003 !

cheers

[Captain Sensible]

Here's some code I used to use years ago that I've tweaked, I used to use it to capture the lowest traded odds and store them on the sheet (removed that coding so it's simpler). No idea if it's too highflying , alot depends on what you're doing with the data as in the very early days I just used to use some vlookup to highlight the lowest traded odds for me. These days I try to have as little interaction as possible with my bots throughout the day and if anything that constant checking up on them isn't worth the effort as unavoidable poor runs can make you overreact. But anyway here's the code.

Just timestamps in cell AA1 the time the race has gone suspended after being inplay, it then waits 15 seconds and stics the lowest traded odds runner in cell AB1. Clears the cells if the market goes back to live i.e. photo finish and clears te cells when a new market has been selected. Not sure how well it'll work as it hasn't been tested other than one race but may help and kept me busy before going to the pub

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Static MyMarket As Variant
   
If Target.Columns.Count <> 16 Then Exit Sub
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
   
   
On Error GoTo ErrorHandler
If Range("E2").Value = "In Play" Then
If Range("F2").Value <> "Suspended" Then Range("AA1").Value = ""
If Range("F2").Value = "Suspended" And Range("AA1").Value = "" Then Range("AA1").Value = Range("C2").Value

If DateDiff("s", Range("AA1").Value, Range("C2").Value) > 15 And Range("AB1").Value = "" Then
Dim Win_odds As Currency
Dim Winner_name As Variant
Win_odds = 1000
DataArray = Range("A5:O60").Value
    For i = LBound(DataArray, 1) To UBound(DataArray, 1)

    If DataArray(i, 15) >= 1.01 And DataArray(i, 15) < Win_odds Then
    Winner_name = DataArray(i, 1)
    Win_odds = DataArray(i, 15)
    End If
   
    Next i
    Range("AB1").Value = Winner_name
End If

End If

ErrorHandler:

If Range("E2").Value <> "In Play" Then Range("AA1:AB1").Value = ""





Xit:
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
 
   

   
End Sub


[Captain Sensible]

Just a slight tweak as it dumped the lowest price runner at the off just

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Static MyMarket As Variant
   
If Target.Columns.Count <> 16 Then Exit Sub
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
   
   
On Error GoTo ErrorHandler
If Range("E2").Value = "In Play" Then
If Range("F2").Value <> "Suspended" Then Range("AA1").Value = ""
If Range("F2").Value = "Suspended" And Range("AA1").Value = "" Then Range("AA1").Value = Range("C2").Value

If DateDiff("s", Range("AA1").Value, Range("C2").Value) > 15 And Range("AB1").Value = "" And Range("AA1").Value <> "" Then
Dim Win_odds As Currency
Dim Winner_name As Variant
Win_odds = 1000
DataArray = Range("A5:O60").Value
    For i = LBound(DataArray, 1) To UBound(DataArray, 1)

    If DataArray(i, 15) >= 1.01 And DataArray(i, 15) < Win_odds Then
    Winner_name = DataArray(i, 1)
    Win_odds = DataArray(i, 15)
    End If
   
    Next i
    Range("AB1").Value = Winner_name
End If

End If

ErrorHandler:

If Range("E2").Value <> "In Play" Then Range("AA1:AB1").Value = ""





Xit:
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
 
   

   
End Sub

[Fixador]

Hi Captain Sensible,

Thanks for your code snippet, bit late this evening to play with. And I will try it. I had got as far as contemplating nesting an OnTime ( for the 10 secs delay ) within an OnTime ( fired by Inplay to run say after 2 mins). Might be next week sometime before I have tried both.

Will put up code of solution I adopt

cheers

[Fixador]

Oww ! I dont like this term

Code: Select all

If DataArray(i, 15) >= 1.01 And DataArray(i, 15) < Win_odds Then

ofcourse, plonker me - for my application , I will be looking for a group of zero value cells on the Back side.

Ingenious coding though - I wouldn't have thought of that !