Finding out which name an XLL custom worksheet function was called under

user_1419315 0
Viewed 103

It is possible to register the same XLL function as a custom worksheet function multiple times using xlfRegister.

When the XLL function is called, is there any way of finding out which name was used in the worksheet to call the function (other than parsing the Excel formula, which is probably not very reliable)?

xll excel c
2 Answers

I found a way to get the function name without parsing the Excel Formula. In the official documentation, it is said that xlfRegister :

defines a hidden name which is the function text argument, pxFunctionText, and which evaluates to the registration ID of the function or command.

So we can use the registration ID to retrieve this hidden name. The following code does it, using xlfGetDef:

   XLOPER12  xDLL, xRegId, xTypeNum, xFunctionName;      
   xTypeNum.xltype = xltypeInt;
   xTypeNum.val.w = 3; // all names 
   Excel12(xlGetName, &xDLL, 0);// xDLL will be xltypeStr
   Excel12(xlfRegisterId,&xRegId,2,(LPXLOPER12)&xDLL,  TempStr12(_T(__FUNCTION__))); //xRegId will be XltypeNum
   Excel12(xlfGetDef,&xFunctionName,3,(LPXLOPER12)(&xRegId),nullptr,(LPXLOPER12)&xTypeNum);  // xFunctionName is the value we look for 
   Excel12(xlFree, nullptr, 1,  (LPXLOPER12) &xDLL);
   ... //copy xFunctionName
   Excel12(xlFree, nullptr, 1,  (LPXLOPER12) &xFunctionName);

Note that in this code, the first argument (pxDefText ) of the call to xlfGetDef has type xltypeNum, this doesn't correspond to the offical documentation but it works (a hidden feature for a hidden name ?). This code can be optimised by calling only once xlGetName.

An alternative and probably more efficient way (without calling the Excel C API) would be to set a static std::map during registration (with key = name of the function as exported and value = pxFunctionText ), and to retrieve the value inside the function using the key : __FUNCTION__.

user_32055290

Malick's answer can get you the registered name of the current function, but won't work if you register the same function under multiple names - it will just return the first one as per the xlfGetDef documentation.

Parsing the formula is not reliable: for example, if Foo1 and Foo2 are both registered names pointing at the implementation foobar, inside foobar you can't know which part of the formula =IF(B1, Foo1(A1) * Foo2(A2), Foo1(Foo2(A2)) is currently being executed, particularly in threaded mode.

You could define static function stubs which all point to foobar, but to solve this in greater generality, you can use xlOil (disclaimer: I wrote it). xlOil allows you to register lambdas with the syntax:

 auto foobar(int whichOne, const ExcelObj& arg)
 {
   ...
   return xloil::returnValue(...)
 }

 ...

 auto registerId = RegisterLambda<>(
    [](const ExcelObj& arg1)
    {
        return foobar(1, arg1);
    })
    .name("Foo1")
    .help("Appears in Wizard")
    .registerFunc();

(the ExcelObj here is just a safe wrapper around an XLOPER12). By capturing values in the lambda you can pass an identifier to the implementation foobar to let it know which alias is being called. Alternatively, by specifying the type of the first lambda argument as const FuncInfo& it will be handed a struct containing the registration info, such as the function name.

user_132736470
Related