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

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.

  1. 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.
  2. 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.

1 answer

  • answered 2018-01-14 11:02 Malcolm Hornsby

    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.