How do I create a graph from a DDE feed in excel workbook

Question

I have been attempting to create a graph from a API. I have set up the connection to the API in an excel worksheet and it displays the moving price of BTCUSD from an exchange. The value changes at a set interval as by the connection properties in the data tab in excel. I have tried a number of times with various approaches.

Worksheet_Change(ByVal Target As Range) this does not work as Worksheet_Change does not trigger when the value changes in the API data cell don't understand why.
An ActiveX textbox linked to the data cell, this triggers textbox_change() event however I have run into another problem with the error 1004 Unable to get the CurrentRegion property of the range class. Your assistance would be appreciated. This is the code.
```
Public Sub TextBox1_Change()
With ThisWorkbook.Names("DDEList").RefersToRange.CurrentRegion
With .Offset(.Rows.Count, 0).Resize(1, 1)
        .Value = Now
     .Offset(0, 1).Value = TextBox1.Value
End With
End With

End Sub
```

In the workbook sheet is a named cell called DDEList and the ActiveX textbox and cell A1 has a reference to the table data created by the API. I know how to create a graph from the data in cells the creation of the cell data is the issue.

Thank-you Foxfire and Burns and Burns the Sheet Pivot Table Update did the trick. — Malawby

Malawby Malawby · Accepted Answer · 2018-01-15T05:39:58

Ok The Workbook.SheetPivotTableUpdate did the trick.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim X As Double
Dim Y As Double

X = Sheets("DDE").Range("C2").Value
Y = Sheets("DDE").Range("A16").Value
Z = Y - X
P = Z / X

  With ThisWorkbook.Names("DDEList").RefersToRange.CurrentRegion
    With .Offset(.Rows.Count, 0).Resize(1, 1)
        .Value = Now
        .Offset(0, 1).Value = Y
        .Offset(0, 2).Value = X
        .Offset(0, 3).Value = Z
        .Offset(0, 4).Value = P
    End With
  End With

End Sub

I now have a graph on my worksheet that tells me the best time and exchange to buy or sell Bitcoin based on the percentage differences list on various exchanges.

How do I create a graph from a DDE feed in excel workbook

1 Answers