ParamArray bugs?
 

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

ParamArray bugs?
 

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

ParamArray bugs?
 

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.

In article . com,
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

ParamArray bugs?
 

Thanks Tom,

Simply explained as always

Best Regards
Lloyd

ParamArray bugs?
 

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.

--
Regards,
Tom Ogilvy


"JE McGimpsey" wrote:

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.

In article . com,
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

ParamArray bugs?
 

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.

--
Regards,
Tom Ogilvy


"pinkfloydfan" wrote:

Thanks Tom,

Simply explained as always

Best Regards
Lloyd

ParamArray bugs?
 

In article ,
Tom Ogilvy wrote:

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()...

ParamArray bugs?
 

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

--
Regards,
Tom Ogilvy


"JE McGimpsey" wrote:

In article ,
Tom Ogilvy wrote:

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()...