User experience
As an example, we again look at a function that gets the up-to-date price of a stock. The user experience is summarized below.
| Step | Description | Cell value |
|---|---|---|
| 1 | User types a formula into a cell e.g. =GetPrice("MSFT") | |
| 2 | Temporary error result appears immediately. | #WAIT! |
| 3 | A little later, the latest price appears. | 63.97 |
| 4 | Some time later a new price arrives, and the cell automatically updates (perhaps with a color change to indicate the direction of change). | 64.03 |
Diagrams
The flow diagrams below illustrate the data flow between the three major actors, for this example:
- Excel
- The add-in library
- The data server process or thread
In each case the commentary lists all the important push library methods that are being called by the implementation.
Phase A – User enters formula
The user enters a formula =GetPrice('MSFT') into cell B2. At this stage the Data cache is empty, and the add-in does not have any data for 'MSFT'.
| Step | Description | Methods |
|---|---|---|
| 1 | Excel invokes the add-in function GetPrice(). | |
| 2 | Add-in notes a connection between topic "MSFT" and cell "B2", by calling AddConnection("MSFT"). | AddConnection() |
| 3 | Add-in checks the data cache for "MSFT" and finds nothing. | |
| 4 | Add-in sends an Advise message to the server. | |
| 5 | Add-in returns #WAIT! to Excel as the result of the GetPrice() formula. | CXlOper::Ret() |
Phase B – Server responds
At some later time, the server responds with a message containing the current price of MSFT. It is the responsibility of the add-in to pass the price to any cell in Excel that has asked for it.
| Step | Description | Methods |
|---|---|---|
| 1 | Server responds to Advise message with current price of "MSFT". | ProcessAsyncMessage() |
| 2 | Add-in copies the price data to its data cache. | |
| 3 | Add-in gets a list of cells that are connected to topic "MSFT". | UpdateCells() |
| 4 | Add-in persuades Excel to recalculate all connected cells. |
Phase C – Excel updates itself
As a direct result of phase B, step 4, Excel now immediately forces a recalculation of all cells that have connections to the topic 'MSFT'.
| Step | Description | Methods |
|---|---|---|
| 1 | Excel invokes the add-in function GetPrice("MSFT"). | |
| 2 | Add-in notes the continued presence of a connection between topic "MSFT" and cell "B2". | AddConnection() |
| 3 | Add-in checks data cache for "MSFT" and finds the price. | |
| 4 | Add-in returns value to Excel. | CXlOper::Ret() |
