[ Home ] [ Products ] [ Data Sheets ] [ Support ] [ FAQ ] [ Sales Offices ] [ Request Catalog ] [ Contact duTec ]

duTec logo and link to home page


The DDE (Dynamic Data Exchange) feature of Microsoft's Windows operating systems gives the control engineer a tool to connect a live, operating process to a spreadsheet. This article shows what it takes to make that connection.

Microsoft's Dynamic Data Exchange (DDE) feature allows separate programs, running simultaneously, to exchange data. The feature is part of application programs like Excel™, FoxPro™, and Lotus-123™. Most MMI software vendors and some I/O hardware vendors now offer DDE-compliant drivers, opening up real potential for the control engineer. A DDE server (or driver) is the source of the data. A DDE client (like Excel) asks for and receives data from the server. More than one client can view data provided by a single server.

With DDE, spreadsheets can collect data in "real-time" (more about that later). Operations like engineering unit conversion, data logging, and trending are obvious uses for the significant computational power of the spreadsheet. The spreadsheet can even implement control decisions by "poking" data back to the server.

When the authors' company, a manufacturer of remote I/O control devices, introduced a DDE driver recently, the technical staff set out to see just how straightforward this process could be. Using Excel for Windows 95 Version 7.0, they found that

Because there are real questions in the literature about the responsiveness of the operating systems involved, the control technique described here should be confined to diagnostic, prototyping, and non-time-critical applications. Critical process control is still the domain of dedicated application programs using a true real-time "kernel," or a separate I/O processor.


DDE-compliant processes communicate with one another through the operating system. Clients identify values to be exchanged with a three-level hierarchy of names, separated by specific punctuation marks:

application | topic ! item

(The first symbol, called a "pipe," often appears above the backslash on a keyboard. The second is an exclamation point.) All three elements of the name are required to uniquely identify the value being passed. At any moment there can be multiple applications at work, supplying and requesting data from one another, using these names. The operating system works like a switchboard, routing requests to the correct server, and returning the requested values to the client that needs them.

In the worked example studied here, the "application" is duTecDDE, a DDE driver (server) program that is in constant communication with a remote duTec I/O PLEXER™ and modules through a serial communication port.

Picture of duTec test fixture.

*Figure 1: Photo of duTec test fixture.

Engineers at duTec configured an I/O PLEXER test fixture, attached to real sensors and actuators-a potentiometer, a thermocouple, a pair of toggle switches, some LED indicators, and a voltmeter [figure 1]. They ran the duTecDDE configuration program to describe the location and type of each signal for later use by the DDE driver. The output of the configuration program appears in a table [see figure 2.]

Output of duTec DDE configuration program
*Figure 2: Output from duTecDDE configuration program.

At this point, the three-part names are completely defined: the "application," duTecDDE, is the name of the driver that will send and receive values from the remote I/O device. The "topic" is the name assigned to the particular configuration we set up, in this case "FIXTURE." Finally, the "items" are defined by the names appearing in the FIXTURE configuration table (i.e., 'Ana(1)' for the analog input, a pot, located in position 1 of the I/O PLEXER.) The fully qualified DDE name of the first analog input point is thus:



To see the current values of the measurements in a spreadsheet, it is only necessary to type the correct DDE names into the cells in which the corresponding values should appear. The correct format is shown in the data-entry just completed for the last signal on the fixture, cell C8, in figure 3.

formula to reference a DDE value from a spreadsheet cell
*Figure 3: Naming a DDE value to appear in a spreadsheet cell

When the spreadsheet is first loaded, Excel notes the existence of any DDE references in its cells. It asks if it should connect to the DDE server(s) referenced in the cells [figure 4.] If the required DDE server is already running, the data will begin appearing after the next scan cycle by the driver. If it is NOT running, Excel will ask if the duTecDDE driver should be started [figure 5.] Excel will look for the driver program only in its current data-path. At the next scan cycle, raw values read from the I/O PLEXER become visible.

Excel notes references to DDE values while loading the spreadsheet
*Figure 4: Excel notes DDE references in a spreadsheet being loaded.
Excel offers to start the DDE server required by the spreadsheet.
*Figure 5: Excel offers to start the DDE driver required by the spreadsheet.

An obvious use for the spreadsheet is to convert raw values to engineering units. The duTecDDE server uses values ranging from 0 to 100 (representing percentage of full scale) for each analog module, and "0" or "1" for discrete values. Given the equivalent zero- and full-scale values, an all-purpose formula, such as that in cell G10 (named "thermocouple") in figure 6, converts analog values to engineering units.

general purpose unit conversion method
*Figure 6: Simple formula for linear engineering unit conversions.

