The first example of an add-in that uses object handles can be found in the HandleAPI sample, and all the source code discussed below can be found in the Samples/HandleAPI sub-directory.
First we need to define the class that will hold the data for our example, MyObj. This is a simple class which holds two numbers X and Y, and the vector results of a calculation that uses them.
class MyObj {
public:
MyObj(double X, double Y);
MyObj(double X);
~MyObj();
double X() const { return m_dX; }
double Y() const { return m_dY; }
const std::vector<double>& Vec() const { return m_vec; }
protected:
double m_dX, m_dY;
std::vector<double> m_vec;
};
You will notice that the object has two constructors (each of which also performs a calculation), and three getter functions, X(), Y() and Vec().
We add a line of code to the application class, CHandleAPIApp, in HandleAPI.h, which declares a handle store at application level, containing instances of MyObj:
class CHandleAPIApp : public CXllApp
{
public:
...
CXlHandleStore<MyObj> m_hsMyObj;
...
};
Note that the second argument to the template - the scoping class - is omitted. The default value scope_ptr<MyObj> will therefore be used. This is a very simple pointer container class, which will destroy the MyObj instance when the handle is removed from the handle store. In effect, the handle store "owns" each MyObj instance, and has complete control of its lifetime.
Next, we add some wrapper functions which expose the handle store's functionality, to be used by add-in functions.
class CHandleAPIApp : public CXllApp
{
public:
...
long AddObjectHandle(MyObj* obj) {
return m_hsMyObj.AddObjectHandle(obj);
}
const MyObj* ObjectFromHandle(long Handle) const {
return m_hsMyObj.ObjectFromHandle(Handle);
}
...
};
When an add-in function (specifically, an object creation function) needs to create a MyObj and return its handle, it calls AddObjectHandle() and returns the handle to Excel.
When a getter add-in function needs to translate a handle received from Excel, it calls ObjectFromHandle() and extracts the desired data from the returned MyObj pointer.
Note that ObjectFromHandle() returns a const pointer, because the MyObj instance should generally be read-only in a getter function.
We add the object creation functions to the project as new add-in functions. The first creation function takes two arguments, X and Y:
IMPLEMENT_XLLFN2(MyObjCreate1, "JBB", "MyObj.Create1", "X,Y",
"User Defined", "Object creation function", "No description p"
"rovided\000No description provided\000", "\0\0appscope=1\0",
1)
extern "C" __declspec( dllexport )
long MyObjCreate1(double X, double Y)
{
XLL_FIX_STATE;
MyObj* o = new MyObj(X, Y);
return XllGetTypedApp()->AddObjectHandle(o);
}
Note that the function returns a long integer.
Almost all the work of coding is done by the XLL+ Function Wizard; we only have to add the lines shown above in bold. First we create a MyObj instance, passing the add-in function's arguments to the constructor. The constructor performs its calculations and retains the results within the object.
The second line calls the AddObjectHandle() method of the application object, which adds the new object to the handle store, and returns the resulting handle to Excel.
One more thing to notice about this function is that it is exposed in Excel as MyObj.Create1, since dot notation is more natural in Excel than using an underscore.
The second object creation function is very similar, and takes only one argument (X) for the calculation of MyObj:
IMPLEMENT_XLLFN2(MyObjCreate2, "JB", "MyObj.Create2", "X",
"User Defined", "Object creation function", "No description p"
"rovided\000", "\0appscope=1\0", 1)
extern "C" __declspec( dllexport )
long MyObjCreate2(double X)
{
XLL_FIX_STATE;
MyObj* o = new MyObj(X);
return XllGetTypedApp()->AddObjectHandle(o);
}
Now we add the getter functions which extract data from an object and return it to Excel. Again, note the dot notation used for the function name in Excel.
IMPLEMENT_XLLFN2(MyObj_X, "RJ", "MyObj.X", "Handle", "User Define"
"d", "No description provided", "No description provided\000"
, "\0appscope=1\0", 1)
extern "C" __declspec( dllexport )
LPXLOPER MyObj_X(long Handle)
{
XLL_FIX_STATE;
CXlOper xloResult;
const MyObj* obj = XllGetTypedApp()->ObjectFromHandle(Handle);
if (obj != 0)
xloResult = obj->X();
else
xloResult = xlerrRef;
return xloResult.Ret();
}
Let us examine the interesting code (shown in bold above). First we pass the Handle argument to the application object, to translate it to an object pointer. If the translation fails, because the handle is not valid, then ObjectFromHandle() returns a null pointer and the add-in function returns #REF! to Excel.
If the translation succeeds, then the add-in function extracts the value of X and returns that to Excel.
Other getter functions follow an identical pattern. For example, to return the vector of data contained in a MyObj object:
IMPLEMENT_XLLFN2(MyObj_Vec, "RJ", "MyObj.Vec", "Handle",
"User Defined", "No description provided", "No description pr"
"ovided\000", "\0appscope=1\0", 1)
extern "C" __declspec( dllexport )
LPXLOPER MyObj_Vec(long Handle)
{
XLL_FIX_STATE;
CXlOper xloResult;
//}}XLP_SRC
const MyObj* obj = XllGetTypedApp()->ObjectFromHandle(Handle);
if (obj != 0)
xloResult = obj->Vec();
else
xloResult = xlerrRef;
return xloResult.Ret();
}
Readers may already have asked themselves two questions about this implementation:
What about persistence - what happens to the object when the workbook containing it is closed, and what does the handle mean when the workbook is opened again?
What about garbage collection - how do we release the memory used by MyObj instances that are no longer required?
Both questions are addressed by making use of a utility class, CXlFormulaRefresh. This class contains methods for forcing a recalculation of any cell that contains an object creation function. We use the class on two occasions.
Firstly, whenever the add-in is closed (i.e. when Excel is shut down), all the MyObj instances are thrown away. So when a workbook containing handles is reopened, they are invalid, since they refer to the memory occupied by objects which no longer exist. To ensure that all handles in a newly opened workbook are valid, we must ensure that they are all recalculated - as if the user selected the cell and then pressed F2 then Enter.
We need to add a method to the application class to force this recalculation. The application class method is declared inline as follows:
class CHandleAPIApp : public CXllApp
{
public:
...
static const char* mc_apszMyObjCreationFns[];
...
void RefreshWorkbook(const char* pszWorkbookName) {
CXlFormulaRefresh refr(mc_apszMyObjCreationFns);
refr.RefreshWorkbook(pszWorkbookName);
}
...
};
The static array mc_apszMyObjCreationFns contains a null-terminated list of the names of each of our object creation functions, as they appear in Excel:
const char* CHandleAPIApp::mc_apszMyObjCreationFns[] = {
"MyObj.Create1",
"MyObj.Create2",
0
};
The RefreshWorkbook method creates a CXlFormulaRefresh object and loads it up with a list of the names of our object creation functions. It then runs the CXlFormulaRefresh::RefreshWorkbook() method, which searches through the named workbook and finds each cell that has a formula which contains a call to either of the functions. For every cell found it forces a recalculation.
Finally, we need to make sure that the RefreshWorkbook() method is called for every workbook as it is opened. We do this by writing an add-in function to call the method, and by registering the add-in function as an event handler for the WorkbookOpen event.
The add-in function is a simple shell, which has the signature required for a WorkbookOpen event handler (see CXllApp::SetComEventHandler() for details).
IMPLEMENT_XLLFN2(HandleAPI_WorkbookOpen, "RC", "HandleAPI_WorkbookOpen"
, "WorkbookName", "User Defined", "No description provided",
"No description provided\000", "\0appscope=1\0", 2)
extern "C" __declspec( dllexport )
LPXLOPER HandleAPI_WorkbookOpen(const char* WorkbookName)
{
XLL_FIX_STATE;
CXlOper xloResult;
// Update all handles in this workbook
XllGetTypedApp()->RefreshWorkbook(WorkbookName);
// Return value is ignored
return xloResult.Ret();
}
To register the function as an event handler, we add a couple of lines to the application class's OnXllOpenEx() method:
BOOL CHandleAPIApp::OnXllOpenEx()
{
...
SetSinkEvents(TRUE);
SetComEventHandler(EVT_WORKBOOKOPEN, "HandleAPI_WorkbookOpen");
return TRUE;
}
The first line switches on event handling for this XLL, and the second registers the add-in function HandleAPI_WorkbookOpen as the event handler for WorkbookOpen events.
From time to time we may wish to recover the memory used by MyObj instances that are no longer of interest to any getter function. We need to add the required functions:
The first method is declared inline in the application class definition:
class CHandleAPIApp : public CXllApp
{
public:
...
void RefreshAllWorkbooks() {
// Clear all existing objects from handle store
m_hsMyObj.Clear();
// Recreate all handles
CXlFormulaRefresh refr(mc_apszMyObjCreationFns);
refr.RefreshAllWorkbooks();
}
...
};
First, the method clears the handle store, removing all handles and deleting all the corresponding objects. Second, the method uses an instance of CXlFormulaRefresh to recalculate every object creation function in all open workbooks. This has the effect of removing all handles that are no longer used, and creating new handles for all objects that are in use.
(Note: there is a performance cost to this approach, since all objects are completely recreated each time garbage is collected. The example following this one demonstrates a technique for avoiding this cost.)
The add-in function is a simple wrapper:
IMPLEMENT_XLLFN2(HandleAPI_RefreshAll, "A", "HandleAPI_RefreshAll"
"", "", "User Defined", "Refresh all object handles, and"
" collect garbage", "", "appscope=1\0", 2)
extern "C" __declspec( dllexport )
BOOL HandleAPI_RefreshAll()
{
XLL_FIX_STATE;
XllGetTypedApp()->RefreshAllWorkbooks();
return 0;
}
The add-in function is plugged into a menu by the application class's OnXllOpenEx method:
BOOL CHandleAPIApp::OnXllOpenEx()
{
// Set up menu
m_menu.SetTexts("H&andles");
m_menu.AddItem("&Refresh", "HandleAPI_RefreshAll");
m_menu.Create();
...
return TRUE;
}
Implementing the object handle technique in this add-in required the following steps:
Instantiate a handle store of type CXlHandleStore<MyObj> as a member of the application class CHandleAPIApp, and add two wrapper functions to expose it's methods.
Write an add-in function for each method that creates a MyObj data set. The add-in function should call AddObjectHandle() in order to add the object to the store, and return the handle to Excel.
Write getter add-in functions for each method which will extract data from MyObj. A getter function takes an object handle as one of its arguments. Each getter function should call ObjectFromHandle() to transform the handle to an object pointer, and then return data extracted from the object.
Write an add-in function that will force a recalculation of any cell in a particular workbook that contains an object creation function. Register the add-in function as a WorkbookOpen event handler.
Write an add-in function that will clear the handle store and then recalculate any cell in any workbook that contains an object creation function. Make the function available from a menu.
It is worth noting that the implementation itself is considerably shorter than this discussion of it.