HOW TO: How do I call an add-in function in one XLL from another XLL?

HOW TO: How do I call an add-in function in one XLL from another XLL?

Question

My XLL needs to be sure that another XLL is open. How can I make sure that an XLL is opened?

Answer

Use the xlfUDF macro function to call an add-in function located in another XLL. Before that, you need to use the xlfEvaluate function to identify the unique identifier of the add-in function.

You should also ensure that the XLL which contains the add-in function to be called is open. See FAQ #0029 for more information.

Example

The code below calls the function CalDaysInYear (which can be found in the SimpOpt sample). There are 2 steps:

1. Use xlfEvaluate to convert the name of the add-in function into its unique identifier.
2. If successful, use xlfUDF to call the function itself. If the function has N arguments, you should pass N + 1 arguments to xlfUDF. The first argument is the unique ID of the function (as acquired in step 1). As always when calling the Excel SDK API, each of the arguments should passed as a pointer to CXlOper.

Note that the CXlOper constructor that is used to convert short DayCount to a CXlOper takes a second dummy argument to differentiate it from the constructor for a Boolean type. See the online documentation for CXlOper::CXlOper() for more information.

// Function:    CalDaysInYear2
// Purpose:     Calls a function in another XLL

//{{XLP_SRC(CalDaysInYear2)
    // NOTE - the FunctionWizard will add and remove mapping code here.
    //    DO NOT EDIT what you see in these blocks of generated code!
IMPLEMENT_XLLFN2(CalDaysInYear2, "RI", "CalDaysInYear2", 
    "DayCount", "Date & Time", "Calls a function in another XLL,"
    " which returns number of days in a year according to the"
    " day count", "Day count convention\000", "\0appscope=1\0",
    1)

extern "C" __declspec( dllexport )
LPXLOPER CalDaysInYear2(short DayCount)
{
    XLL_FIX_STATE;
    CXlOper xloResult;
//}}XLP_SRC

    static int xlfEvaluate = 257;
    static int xlUDF = 255;

    CXlOper xloName, xloRef;
    int rc = 0;
    xloName = "CalDaysInYear";
    if (!rc)
        rc = xloRef.Excel(xlfEvaluate, 1, &xloName);
    if (!rc)
        rc = xloResult.Excel(xlUDF, 2, &xloRef, &CXlOper(DayCount, 0));

    return xloResult.Ret();
}

A general solution

The function CallUDF() below can be used to handle calls to any XLL function.

// Function:    CallUDF
// Purpose:     Call an add-in function located in another XLL.
// Returns:     0 if successful; non-zero if call failed
//
// Notes:       argString must contain one character for each argument 
//              which will be passed to the called function. These
//              characters should correspond to the type of the argument 
//              passed, as follows:
//
//              A - Boolean argument of type BOOL
//              B - Numeric argument of type double
//              C - String argument, zero-terminated
//              H - Numeric argument of type unsigned short
//              I - Numeric argument of type short
//              J - Numeric argument of type long
//              K - LPXLARRAY type passed in from Excel
//              P - COper*, a pointer to a COper type, either passed in 
//                  from Excel or constructed locally
//              R - CXlOper*, a pointer to a CXlOper type, either passed in 
//                  from Excel or constructed locally

int CallUDF(CXlOper& xloResult, const char* fnName, const char* argString, ...)
{
    static int xlfEvaluate = 257;
    static int xlUDF = 255;

    CXlOper xloName, xloRef;
    int rc = 0;
    int cArgs = 0;
    CXlOper* axloArgs = 0;

    xloName = fnName;
    if (!rc)
        rc = xloRef.Excel(xlfEvaluate, 1, &xloName);
    if (!rc) {
        va_list va;
        va_start(va, argString);
        cArgs = 1 + strlen(argString);
        axloArgs = new CXlOper[cArgs];
        axloArgs[0] = xloRef;
        for (int i = 1; i < cArgs; i++) {
            CXlOper& xloArg = axloArgs[i];
            switch (argString[i - 1]) {
            case 'A':
                xloArg = va_arg(va, BOOL);
                break;
            case 'B':
                xloArg = va_arg(va, double);
                break;
            case 'C':
                xloArg = va_arg(va, const char*);
                break;
            case 'H':
                xloArg = (double)va_arg(va, USHORT);
                break;
            case 'I':
                xloArg = (double)va_arg(va, short);
                break;
            case 'J':
                xloArg = (double)va_arg(va, long);
                break;
            case 'K':
                {
                    LPXLARRAY pxla = va_arg(va, LPXLARRAY);
                    if (pxla != 0)
                    {
                        CXlArray xla(pxla);
                        USHORT usRows, usCols;
                        xla.GetDims(usRows, usCols);
                        xloArg.AllocArray(usRows, usCols);
                        for (USHORT i = 0; i < usRows; i++)
                            for (USHORT j = 0; i < usCols; i++)
                                xloArg.Cell(i, j) = xla.Cell(i, j);
                    }
                }
                break;
            case 'P':
                xloArg = *va_arg(va, LPOPER);
                break;
            case 'R':
                xloArg = *va_arg(va, LPXLOPER);
                break;
            }
        }
        va_end(va);
    }
    if (!rc)
        rc = xloResult.ExcelA(xlUDF, cArgs, axloArgs);

    if (axloArgs)
        delete[] axloArgs;
    return rc;
}

The code below shows how to use it for the CalDaysInYear example above.

// Function:    CalDaysInYear3
// Purpose:     Calls a function in another XLL, which returns number of days in a year according to the day count

//{{XLP_SRC(CalDaysInYear3)
    // NOTE - the FunctionWizard will add and remove mapping code here.
    //    DO NOT EDIT what you see in these blocks of generated code!
IMPLEMENT_XLLFN2(CalDaysInYear3, "RI", "CalDaysInYear3", 
    "DayCount", "Date & Time", "Calls a function in another XLL,"
    " which returns number of days in a year according to the"
    " day count", "Day count convention\000", "\0appscope=1\0",
    1)

extern "C" __declspec( dllexport )
LPXLOPER CalDaysInYear3(short DayCount)
{
    XLL_FIX_STATE;
    CXlOper xloResult;
//}}XLP_SRC

    int rc = CallUDF(xloResult, "CalDaysInYear", "I", DayCount);
    return xloResult.Ret();
}

See also

FAQ #0029 has information on using an XLL to open another XLL.