execute continuously

[SWV]

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

[Captain Sensible]

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

[Captain Sensible]

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.

[SWV]

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

[Captain Sensible]

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

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


[SWV]

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
 

[Captain Sensible]

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

[SWV]

Awesome mate thank you

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?

[Captain Sensible]

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