Common reasons why an XLL UDF might fail to register...?

Add-in Express™ Support Service
That's what is more important than anything else

Common reasons why an XLL UDF might fail to register...?
 
bobcalco




Posts: 64
Joined: 2019-03-20
I have an XLL Module added to a COM Addin Module and for some reason the one function I have defined so far is failing to register when I run Excel in debug after registering the add-in. The popup that tells me of the failure doesn't tell me anything useful that I can see, not specifics about where it blew up or anything like that - what should I do to troubleshoot this?
Posted 08 Dec, 2021 03:17:35 Top
Andrei Smolin


Add-in Express team


Posts: 18185
Joined: 2006-05-11
Hello Bob,

Typically, this is caused by a type that Excel doesn't support. You can try to modify the method declaration to check this.

Regards from Poland (CET),

Andrei Smolin
Add-in Express Team Leader
Posted 08 Dec, 2021 03:23:06 Top
bobcalco




Posts: 64
Joined: 2019-03-20
All my parameters are of type string or Object. So, I need to explain what I'm trying to do.

You may recall I need the behavior of ParamArray for my UDF. Since, as you have pointed out, the API itself doesn't really have the formal concept of optional parameters, one must figure out how to present the functions to Excel by convention so that it treats the signature of a function as if ending with an optional ParamArray, because it clearly supports that functionality (e.g., VBA custom functions). It turns out, all the clues are in the XLL SDK kit example, GENERIC.C, and confirmed by my experience with Excel-DNA.Registration.

What one needs to do, is have some number of parameters of type Object (however many one wishes to support - the example uses 29, the old max number of params pre 2003... but one can also do up to 255 if one is confident with later versions of Excel (which I am)). The key is to present these in the function signature string as type "U" (which means, I suppose, AcceptAsArray = False for the ADX function descriptor), and the parameter description must end in "...". See the
FuncSum
example in GENERIC.C, because that's what it does, and it works: each parameter pops up only when you add another one (indicating optionality), rather than all of them popping up at once.

Excel-DNA.Registration does reflection magic to "transform" a function with a "params object[] args" signature into a function with either 29 for pre 2003 Excel, or 140-ish (I forget the exact number off hand) arg1...argN parameters of type Object. The original function must have an object array final parameter that must have the annotation [ParamArray] and AllowReference=True in its [ExcelArgument] attribute, in order to work. At the end of this, ExcelDNA presents Excel with a function compiled on the fly which has the signature Excel needs, registered with the "UUUU*" (however many), and a function parameter descriptor ending in "...". When it is called, it creates the param array to be passed to the original function by stopping processing at the first null or Missing parameter. So, my code sees the param array as intended, with Excel doing the heavy lifting behind the scenes.

So while it isn't a formal concept of the C API, there are conventions which, if followed ,permit one to emulate a param array, with Excel's cooperation in the Function Parameters dialog.

Unfortunately, the way Add-In Express deals with function descriptors is not entirely convenient for this use case. Point-click creating function descriptors of this sort manually can be... tedious and error prone. I eventually sought to create the function descriptors for the final param arrays of my UDFs that need this functionality programmatically in the XLL module constructor, after InitializeComponents() is called. I assumed this is why it was failing to register, so I debugged it, but no: everything seems fine, at the end, the 50 extra args of type Object all receive a corresponding parameter descriptor added to the function descriptor. So whatever is going wrong happens after that.

Nevertheless I commented that code, and still it didn't register. So now I have just one parameter descriptor for one function, the string, but it has the extra Object args without a descriptor. My next step was to manually create the function descriptors, which would by default add each one as a member field of the class. Perhaps ADX needs that?

Is ADX checking to see if the function signature aligns with the function and parameter descriptors and throwing some exception perhaps? If that's the case, then why did programmatic creation of the parameter descriptors not satisfy it? (Yes, I was careful to pass
this.components
as the owner in the constructor of each ADX param descriptor.)

More fundamentally, are there any logging or debugging techniques I can employ to capture the failed registration and understand just what's causing it to be borked?

I believe if the final N arguments to a function are of type Object, the parameter descriptors are set to AcceptAsArray=False, and the function parameters description ends with "...", then AddIn Express can also support an "optional ParamArray" construct as well.

