Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA® نسخه متنی

The Structure of an XLL

We'll start our discussion by creating an XLL that contains two trivial custom worksheet functions. This will allow us to concentrate on the structure required to create an XLL independent of whatever worksheet functions it happens to contain. We look at an example of a real-world function later in the chapter. Listing 19-1 shows the two custom worksheet functions our first XLL will provide.

Listing 19-1. Sample Custom Worksheet Functions

double WINAPI AddTwo(double d1, double d2)
{
return d1 + d2;
}
double WINAPI MultiplyTwo(double d1, double d2)
{
return d1 * d2;
}

The Function Table

The first thing you need to do when creating your XLL is build a function table. This is a three-dimensional string array holding detailed descriptions of each of the custom worksheet functions that your XLL contains. The function table will be used to register each custom worksheet function with Excel when the XLL is opened. The first dimension of the table holds an entry for each custom worksheet function in the XLL. The second dimension of the table holds all of the arguments that will be passed to the Register function for a given custom worksheet function. The third dimension of the table holds the string values for each argumentRegistering and Unregistering Custom Worksheet Functions section later in the chapter.

Listing 19-2. The Sample XLL Function Table

#define NUM_FUNCTIONS         2
#define NUM_REGISTER_ARGS     11
#define MAX_LENGTH             255
static char gszWorksheetFuncs[NUM_FUNCTIONS][NUM_REGISTER_ARGS][MAX_LENGTH] =
{
{" AddTwo",                     // procedure
" BBB",                        // type_text
" AddTwo",                     // function_text
" d1, d2",                     // argument_text
" 1",                          // macro_type
" Sample Add-in",              // category
" ",                           // shortcut_text
" ",                           // help_topic
" Adds the two arguments.",    // function_help
" The first number to add.",   // argument_help1
" The second number to add."   // argument_help2
},
{" MultiplyTwo",
" BBB",
" MultiplyTwo",
" d1, d2",
" 1",
" Sample Add-in",
" ",
" ",
" Multiplies the two arguments.",
" The first number to multiply.",
" The second number to multiply."
}
};

The following are brief descriptions of the purpose and usage of each entry in the function table in the order in which they appear. We describe how to actually register your functions with Excel based on this information in the Registering and Unregistering Custom Worksheet Functions section.

procedure
This is the name of your custom worksheet function. It should be exactly the same as the name that appears in your function definition.

type_text
This is a coded string that specifies the data types of all the function's arguments as well as its return value. The first letter specifies the return type of the function and all following letters specify the data types expected by each of the functions arguments, from left to right. Additional Resources section at the end of the chapter.

Excel normally recalculates worksheet functions only when they are first entered into a worksheet cell or when one of their dependencies changes. You can make a custom worksheet function volatile by appending an exclamation point character (!) to the end of that function's type_text string. Volatile functions are recalculated whenever any worksheet cell is recalculated. Therefore, you must be very careful with them, because they can cause extreme degradation in recalculation performance.

function_text
This is the name of the function as it will appear in the Excel Function Wizard.

argument_text
This text string enables you to display a list of arguments that your function accepts in the Excel Function Wizard.

macro_type
This is a numeric value indicating the type of the function. Excel worksheet functions always have a macro type of 1. We will make use of the hidden function macro type 0 to overcome a bug in unregistering custom worksheet functions. The last macro type is 2, which defines a function that can only be called from an XLM macro sheet. This function type is beyond the scope of this chapter.

category
Enables you to specify the category that your function will appear in when viewed in the Function Wizard. You should always create a separate category for custom worksheet functions so you do not confuse the user about which functions are built-in and which functions require your XLL to be loaded in order to be used.

shortcut_text
This is used to assign a shortcut key to command-type functions. This function type will not be covered here, so this entry can be left empty.

help_topic
If you have a custom help file associated with your XLL, this will be the help topic ID for this worksheet function.

function_help
This is a short descriptive help text string that will appear in the Excel Function Wizard when the user selects your function.

