worksheet_calculate event - firing constantly

[Nhouse1234]

Hi !
I have a sheet that calculates 5 most likely correct scores for a football market, based on certain odds conditions.

I am using VBA to copy 'live' odds from one part of the sheet (linked to Betangel excel) to another - that then performs the calc. This is triggered from the Betangel excel countdown clock - so the clock is linked on my sheets & when it hits a certain time to kick off - a formula triggers - changing a cell value - which the VBA code is looking for .

I have other code that does something similar to lay at HT and at 75.

BUT using the calculate event is problematic as I have a lot of formulas working on the sheet - which when they resolve triggers the event.

I cannot use the change event as that doesn't work with formulas - only if I manually change a cell - which I am not doing as the object of the exercise is to automate.

I am using application.enableevents = false in my subs so my own changes don't trigger it.

Anyone know a way of avoiding this? I have seen lots of posts about using VBA but nothing around this particular problem.

Been driving me mad for weeks now - so any help appreciated.

Code: Select all

Private Sub Worksheet_Calculate()

Dim row_number As Integer
Dim end_row As Integer
end_row = 30

    For row_number = 11 To end_row
    
            If Range("FT" & row_number).Value = 1 And IsEmpty(Range("B" & row_number)) Then
            Copy_Starting_Odds (row_number)
            End If
            
            If Range("FX" & row_number).Value = 1 And IsEmpty(Range("RD" & row_number)) Then
            InitialBet (row_number)
            End If
            
            If Range("GN" & row_number).Value = 1 And Range("GO" & row_number) = 0 Then
            Call Place_Lay(row_number, "GO")
            End If
            
            If Range("HF" & row_number).Value = 1 And Range("HG" & row_number) = 0 Then
            Call Place_Lay(row_number, "HG")
            End If


    Next row_number

[conduirez]

your Application.EnableEvents toggles are in the wrong place see the following

https://stackoverflow.com/questions/467 ... over-again

[Walshie1987]

Hi,

Don't know if this will help, but I think I've experienced similar, I pull in Greyhound stats 60secs before the race starts, apply my calculations then place bets that meet criteria. The problem was that the stats were getting refreshed before the dogs names were getting populated, throwing up errors.

To get around this I added some logic into my vba, in the Private Sub Worksheet_Change(ByVal Target As Range) section of the Bet Angel sheet, such as:

Code: Select all

if Format(Sheets("Bet Angel").Range("countdown"), "hh:mm:ss") < #12:00:59 AM# then
 .....Run Code
end if

In the above, the named range "countdown" is the betfiar countdown timer and the #12:00:59 AM# translates to 00:00:59 (weird excel vba format) so it only refreshes the data 1 minute before the off, I then have similar logic in the functions in my cells to place the bets, e.g. Place the bet if Countdown timer <30secs, that way I know the result information has updated (it takes 5 seconds) and I'm getting close to the off odds.

I also do other thinks like write in the "last update time" into a cell, "last updated market" etc. then do comparisons to make sure everything is working as expected:

e.g.

Code: Select all

If Sheets("Bet Angel").Range("raceNameNew") <> Sheets("Dashboard").Range("raceNameOld") And Format(Sheets("Bet Angel").Range("countdown"), "hh:mm:ss") < #12:00:59 AM# Then
     ....Run Code

     'Update cell so I know this was successful
    Sheets("Dashboard").Range("raceNameOld") = Sheets("Bet Angel").Range("raceNameNew")

   'Clear old bets
    Sheets("Bet Angel").Range("betdata") = ""

end if

Hope this helps!

Cheers,
Chris