Plotting Line Graph of Dynamic Data

[sslack]

I have an excel spreadsheet which is linked to a data source (via DDE)
and is constantly feeding a single cell different values.

Is it possible to plot a line graph of the value in the cell everytime
it changes?

For example,
The current cell value is 10.00 and the time is 9:00am. Now the time
is 9:15am and the value just changed to 10.25. I want to plot a line
graph that displays the time on the x-axis and the value on the
y-axis.

Please let me know if you can help out. I have been pulling my hair
out trying to figure this out.

Best,
Steve

 

[Tushar Mehta]

You would have to save all the old values. That would require a
programmatic solution.

The following untested code should go in the worksheet code module. It
assumes that the DDE link updates a cell in column B, say B2.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim EmptyCell As Range
If Target.Column <> 2 Then Exit Sub
With Target.Parent
Set EmptyCell = _
.Cells(.Rows.Count, Target.Column).End(xlUp).Offset(1, 0)
End With
On Error GoTo ErrXIT
Application.EnableEvents = False
EmptyCell.Value = Target.Validation
EmptyCell.Offset(0, -1).Value = Now()
ErrXIT:
Application.EnableEvents = True
End Sub

You can now use the ideas at Dynamic Charts (http://www.tushar-
mehta.com/excel/newsgroups/dynamic_charts/index.html) to create a chart
that updates itself automatically.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions