N number of arguments in a UDF

[Vermeet Kaur]

Hi All

I am developing an excel Addin with Excel DNA and I need to pass
n number of arguments to it. Just like Concatenate function in excel,
I want that in the function Argument Template when i click the text
box for second arguments , the row for third argument is automatically
created and so on.

[Govert van Drimmelen]

Hi,

You are asking about functions with variable numbers of arguments.

I want to distinguish between the how you deal with this in your .NET
function, and how it works and looks on the Excel side.
* In current versions of Excel-DNA, optional parameters and
ParamArray / params style variable parameter arrays are not supported.
So in your function you need to add a bunch of 'object' parameters,
and then for each of these you have to check whether the value passed
from Excel is of type "ExcelMissing" or some value for you to use.
This is a bit messy, but could be put into a wrapper function that
then calls your 'real' function. The only issue is how to deal with
the fact the Excel 2007/ Excel 2010 supports different numbers of
parameters, so you might need two functions.
A future version of Excel-DNA is likely to implement this wrapping
automatically, so that we do support the param arrays and optional
parameters.

* The way Excel-DNA registers functions in Excel (through the C API)
leads to some limitations in how the functions are displayed in the
function wizard. There is no way to register a parameter as
'optional', so a function with 20 parameters (or 254 parameters) will
not display in the function wizard the way your VBA functions display,
where the extra parameters are added one by one. The function wizard
also has a number of bugs in handling function with large numbers of
parameters. At the moment I don't know how to work around these.

So to be clear:
In the current version of Excel-DNA you can create functions that have
many parameters, and you can check which were passed when called from
the worksheet. The function wizard display has issues that I don't
know how to work around, so I don't know how to (and there certainly
is no officially documented way to) give an improved or different
experience on the Excel side to what you can do with Excel-DNA now.
But future versions of Excel-DNA should make some of the ugly plumbing
in your add-in code disappear, by automatically generating the extra
parameters and converting to your 'params' array or optional
parameters.

-Govert