The following is the code from GENERIC.C where the "final optional param array" construct is illustrated (if not formally explained), lines 118-129 in context:


	{ L"FuncSum",
		L"UUUUUUUUUUUUUUUUUUUUUUUUUUUUUU", // up to 255 args in Excel 2007 and later,
										   // upto 29 args in Excel 2003 and earlier versions
		L"FuncSum",
		L"number1,number2,...", // the ... is significant here, according to Govert
		L"1",
		L"Generic Add-In",
		L"",                                    
		L"",                                  
		L"Adds the arguments",   
		L"Number1,number2,... are 1 to 29 arguments for which you want to sum."                   
	},



I'm trying to prove that concept, and just want to take this more methodically since I'm running into apparently very basic registration problems.
Posted 08 Dec, 2021 11:37:15 Top
bobcalco




Posts: 64
Joined: 2019-03-20
Some more general information about my UDFs that require this functionality:

1. They are volatile, requiring recalculation every time, so I set their function descriptor property IsVolatile=True.
2. They are not thread-safe (yet), so IsThreadSafe=false.
3. I am ambivalent about whether IsMacro should be true or false. Probably it should be false, as they do require knowledge of the cell from which they are called. So I am setting that property to false for now.

Regarding cell location, I seem to have found everything I need, but want to confirm this will work:


      private static string CurrentCellName()
      {
        object[] parameters = { };
        ADXExcelRef caller = 
          (ADXExcelRef) ADXXLLModule.CurrentInstance.CallWorksheetFunction
            (ADXExcelWorksheetFunction.Caller, parameters);
        return CellRefName ( caller );
      }

      private static string CellRefName ( ADXExcelRef reference )
      {
        string result = "none";
        if ( null != reference )
        {
          string col = reference.ColumnFirst.ToString();
          string row = reference.RowFirst.ToString();
          object[] parameters = { reference };
          string sheetFullName =
           (String) ADXXLLModule.CurrentInstance.CallWorksheetFunction
            (ADXExcelWorksheetFunction.SheetId, parameters).ToString().ToLower().Replace (" ", "-");
          string[] sheetParts = sheetFullName.Split (']');
          string sheetName = sheetParts.LastOrDefault();
          result = sheetName + "$" + row + "$" + col;
        }
        return result;
      }


The point I want to confirm is in regard to the ADXExcelWorksheetFunction.SheetId function. I am hoping it returns the full sheet name, like Excel-DNA's xlSheetNm does:


  static member CurrentCell = 
    let caller = XlCall.Excel (XlCall.xlfCaller) :?> ExcelReference 
    let cellRefAlt = AddInFunctions.CellRefName caller
    cellRefAlt   
    
  static member CellRefName (ref : ExcelReference) =
    if null <> ref then   
      let sheetFullName = XlCall.Excel(XlCall.xlSheetNm, ref).ToString().ToLower().Replace (" ", "-")
      let sheetNameParts = sheetFullName.Split (']')  // sheetnames get prepended with the book name in brackets
      let sheetName = sheetNameParts.LastOrDefault()
      let c1 = ref.ColumnFirst
      let r1 = ref.RowFirst
      sheetName + "$" + r1.ToString() + "$" + c1.ToString() 
    else 
      "None"


I have not gotten so far as to test my C# port of this functionality on account of the registration failing. I am concerned because ExcelDNA has a SheetId property that is some internal integer and is useless for my purposes. I am hoping your SheetId is the same as xlSheetNm. I know I have the source code but have not spelunked that yet - I figured for now it's easier just to ask.
Posted 08 Dec, 2021 12:05:24 Top
bobcalco




