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 :
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,A1
7,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,A4 7,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.