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,A1 3,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,A 13,A14,A15,A16,A17,A18,A19,A20,A21,A22,A23,A24,A25 ,A26,A27,A28,A29,A30,A31,A32,A33,A34,A35,A36,A37,A 38,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,A 14,A15,A16,A17,A18,A19,A20,A21,A22,A23,A24,A25,A26 ,A27,A28,A29,A30,A31,A32,A33,A34,A35,A36,A37,A38,A 39,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,A1 2,A13,A14,A15,A16,A17,A18,A19,A20,A21,A22,A23,A24, A25,A26,A27,A28,A29,A30,A31,A32,A33,A34,A35,A36,A3 7,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.