Sounds like your argument is a single contiguous range such as A1:D112.
the entire range would be in the first element of the paramarray. It
doesn't put each cell in a different element.

for points 2 and 3 these appear to be correct. I suspect the limitation on
the paramarray being passed is because I believe arguments are stored on the
Call Stack and it would need to be protected. Just a guess. You could
always use another variant to hold its values and pass that

Function FuncC(paramarray v() as Variant)
Dim v1() as Variant
v1 = v
FuncB v1
End Function

Function FuncB(vv() as Variant)

End function

but now that you realize the range is in a single element, you may not need
to pass an array.

Sub BB()
AA Range("A1:D12"), 20
End Sub

Sub AA(ParamArray v() As Variant)
Dim v1() As Variant
b = Array(1, 2, 3, 4)
Debug.Print LBound(b), LBound(v), v(0).Address
v1 = v
C v1
End Sub

Sub C(vv() As Variant)
Debug.Print LBound(vv)
End Sub

produces
1 0 $A$1:$D$12
0

so you see the range is in the first element of the paramarray

--
Regards,
Tom Ogilvy

--
Regards,
Tom Ogilvy




"pinkfloydfan" wrote:

Hi all

I am using XL2003 and seem to have a few problems when trying to use
ParamArray within a UDF...can anyone tell me what is going on here?
The problems I have are the following:

1) In the UDF, ParamArray is the final argument of 12. If I pass a
range as this final argument (e.g. A1:A3) then when trying to access
any element of the paramarray via its index number the code just stops
and the function returns #VALUE error. Later, when debugging in the
immediate window and trying to do the same thing I get a "subscript
out of range" error. However, if I pass each argument separately in
the UDF (i.e. A1, A2, A3 so that in this example there are now 14
arguments) then I can access each element in this way. Why can't I
pass a range instead of having to pass each element separately?

2) VBA does not seem to like to pass ParamArray from one UDF to
another? Is this correct?

3) Although I am using Option Base 1 it appears that the ParamArray is
still zero-based. Is this correct?

If anybody has any comments it would be greatly appreciated.

Many Thanks
Lloyd