Appendix E. Excel Macro Library

Table of Contents

E.1. Configure Excel to receive data from the Cascade DataHub (using DDEAdvise)
E.2. Write data from Excel - User initiated (using DDEPoke)
E.3. Write data from Excel - Automatically on value change (using DDEPoke)
E.4. Other Useful Macros

This set of Excel macros each work on a 100 row x 40 column table of data in Sheet1 of the worksheet, starting at cell position A1. We have tried to make these macros generic so you can easily modify them to suit your needs.

E.1. Configure Excel to receive data from the Cascade DataHub (using DDEAdvise)

These macros normally need to be run only once, when first setting up a spreadsheet to receive data.

    Attach array data in the Cascade DataHub, one array per row, to a table of values in Excel.  It is often more convenient to transmit large sets of Excel data as an array because this significantly reduces the bandwidth requirements and increases the speed of transmission. This macro sets up DDEAdvise loops from the DataHub to Excel, so that each row of the table is linked to an array point in the DataHub.

    Each data point represents a row of data. This macro assumes the names are "array0001", "array0002", etc.

    ---------------------------------------------------
    Sub register_arrays()
        Dim pname As String
        
        For i = 1 To 100
            pname = Format(i, "0000")
            pname = "=datahub|default!array" & pname
            Worksheets("Sheet1").Range(Cells(i, 1), Cells(i, 40)).FormulaArray = pname
        Next i
    End Sub
    ---------------------------------------------------

    Attach individual point data in the Cascade DataHub, one point per cell, to a table of values.  This macro sets up DDEAdvise loops from the DataHub to Excel, so that each cell in the table is linked to a point in the Cascade DataHub.

    This macro assumes that the data points are named "point0001", "point0002", etc.

    ---------------------------------------------------
    Sub register_points()
        Dim pname As String
        
        For i = 1 To 100
            For j = 1 To 40
                pname = Format((i - 1) * 40 + j, "0000")
                pname = "=datahub|default!point" & pname
                Worksheets("Sheet1").Cells(i, j).Formula = pname
            Next j
        Next i
    End Sub
    ---------------------------------------------------