Many programmers wish to be able to hold objects in memory and access them from Excel worksheets. A calculation may produce a vast amount of data, including for example:
For example, a function MonteCarlo(...) may produce a final value, statistical measures indicating the certainty of the value, a very large series of random numbers used by the calculation, and performance figures that show the cost of the calculation. The entire result set can be placed in an object, and exposed in Excel as a numeric handle to the object.
Thus the main function, MonteCarlo(...) returns a long integer, which is a handle to the result set. Various getter functions, such as MonteCarlo.Value(handle), MonteCarlo.Statistics(handle) and MonteCarlo.RandomSeries(handle, index) can be used to show the interesting results in the worksheet.
This pattern can be implemented very simply using the CXlHandleStore<T, S> template class. The arguments to the template are as follows:
| Argument | Description |
|---|---|
| class T | The class of the object to be stored |
| class S | The scoping class, which ensures that the object stored remains alive as long as it is needed, and that it is destroyed when it is no longer needed. The scoping class argument can be omitted, in which case the class scope_ptr<T> will be used. |
The CXlHandleStore object provides three methods that can be used to transform objects to handles and vice-versa, and to manage the liftimes of the objects contained in the store.
| Method | Description |
|---|---|
| AddObjectHandle | Convert an object pointer to a handle, and add it to the store. |
| ObjectFromHandle | Convert a handle passed from Excel to an object pointer. |
| Clear | Clear all objects from the store. |
There are 5 important steps in implementing a handle store in an add-in.
Create a class which will represent the object. This will usually be either an existing object class or a thin wrapper around an existing object.
Add an instance of the handle store class to the add-in's application object, e.g. CXlHandleStore<MyObject>.
Write an add-in function that creates the object, adds it to the handle store and returns the add-in's handle to Excel. (This function is referred to as the "object creation function".)
Write "getter" add-in functions that take a handle as their first argument, transform the handle to an object pointer, extract data from the object, and return the data to Excel.
Write two macro add-in functions that force a recalculation of any cell that contains an object creation function. These are required for "house-keeping" purposes.
The examples which follow this topic demonstrate each step of the implementation.
The CXlHandleStore class is very simple. It's full implementation is shown below.
template <class T, class S = scope_ptr<T> >
class CXlHandleStore {
public:
typedef CXlHandleStore<T, S> this_type;
typedef T value_type;
typedef S scope_type;
typedef long handle_type;
public:
CXlHandleStore<T, S>() {}
~CXlHandleStore<T, S>() {}
const value_type* ObjectFromHandle(handle_type handle) const {
coll_type::const_iterator itF = m_coll.find((value_type*)handle);
return (itF != m_coll.end()) ? itF->first : 0;
}
handle_type AddObjectHandle(value_type* value) {
m_coll[value] = scope_type(value);
return (handle_type)value;
}
void Clear() {
m_coll.clear();
}
protected:
typedef std::map<value_type*, scope_type> coll_type;
coll_type m_coll;
};
You can observe that the collection m_coll contains a map in which values of type T* are used to index values of type S. In addition each instance of S is constructed from the corresponding T*. Since S is a smart pointer to T, it manages the lifetime of the object pointed to by T*. Thus, when a key is removed from the map, it is automatically released by the desctruction of the smart pointer.
You can also see that a handle is simply a numeric representation of a pointer, and that the handle and the pointer can be transformed into each other by casting.