At this point, the spreadsheet is a simple monitor for a running process, used only to capture data and convert it to engineering units. The spreadsheet will function in this mode even if the process is actually under the control of an MMI package, or of the I/O PLEXER itself (which is capable of remote control functions.)

The next obvious step is to compute output values within the spreadsheet and send them, via duTecDDE, to the process under control. The technical staff decided to compute a simple function of elapsed time as the desired output:

f(t) = [ sin ( t )+1 ]*P / 2

This is a low-frequency sine-wave with the amplitude, P, controlled by the potentiometer reading. At each scan cycle, the value f(t) will be sent to an analog output module attached to the voltmeter.


To send data to a DDE process, the operating system requires the client to acquire a "channel" number along which the data will be sent. The Excel built-in function "DDEinitiate" returns such a channel number for a selected application and topic. The spreadsheet itself is used to store this channel number for future use.

The macros in figure 7 control the output process. The macro "ControlDDE", initiated with a keystroke, acquires a channel number and sets up the second macro, "WriteDDE" to execute at the next scan cycle. Aside from housekeeping, the central operation in WriteDDE is the "DDEpoke." The balance of the code is used to turn the output process on and off, by changing values in the spreadsheet.

Figure 7: Control macro
' ControlDDE Macro
' Macro recorded 7/9/97 by duTec
' Intiates DDE output by aquiring a channel number
' and setting WriteDDE as handler for next incoming DDE data
' Keyboard Shortcut: Ctrl+c
Sub ControlDDE()
AppName = Worksheets("Sheet1").Cells(1, 2)
TopName = Worksheets("Sheet1").Cells(2, 2)
Channel = Worksheets("Sheet1").Cells(3, 2)
DDETerminate Channel
Worksheets("Sheet1").Cells(3, 2).Value = _
DDEInitiate(AppName, TopName)
Channel = Worksheets("Sheet1").Cells(3, 2)
Worksheets("Sheet1").Cells(4, 5).Value = "AUTO"
Worksheets("Sheet1").OnData = "WriteDDE"
End Sub
' WriteDDE Macro
' Macro recorded 6/27/97 by duTec
' controls output from spreadsheet to DDE and modules
' Keyboard Shortcut: Ctrl+w
Sub WriteDDE()
' disable DDE conection momentarily
Worksheets("Sheet1").OnData = ""
' check for a change in scan time. Get out if it hasn't changed.
Channel = Worksheets("Sheet1").Cells(3, 2)
PassiveLast = DDERequest(Channel, "LastScanTime")
If PassiveLast(LBound(PassiveLast)) = Worksheets("Sheet1").Cells(3, 5) Then
Worksheets("Sheet1").OnData = "WriteDDE"
Exit Sub
End If
' Cells (4,5) controls state of DDE outputs:
' "OFF" means leave it disabled
' "AUTO" means poke data and keep it running
Control = UCase(Worksheets("Sheet1").Cells(4, 5))
If Control <> "OFF" Then
' Set to automatic rerun, so poke selected outputs to DDE
DDEPoke Worksheets("Sheet1").Cells(3, 2), _
Worksheets("Sheet1").Cells(13, 1), _
Worksheets("Sheet1").Cells(13, 5)
DDEPoke Worksheets("Sheet1").Cells(3, 2), _
Worksheets("Sheet1").Cells(15, 1), _
Worksheets("Sheet1").Cells(15, 5)
DDEPoke Worksheets("Sheet1").Cells(3, 2), _
Worksheets("Sheet1").Cells(16, 1), _
Worksheets("Sheet1").Cells(16, 5)
' If plotting is set to "SCAN", then do Update once now.
Freq = UCase(Worksheets("Sheet2").Cells(2, 5))
If Freq = "SCAN" Then Update
' recall this macro again on next DDE scan.
Worksheets("Sheet1").OnData = "WriteDDE"
End If
End Sub

This WriteDDE macro also illustrates an important point-the programmer must guard against "interrupts." Conventionally, this is done by momentarily disabling the DDE connection early in the macro, then turning it back on at the very end. Without this precaution, a second DDE event could arrive before the first is completely processed.

Our staff also found it necessary to control for multiple initiations of the macro at each scan cycle. (Each arriving input data point apparently triggered a macro cycle.) The duTecDDE driver supplies a useful time-of-day value as one of its "system" DDE variables


which can be read like any other. It provides the computer's clock value as of the latest duTecDDE scan. Code was included in Write DDE to ensure that output operations occur only if the LastScanTime value has changed since the last time through the macro code.

Since the spreadsheet MONITOR . XLS combines data from a process (the pot setting) with a computed value (the wave-form above, as a function of "real" time) and outputs it to a control actuator (in this case a voltmeter), "process control with a spreadsheet" has now in fact been demonstrated.