argument_help1 ... 20
This is a short descriptive help text string that will appear in the Function Wizard when the user is entering data for each of your arguments. An Excel worksheet function can take up to 29 arguments. Unfortunately, the Register function, which we discuss later, uses the first nine of its arguments for other purposes. Therefore, you can only document the first 20 arguments of any custom worksheet function. All arguments beyond the 20th will have an argument help string that is a duplicate of that used for the 20th argument.

Note again that every function must have exactly the same number of entries in the function table, so the function with the maximum number of arguments determines the number of function table entries for all the functions in your XLL. If an argument_helpX string is not used by a function, leave it empty and Excel will ignore it.

The K data type is most frequently used as a custom worksheet function argument type because it is the nearest thing in the Excel C API to a strongly typed array data type. To use K data type arguments in your XLL, you need to add the definition for the FP struct shown in Listing 19-3 to your code.

Listing 19-3. The FP Struct

typedef struct _FP
{
unsigned short int rows;
unsigned short int columns;
double array[1];
} FP;

When received as an argument, the array[] member of the FP struct will be sized such that it contains rows*columns elements.

The DLLMain Function

Because an XLL is just a variation on a standard Windows DLL, Windows will be expecting to find a DLLMain function to call when it loads the XLL. In most XLLs, this function doesn't have to do anything other than return TRUE. You may use the DLLMain function for any normal initialization operations if you want, but in an XLL it is more customary to use the xlAutoOpen callback function for this purpose.

There is one situation where use of the DLLMain function makes more sense than xlAutoOpen. This is when your XLL requires some critical internal initialization to succeed, and if that initialization fails you want to prevent Excel from loading the XLL. By returning FALSE from DLLMain, you can prevent Excel from loading your XLL. In our sample XLL, DLLMain will be empty except for a return TRUE; statement, as shown in Listing 19-4.

Listing 19-4. The DllMain Function

BOOL WINAPI DllMain(HINSTANCE hInstance, DWORD fdwReason, PVOID pvReserved)
{
return TRUE;
}

Standard XLL Callback Functions

Excel calls the following three functions at various times during its use of an XLL. Only the xlAutoOpen function is strictly required, but most XLLs will make use of all three of these callback functions.

xlAutoOpen

The xlAutoOpen function is the startup function of your XLL. xlAutoOpen is called whenever:

You open the XLL file from the Excel File > Open menu.

You load the XLL as an add-in using the Tools > Add-ins menu.

The XLL is in the XLSTART directory and is automatically opened when Excel starts.

Excel opens the XLL for any other reason.

A macro calls the XLM REGISTER() function with only one argument, which is the name of the XLL.

Note that the xlAutoOpen function will not be called if your XLL is opened from a VBA macro using the Workbooks.Open method. This is consistent with the behavior of VBA add-ins. If you want to load your XLL from VBA, use the Application.RegisterXLL method instead.

xlAutoOpen should register all the custom worksheet functions in the XLL and perform any other initialization your XLL requires. Listing 19-5 shows the xlAutoOpen code for our sample XLL. We defer discussion of how the HandleRegistration function works until we've covered the XLOPER data type and the Excel4 function.

Listing 19-5. The xlAutoOpen Function

EXPORT int WINAPI xlAutoOpen(void)
{
static XLOPER xDLL;
int i, j;
// In the following loop, the strings in
// gszFunctionTable are byte-counted.
for (i = 0; i < NUM_FUNCTIONS; ++i)
for (j = 0; j < NUM_REGISTER_ARGS; ++j)
gszFunctionTable[i][j][0] =
(BYTE) lstrlen(gszFunctionTable[i][j] + 1);
// Register the functions using our custom procedure.
HandleRegistration(TRUE);
return 1;
}

xlAutoClose

The xlAutoClose function is the shutdown function of your XLL. xlAutoClose is called whenever:

You quit Excel.

You unselect the XLL from the add-ins list under the Tools > Add-ins menu.

