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 HandleRegistration(BOOL bRegister)
{
XLOPER xlXLLName, xlRegID, xlRegArgs[NUM_REGISTER_ARGS];
int i, j;
// Get the filename of the XLL by calling xlGetName.
Excel4(xlGetName, &xlXLLName, 0);
// All of the XLOPER arguments passed to the Register
// function will have the type xltypeStr.
for (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.
for(j = 0; j < NUM_REGISTER_ARGS; ++j)
xlRegArgs[j].val.str = gszFunctionTable[i][j];
if (TRUE == bRegister)
{
// Register each function.
// NOTE: The number of xlRegArgs[] arguments passed
// here must be equal to NUM_REGISTER_ARGS - 1.
Excel4(xlfRegister, 0, NUM_REGISTER_ARGS + 1,
&xlXLLName,
&xlRegArgs[0], &xlRegArgs[1], &xlRegArgs[2],
&xlRegArgs[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 each custom worksheet function
// as a hidden function (change the macro_type
// argument to 0).
xlRegArgs[4].val.str = "\0010";
// Step 2: Re-register each function as a hidden
// function.
// NOTE: The number of xlRegArgs[] arguments passed
// here must be equal to NUM_REGISTER_ARGS - 1.
Excel4(xlfRegister, 0, NUM_REGISTER_ARGS + 1,
&xlXLLName,
&xlRegArgs[0], &xlRegArgs[1], &xlRegArgs[2],
&xlRegArgs[3], &xlRegArgs[4], &xlRegArgs[5],
&xlRegArgs[6], &xlRegArgs[7], &xlRegArgs[8],
&xlRegArgs[9], &xlRegArgs[10]);
// Step 3: Unregister the now hidden function.
// Get the Register ID for the function.
// 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,
&xlRegArgs[0]);
// Unregister the function using its Register ID.
Excel4(xlfUnregister, 0, 1, &xlRegID);
}
}
// Since xlXLLName holds a pointer that is managed by Excel,
// we must call xlFree on it.
Excel4(xlFree, 0, 1, &xlXLLName);
}
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. |
لطفا منتظر باشید ...
