How to capture cell value on update?

[martygladin]

Hey guys! Thank you for taking time to read my post. I am stuck. I have some code that captures a cell's value on a timer, but I want it to capture the value only when it updates. It is for a live data connection to my stock broker. I want to capture the "last price" and record it on "Sheet2" so that I can pull that information into a chart. Any help would be greatly appreciated! I pasted the code below.

Dim NextTime As Double

Sub RecordData()
Dim Interval As Double
Dim cel As Range, Capture As Range
Interval = 5 'Number of seconds between each recording of data
Set Capture = Worksheets("Sheet1").Range("B9") 'Capture this column of data
With Worksheets("Sheet2") 'Record the data on this worksheet
Set cel = .Range("A2") 'First timestamp goes here
Set cel = .Cells(.Rows.Count, cel.Column).End(xlUp).Offset(1, 0)
cel.Value = Now
cel.Offset(0, 1).Resize(1, Capture.Cells.Count).Value = Application.Transpose(Capture.Value)
End With

NextTime = Now + Interval / 86400
Application.OnTime NextTime, "RecordData"
End Sub

Sub StopRecordingData()
On Error Resume Next
Application.OnTime NextTime, "RecordData", , False
On Error GoTo 0
End Sub

 

[James006]

Hello,

You will probably need to use the Event Macro : Worksheet_Change(ByVal Target As Range)

HTH

 

[martygladin]

Hello,

You will probably need to use the Event Macro : Worksheet_Change(ByVal Target As Range)

HTH

How would I do that? Where would I insert that Mr James006

 

[James006]

Hello,

An Event macro needs to be stored in the Sheet's module ...

See explanation : https://docs.microsoft.com/en-us/office/vba/api/excel.worksheet.change

HTH

 

[martygladin]

Ok so I put this together but it doesn't quite work either. Can anyone see whats wrong with it?

Dim oldValue As Variant
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
oldValue = Target.Value
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Set Capture = Worksheets("Sheet1").Range("B9") 'Capture this column of data
With Worksheets("Sheet2") 'Record the data on this worksheet
Set cel = .Range("A2") 'First timestamp goes here
Set cel = .Cells(.Rows.Count, cel.Column).End(xlUp).Offset(1, 0)
cel.Value = Now
cel.Offset(0, 1).Resize(1, Capture.Cells.Count).Value = Application.Transpose(Capture.Value)
End Sub

 

[martygladin]

Can you take a look at the code and see if you can find whats wrong?

 

[James006]

Could you explain in plain English what you are after ...

What is the purpose of creating the variable ' oldvalue ' ... if you are not using it anywhere else ...

 

[martygladin]

Could you explain in plain English what you are after ...

What is the purpose of creating the variable ' oldvalue ' ... if you are not using it anywhere else ...

Yes. Lets say that I have a data feed of a stock quote coming into the sheet. What I want the macro to do is everytime the stock quote changes, the macro grabs the old value and logs it into a list.

It may help if I give an example: In cell A2 is Ticker Symbol "XYZ" and cell B2 $15, Then stock price changes to 15.50 then changes to 15.75, then 15.60, 15.20, and again and again...you get the idea.

I want the macro to record:
$15
$15.50
$15.75
$15.60
$15.20
...
...
...

The original code that I posted worked, but it was based on a timer. If the timer is short (like 5 seconds) then it would record duplicate info and mess up the chart that was being made from the logged prices. (It would mess it up by creating flat lines marking time change but no price change.) I don't care about time except knowing when the price changed. Thats why I have the time stamp line in the code. Does that make sense?

So to answer your question as to why I made the 'oldvalue' variable was a novice attempt to make it capture the last quoted price before it changed. I don't know how to take the variable to make it work. I hope this helps clear things up. I really do appreciate your time and thoughts!

 

[James006]

Hello,

Thanks for your very clear explanations ...

With a live feed ( my guess DDE ..) Worksheet_Change event will not function ...

You would need to test the Worksheet_Calculate event ...

Something along these lines ...

Private Sub Worksheet_Calculate()
Dim cel As Range
Static oldvalue
  
  If Range("B9").Value <> oldvalue Then
      oldvalue = Range("B9").Value
      
      ' Insert your code
      With Worksheets("Sheet2")
        Set cel = .Range("A" & .Cells(.Rows.Count, "A").End(xlUp).Row + 1)
        cel.Value = Now
        cel.Offset(0, 1) = oldvalue
      End With
  End If
  
End Sub

Hope this will help