|
|
|
bobcalco
Posts: 66
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: 18719
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: 66
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: 66
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: 66
Joined: 2019-03-20
|
|
|
Posted 17 Nov, 2021 17:31:12
|
|
Top
|
|
|
Andrei Smolin
Add-in Express team
Posts: 18719
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: 66
Joined: 2019-03-20
|
|
|
Posted 18 Nov, 2021 09:07:48
|
|
Top
|
|
|
Andrei Smolin
Add-in Express team
Posts: 18719
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
|
|
Forums
Topics
Search
Help
Login
Register