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 thought perhaps I was wrong to pass in an actual object[] since the params keyword basically means "a list of optional params" so I just passed in the reference:


      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 };
          object res = ADXXLLModule.CurrentInstance.CallWorksheetFunction
            (ADXExcelWorksheetFunction.SheetId, reference).ToString().ToLower().Replace (" ", "-");
      ...


And still no love, it returns "xlreturninvxloper" as before. Even if SheetId isn't supposed to return the sheet name, I'm pretty sure it shouldn't always be returning the string "xlreturninvxloper"... So, help. :)
Posted 09 Dec, 2021 00:45:15 Top
bobcalco




Posts: 64
Joined: 2019-03-20
Returning to the subject of my efforts to get ParamArray like functionality to work, I added just 3 params of type Object and used the GUI designer to add their descriptors, and it worked.

All four arguments (the first string parameter, and the 3 Object params) show up in the Function Parameters dialog. My logic to reduce the inputs to an array of actually passed-in args which are then forwarded to my actual implementation function works fine.

This is not really a win, not yet; though if I must slog through adding all these parameters for every param, I will.

I really want the user to have that visual cue that the parameters are optional up to some reasonable limit (that they'll likely never reach), and I definitely don't want to use the GUI to add these descriptors.

There is a property for each descriptor called "GenerateMember" which can be set to false. So I set it to false, and it basically does what one intuitively expects: it creates the descriptor instances as local variables in the InitializeComponent() method, instead of creating private class members, as it was doing when "GenerateMember" was set to true.

Now I will turn my attention to figuring out how many params added dynamically is too many. 3 worked, but 50 didn't, is all I know right now...

To my mild astonishment, Excel did not reject the registration. I looked at the code and could not see what the designer was doing any differently for each parameter (as a practical matter) than what I was doing. Each param had the same 3 properties updated. OK, I mean, I am doing this in a loop and the local variable names are reused, but on inspection of the 50 parameters I was adding dynamically, I randomly selected some for inspection and saw the same thing I expected. Maybe it's how many I created? I considered.

So, I set my MAX_ARGS to 3, removed the descriptors from the designer, ensured all were cleared out of the InitializeComponents, and uncommented my loop. And... the function registered fine. All three extra params were there, just as if they had been added by the designer.

Now my working theory is "50 extra args was too many, for some reason."

This is puzzling, but if I can get it to at least 20 or so extra params, that may be enough. However, I really don't like the user experience - I really want the fact the params are optional and that the first missing param is treated as the end of input, to be intuitive in the Function Parameter dialog. I *know* it's doable because it was working in Excel-DNA, and I have a working theory based on the C API GENERIC.C example, from my experience, and Govert's helpful explanations what would make it behave, as presented previously in this thread. It would be really great if you guys could adjust how you're calling the registration function under the hood to supply the right function string descriptor (basically, all U's) and the parameters help string ending in "..." if I indicate in the function descriptor that the function is intended to end with a param array. And of course, I still need some way to get the current sheet name when obtaining the coordinates of the caller.
Posted 09 Dec, 2021 01:27:58 Top
bobcalco




Posts: 64
Joined: 2019-03-20
OK, my theory seems right.

I incrementally added parameters ... 10, then 20, then 30. All good.

40, kaboom.

35, kaboom.

So the answer is either 30, or somewhere between 30 and 35. I suppose the total number of parameters must be considered so in the case of 30 extra args to my function with one required arg, the total was 31. If there is a hard coded limit (32?), I can have my loop subtract the number of actual params added via the designer from the MAX_ARGS total for the purposes of not hitting it, but that means some functions will allow more extra args than others, which might confuse users.

I'm not pushing any further. Honestly, I'm not sure that's an unworkable limit as-is (I can't imagine passing that many to one function, but it IS Excel...). But in summary it would be nice if:

1. There was no hard coded (?) limit to how many params I can add to a XLL UDF lower than Excel's limits (which since 2013 is 255);

2. I could somehow tell Excel via the function descriptor to do for my functions in the Function Parameters dialog what it does for GENERIC.C "FuncSum", and what Excel-DNA figure out how to do (show new optional params as one is actually added). I think it's a matter of optionally adding a "..." to the function parameters description when registering the function and making sure each param in the function is viewed as "U".

3. I could get the sheet name. That's really important, too, and now that's my main showstopper. At least I can add up to 30 optional parameters even though the UX is kinda ugly and IMHO unnecessarily harder to use.
Posted 09 Dec, 2021 01:58:55 Top
bobcalco




Posts: 64
Joined: 2019-03-20
This time, a mea culpa.

Somehow, I missed that SheetNm also exists in the ADXExcelWorksheetFunction enum. I tried many times to look for it via intellisense but only saw SheetId, not SheetNm, until now.

So, scratch point three in my previous update. I get the sheet name just fine.

It's really down to the UX for the Function Parameters dialog, the apparently arbitrary hard coded limit to how many parameters can be passed (though I'm less concerned about that is 30 seems just fine for most practical uses, it's in principle not good to artificially limit it below what Excel does).

At least now I don't have a *showstopper*. But the UX is business important.
Posted 09 Dec, 2021 02:17:55 Top
bobcalco




Posts: 64
Joined: 2019-03-20
So, can someone confirm:

1. That you're looking into whether there is an internal, ADX hardcoded limit to the number of parameters that can be registered? One isn't documented plainly (or I just missed it?), but I was hitting that limit between 31 and 35 actual parameters.

2. That you are looking into supporting a registration strategy that comports with the FuncSum example in GENERIC.C, which demonstrates that emulating optional param array style registration makes the Function Parameters dialog work to make the optional nature of the extra parameters obvious and intuitive for the user. This approach has been validated by Excel-DNA, which also relies on the XLL SDK, and exposes the C API, like ADX does.

Sorry for the blast of posts as I was working through it, but it was an urgent problem for me, and I was just trying to keep you abreast of my efforts to figure out what was wrong with my early attempt at registering the extra parameters.

It is important to note that I clearly demonstrated that the root issue is some apparent limitation in the number of parameters that can be registered, which is more than 29 (the old pre 2013 limit), but not much more, despite Excel post 2013 supporting 255 parameters.

It was not a problem with the types of parameters I was using, as was originally suggested. Utimately by trial and error I got to a place where I eliminated all other possible explanations but the actual number of params, I quickly tested it and discovered it, in the range of 31 (no kaboom) to 35 (kaboom). This seems a hardcoded limitation, but maybe there is another technical explanation?
Posted 09 Dec, 2021 10:59:50 Top
Andrei Smolin


Add-in Express team


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

Thank you for the details provided. I'm sure our guys will check them.

Unfortunately, no quick fix or patch or code change(s) is possible. Introducing this feature requires us to do a research. As to implementation, it would require coordinated changes both in Add-in Express itself and in the code of Add-in Express loader. This is a serious change requiring us to plan resources and effort. So we can only do this as part of our build cycle.

No hard-coded limit exists in Add-in Express. Excel implies a limitation on the length of the string that Add-in Express passes to Excel in order to register your function. Check details in section My XLL Add-in doesn't show descriptions at https://www.add-in-express.com/docs/net-excel-udf-tips.php#xll-no-descriptions. There you'll find a useful method performing these calculations for your UDF.

Thus 255 parameters is a theoretical maximum. We remember getting more than 230 parameters; was it 231 or 238? In that test, a parameter name was 1-2 symbols and the descriptor was either an empty string or it had a single symbol; I can't remember.

On ExcelDNA and antivirus software. Did you try to sign your XLL with a digital certificate?

Regards from Poland (CET),

Andrei Smolin
Add-in Express Team Leader
Posted 09 Dec, 2021 11:58:58 Top
bobcalco




Posts: 64
Joined: 2019-03-20
We are in the process of obtaining a certificate and will sign our add in software in any case, but my understanding is that while it helps with the poorly named "SmartScreen" process, it still doesn't prevent AV software from finding false positives.

Basically, signing certs are like digital notaries - they use the reputation of the CA vendor to vouch that we are who we think we are at the time we interact with a customer during download/installation. But they can't be responsible for motives or actions of the software once it's on the machine. So, this is why I separate the AV stuff from the SmartScreen stuff, which is more about trusting a vendor than vouching for a specific executable (whether it comes from that vendor or not).

I am glad you're looking into supporting the functionality we call "param array" but is really about presenting "optional" params to users correctly in the Function Parameters dialog, based on an apparent convention implicit in the SDK but not formalized by it. I'm happy to agree to keep the "extra" param count "low" (like ~30) and the param names small. Do I correctly assume the ",..." that must be appended to the function param names descriptor at the XLL SDK level would take another 4 characters off the 255 max? Should I include making room for that in my computation, so that when you do implement the changes required to support correct Function Parameters dialog functionality in this case, my stuff will continue to "just work"?

Speaking of which: Thanks for the link to computing the lengths of parameter names and descriptions. It will help me compute the maximum number of allowable parameters per UDF, which may vary based on how many required params a specific function has, and how badly my literary elephantitis is flaring up on me at the time I implemented it. :-)
Posted 09 Dec, 2021 15:23:50 Top
Andrei Smolin