xlAutoClose should perform any cleanup operations required by your XLL as well as unregister the worksheet functions it contains so they no longer appear in the Function Wizard or the Paste Functions list. Note that if the user attempts to exit Excel when there is an unsaved workbook open, Excel will call the xlAutoClose function of any open XLLs before prompting the user to save changes to the unsaved workbook. If the user cancels the save prompt, Excel and your XLL will remain open. This may constrain the amount of cleanup you can safely do in the xlAutoClose function in some circumstances. Listing 19-6 shows the xlAutoClose function for our sample XLL. Again, we defer discussion of how the HandleRegistration function works until later in the chapter.

Listing 19-6. The xlAutoClose Function

EXPORT int WINAPI xlAutoClose(void)
{
// Unregister the worksheet functions
// using our custom procedure.
HandleRegistration(FALSE);
return 1;
}

xlAddInManagerInfo

The Excel Add-in Manager calls the xlAddinManagerInfo function when it loads your XLL in order to determine the descriptive string that it should display for your XLL in the list of add-ins. This function is not strictly required. If you don't provide it, the Add-in Manager will use the filename of the XLL as the descriptive text. However, providing a descriptive name for your XLL makes it much easier for users to locate. Listing 19-7 shows the xlAddinManagerInfo code for our sample XLL. We describe what most of this code is doing in the sections on the XLOPER data type and the Excel4 function.

Listing 19-7. The xlAddInManagerInfo Function

EXPORT LPXLOPER WINAPI xlAddInManagerInfo(LPXLOPER xlAction)
{
static XLOPER xlReturn, xlLongName, xlTemp;
// Coerce the argument XLOPER to an integer.
xlTemp.xltype = xltypeInt;
xlTemp.val.w = xltypeInt;
Excel4(xlCoerce, &xlReturn, 2, xlAction, &xlTemp);
// The only valid argument value is 1. In this case we
// return the long name for the XLL. Any other value should
// result in the return of a #VALUE! error.
if(1 == xlReturn.val.w)
{
xlLongName.xltype = xltypeStr;
xlLongName.val.str = "\021Sample XLL Add-in";
}
else
{
xlLongName.xltype = xltypeErr;
xlLongName.val.err = xlerrValue;
}
return &xlLongName;
}

Note how we've manually byte-counted the descriptive text string for our XLL using an octal length prefix, \021. This is the format in which Excel expects to receive all string values. Rather than using the C convention of relying on the position of a null character within a string to determine its length, Excel uses the Pascal convention of a numeric prefix specifying the length of a string.

Additional XLL Callback Functions

The following functions are optional and will not be covered in detail in this chapter.

xlAutoRegister

Excel will call the xlAutoRegister function if an XLM macro tries to register one of the custom worksheet functions contained in the XLL without specifying the type_text argument. In that case, Excel passes the name of the function the XLM macro tried to register to the xlAutoRegister function and the xlAutoRegister function should fully register the function it was passed. If the function name passed by Excel is not recognized, xlAutoRegister should return a #VALUE! error. The prototype for the xlAutoRegister function is as follows:

LPXLOPER WINAPI xlAutoRegister(LPXLOPER);

xlAutoAdd

The xlAutoAdd function works exactly like the xlAutoOpen function except Excel only calls xlAutoAdd when the Excel Add-in Manager loads the XLL. The prototype for the xlAutoAdd function is as follows:

int WINAPI xlAutoAdd(void);

xlAutoRemove

The xlAutoRemove function works exactly like the xlAutoClose function except Excel only calls xlAutoRemove when the Excel Add-in Manager unloads the XLL. The prototype for the xlAutoRemove function is as follows:

int WINAPI xlAutoRemove(void);

xlAutoFree

We discuss this function in a bit more detail in the section on the XLOPER data type below. Briefly, when your XLL passes an XLOPER containing a pointer to a large amount of memory that is managed by the XLL, you can tell Excel to call the xlAutoFree function as soon as it is finished with that XLOPER so the memory it uses can be freed as soon as possible. The prototype for the xlAutoFree function is as follows:

void WINAPI xlAutoFree(LPXLOPER xlToFree);