Stumped with basic VBA 'trigger'

[PennyFiddler]

Hi.

I have used Excel from the beginning, but never VBA nor Excel triggered betting.

While technically still not attempting Triggered betting I have a problem Im obviously overlooking due to lack of experience.

When I link an Excel workbook, code that works ordinarily, doesnt.

I have simplified the tests down to this most basic scenario to establish what Im doing wrong.
Im simply attempting to have a cell reference one of the cells updating through gruss (or subsequent cells with formulas in) but nothing happens....when If I manually alter the cells they work fine. (its as though it doesnt see the information in the cells)

Private Sub Worksheet_Change(ByVal Target As Range)
'testing price change Lay1
If Target.Address = "$H$5" Then
Cells(5, 48).Value = Cells(5, 48).Value + 1
End If
End Sub

In addition to this, could somebody just give general advice.
I created a rather intensive loop (for me) and it goes off like the clappers on the numbers already in the spreadsheet linked to gruss, but stops the updating to gruss. Its either one thing of the other.
Can you run Loops continually while the spreadsheet is linked and prices updating?

Thanks

[Captain Sensible]

There's lots of example code on the site you generally find people run their code each time the sheet refreshes rather than targeting a single cell to drive the sheet. You can then monitor that cell on each refresh and if it hasn't done what you want just exit the routine early.

The way BA works is by sending two lots of data to the worksheet , first lot is the columns A:P (16 columns) which contain price data refreshes, second lot is the betting ref,profit and loss columns etc. I prefer to run code after the last set of data but the number of columns updated depends if you send additional data.

Another problem you may find is your code is sending the sheet into an endless loop by amending data on the sheet and therefore kicking in another worksheet_change event. To stop that we simply turn events off before the code runs then back on again after the code has finshed.

So our starting point may look something like below.

Every worksheet_change will kick off the code, if the number of columns changed isn't 16 i.e. a price data refresh A:P then the code exits. We then turn off events so we don't get into endless loops when we amend data on the sheet and once the code has run we turn events back on for the the next refresh.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo xit
    If Target.Columns.Count <> 16 Then Exit Sub
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
   
'main code goes in here



xit:
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
   
End Sub

[PennyFiddler]

Thank you Captain Sensible for you input.

As ever with this type of thing I grow frustrated by not knowing what the questions are Im supposed to be asking, and that code comes in varying forms that often looks alien to the uninitiated.
I have a very shoddy grasp of programming and get by by trial and error and what I find on the internet.
I had established I was giving scripts to the workbook that ran contrary to the updates, but couldnt find how to integrate my code with the resfreshing of data....and unfortunately still cant.

The 'not equal to 16 columns' etc throws me completely and if I have read your help wrongly I appologise, but not wanting to send any commands to BA (Im simply collecting data) how do I merge my coding in with the refreshes, as Ive tried putting a very simple line of code into what I thought was your direction, only for nothing to happen.

I wont be bothersome asking one question after another, but if you could just set me straight on how to do this action I will find my way from there.

I just (for the purposes of getting something working) want to have a cell count the number of times another cell updates, in this case $H$5 (the Lay1 price of the fave) and take the number already in cell $T$1 (could actually be any cell for this) and add 1 to it.

Sorry and thank you.

[Captain Sensible]

Don't worry about asking questions you may think are silly as we've all started VBA and excel from scratch. And with VBA and excel there are many ways to do the same thing. With most of the code on the forum it's based from code Gary has posted up in the past that's why you'll see many snippets using the not equal to 16 columns. And that's simply because BA sends two lots of data one the price refresh and one the betting refresh.

But rather than confuse you with lots of code your original code should be using the Worksheet_SelectionChange rather than simply Worksheet_Change

So this should work when connected to BA

Code: Select all

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Address = "$H$5" Then
    Cells(5, 48).Value = Cells(5, 48).Value + 1
  End If
End Sub

That should change cell AV5 when a change occurs in H5.

[PennyFiddler]

I appreciate your assistance Captain Sensible...but still nothing.

I started from a fresh wookbook, saved it as the macro enabled sort, pasted the code into both the sheet1 and module scripts, saved it changed cells to test...all to no avail.

