Sheet Required

[SWV]

After i aborted the sheet i "manually" fetched the markets and it worked first time so i don't think it is an API problem. I feel the problem is that is the next market trigger -1 and the fetch new market trigger(not sure which one that is) are conflicting with each other.

[Captain Sensible]

BA only picks up one Q2 trigger at a time so if it was fetching then it had started that routine internally and subsequent triggers in Q2 wouldn't mae any difference especially as they're only being entered once every 5 seconds. I tried the code just , jut amended the reload times to 10:28 and all worked fine. But you can always just reinstate the lone that decides what to do when it reaches the last market so nothing gets entered into Q2 if you think its the -1 just amend it to


If Range("J3").Value = "L" Then timeStamp = Now(): GoTo Xit

That way it still skips the -1 being sent after it reaches the last market.

But like I said earlier you should really think about what you want it to do when it reaches the last market as it's a bit wasteful on your bandwidth having it continually refreshing throughout the day when it's doing nothing and also Betfair have been limiting accounts if they think they're data gathering rather than betting.

[Captain Sensible]

With the delay at Newbury I had time to amend the code for you, once it hits the last market and runs it's 5 seconds it will enter a value of 60 into the Q2 (refresh rate) cell, on the next refresh it will clear the cell Q2 so any refresh rate when it does it's restart at market 1 in the QPL will come from whatever your default rate is.

Code: Select all

Option Explicit
Public timeStamp As Date

Private Sub Worksheet_Change(ByVal Target As Range)
Static lastMarket As Boolean
On Error GoTo Xit
    If Target.Columns.Count = 16 Then
        Application.EnableEvents = False
        If IsEmpty(timeStamp) Then timeStamp = Now() + TimeValue("00:01:00") ' set time so we don't rush thru first market when first opening the sheet
         
            If triggerQuickPickListReload = True Then
            triggerQuickPickListReload = False
            Range("Q2").Value = -3.1
            triggerFirstMarketSelect = True
            timeStamp = Now() + TimeValue("00:01:00") ' allow time so refresh can load
            GoTo Xit
        Else
            If triggerFirstMarketSelect = True Then
                triggerFirstMarketSelect = False
                Range("Q2").Value = -5
                lastMarket = False
                GoTo Xit
            End If
        End If
       
       
        If Now() > TimeValue("00:00:05") + timeStamp Then
        timeStamp = Now()
       
        If Range("J3").Value = "L" Then
        If lastMarket <> False Then Range("Q2").Value = "": GoTo Xit 'Clears Q2  so it won't affect first market
        Range("Q2").Value = 60
        lastMarket = True:
        GoTo Xit
        End If
       
       
        lastMarket = False
        Range("Q2").Value = -1
        End If
       
               
       
       
Xit:
        Application.EnableEvents = True
    End If
End Sub

[SWV]

Hi Captain once again thank you for your help.

I tried the new code this morning but i couldn't get it to work for some reason. The refresh rate stayed at 60secs regardless and the markets did not update. I must be something wrong but i don't know as this code/vba business is well out of my league. Maybe i expected too much from Betting Assistant i don't know.

Anyway thank you and good luck i really appreciate you taking the time to reply and look at this for me.

All the best

SWV

[Captain Sensible]

Did you allow it time to fully run? It'll pause on the first market and also enter 60 in Q2 but that's partly down to the way BA handles refreshes that they don't take affect immediately and won't take effect til the next refresh.

I tested it just and seemed fine, you can add the code below to do testing of the quick pick routine, you'd simply enter the time 16:00:00 into cell S1, press together th following buttons (to bring up the available macros) Alt + F8 and then run TestQPList, that will schedule the Quick pick list to run at whatever time is in S1, it won't overwrite the times you've set so no need to change them. Obviously disable any betting routines whilst testing the QPList routine as you don't want to place bets. Just changing the times in the routines wouldn't schedule them unless the sheet was reopened or the macro run so you would need to run the TestQPList routine for it to schedule the time and work.

Code: Select all

Public Sub TestQPList()


    Application.OnTime TimeValue(Worksheets("Sheet1").Range("S1").Text), "loadQuickPickList"
End Sub

If you don't want refresh rates slowing down once it's finished those parts can always be take out or skipped