Posts: 64
Joined: 2019-03-20
in terms of the actual function signatures of my UDFs, this is the pattern:


      public static object MyFunc
        ( // ... some number of required parameters, with descriptors set visually in designer
        , Object arg001, Object arg002, Object arg003, Object arg004, Object arg005
        , Object arg006, Object arg007, Object arg008, Object arg009, Object arg010
        , Object arg011, Object arg012, Object arg013, Object arg014, Object arg015
        , Object arg016, Object arg017, Object arg018, Object arg019, Object arg020
        , Object arg021, Object arg022, Object arg023, Object arg024, Object arg025
        , Object arg026, Object arg027, Object arg028, Object arg029, Object arg030
        , Object arg031, Object arg032, Object arg033, Object arg034, Object arg035
        , Object arg036, Object arg037, Object arg038, Object arg039, Object arg040
        , Object arg041, Object arg042, Object arg043, Object arg044, Object arg045
        , Object arg046, Object arg047, Object arg048, Object arg049, Object arg050
        )
      {
        Object[] argArray =
        {
          arg001, arg002, arg003, arg004, arg005, arg006, arg007, arg008, arg009, arg010
        , arg011, arg012, arg013, arg014, arg015, arg016, arg017, arg018, arg019, arg020
        , arg021, arg022, arg023, arg024, arg025, arg026, arg027, arg028, arg029, arg030
        , arg031, arg032, arg033, arg034, arg035, arg036, arg037, arg038, arg039, arg040
        , arg041, arg042, arg043, arg044, arg045, arg046, arg047, arg048, arg049, arg050
        };
        Object[] args = ReduceArgs ( argArray ); // first null or Missing param stops args construction      
        return MyFuncImpl ( aLambdaExpression, args ); // call my actual UDF
      }


I also just amended the logic to create the 1...50 parameter descriptors as follows (an arbitrary number, I will probably change it, even that many seems pathological to me) :


      ADXExcelFunctionDescriptor[] descriptors = { myFuncDescriptor };

      for ( int u = 0 ; u < descriptors.Length ; u++ )
      {
        ADXExcelFunctionDescriptor currentDescriptor = descriptors[u];
        for ( int i = 1 ; i <= MAX_ARGS ; i++ )
        {
          string argNum;
          if ( i < 10 )
            argNum = "00" + i.ToString ();
          else if ( i < 100 )
            argNum = "0" + i.ToString ();
          else
            argNum = i.ToString ();

          ADXExcelParameterDescriptor arg = new ADXExcelParameterDescriptor ( this.components )
          {
            AcceptAsArray = false // must be false to accept as a reference so function input dialog works as expected
          , ParameterName = argNum
          , Description = "arg001, arg002, ...," + argNum + ",..."
          };
          currentDescriptor.ParameterDescriptors.Add ( arg );
        }
      }


It occurred to me one possible reason it was originally failing to register is I was giving the parameters names that didn't match with the signature... I needed to prepend "00" or "0" to i so it matches, am about to test if this helps.

The only thing I am wondering about is how to ensure the necessary parameters description has the required "..." appended to it. I don't think ADX exposes this, it probably constructs it on the fly, so in theory, all that may be required is a HasOptionalParamArray property in an ADX Function descriptor, so ADX knows to append this to the necessary description string being fed to Excel. Otherwise, as long as the parameters are passed in as "U", it should work. In theory. :)

"The difference between theory and practice is greater in practice than it is in theory." -- famous proverb, not sure whom to credit
Posted 08 Dec, 2021 12:19:56 Top
bobcalco




Posts: 64
Joined: 2019-03-20
Alas, I am still getting the following error:


Detailed technical information follows: 
---
Date and Time:         12/8/2021 12:21:47 PM
Machine Name:          APEXDEV001
IP Address:            fe80::8125:580e:f877:3ace%9
Current User:          APEXDEV001dsmactf01

Application Domain:    <path-to-my-output>
Assembly Codebase:     file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/AddinExpress.MSO.2005/v4.0_10.0.4700.0__4416dd98f0861965/AddinExpress.MSO.2005.dll
Assembly Full Name:    AddinExpress.MSO.2005, Version=10.0.4700.0, Culture=neutral, PublicKeyToken=4416dd98f0861965
Assembly Version:      10.0.4700.0

Exception Source:      AddinExpress.MSO.2005
Exception Type:        System.ApplicationException
Exception Message:     'xlfRegister' failed. Function name: MyFunc
Exception Target Site: RegisterXlMethod

---- Stack Trace ----
   AddinExpress.MSO.ADXXLLModule.RegisterXlMethod(mi As ADXExcelMethodInfo)
       AddinExpress.MSO.2005.dll: N 1200 (0x4B0) IL 



