BA and Excel Freeze

[xraymitch]

Hi Gary,

I am using your time in play code which works fine for horse racing but when I try it on football markets it causes BA and excel to freeze.

Here is the code:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
If updating Then Exit Sub
updating = True
If Cells(2, 5) = "In Play" Then
If Cells(1, 27) = "" Then Cells(1, 27) = Cells(2, 3)
If Cells(1, 27) <> "" Then Cells(1, 28 ) = DateDiff("s", Cells(1, 27), Cells(2, 3))
End If
If Cells(2, 5) <> "In Play" And Cells(2, 6) <> "Suspended" Then
Cells(1, 27) = ""
Cells(1, 28 ) = ""
End If
updating = False
End Sub


I am trying to find a way of knowing when a match has started HT and when the second half goes into play, hence my reason for experimenting with your code.

As usual any ideas much appreciated

Thanks

[GaryRussell]

I must have done that some time ago because I would write that slightly differently now. Try the following.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Columns.Count = 16 Then
      Application.EnableEvents = False
      If Cells(2, 5) = "In Play" Then
         If Cells(1, 27) = "" Then Cells(1, 27) = Cells(2, 3)
         If Cells(1, 27) <> "" Then Cells(1, 28 ) = DateDiff("s", Cells(1, 27), Cells(2, 3))
      End If
      If Cells(2, 5) <> "In Play" And Cells(2, 6) <> "Suspended" Then
         Cells(1, 27) = ""
         Cells(1, 28 ) = ""
      End If
      Application.EnableEvents = True
   End If
End Sub


[Captain Sensible]

What markets are you using to guage when the second half starts , xraymitch? I didn't think they currently have any markets that are actively managed and closed for the restart. In the old days they used to reopen and close some of the range and goal markets at the half time intervals but I think now they just autosuspend those an hour after kick off because they can't be bothered to put up staff to actively manage them.

[xraymitch]

I must have done that some time ago because I would write that slightly differently now. Try the following.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Columns.Count = 16 Then
      Application.EnableEvents = False
      If Cells(2, 5) = "In Play" Then
         If Cells(1, 27) = "" Then Cells(1, 27) = Cells(2, 3)
         If Cells(1, 27) <> "" Then Cells(1, 28 ) = DateDiff("s", Cells(1, 27), Cells(2, 3))
      End If
      If Cells(2, 5) <> "In Play" And Cells(2, 6) <> "Suspended" Then
         Cells(1, 27) = ""
         Cells(1, 28 ) = ""
      End If
      Application.EnableEvents = True
   End If
End Sub


lindemann wrote:
Excel,

Have a look at this thread:

http://www.gruss-software.co.uk/forum/v ... php?t=7455


Thanks for that Gary - now not freezing ! . Strange that my amended version works fine on the horses though.

Thanks also go out to lindemann I am using his/your code with my minor change in the following markets:

Half Time Score - Sheet 1
Half Time / Full time - Sheet 2

I am hoping I can use those markets to tell when HT starts and second half begins. Should know in about 2 hours. Here is the code:

Sheet1:

Dim timeInPlay As Date
Dim turnedInPlay As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count = 16 Then
If Range("F2").Value = "Closed" Then
If Not turnedInPlay Then
turnedInPlay = True
timeInPlay = Now
End If
Else
turnedInPlay = False
End If
If turnedInPlay Then Range("T1").Value = DateDiff("s", timeInPlay, Now) Else Range("T1").Value = 0
End If
End Sub

Sheet2:

Dim timeInPlay As Date
Dim turnedInPlay As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count = 16 Then
If Range("E2").Value = "InPlay" And Range("U1") = "Closed" Then
If Not turnedInPlay Then
turnedInPlay = True
timeInPlay = Now
End If
Else
turnedInPlay = False
End If
If turnedInPlay Then Range("T1").Value = DateDiff("s", timeInPlay, Now) Else Range("T1").Value = -1
End If
End Sub

[xraymitch]

What markets are you using to guage when the second half starts , xraymitch? I didn't think they currently have any markets that are actively managed and closed for the restart. In the old days they used to reopen and close some of the range and goal markets at the half time intervals but I think now they just autosuspend those an hour after kick off because they can't be bothered to put up staff to actively manage them.

Hi Captain,

Half Time Score & Half Time / Full time

I know when HT starts because Half Time Score market gets closed.

Still to find out what happens in Half / Time Full Time so you may be right and my efforts will come to naught...

But still I keep on learning

[Captain Sensible]

I guess alot will depend on the games, even though their rules state they'll re-suspend at half time and hour after the scheduled KO they might actively manage and re-suspend at the second half kick off for the bigger matches. Should be quite obvious after a few matches if they are auto suspending or not. Keep us informed how it goes , thanks.

[xraymitch]

Hi again Captain,

This was my thinking too and I will update here how I got on this evening.

Thanks,

[xraymitch]

Hi folks,

It is now clear (as the Captain suggested) that you cannot rely upon an autosuspend in the Half Time / Full Time. The only thing that seems reliable is that the Half Time Score market (based on 30 matches) does get closed, but times vary between 1 and 5 minutes before it happens.

I guess it is back to the drawing board



PS I suppose I could try web scraping...