Excel UDFs with variable arguments

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

Excel UDFs with variable arguments
How to specify a function that takes a variable number of arguments 
bobcalco




Posts: 64
Joined: 2019-03-20
My add-in, which I'm at last about to embark upon writing using ADX, will contain a handful of UDFs for Excel that will accept variable numbers of parameters, after a certain number of required arguments (usually the first argument, always of type string, is the only one).

The documentation does not cover this per se. Or if it does, my brain seemed to convince me as I was perusing it there were some nuances that I needed to sort out. It talks, for example, about a Range parameter optionally serving as an array; but what I'm talking about is the kind of function that can have anywhere from 0 to N additional parameters, beyond the one or more required ones.

Unfortunately I recently upgraded my overall project to VS2022 and so to play with ADX I'll have to fire up my VS2019 instance, which is not a problem per se; but given all the other things I can work on right now, I thought perhaps I could save some time in the planning phase of adding add-ins to my overall project while release of the next version of ADX supporting VS2022 is pending by asking here for some guidance now. Assuming the next ADX version supporting ADX isn't held up for long, I can still productively work on other things till it is available. Otherwise I'll bite the bullet and start the project in VS2019.

Forgive me if this has been asked elsewhere or the documentation has a topic on this point I simply missed.
Posted 15 Nov, 2021 15:07:05 Top
Andrei Smolin


Add-in Express team


Posts: 18506
Joined: 2006-05-11
Hello,

Optional parameters is a feature of the programming language that you use. Excel doesn't support Excel user-defined functions with optional parameters. You must get all the parameters and check whether the user supplies all or some of them. Similarly, Excel doesn't support same-name UDFs with different sets of parameters.

bobcalco writes:
about a Range parameter optionally serving as an array


This is about passing a range of cells to your UDF. You may prefer to get an array containing values from these cells. Or, you may prefer to get an ADXExcelRef pointing to that range.

I believe that we will be able to publish the new version before the weekend. If something goes wrong, well, you know.

Regards from Poland (CEST),

Andrei Smolin
Add-in Express Team Leader
Posted 16 Nov, 2021 05:23:44 Top
bobcalco




Posts: 64
Joined: 2019-03-20
Andrei Smolin writes:
Excel doesn't support Excel user-defined functions with optional parameters. You must get all the parameters and check whether the user supplies all or some of them. Similarly, Excel doesn't support same-name UDFs with different sets of parameters.


Is there not some kind of "ParamArray" type, that is a catch-all last/optional parameter?

The LET(...) function in Excel allows variable parameters, so it must be possible, no?
Posted 17 Nov, 2021 17:16:05 Top
bobcalco




Posts: 64
Joined: 2019-03-20
Incidentally it's quite hard to find documentation at MS's site regarding COM add-ins now. It seems they are pushing the new JavaScript/web style add ins very hard for upcoming versions of Office. What are your plans where those kinds of add-ins are concerned?
Posted 17 Nov, 2021 17:26:41 Top
bobcalco




Posts: 64
Joined: 2019-03-20
This is where I got the idea it must be possible:

Stack Overflow

That plus the fact I've prototyped this approach using .NET controls that implement Excel spreadsheet functionality. All of them support custom functions with optional parameter array types for this kind of thing. Since none of them really implement all the functionality an Excel user expects to be in a spreadsheet it is desirable to port these into Excel itself. Which is why we acquired Add-Ins Express, to help with that.
Posted 17 Nov, 2021 17:31:12 Top
Andrei Smolin


Add-in Express team


Posts: 18506
Joined: 2006-05-11
Hello,

bobcalco writes:
Is there not some kind of "ParamArray" type, that is a catch-all last/optional parameter?


No. That's a feature of a programming language; the Excel API doesn't support this.

bobcalco writes:
The LET(...) function in Excel allows variable parameters, so it must be possible, no?


They may not use that API so...

bobcalco writes:
What are your plans where those kinds of add-ins are concerned?


We have no plans to develop an equivalent of Add-in Express as JS-based Office Add-ins doesn't let you do this.

bobcalco writes:
This is where I got the idea it must be possible:


This is 1) a VBA-based user-defined function (not an XLL add-in or Excel Automation add-in) 2) written in VBA (Visual Basic for Applications). That language provides ParamArray; other languages may provide other ways. XLLs are based on the Excel API and it doesn't let you do this. Meaning: all parameters must be declared, the user may omit some or all of them.

To imitate a variable argument list, consider providing it indirectly. Say, you can pass a range of cells to your function.

Regards from Poland (CEST),

Andrei Smolin
Add-in Express Team Leader
Posted 18 Nov, 2021 01:57:00 Top
bobcalco




Posts: 64
Joined: 2019-03-20
How does Excel.DNA do it, I wonder?

Stack Overflow

ExcelDNA-Registration at Github

It seems for my UDF use case involving functions that require variable parameters I should use Excel-DNA. However, I still plan to use Add-In Express for virtually every other feature of my Office integration. And of course Excel-DNA does not have any integration with other Office apps.
Posted 18 Nov, 2021 09:07:48 Top
Andrei Smolin


Add-in Express team


Posts: 18506
Joined: 2006-05-11
Hello,

ExcelDna is an outstanding project: our guys told me about interesting features implemented in it. But we don't compete with it so I don't know how ExcelDNA works.

You should know, however that the underlying Excel API accepts only 255 parameters. Plus there's a restriction that we describe 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.

I believe using ExcelExcelDna in combination with Add-in Express is a viable solution.

Regards from Poland (CEST),

Andrei Smolin
Add-in Express Team Leader
Posted 19 Nov, 2021 03:36:51 Top