So how do I sleuth this?
Posted 08 Dec, 2021 12:28:06 Top
bobcalco




Posts: 64
Joined: 2019-03-20
And yes, I caught the error in the parameter descriptors creation logic, where I forgot to prepend "arg":


    string argNum; 
    if ( i < 10 ) 
      argNum = "arg00" + i.ToString (); 
    else if ( i < 100 ) 
      argNum = "arg0" + i.ToString (); 
    else 
      argNum = "arg" + i.ToString (); 


And still I get the unexplained registration error.
Posted 08 Dec, 2021 12:32:22 Top
bobcalco




Posts: 64
Joined: 2019-03-20
Another point worth mentioning: my XLL module is part of a COM automation add-in with a ADXAddInModule. They do need to run in the same AppDomain, but the documentation seems to say that I have to make some call to ExcelApp.Evaluate (...) to ensure this is the case. I'm a bit confused by how it's worded.


If you use both XLL module (ADXXLLModule) and add-in module (ADXAddinModule) in the same project, they
are always loaded into the same AppDomain. But Excel Automation add-ins (ADXExcelAddinModule) are
loaded into the default AppDomain if you don't take any measures. The need to have them in the same
AppDomain can be caused by the necessity to share the same settings, for instance. To load the Automation
add-in to the AppDomain of your COM add-in, you need to call any method of your Excel add-in using
ExcelApp.Evaluate(...) before Excel (or the user) has a chance to invoke your Excel add-in. If such a call
succeeds, your Excel Automation add-in is loaded into the AppDomain of your COM add-in.


Is this talking about the case where an additional ADXExcelAddInModule is also loaded in a project with ADXAddInModule and ADXXLLModules in it? Should I have created an ADXExcelAddIn as the main project, and added the XLL to this, or... what exactly is that paragraph saying? I guess I don't understand the relation of a COM Add-in to the default AppDomain (where here it's clarifying that an Excel automation add in always loads in the default domain without extra action). Some elucidation of AppDomain management with different project configurations is still needed for me to understand that, it seems.
Posted 08 Dec, 2021 13:23:07 Top
bobcalco




Posts: 64
Joined: 2019-03-20
Since this is kinda urgent for me, having decided to use AddIn Express instead of ExcelDNA now that I know this "ParamArray" feature can at least in theory be done, and I'm having completely unrelated problems with ExcelDNA (namely, AV software is affecting a lot of users because of recent malware some bad actors created using it), I'm escalating this to core devs so we can work through this. I'm willing to compromise and use whatever band aid and bugglegum is necessary to get my UDFs working as seamlessly as they were on ExcelDNA and to use all the other high end features of AddIn Express for the UX stuff I have to work on once the UDFs are ported.
Posted 08 Dec, 2021 16:47:28 Top
bobcalco




Posts: 64
Joined: 2019-03-20
OK so I removed (commented out) all my parameters/arg array stuff just to isolate the question about what ADXExcelWorksheetFunction.SheetId returns, and the function now registers correctly. I'll return to that separately.

Unfortunately, it's always the string "xlreturninvxloper". I'm guessing it's saying I passed in an invalid xloper reference, so maybe I'm just calling it incorrectly:


          object[] parameters = { reference };
          object res = ADXXLLModule.CurrentInstance.CallWorksheetFunction
            (ADXExcelWorksheetFunction.SheetId, parameters).ToString().ToLower().Replace (" ", "-");
          string sheetFullName = (String) res;



Here "reference" is an ADXExcelRef, from which I am able reliably to obtain the row (RowFirst) and column (ColumnFirst).

I need the actual name of the current sheet. The full name is expected to return [<workbook-name>]<sheet-name>, which is what I get from ExcelDNA's wrapper of the same API, which is called like this:


  static member CellRefName (ref : ExcelReference) =
    if null <> ref then   
      let sheetFullName = XlCall.Excel(XlCall.xlSheetNm, ref).ToString().ToLower().Replace (" ", "-")
  ...


I get what I expect, chop off the workbook name and kibob-case it.

Any help with just this is appreciated, as it's another potential showstopper if I can't obtain the sheet name.
Posted 09 Dec, 2021 00:38:00 Top