Tip #2000-7: Keep your DDE links running smoothly
How can I make my Dynamic Data Exchange (DDE)
links update faster?
No one likes to see N/A in their cells, waiting for DDE links to
update. Here's a quick checklist of actions that halt DDE traffic.
Keep these actions to a minimum to make your cells update as
fast as possible.
- Dialog box open
- Pull-down menus
- Edit Mode active
- Macros in action
- Printing large documents
- Saving large documents
As the name implies DDE conversations are dynamic - whenever an
update occurs for a particular instrument, a message is immediately
sent to the worksheet cell containing the link and the new value
is displayed. However, sometimes Excel is busy doing something
else and updates cannot be sent to the worksheet. When Excel
is otherwise occupied, updates are queued by the DDE server
until Excel is available to process them. If Excel is busy for
a very long time, or if there are many DDE messages, the queue
may fill up. When this happens, a DDE over-run occurs and updates
can be lost. At first glance, this may not seem like a big deal,
but to the trader who has invested millions of dollars in a
particular trade, it can be critical, especially if the trader
misses a crucial price update.
The best way to avoid these situations is to be aware of the activities
that can tie up Excel for extended periods of time. These include:
Dialog Boxes/Pull-Down Menus: Whenever a dialog box is displayed
on the desktop or a menu is pulled down, DDE traffic halts.
This includes dialog boxes that query you for information, such
as the Save As box, as well as Alerts that notify you of an
important situation or prompt you for more information, such
as "File exists. Overwrite?" Be sure to clear dialog
boxes from your desktop promptly.
Editing: Any form of cell editing, whether you're using the formula bar
or in-cell editing, halts DDE traffic. Excel waits until you
press the Enter key before allowing messages to continue to
flow between applications. It's particularly important for you
to be aware of this fact because it's very easy to be in edit
mode without realizing it; all you have to do is double-click
a cell or click within the formula bar to enter edit mode.
Printing & Saving Large Documents: Because printing and saving
large files can take a considerable amount of time, they can
delay DDE traffic.
Macros: When macros are running, live DDE traffic to the worksheet is
halted, even though macro commands that request DDE data (such
as snap requests) can still be performed. This is especially
important to understand because, if a macro writes a DDE formula
onto a spreadsheet, the actual value will not be returned to
this formula until after the macro ends.
Some clarification may be necessary at this point to understand the
difference between delayed updates and a DDE over-run. When
updates are delayed, perhaps because you're editing a cell or
printing a document, the updates are queued by the server. As
soon as you quit editing or printing, these updates are immediately
sent to the worksheet. If multiple updates exist for the same
cell, they'll be sent immediately, one after another and at
such a fast pace that you may not be able to see each individual
update. However, any active programs that are monitoring the
updates, such as an alert minder, would read and analyze each
and every update. And if one of the updates exceeded a predefined
limit, you'd be notified.
Contrast this scenario with another in which the DDE server actually
goes into an over-run situation. In this case, because Excel
is busy for an extended length of time, the DDE queue becomes
full. When the queue is full, the DDE server cannot hold any
more updates and must drop the most recent updates. When Excel
is again free, the updates that have been queued are immediately
sent to the worksheet. However, because the specific update
that broke the alert minder's predefined limit was one of the
last updates, it was never added to the queue, so the program
monitoring the worksheet was never triggered and you are never
notified.
These two scenarios illustrate why it's essential that you avoid actions
that halt DDE traffic for any extended period of time, particularly
when the source of the live data, the financial markets, is
very active.
|
