execute continuously

Please post any questions regarding the program here.

Moderator: 2020vision

execute continuously

Postby SWV » Sun Jan 30, 2022 2:09 pm

Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("O5:O6")) Is Nothing Then
        If Range("X1") = True Then
           Call NextAvaliableCol
        End If
    End If
End Sub


The above code works fine manually but once connected to Gruss it continues to execute continuously. Any advise me where I am going wrong please.

TIS
SWV
 
Posts: 61
Joined: Sat Aug 12, 2017 8:11 am

Re: execute continuously

Postby Captain Sensible » Sun Jan 30, 2022 5:43 pm

The Worksheet_Change event will fire on every price refresh so if the conditions are true the rest of your code will continually execute. If you only want it to run once you'd need to set some other condition to stop it repeating and reset that for when you want it to run again i.e. a new market
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: execute continuously

Postby Captain Sensible » Sun Jan 30, 2022 5:49 pm

Plus you haven't disabled events ( i.e. Application.EnableEvents = False) in your code so deoending what it's doing it could go into an endless loop continually triggering the Worksheet_Change event.
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: execute continuously

Postby SWV » Sun Jan 30, 2022 9:29 pm

Captain Sensible ok thanks. All gets above me this. I've spent hours on this over the weekend. You think you've cracked it but its never that easy with excel is it :oops:
SWV
 
Posts: 61
Joined: Sat Aug 12, 2017 8:11 am

Re: execute continuously

Postby Captain Sensible » Mon Jan 31, 2022 1:23 pm

SWV wrote:Captain Sensible ok thanks. All gets above me this. I've spent hours on this over the weekend. You think you've cracked it but its never that easy with excel is it :oops:


Yep there's a lot to take in but once you grasp the basics of how BA and excel work together VBA becomes a lot easier.

Basically when BA refreshes it sends data to excel in two chunks, first one is the set of updated prices (columns A:P) and the next chunk is the betting data and any additional columns (column T onwards).

To fire our VBA code we use what excel calls Events, these events occur when the excel sheet is amended in some way. Because that set of price data changes loads of cell values we usually us Worksheet_Change. The Worksheet_Change event simply sees one or many cells have changed and then runs the VBA code within that sub routine.

Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)

'Our code fires in here
 
End Sub


Must admit I never use If Not Intersect, it seems to be used mainly when people are setting up manual spreadsheets for whatever reason. Because BA is sending data on each refresh it never seems neccessary to me and I'd simply use an If condition to trigger any routine. If something is occuring in O5:O6 that's needed i'd just include it in my condition i.e. If Range("X1") = True And Range("O5") = 1 Then

Once you have some working code it's then mainly about making it effiecient by not running code when it's uneccessary, so things like turning off events whilst code runs

Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False 'Turn off events so changes to cell don't retrigger event

'Our code fires in here

Application.EnableEvents = True 'Turn on events again
End Sub



Or even exiting the sub routine early if it's not a price data refresh

Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count <> 16 Then Exit Sub ' Only run on price data refresh

Application.EnableEvents = False 'Turn off events so changes to cell don't retrigger event

'Our code fires in here

Application.EnableEvents = True 'Turn on events again
End Sub
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: execute continuously

Postby SWV » Mon Jan 31, 2022 6:42 pm

Brilliant thank you. This going to take some studying. I think it is the terminology that causes a lot of the confusion!

I've simplified things so that if X1 = True it should call the Macro "NextAvaliableCol".
I thought the following would work but does not :?

Code: Select all
 Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count <> 16 Then Exit Sub ' Only run on price data refresh

Application.EnableEvents = False 'Turn off events so changes to cell don't retrigger event

         If Range("X1") = True Then
           Call NextAvaliableCol
        End If
    End If
End Sub

Application.EnableEvents = True 'Turn on events again
End Sub
 
SWV
 
Posts: 61
Joined: Sat Aug 12, 2017 8:11 am

Re: execute continuously

Postby Captain Sensible » Mon Jan 31, 2022 8:34 pm

And the code isn't executing when you're linked to BA? Remember it won't work by manually changing X1 to true as that wouldn't be amending 16 columns. Also if X1 is being controlled by a formula VBA won't treat Range("X1") as True it'd treat it as whatever the formula i.e. IF(.......) . You'd need to change that to

Code: Select all
Range("X1").Value = True
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: execute continuously

Postby SWV » Mon Jan 31, 2022 8:59 pm

Awesome mate thank you :D

Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count <> 16 Then Exit Sub ' Only run on price data refresh

Application.EnableEvents = False 'Turn off events so changes to cell don't retrigger event

         Range("X1").Value = True
           Call NextAvaliableCol
        End If
    End If
End Sub

Application.EnableEvents = True 'Turn on events again
End Sub
 
 


*edit I get the following error if I use the code as above "End If without block if"
Any idea on this please?
SWV
 
Posts: 61
Joined: Sat Aug 12, 2017 8:11 am

Re: execute continuously

Postby Captain Sensible » Mon Jan 31, 2022 11:05 pm

You have lots of redundant End If's and even an extra End Sub for some reason. The line

Code: Select all
If Target.Columns.Count <> 16 Then Exit Sub ' Only run on price data refresh


Doesn't need an End If as it's all on one line, alternatively it could have been written as

Code: Select all
If Target.Columns.Count <> 16 Then
Exit Sub
End If


Plus your line Range("X1").Value = True Is just setting the value to True it is not querying it as per your previous code i.e. If Range("X1").Value = True

I'd guess the code should be something like

Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count <> 16 Then Exit Sub ' Only run on price data refresh

Application.EnableEvents = False 'Turn off events so changes to cell don't retrigger event

    If Range("X1").Value = True
           
           Call NextAvaliableCol
   
    End If


Application.EnableEvents = True 'Turn on events again
End Sub
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm


Return to Help

Who is online

Users browsing this forum: No registered users and 53 guests

Sports betting software from Gruss Software


The strength of Gruss Software is that it’s been designed by one of you, a frustrated sports punter, and then developed by listening to dozens of like-minded enthusiasts.

Gruss is owned and run by brothers Gary and Mark Russell. Gary discovered Betfair in 2004 and soon realised that using bespoke software to place bets was much more efficient than merely placing them through the website.

Gary built his own software and then enhanced its features after trialling it through other Betfair users and reacting to their improvement ideas, something that still happens today.

He started making a small monthly charge so he could work on it full-time and then recruited Mark to help develop the products and Gruss Software was born.

We think it’s the best of its kind and so do a lot of our customers. But you can never stand still in this game and we’ll continue to improve the software if any more great ideas emerge.