Calling user-defined functions from DLLs
Applies to: Excel 2013 | Office 2013 | Visual Studio
Calling user-defined functions (UDFs) from a worksheet is as simple as calling built-in functions: You enter the function via a cell formula. However, from the C API, there are no pre-defined function codes to use with the call-backs. To enable you to call UDFs, the C API exports an XLL-only function, the xlUDF function. The function's first argument is the function name as a string, and subsequent arguments are those that the UDF would normally expect.
You can obtain a list of the currently registered XLL add-in functions and commands by using the xlfGetWorkspace function with the argument 44. This returns a three-column array where the columns represent the following:
The full path and name of the XLL
The name of the UDF or command as exported from the XLL
The return and argument code string
Note
The name as exported from the XLL might not be the same as the registered name by which Excel knows the UDF or command.
Starting in Excel 2007, the Analysis Toolpak (ATP) functions are fully integrated, and the C API has its own enumerations for functions such as PRICE, xlfPrice. In earlier versions, you had to use xlUDF to call these functions. If your add-in needs to work with Excel 2003 and Excel 2007 or later versions, and it uses these functions, you should detect the current version and call the function in the appropriate way.
Examples
The following example shows the xlUDF function being used to call the ATP function PRICE when the running version of Excel is 2003 or earlier. For information about the setting of a global version variable, such as gExcelVersion12plus in this example, see Backward Compatibility.
Note
This example uses the Framework functions TempNum, TempStrConst to set up the arguments and Excel to call the C API.
LPXLOPER TempNum(double d);
LPXLOPER TempStrConst(const LPSTR lpstr);
int cdecl Excel(int xlfn, LPXLOPER pxResult, int count, ...);
double call_ATP_example(void)
{
XLOPER xPrice;
int xl_ret_val;
if(gExcelVersion12plus) // Starting in Excel 2007
{
xl_ret_val = Excel(xlfPrice, &xPrice, 7,
TempNum(39084.0), // settlement date 2-Jan-2007
TempNum(46706.0), // maturity date 15-Nov-2027
TempNum(0.04), // Coupon
TempNum(0.05), // Yield
TempNum(1.0), // redemption value: 100% of face
TempNum(1.0), // Annual coupons
TempNum(1.0)); // Rate basis Act/Act
}
else // Excel 2003-
{
xl_ret_val = Excel(xlUDF, &xPrice, 8,
TempStrConst("PRICE"),
TempNum(39084.0), // settlement date 2-Jan-2007
TempNum(46706.0), // maturity date 15-Nov-2027
TempNum(0.04), // Coupon
TempNum(0.05), // Yield
TempNum(1.0), // redepmtion value: 100% of face
TempNum(1.0), // Annual coupons
TempNum(1.0)); // Rate basis Act/Act
}
if(xl_ret_val != xlretSuccess || xPrice.xltype != xltypeNum)
{
// Even though PRICE is not expected to return a string, there
// is no harm in freeing the XLOPER to be safe
Excel(xlFree, 0, 1, &xPrice);
return -1.0; // an error value
}
return xPrice.val.num;
}
Where you are calling an XLL function that returns a value by modifying an argument in place, the xlUDF function still returns the value via the address of the result XLOPER/XLOPER12. In other words, the result is returned as if through a normal return statement. The XLOPER/XLOPER12 that corresponds to the argument that is used for the return value is unmodified. For example, consider the following two UDFs.
// Registered as "1E". Returns its argument incremented by 1.
void WINAPI UDF_1(double *pArg)
{
*pArg += 1.0;
}
// Registered as "QQ". Returns its argument unmodified
// unless it is a number, in which case it increments it
// by calling UDF_1.
LPXLOPER12 WINAPI UDF_2(LPXLOPER12 pxArg)
{
static XLOPER12 xRetVal; // Not thread-safe
XLOPER12 xFn;
xFn.xltype = xltypeStr;
xFn.val.str = L"\005UDF_1";
Excel12(xlUDF, &xRetVal, 2, &xFn, pxArg);
xRetVal.xltype |= xlbitXLFree;
return &xRetVal;
}
When UDF_2 calls UDF_1, the value of pxArg is unchanged after the call to Excel12, and the value returned by UDF_1 is contained in xRetVal.
When you are making a large number of calls to a UDF in this way, you can evaluate the function name first by using the xlfEvaluate function. The resulting number, which is the same as the registration ID that is returned by the xlfRegister function, can be passed in place of the function name as the first argument to the xlUDF function. This enables Excel to find and call the function more quickly than if it has to look up the function name every time.