Add-in Express team


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

bobcalco writes:
Do I correctly assume the ",..." that must be appended to the function param names descriptor at the XLL SDK level would take another 4 characters off the 255 max? Should I include making room for that in my computation, so that when you do implement the changes required to support correct Function Parameters dialog functionality in this case, my stuff will continue to "just work"?


I can't answer these questions: I simply don't know these details.

Regards from Poland (CET),

Andrei Smolin
Add-in Express Team Leader
Posted 13 Dec, 2021 04:31:32 Top
bobcalco




Posts: 64
Joined: 2019-03-20
Andrei,

I gather the string of parameters is something ADX must be generating via reflection, as my functions get registered with all the parameters I actually defined whether I create descriptors for them or not. I was glad to discover this by accident, and it greatly simplified matters since I only provide descriptors for the first couple of them, so users get the idea... I don't need to create them for all 65, and this also economizes function description space well below the 255 combined character limit.

My hypothesis is that all ADX has to do is offer a function descriptor parameter called, say, "EndsWithParamArray" or something like that, a simple boolean defaulting to false, which, if set to true, appends ",..." to that generated function parameter string, and Excel will honor it.

So if the function is defined as follows:


static public object foo ( string s, object a1, object a2, ... object a65) { ... }


(where ... is actual hard coded parameters, I just omit them here for brevity)

Then the generated string would look like:


"s,a1,a2,..." 


and I believe, based on GENERIC.C example, that this would compel Excel's Function Arguments dialog to add each "optional" param at the end one at a time up to the hard limit (which for me seems to have settled at 65 parameters, by the way). This is the desired behavior. The only other requirement is that all "optional" function parameters be typed as "U" in the parameter type string, which denotes a reference if I understand the examples correctly. The user can ensure this by AcceptAsArray=false, I suppose?

But current behavior isn't so bad, it turns out; and I can live with it for now. All 65 parameters are eagerly displayed, but the user only sees 5 of them at a time. The UX isn't as terrible as I was afraid. Tabbing up and down is fine, but trying to scroll with the mouse doesn't really work.

I would be pleased to write up how I got this working so far, for people who may want to emulate support for writing functions with optional or variadic parameters like I have, as part of your knowledge base, if you like. If I write such a post here, can you use it that way? The pattern is simple now that I understand what I'm doing, and even (I think) how ADX can implement the ultimate desired behavior.
Posted 13 Dec, 2021 10:26:50 Top