Hi
Is there anything I can do to the incoming numbers of a connected spreadsheet so that they can be manipulated with vba ?
For example the prices, LTP's or volume figures ? ..
If I refer to them with vba they are ignored, but if I type in the cells then the vba works, so I am seeing that there is a difference in figures coming from BA/BF as opposed to numbers you actually type in, is there a word or description for that?
I have tried using formulas like multiplying by one or adding zero to no avail ... is ther another way to make the incoming numbers "real" for want of a better expression?
Thanks for any help
Paul
Manipulating the incoming numbers
-
- Posts: 120
- Joined: Mon May 30, 2016 5:19 pm
Just a thought (not tested) if BA is feeding a value into A1, if you set A2 value as =A1 then reference A2 in the VBA what happens?
-
- Posts: 3140
- Joined: Sun Jan 31, 2010 8:06 pm
How are you trying to fire off your VBA? Worksheet_change/calculate?
You can see how the data gets changed on the sheet using
And then when you decide which block you want to trigger your code use something like
You can see how the data gets changed on the sheet using
Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
Debug.Print Target.Address
End Sub
Code: Select all
If Not Intersect(Target, Range("C2")) Is Nothing Then
thanks for the reply .. i'm not too hot on vba but here is an example code ..
The idea would be to note the change in value (as the numbers change) in col H and put the answer in Col
AN
don't know if that'sthe best way to go about it, but it works in an excel sheet if you type numbers in Col H (9 to 16) ...
Cheers
Paul
Code: Select all
Option Explicit
Dim OldVal
Public Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("H9:H16")) Is Nothing Then
Application.EnableEvents = False
OldVal = Target.Value
Application.EnableEvents = True
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("H9")) Is Nothing Then
Application.EnableEvents = False
Range("AN9") = Target.Value - OldVal
Application.EnableEvents = True
End If
If Not Intersect(Target, Range("H10")) Is Nothing Then
Application.EnableEvents = False
Range("AN10") = Target.Value - OldVal
Application.EnableEvents = True
End If
If Not Intersect(Target, Range("H11")) Is Nothing Then
Application.EnableEvents = False
Range("AN11") = Target.Value - OldVal
Application.EnableEvents = True
End If
If Not Intersect(Target, Range("H12")) Is Nothing Then
Application.EnableEvents = False
Range("AN12") = Target.Value - OldVal
Application.EnableEvents = True
End If
If Not Intersect(Target, Range("H13")) Is Nothing Then
Application.EnableEvents = False
Range("AN13") = Target.Value - OldVal
Application.EnableEvents = True
End If
If Not Intersect(Target, Range("H14")) Is Nothing Then
Application.EnableEvents = False
Range("AN14") = Target.Value - OldVal
Application.EnableEvents = True
End If
If Not Intersect(Target, Range("H15")) Is Nothing Then
Application.EnableEvents = False
Range("AN15") = Target.Value - OldVal
Application.EnableEvents = True
End If
If Not Intersect(Target, Range("H16")) Is Nothing Then
Application.EnableEvents = False
Range("AN16") = Target.Value - OldVal
Application.EnableEvents = True
End If
End Sub
AN
don't know if that'sthe best way to go about it, but it works in an excel sheet if you type numbers in Col H (9 to 16) ...
Cheers
Paul
- ShaunWhite
- Posts: 9731
- Joined: Sat Sep 03, 2016 3:42 am
Try running it in debug mode and watching the variables. (press f9 on the line you want to break at and then use F8 to step through your code, hover your mouse over a variable to see its value)
It might be that Target.Value isn't what you think it is, Target is a range and therefore not necessarily a single cell, esp as BA updates excel in chunks of cells at a time.
Oldval = Range("H9").Value or similar might work better for you seeing as you know the cell you want to look at.
It might be that Target.Value isn't what you think it is, Target is a range and therefore not necessarily a single cell, esp as BA updates excel in chunks of cells at a time.
Oldval = Range("H9").Value or similar might work better for you seeing as you know the cell you want to look at.
Shaun, i tried to run in debug but tbh I'm not knowledgable enough with vba to glean anything ...
The fact is that the code works fine on the BA sheet or a new blank sheet ...
ie if I type 24 into H9, then 24 will appear in AN9, then if I change H9 to 26, then AN9 will display 2 ..etc this works fine
But If I connect the sheet to BA and lets say H9 shows 24, nothing will appear in AN9, and nothing will happen when H9 changes ..
It seems that the numbers displayed on a "Connected" sheet arn't recognised ..
How would you best describe the situation ? are the numbers there via API stream ?
Appreciate any help, thanks
Paul
The fact is that the code works fine on the BA sheet or a new blank sheet ...
ie if I type 24 into H9, then 24 will appear in AN9, then if I change H9 to 26, then AN9 will display 2 ..etc this works fine
But If I connect the sheet to BA and lets say H9 shows 24, nothing will appear in AN9, and nothing will happen when H9 changes ..
It seems that the numbers displayed on a "Connected" sheet arn't recognised ..
How would you best describe the situation ? are the numbers there via API stream ?
Appreciate any help, thanks
Paul
- ShaunWhite
- Posts: 9731
- Joined: Sat Sep 03, 2016 3:42 am
It's nothing to do with the api. BA will probably use something called DDE (Dymanic data exchange) to talk to Excel.sherman wrote: ↑Tue Sep 25, 2018 9:31 amShaun, i tried to run in debug but tbh I'm not knowledgable enough with vba to glean anything ...
The fact is that the code works fine on the BA sheet or a new blank sheet ...
ie if I type 24 into H9, then 24 will appear in AN9, then if I change H9 to 26, then AN9 will display 2 ..etc this works fine
But If I connect the sheet to BA and lets say H9 shows 24, nothing will appear in AN9, and nothing will happen when H9 changes ..
It seems that the numbers displayed on a "Connected" sheet arn't recognised ..
How would you best describe the situation ? are the numbers there via API stream ?
Appreciate any help, thanks
Paul
Put an breakpoint on the line..OldVal = Target.Value.. by pressing f9.
When you code runs, execution will stop at that line.
Right click on "Target.Value" and select 'Watch'
What does it say the value of Target.Value is in the window at the bottom below your code?
Then each time you press f8 the code will move on to the next line it's going to execute. That is handy when you have 'If' lines as you can see if the execution steps into your conditional code or not.
thanks nigelk
What I am trying to achieve in this instance is to monitor a col ie "H" and when the numbers change to have the new updated value minus the old value put into col AN ..
so if H9 is 5.8, then it changes to 6.0, then AN9 should show 0.2, if it then changes to 5.5 then AN9 should show -0.5 and so on .. and of course during in-play this needs to happen fast .. and continously ..
thanks
Paul
What I am trying to achieve in this instance is to monitor a col ie "H" and when the numbers change to have the new updated value minus the old value put into col AN ..
so if H9 is 5.8, then it changes to 6.0, then AN9 should show 0.2, if it then changes to 5.5 then AN9 should show -0.5 and so on .. and of course during in-play this needs to happen fast .. and continously ..
thanks
Paul
- ShaunWhite
- Posts: 9731
- Joined: Sat Sep 03, 2016 3:42 am
There's always several ways to code a problem, but just to get something working you could do something like this code fragment. If you have trouble with code it's often best to strip it back to something super simple, if not expecially efficient, and work up from there adding sophistication.
The biggest potential problem I see is that the refresh rate is so fast that your figures in col AN (the price change?) will always appear to be zero. With that in mind the code below has an additional 'IF' which will mean it only updates if the price has changed, basically freezing the last change figure. Just remove the If & End If statements if you don't want it to work like that.
This code is inefficient as it will be called when anything changes anywhere, but as I said...let's get something working and build it up.
(I haven't tried this so E&OE)
Try that.
Next step would be to rename this proceedure eg replace "Worksheet_Change(ByVal Target As Range)" with something like 'UpdatePriceChanges()" and then insert some more code to call this only when your specific column changes. eg.
It's not pretty and it's not clever but it might get you moving. As I said it's untested so I hope it works.
The biggest potential problem I see is that the refresh rate is so fast that your figures in col AN (the price change?) will always appear to be zero. With that in mind the code below has an additional 'IF' which will mean it only updates if the price has changed, basically freezing the last change figure. Just remove the If & End If statements if you don't want it to work like that.
This code is inefficient as it will be called when anything changes anywhere, but as I said...let's get something working and build it up.
(I haven't tried this so E&OE)
Code: Select all
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'Disable event monitoring so that changes don't retrigger Worksheet_Change
Application.EnableEvents = False
'Now update the price change figure if the price has changed, H = New prices, AN = price changes, AO = Old prices
If Range("AO9").Value <> Range("H9").Value then
'Store the change from the old saved price
Range("AN9").Value = Range("AO9").Value - Range("H9").Value
'Store current price as the old price
Range("AO9").Value = Range("H9").Value
End if
If Range("AO10").Value <> Range("H10").Value then
'Store the change from the old saved price
Range("AN10").Value = Range("AO10").Value - Range("H10").Value
'Store current price as the old price
Range("AO10").Value = Range("H10").Value
End if
...etc
'Enable event monitoring so that changes trigger Worksheet_Change
Application.EnableEvents = True
End Sub
Next step would be to rename this proceedure eg replace "Worksheet_Change(ByVal Target As Range)" with something like 'UpdatePriceChanges()" and then insert some more code to call this only when your specific column changes. eg.
Code: Select all
Public Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("H9:H16")) Is Nothing Then
Call UpdatePriceChanges
End If
End Sub
- ShaunWhite
- Posts: 9731
- Joined: Sat Sep 03, 2016 3:42 am
I think the flaw in your logic came down to this...
OldVal = Target.Value
code code code code code
Range("AN9") = Target.Value - OldVal
It would always be zero.
It's sometimes just a matter of writing down first how you'd do it on paper.
eg
Look at the new price
Is is different to the price I wrote down earlier?
If so, calc the change
Write down the 'old' price so I can remember it later
Repeat
...what was missing was writing down the old price to compare with the new one.
All coding is like that really, just re-doing steps that used to be done manually in ledgers. The problem people have though is that they discount steps which are done subconsciously such as remebering previous things, which of course a computer needs to be told to do. Basically you have to treat a computer like an idiot and tell it everything. Don't tell it what you do, tell it how you think, if that makes sense.
OldVal = Target.Value
code code code code code
Range("AN9") = Target.Value - OldVal
It would always be zero.
It's sometimes just a matter of writing down first how you'd do it on paper.
eg
Look at the new price
Is is different to the price I wrote down earlier?
If so, calc the change
Write down the 'old' price so I can remember it later
Repeat
...what was missing was writing down the old price to compare with the new one.
All coding is like that really, just re-doing steps that used to be done manually in ledgers. The problem people have though is that they discount steps which are done subconsciously such as remebering previous things, which of course a computer needs to be told to do. Basically you have to treat a computer like an idiot and tell it everything. Don't tell it what you do, tell it how you think, if that makes sense.