 |
 |
||
 |
||
        |
||
|
Helpful Excel Macros
Introduction
You are free to use the following Macro examples in your own Excel applications. These macros provide useful shortcuts and hints that you can use to create interactive Excel application that read and write data with the DataHub.
Review of DataHub to Excel Connection Options
Overview
These Excel macros are based 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.
Macros to help configure Excel to read data from the DataHub (using DDEAdvise)
1. Macro to attach array data in the 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. Normally, you would only need to run this macro once, when you are designing your spreadsheet. 
Each data point, representing a row of data, is named "array0001", "array0002", etc.
2. Attach individual point data in the DataHub, one point per cell, to a table of values.
--------- Add to Sheet 1 Macro Code -------------
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
---------------------------------------------------
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 DataHub. Normally, you would only need to run this macro once, when you are designing your spreadsheet. 
Each data point is named "point0001", "point0002", etc.
--------- Add to Sheet 1 Macro Code -------------
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
---------------------------------------------------
Macros to write data from Excel - User initiated write on demand (using DDEPoke)
The following macros are useful for writing data out from Excel on demand, in other words, the user decides when to write the data and initiates the write by running one of these macros (usually from an assigned button click). 3. Transmit array data, one array per row to points in the DataHub. This macro would be triggered any time you wanted to write data from the table in Excel to the DataHub. This macro would write each row of the table as an array point in the DataHub. The macro transmits all rows of the table, one after another. 
Each data point, representing a row of data, is named "array0001", "array0002", etc.
4. Transmit individual point data, one point per cell to points in the DataHub.
--------- Add to Sheet 1 Macro Code -------------
Sub transmit_arrays()
Dim chan As Integer
Dim pname As String
chan = DDEInitiate("datahub", "default")
For i = 1 To 100
pname = Format(i, "0000")
pname = "array" & pname
DDEPoke chan, pname, Worksheets("Sheet1").Range(Cells(i, 1), Cells(i, 40))
Next i
DDETerminate (chan)
End Sub
---------------------------------------------------
This macro would be triggered any time you wanted to write data from the table in Excel to the DataHub. This macro would write each cell in the table to a single point in the DataHub. The macro transmits all cells in the table, one after another. 
Each data point is named "point0001", "point0002", etc.
--------- Add to Sheet 1 Macro Code -------------
Sub transmit_points()
Dim chan As Integer
Dim pname As String
chan = DDEInitiate("datahub", "default")
For i = 1 To 100
For j = 1 To 40
pname = Format((i - 1) * 40 + j, "0000")
pname = "point" & pname
DDEPoke chan, pname, Worksheets("Sheet1").Cells(i, j)
Next j
Next i
DDETerminate (chan)
End Sub
---------------------------------------------------
Macros to write data from Excel - Automatic write on value change (using DDEPoke)
The following macros can be useful for automatically transmitting data from Excel into the DataHub. 5. Whenever the user enters a new value, check to see if that cell is named. If so, emit the new value to a DataHub point of the same name. The subroutine name "Worksheet_Change" is special - it is called by Excel whenever a change occurs on the Worksheet due to user input or recalculation (though not a change due to a DDE message, see #7 below).
--------- Add to Sheet 1 Macro Code -------------
Sub Worksheet_Change(ByVal Target As Range)
Dim rname As String
Dim channel As Variant
On Error Resume Next
rname = Target.name.name
If Not rname = "" Then
channel = DDEInitiate("datahub", "default")
DDEPoke channel, rname, Target
DDETerminate (channel)
End If
End Sub
---------------------------------------------------
This macro is useful because you do not have to configure each cell you want to write out to the DataHub. If the cell that is changed lies within a defined range, then all values in that range are automatically written out to the DataHub. This routine determines the enclosing range for the change, and if the range matches one of a predefined set, it will send that range to the DataHub. The function determines the name of the cell range that intersects a given range. If more than one named range in the worksheet intersects the given range, return the first one.
-------- Add to Workbook Macro Code -------------
Function NameOfParentRange(Rng As Range) As String
Dim Nm As Name
For Each Nm In ThisWorkbook.Names
If Rng.Parent.Name = Nm.RefersToRange.Parent.Name Then
If Not Application.Intersect(Rng, Nm.RefersToRange) Is Nothing Then
NameOfParentRange = Nm.Name
Exit Function
End If
End If
Next Nm
NameOfParentRange = ""
End Function
--------- Add to Sheet 1 Macro Code -------------
Sub Worksheet_Change(ByVal r As Range)
Dim pname As String
Dim chan As Integer
pname = ThisWorkbook.NameOfParentRange(r)
If Not pname = "" Then
On Error Resume Next
chan = DDEInitiate("datahub", "default")
DDEPoke chan, pname, Worksheets("Sheet1").Range(pname)
DDETerminate (chan)
End If
End Sub
---------------------------------------------------
Other useful Excel macros
7. Cause a macro to run if a DDE message is received for a particular point. The following two macros illustrate how to set up a linkage so that a macro is run every time a cell value is updated by a DDE message. In our example below, we assume there is a cell being updated with a point from the DataHub. We define a linkage, so that a macro is run every time this point is updated. 
We will assume that point0001 is being updated by a DDEAdvise link from the DataHub. The
set_link macro creates the linkage between the point0001 DataHub point and the link_updated macro. The link_updated() macro simply increments the value in cell A1. You only need to run the set_link macro once (possibly when the sheet is loaded) to establish the linkage. Whenever the cell containing point0001 is updated by the DataHub, the link_updated macro will run and the value in Cell (1,1) will be incremented.
--------- Add to Sheet 1 Macro Code -------------
Sub link_updated()
Cells(1, 1) = Cells(1, 1) + 1
End Sub
-------- Run once to establish link -------------
Sub set_link()
ThisWorkbook.SetLinkOnData "datahub|default!'point0001'", "Sheet1.link_updated"
End Sub
---------------------------------------------------
More Information
|
Hide macro code