RTD - detecting change in price

[Stella]

Hi All,

i have a spreadsheet which uses RTD functionality to stream share/stock
prices from LIFFE. what i want to do is plot a graph based on the changes on
the price. to do this i was told i would need to detect that there has been
a change in price and then copy and paste that into another spreadsheet. for
example:

Spreadsheet 1
Cell A1
150 -> 152 -> 149 -> 155

Spreadsheet 2
Cell A1 150
A2 152
A3 149
A4 155

In spreadsheet 1 cell A1 (this has the streaming prices)
Spreadsheet 2 stores the price changes. using this info. i will be bale to
plot a graph.

Any ideas how to do this in VBA? are there any tutorials that anybody knows?

Thanks,
Stella

 

[Bernie Deitrick]

Stella,

I believe that you need to have another cell referencing the cell with the
DDE, since DDE updates don't fire events by themselves.

The cell reference should fire the workbook's sheetcalculate event, which
you can use to make a record of your values.

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
If Worksheets("Sheet1").Range("A1").Value <> _
Worksheets("Sheet2").Range("A65536").End(xlUp).Value Then _
Worksheets("Sheet2").Range("A65536").End(xlUp)(2).Value = _
Worksheets("Sheet1").Range("A1").Value
End Sub