I expected it to work when I did it the first time, its not like Im asking it to do much, just count the times a value in a cell updates....but no.

This is the sort of thing that makes me head butt the damn thing.

[Captain Sensible]

One of the easiest things to do when testing is having something updating on the excel sheet using VBA so you can see the VBA is working. If we echo the value of H5 into a cell S1 you should be able to see the VBA is working when the sheet is connected to BA. If S1 isn't updating then there is something wrong with the VBA or connection with BA.

Try this code into under Sheet1 and connect BA to Sheet1.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo xit
    If Target.Columns.Count <> 16 Then Exit Sub
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
   
        With Target.Parent

            If .Range("S1").Value = "" Then .Range("S1").Value = .Range("H5").Value
           
           
            If .Range("H5").Value <> .Range("S1").Value Then
            .Range("T1").Value = .Range("T1").Value + 1
            .Range("S1").Value = .Range("H5").Value
            End If

        End With

xit:
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
End Sub


Basically the code is simply checking we have received a price update from BA (sent as 16 columns of data A:P) .

If so it turns events off (Application.EnableEvents = False) so anything happening on the sheet won't start another Worksheet_Change event

With Target.Parent means we only deal with data sent to this sheet and allows us to reference cells by simply putting .Range(... rather than a full address Worksheet(Sheet1).Range(.... etc

If .Range("S1").Value = "" Then .Range("S1").Value = .Range("H5").Value If S1 is empty we simply stick in the value of H5, this mean we can then track the values to see if there's any difference.

If .Range("H5").Value <> .Range("S1").Value Then .Range("T1").Value = .Range("T1").Value + 1 If H5 is different to our stored value of H5 ,stored in S1 we add one to T1 and update the stored value of H5 into S1

The rest simply turns events back on for the next refresh.

See if that code works , if anything is confusing just ask.

[PennyFiddler]

Yes, on initial inspection that seems to work Captain Sensible, thanks.

It 'sees' the comparison between cells, rather than it being expected to fire on simply recognising a cell changing. Perhaps it's the nuanced difference between human thinking and computer thinking.

I will figure it from here, I just needed a sign my VBA was actually interacting with the data coming from BA, I couldnt see where those commands were coming from to try integrate with it (I guess thats in BA)

Two general questions then; Would the 'on error' and 'xit' sections be the framework for all commands I want to add and just have it all within that code?
And, once it reaches the 'End Sub', does it continue indefinately back to the top?
I was under the impression it ran through the code once then stopped, and if you wanted codes to fire on changing events thats what loops were required to do.

Ill build on this when I get more time. Thank you for your help.

[Captain Sensible]

Once it reaches End Sub that's it, the code has ended. It'll then only start again when the sheet has another worksheet_change event i.e. BA refreshes excel again. You changing a cell on the sheet would also trigger a worksheet_change but because we stuck in the line "If Target.Columns.Count <> 16 Then Exit Sub" it wouldn't run the rest of the code unless you'd changed 16 columns at once in one go.

For it to recognise a cell changing it needs to know what the previous value of that cell was, many ways to do that I just thought it's be easiest if it was sent to the sheet so you could see it changing and know your VBA is working. You could store it in memory as a Static variable if you didn't want it echo'd to the screen and simply compare the static variable to H5.

The "On Error Goto xit" was just there so if there was an error in your code, or you amended it when connected to BA and froze the code, it'd go to the line it and then turn the events back on. It's always worth having some kind of error trapping when the code starts getting complicated, but probably not needed in this case.

[Captain Sensible]

Last paragraph should have been , somehow it added a smiley

The "On Error Goto xit" was just there so if there was an error in your code, or you amended it when connected to BA and froze the code, it'd go to the line xit: and then turn the events back on. It's always worth having some kind of error trapping when the code starts getting complicated, but probably not needed in this case.

Another bonus you'll find by storing the price to compare , rather than simply seeing something has changed, is that you can then tack those changes i.e. how many ticks has it moved, has the price gone up or down etc.

If you want to use VBA to register a cell has changed you'd just use

If Not Intersect(Target, Target.Worksheet.Range("H5")) Is Nothing Then

instead of the

If Target.Address = "$H$5" Then

that you used.