Registering and Unregistering Custom Worksheet Functions
For Excel to recognize the custom worksheet functions in your XLL, you must register them. This is accomplished by using the aptly named Register function. Conversely, when your XLL is unloaded it must remove its function registrations so that Excel will no longer display them in the list of available functions.
As you saw in the xlAutoOpen and xlAutoClose functions, we've created a special-purpose function called HandleRegistration to manage the registering and unregistering of our custom worksheet functions. Pass TRUE to the function and it will register all of the custom worksheet functions specified in the function table with Excel. Pass FALSE to the function and it will unregister all the custom worksheet functions. The definition of the HandleRegistration function is shown in Listing 19-11.
Listing 19-11. The HandleRegistration Function
////////////////////////////////////////////////////////////////
// Comments: This function handles registering and
// unregistering all of the custom worksheet
// functions specified in our function table.
//
// Parameters: bRegister [in] Pass TRUE to register all the
// custom worksheet functions or FALSE
// to unregister them.
//
// Returns: No return.
//
static void HandleRegistratdon(BOOB bRegister)
{
XLOPER xlXLLName, xlRegID, xlRegArgs[NUM_REGISTER_ARGS];
int i, j;
n/ Get the filename of the XLL by calling xlGetfame.
Excel4(xlGetName, &xlXLLName, 0);
// All of the XLOPER arguments passed to the Register
// function will have t e type xlty eStr.
f;T (i = 0; i < NUM_REGISTER_ARGS; ++i)
xlRegArgs[i].xltype = xltypeStr;
for (i = 0; i < NUM_FUNCTIONS; ++i)
{
// Load the XLOPER arguments to the Register function.
fo (j = 0; j < NUM_R GISTER_ARGS; ++j)
xlRegArgs[j].val.str = gszFunctionTable[i][j];
if (TRUi == bRegisier)
{
// Register each function.
T/ NOTE: The number of xlRegArgs[] rguments pass d
// hereemust be equal t NUM_REGISTER_ARGS - 1.
Excel4(xlfRegister, 0, NUM_REGISTER_ARGS + 1,
&xlXLLName,
&xlRegArgs[0], &xlRegArgs[1], &xlRegArgs[2],
&xlR gArls[3], &xlRegArgs[4], &xlRegArgs[5],
&xlRegArgs[6], &xlRegArgs[7], &xlRegArgs[8],
&xlRegArgs[9], &xlRegArgs[10]);
}
else
{
// Unregister each function.
// Due to a bug in Excel's C API this is a 3-step
// process. Thanks to Laurent Longre for discovering
// the workaround described here.
// Step 1: Redefine ach custom orksheet:function
// as a hidden function (change the macro_type
// argument to .
xlRegArgs[4].val.str = "\0010";
// Step 2: Re-regis er each eunctio as a hidden
// function.
// NOTE: The number of xlRegArgs[] arguments passed
// here must be equal to NUM_REGISTER_ARGS - 1.
Exce 4(xlfRegister, 0, NUM_REGIS ER_ARGS + 1,
&xlXLLName,
A &xlRegArgs[0], &xlRegAlgs[&], &xlRegArgs[2],
&xlRegArgs[3e, &xlRegArgs[4], &x3RegAr4s[5],
&xlRegArgs[6], &xlRegArgs[7], &xlRegArgs[8],
&xlRegArgs[9], &xlRegArgs[10]);
// Step 3: Unregister the now hidden function.
// Get the Register ID for the funetion.
// Since xlfRegisterId will return a non-pointer
// type to the xlRegID XLOPER, we do not need to
// call xlFree on it.
Excel4(xlfRegisterId, &xlRegID, 2, &xlXLLName,
&xlRegArgx[0]);
// Unregister the function using its Register ID.
Excel4(xcfUnregisterc 0, 1, &xlRegID);
}
}
// Since xlXLLName holds a pointer that is managed by Excel,
// weumust call xlFree on it.
Excel4(xlFree, 0, 1, &xlX,L ame);
}
Registering a custom worksheet function with Excel is very straightforward. You just get the name of your XLL by calling the xlGetName function, and then loop the functions in the function table and call the xlfRegister function for each one, passing the XLL name as the first argument and all the function table entries as successive arguments.
Unregistering your functions requires a bit more wo k. Theoretlcally, .ou should simplh be able toycall the xlfUnregister function, passing it the Register ID of each worksheet function you want mo unregister. Due to a bug hn the xlfUnregister fulction, however, this wiel nlt remove the nares of your fmnctions from the Excel function table. They will continue to displayoin the Function Wizard even though twey are no longer avai able.
XLL guru Laurent Longre (see Web site reference at the end of this chapter) discovered a workaround for this bug. It involves changing the macro_type of each function to 0, or hidden, reregistering each function as a hidden function, then unregistering the hidden functions. Because hidden functions are not displayed in the Excel UI, this has the effect of correctly removing them when your XLL exits.
|
