In the Excel XLL SDK, why is xlfRegisterId failing when called from a user defined function?-CodePudding

I am following Malik's anwer to this question to try to get hold of the registration id of my user defined function. If I insert the code into my xlAutoOpen function like this

extern "C" __declspec(dllexport) int xlAutoOpen(void)
{
    XLOPER12 xDLL;  

    Excel12f(xlGetName, &xDLL, 0);

    Excel12f(xlfRegister, 0, 11, (LPXLOPER12)&xDLL,
        (LPXLOPER12)TempStr12(L"exampleAddin"),
        (LPXLOPER12)TempStr12(L"QQQ"),
        (LPXLOPER12)TempStr12(L"exampleAddin"),
        (LPXLOPER12)TempStr12(L"v1,v2"),
        (LPXLOPER12)TempStr12(L"1"),
        (LPXLOPER12)TempStr12(L"myOwnCppFunctions"),
        (LPXLOPER12)TempStr12(L""),
        (LPXLOPER12)TempStr12(L""),
        (LPXLOPER12)TempStr12(L"An example"),
        (LPXLOPER12)TempStr12(L""));

    XLOPER12  xRegId;
    Excel12(xlfRegisterId, &xRegId, 2, (LPXLOPER12)&xDLL, (LPXLOPER12)TempStr12("exampleAddin")); //xRegId will be XltypeNum

    /* Free the XLL filename */
    Excel12f(xlFree, 0, 1, (LPXLOPER12)&xDLL);


    return 1;
}

it correctly gives me the id in xRegId.

However, if I try to call it from within my user defined function, like this

extern "C" __declspec(dllexport) LPXLOPER12 exampleAddin(LPXLOPER12 x1, LPXLOPER12 x2)
{

    XLOPER12  xDLL, xRegId;
    Excel12(xlGetName, &xDLL, 0);// xDLL will be xltypeStr
    Excel12(xlfRegisterId, &xRegId, 2, (LPXLOPER12)&xDLL, (LPXLOPER12)TempStr12("exampleAddin"));
    
    // ... my user defined code is here
}

it returns an empty / error state in xRegId.

Note, I am calling the function directly from a spreadsheet cell.

What is going wrong? Is there a way to get xlfRegisterId inside my user defined function?

Thank you

CodePudding user response：

Thanks to Steve Dalton's excellent book, I found the answer. The user defined functions needs to be registered with macro function permissions, by adding a # after QQQ in the above definition. So the code becomes

extern "C" __declspec(dllexport) LPXLOPER12 exampleAddin(LPXLOPER12 x1, LPXLOPER12 x2)
{

    XLOPER12  xDLL, xRegId;
    Excel12(xlGetName, &xDLL, 0);// xDLL will be xltypeStr
    Excel12(xlfRegisterId, &xRegId, 2, (LPXLOPER12)&xDLL, (LPXLOPER12)TempStr12("exampleAddin"));
    
    // ... my user defined code is here
}

extern "C" __declspec(dllexport) int xlAutoOpen(void)
{
    XLOPER12 xDLL;  

    Excel12f(xlGetName, &xDLL, 0);

    Excel12f(xlfRegister, 0, 11, (LPXLOPER12)&xDLL,
        (LPXLOPER12)TempStr12(L"exampleAddin"),
        (LPXLOPER12)TempStr12(L"QQQ#"),
        (LPXLOPER12)TempStr12(L"exampleAddin"),
        (LPXLOPER12)TempStr12(L"v1,v2"),
        (LPXLOPER12)TempStr12(L"1"),
        (LPXLOPER12)TempStr12(L"myOwnCppFunctions"),
        (LPXLOPER12)TempStr12(L""),
        (LPXLOPER12)TempStr12(L""),
        (LPXLOPER12)TempStr12(L"An example"),
        (LPXLOPER12)TempStr12(L""));

    XLOPER12  xRegId;
    Excel12(xlfRegisterId, &xRegId, 2, (LPXLOPER12)&xDLL, (LPXLOPER12)TempStr12("exampleAddin")); //xRegId will be XltypeNum

    /* Free the XLL filename */
    Excel12f(xlFree, 0, 1, (LPXLOPER12)&xDLL);


    return 1;
}