Over 29 args in a UDF with double parens

[Greg Lovern]

I noticed that Excel will accept any number of arguments for a worksheet
function if the parens are doubled, and that all of the arguments work with
Excel's SUM and AVERAGE functions (I haven't tried any other functions). For
example, this doesn't work because it exceeds the maximum number of
arguments:

=AVERAGE(A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12,A13,A14,A15,A16,A17,A18,A19,A20,A21,A22,A23,A24,A25,A26,A27,A28,A29,A30,A31)

But this works fine, and the average is correct for all 50 arguments. Note
the doubled parens:

=AVERAGE((A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12,A13,A14,A15,A16,A17,A18,A19,A20,A21,A22,A23,A24,A25,A26,A27,A28,A29,A30,A31,A32,A33,A34,A35,A36,A37,A38,A39,A40,A41,A42,A43,A44,A45,A46,A47,A48,A49,A50))

And this works too:

=SUM((A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12,A13,A14,A15,A16,A17,A18,A19,A20,A21,A22,A23,A24,A25,A26,A27,A28,A29,A30,A31,A32,A33,A34,A35,A36,A37,A38,A39,A40,A41,A42,A43,A44,A45,A46,A47,A48,A49,A50))

(BTW, I'm using Excel 2003 and haven't tried this with other versions.)


I tried to get that to work in a UDF, and found a way to get access to all
the values. But, then I realized I seemed to have access to ALL of the
values in the worksheet -- even those which weren't passed to the function
in an argument -- which seems very strange. Here's what I did:

First, the UDF uses ParamArray:

Public Function MyFunction(ParamArray arglist() As Variant)

Here's how I called it from the worksheet:

=MyFunction((A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12,A13,A14,A15,A16,A17,A18,A19,A20,A21,A22,A23,A24,A25,A26,A27,A28,A29,A30,A31,A32,A33,A34,A35,A36,A37,A38,A39,A40,A41,A42,A43,A44,A45,A46,A47,A48,A49,A50))

This gives me access to the values in A1:A50:

arglist(0)(1)
.....
arglist(0)(50)

So far, so good. BUT, this gives me access to the value in A51, which wasn't
passed to the function:

arglist(0)(51)

And, this gives me access to the value in cell J10, which also was not
passed to the function:

arglist(0)(10,10)

So, I seem to have access to all the values in the worksheet, just by
passing A1. But as weird as that seems, it wouldn't be a problem if I could
somehow determine what cell references were passed. But I haven't been able
to find a way. I've tried setting the paramarray to an array declared in the
UDF, but that didn't help.

Any suggestions?


Many Thanks,

Greg


P.S. -- Is it well known that you can pass any number of arguments to
Excel's SUM and AVERAGE functions (and others for all I know), and still get
a correct result, just by doubling the parens? I'd never heard of that
before I stumbled into it looking for a way to get more than 29 arguments
into a UDF.

 

[keepITcool]

using the extra parenthesis will pass all references as an array to the
first argument of the function.

in your example you are passing an array of ranges
thus arglist(0) => Range("a1") ->
a1.cells(10,10) will get you J10
a1(51) will probably get you A51

else you could test against passing multiareas (double parens)
Function HowMany(ParamArray pa())
If TypeName(pa(0)) = "Range" Then
If pa(0).Areas.Count > 1 Then
HowMany = "NO MULTIAREAS"
End If
End If
End Function


If you write a UDF 'to the max': declare
28 (variant) arguments + 1 paramarray at the end..


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Greg Lovern wrote :