How Many Arguments?

[Jerry W. Lewis]

Is it possible in a C based .xll function that takes a variable number
of arguments, to determine how many arguments were actually given?

In VBA

Function NumArgs(ParamArray Args() As Variant) As Integer
NumArgs = UBound(Args()) + 1
End Function

does the trick.

In the FuncSum example function from the SDK's Generic.c I do not see a
corresponding approach. I could loop through px1 through px29 and
determine which ones do not have xltypeMissing; but that would not
distinguish between =FuncSum() and
=FuncSum(,,,,,,,,,,,,,,,,,,,,,,,,,,,,), where in one case no arguments
were given and in the other 29 missing arguments were given.

Also, is it possible to either program or register FuncSum in such a way
that at least one argument must be given before Excel will call the
function? For example the worksheet function SUM produces an error
dialog if you attempt to enter =SUM(); you must give SUM at least one
argument, even if that argument is missing.

I know that it is not possible to exactly reproduce all behavior of
native Excel functions in user defined functions (either VBA or .xll).
For example Excel 3-D references cannot be passed to either type of UDF
(though some have kludged alternative structures to preserve the
functionality). Are these more examples of incompletely supported features?

Jerry

 

[John Drummond]

I thought FuncSum() and FuncSum(,,,,,,,,,,,,,,,,,,,,,,,,,,,,) were the
same, and I would loop through the XLMissings to find the number of
arguments.
For the first argument you could throw an error if it is XLMissing, for the
others XLMissing would be whatever default you wish.
3D references don't seem to really exist in Excel, where they do they have
been kludged (eg Excels SUM() function) , and I don't know how they did it.

 

[Jerry W. Lewis]

Yes, there is no difference between FuncSum() and
FuncSum(,,,,,,,,,,,,,,,,,,,,,,,,,,,,) as coded in Generic.c; that is
what I want code to cange.

Unlike FuncSum(), native Excel functions that take an arbitrary number
of arguments, treat explicitly passed missing values as zero. Thus
AVERAGE(1)=1 but AVERAGE(1,)=0.5; COUNT(,,,,,,,,,,,,,,,,,,,,,,,,,,,,)=29
COUNT(,)=2, and if it were permitted, COUNT() would equal zero. That
is what I want to be able to do in .xll functions; emulate as exactly as
possible the argument processing of native Excel functions, but with
better numerical accuracy.

Jerry

 

[John Drummond]

Good point.
It seems that MS functions have genuinely variable-length argument lists (I
guess that's why there's no limit to the number of args to a SUM()), whereas
SDK functions only have optional variables, which are not the same thing.
On 3D ranges, I see that the freeware add-in MoreFunc.xll does employ 3D
ranges in a couple of its functions, so 3D might actually be possible in the
SDK. Don't know how though.
Good luck

John

 

[Jerry W. Lewis]

Actually, MoreFunc.xll does not do Excel 3-D references, it achieves the
same results through a different calling syntax.

http://longre.free.fr/downloads/Morefunc.exe
http://groups.google.com/[email protected]

Jerry