RTD update
In order to use the function in a spreadsheet, you first need to add a cell that
will be updated by the RTD mechanism. For this we use the built-in Excel
function RTD. The function's arguments are summarized below.
| Argument | Description |
|---|---|
| progid | The unique program ID of the RTD server module. In our case this is always XllRtdLink.XllRtdSeqNumServer. |
| server | The remote machine on which the RTD server is running. We omit this argument, since the server is run locally as an in-process DLL. |
| topic1, topic2... | A list of strings which uniquely identify the data which has changed. We only
use one topic, which should match the channel name used in the
worker thread function: i.e. "AvgOpt". |
Thus we add a formula as follows:
=RTD("XllRtdLink.XllRtdSeqNumServer",,"AvgOpt")
Every time the sequence number of the channel AvgOpt is changed, the cell will automatically update. You should then use it as an input to the cell containing the formula AvgOptValue, so that the cell will automatically be recalculated.
Using the RTD value
You will remember that we added a new argument to the add-in function AvgOptValue(...),
to contain the value of the cell which is updated by RTD. The value itself is
ignored by the add-in function. However, Excel does not ignore it: every time
it changes, the Excel calls the function again.
If the calculation has completed, then the add-in function will now return the result instead of "#WAIT!".
See the sample spreadsheet Samples\AvgOptAsync\AvgOpt.xls for an
example of this technique in use.
Benefits
When you use the sample spreadsheet, you will see some major improvements over the original synchronous model:
- Unintrusive
- While the calculations are running, you can continue to use Excel, with very little noticeable loss of responsiveness.
- Performance improvement
- If you are using a computer with multiple processors, you will see a significant improvement in performance when running multiple calculations. This is because the various threads can run on any processor, instead of being tied to the processor which is running Excel's main thread.
- Responsive
- If you start a long calculation, and then find that one of the inputs must be changed, you don't have to wait for the calculation to end. You can change it right away, and launch the new calculation immediately (or as soon as a thread is available from the thread pool).