ParamArray bugs?

[pinkfloydfan]

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

 

[Tom Ogilvy]

Sounds like your argument is a single contiguous range such as A1112.
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("A112"), 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

 

[JE McGimpsey]

1) If you pass a range, it's passed as a single object, and it's your
function's responsibility to determine the argument type and whether it
should be treated as a block or as a collection of cells. The compiler
can't possibly anticipate whether you intend a range of x cells to be
one argument or x.

2) No, it can be passed like any other variable, but remember it's a
variant array. For instance, try:

Public Sub try()
foo "a", "b"
End Sub

Public Function foo(ParamArray x())
bar x, Array("c", "d")
End Function

Public Function bar(ParamArray y())
Dim i As Long
Dim j As Long
For i = 0 To UBound(y)
For j = 0 To UBound(y(i))
Debug.Print y(i)(j)
Next j
Next i
End Function


3) Yes - Like the array created with Array(), ParamArray is always
zero-based.

 

[pinkfloydfan]

Thanks Tom,

Simply explained as always

Best Regards
Lloyd

 

[Tom Ogilvy]

J.E.,

With my test code, I had an improper use of the paramarray error in my test,
but I didn't try to pass it to another paramarray.

I didn't experience your point 3. When I set option base 1, your code
failed. And that bore out my previous test.

? lbound(y(0)), lbound(y(1))
0 1

This was with xl2003 in Windows. Maybe you were thinking of a variant
picking up a range as always base 1. Or maybe it is different on the MAC.

 

[Tom Ogilvy]

J.E. shows you can pass the paramarray, at least when passed to a paramarray.
I wasn't successful passing to a variant, but didn't test much beyond that.


I disagreed with his analogy for point 3.

 

[JE McGimpsey]

Or maybe it is different on the MAC.

No, wrong coding platform on the Mac - I've been coding too many other
languages lately, and I misremembered.

Best practice: Always use LBound() and UBound()...

 

[Tom Ogilvy]

Hey, you got all the main points correct. <g>