Download (direct link):
1. The DLL creates the data structure in a call to MakeArray.
2. The DLL creates, populates and returns an array structure that Excel understands. (See sections 6.2.2 Excel floating-point array structure: xl_array and 6.8.7 Array (mixed type): xltypeMulti.)
3. Excel copies out the data into the spreadsheet cells from which MakeArray was called (as an array formula) and frees the resources (which might involve a call to xlAutoFree).
4. Excel recalculates all cells that depend on the returned values, including UseArray.
5. Excel passes a reference to the range of cells to UseArray.
6. The DLL converts the reference to an array of values.
7. The DLL uses the values.
Despite its simplicity of implementation, there are a number of disadvantages with the above approach:
• MakeArray might return a variable-sized array which can only be returned to a block of cells whose size is fixed from edit to edit.
• There is significant overhead in the conversion and hand-over of the data.
• There is significant overhead in keeping large blocks of data in the spreadsheet.
Excel Add-in Development in C/C++
• The data structures are limited in size by the dimensions of the spreadsheet.
• The interim data are in full view of the spreadsheet user; a problem if they are private or confidential.
If the values in the data structure do not need to be viewed or accessed directly from the worksheet, then a far more efficient approach is as follows:
1. DLL creates the data structure in a call to MakeArray as a persistent object.
2. DLL creates a text label that it can later associate with the data structure and returns this to Excel.
3. Excel recalculates all cells that depend on the returned label, including UseArray.
4. Excel passes the label to UseArray.
5. DLL converts the label to some reference to the data structure.
6. DLL uses the original data structure directly.
Even if the structure’s data do need to be accessed, the DLL can export access functions that can get (and set) values indirectly. (When setting values in this way it is important to remember that Excel will not automatically recalculate the data structure’s dependants, and trigger arguments may be required.) These access functions can be made to operate at least as efficiently as Excel’s INDEX(), MATCH() or LOOKUP() functions.
This strategy keeps control of the order of calculation of dependant cells on the spreadsheet, with many instances of UseArray being able to use the result of a single call to MakeArray. It is a good idea to change the label returned in some way after every recalculation, say, by appending a sequence number. (See section 2.11 Excel recalculation logic, for a discussion of how Excel recalculates dependants when the precedents have been recalculated and how this is affected by whether the precedent’s values change or not.)
To implement this strategy safely, it is necessary to generate a unique label that cannot be confused with the return values of other calls to the same or similar functions. It is also necessary to make sure that there is adequate clearing up of resources in the event that a formula for MakeArray gets deleted or overwritten or the workbook gets closed. This creates a need to keep track of those cells from which MakeArray has been called. The next section covers the most sensible and robust way to do just this. The added complexity of keeping track of calls, compared with returning the array in question, means that where MakeArray returns a small array, or one that will not be used frequently, this strategy is overkill. However, for large, computationally intense calculations, the added efficiency makes it worth the effort. The class discussed in section 9.7 A C++ Excel name class example, xlName, on page 307, simplifies this effort considerably.
A simpler approach is to return a sequence number, and not worry about keeping track of the calling cell. However, you should only do this when you know that you will only be maintaining the data structure from one cell, in order to avoid many cells trying to set conflicting values. A changing sequence number ensures that dependencies and recalculations are handled properly by Excel, although it can only be used as a trigger, not a reference to the data structure. A function that uses this trigger must be able to find the data structure without being supplied a reference: it must know from the context or from other arguments. This simpler strategy works well where the DLL needs to maintain a table of global or unique data. Calls to MakeArray would update the table and return
Miscellaneous Topics 307
an incremented sequence number. Calls to UseArray would be triggered to recalculate something that depended on the values in the table.
9.7 A C++ EXCEL NAME CLASS EXAMPLE, xlName
This section describes a class that encapsulates the most common named range handling tasks that an add-in is likely to need to do. In particular it facilitates:
• the creation of references to already-defined names;