The next step is to collect a series of time-stamped values- of the control output in this case-in the form of a simple data log. This took some thought, but is easy to reproduce.

Logging data, for this spreadsheet, meant

  1. discarding the oldest point at the top of a 40-row table,
  2. pushing everything else up one row, then
  3. recording the latest point in the last row of the table.

Fore simplicity of presentation, we created a second "worksheet" within the same Excel spreadsheet to hold this data. Range names and the areas to which they refer are shown in figure 8, and the macro code itself is given in figure 9. A user can redefine [using the Excel "define name" feature] the ranges referenced in figure 8 to suit other requirements- adding data values, or changing the number of points to be logged. The macro itself need not be modified if these ranges are moved or re-dimensioned.

*Figure 8: A data log can include time-of-day stamps with data values.

Figure 9: Macro code for logging data in a table.

' Update Macro
' Macro recorded 6/26/97 by duTec
' Keyboard Shortcut: Ctrl+u
Sub Update()
' code below was generated with 'record macro' function
Application.Goto Reference:="PushFrom"
Application.Goto Reference:="Scratch"
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Application.Goto Reference:="PushTo"
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.Goto Reference:="NewPoint"
Application.CutCopyMode = False
Application.Goto Reference:="LastPoint"
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
' Code added manually to re-run this macro,
' after an interval specified in the spreadsheet
' at the cell (2,5) labelled "Every". Setting "Every" to
' a value of zero or less will halt automatic updates. Setting it
' to "SCAN" will tie updates to the "WriteDDE" scan cycle.
Freq = UCase(Worksheets("Sheet2").Cells(2, 5))
If Freq <> "SCAN" Then
NextTime = Now + Freq
Worksheets("Sheet2").Cells(3, 5).Value = NextTime
If Freq > 0 Then
Worksheets("Sheet2").Cells(2, 3).Value = "AUTO"
Application.OnTime _
EarliestTime:=NextTime, _
Worksheets("Sheet2").Cells(2, 3).Value = "OFF"
End If
End If
End Sub

Once the macro, called "Update( )", was created (and tied to the keystroke control+u for testing purposes) code was added as noted in figure 9 to make it run repetitively. Such code is actually a snippet of Visual Basic™ programming language, and can be edited in once the macro is created. The frequency of updating was taken from cell B5 in the spreadsheet so it could be changed manually. Setting a value of zero or less stops the updating process. The "OnTime" function causes the macro to execute after the specified interval. Our staff found it most convenient to set the Frequency value to "SCAN," which initiates an update every time the WriteDDE macro executes.


Trending recorded values is straightforward once a data log is established. It is only necessary to select the values to be plotted (in this case, the last 40 values sent out to the voltmeter) and click on the "Chart Wizard" button in Excel's menu. The cursor changes to a little "crosshair," asking the user to find a suitable rectangular area on the spreadsheet to receive the chart, then answer a series of questions about the form the chart should take. A simple line chart with horizontal scale indicators [figure 10] was placed near the end of the log.

*Figure 10: The simple trend chart shows the most recent data values logged.


Microsoft cautions that the Windows operating system, even the NT version, is not a "hard" real-time operating system. Others echo the sentiment, and explain why,, while disagreeing about how to overcome it. The biggest problem is dubbed "priority inversion", where low-level, non-critical tasks can interrupt and impede higher-priority tasks.

Once the macros were debugged, the process did not "crash," even while unrelated processes, like editing this article, were carried out on the same PC. (In fact, it continued to plot points while running in the background.)

On the other hand, our experience verifies that the scan times are visibly "non-deterministic." The whole process can be upset by unrelated events (e.g. someone on the network wants to use a file on the PC running the spreadsheet.) Problems of lost data were observed even with the simple process modeled here. Delays of three to four seconds appear as unit conversions are carried out. The plot occasionally missed a scan cycle for updating. Sometimes, the process ground to a halt after 10 or 12 hours of operation, for no discernible reason. For these reasons, the control features of a spreadsheet probably should be limited to

"Soft Real-Time Systems whose requirements allow for deadlines to be missed from time to time."

Prototyping, diagnosing, or monitoring a process under the steadier control of a true real-time processor (such as the duTec I/O PLEXER itself), would be examples of such "soft" systems.

Top of Page

[ Home ] [ Products ] [ Data Sheets ] [ Support ] [ FAQ ] [ Sales Offices ] [ Request Catalog ] [ Contact duTec ]

© Copyright 1996-2001 duTec. All rights reserved.
For feedback on this site, please send email to: webmaster@dutec.net