I have the following, with the first row of the range containing the
values 33,34,35,36. When I run the sub procedure the Locals window shows
in part as below, indicating that x(0)(1) is a Variant array, but I get
a "Subscript out of range" error message at the line of foo10,
z = UBound(x(0)(1). Can someone explain the apparent anomaly?

Thanks,
Alan Beban

Sub abtest5()
x = range("a51:d54")
y = foo10(x)
End Sub

Function foo10(ParamArray x())
z = UBound(x(0)(1))
End Function

x Variant(0 to 0)
x(0) Variant/Variant/Variant(1 to 4,1 to 4)
x(0)(1) Variant(1 to 4)
x(0)(1,1) 33 Variant/Double
x(0)(1,2) 34 Variant/Double
x(0)(1,3) 35 Variant/Double
x(0)(1,4) 36 Variant/Double

The array is x(0) not x(0)(1)

Sub abtest5()
Dim x, y
x = Range("a51:d54")
y = foo10(x)
End Sub

Function foo10(ParamArray x())
Dim z
z = UBound(x(0))
End Function


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Alan Beban" wrote in message
...
I have the following, with the first row of the range containing the values
33,34,35,36. When I run the sub procedure the Locals window shows in part
as below, indicating that x(0)(1) is a Variant array, but I get a
"Subscript out of range" error message at the line of foo10,
z = UBound(x(0)(1). Can someone explain the apparent anomaly?

Thanks,
Alan Beban

Sub abtest5()
x = range("a51:d54")
y = foo10(x)
End Sub

Function foo10(ParamArray x())
z = UBound(x(0)(1))
End Function

x Variant(0 to 0)
x(0) Variant/Variant/Variant(1 to 4,1 to 4)
x(0)(1) Variant(1 to 4)
x(0)(1,1) 33 Variant/Double
x(0)(1,2) 34 Variant/Double
x(0)(1,3) 35 Variant/Double
x(0)(1,4) 36 Variant/Double

Bob Phillips wrote:
The array is x(0) not x(0)(1)

Sub abtest5()
Dim x, y
x = Range("a51:d54")
y = foo10(x)
End Sub

Function foo10(ParamArray x())
Dim z
z = UBound(x(0))
End Function



Thanks for responding.

Yes, one would think. But what is the meaning then, in the Locals
window, of the line questioned below?


x Variant(0 to 0)
x(0) Variant/Variant/Variant(1 to 4,1 to 4)
x(0)(1) Variant(1 to 4) '<---------------------??
x(0)(1,1) 33 Variant/Double
x(0)(1,2) 34 Variant/Double
x(0)(1,3) 35 Variant/Double
x(0)(1,4) 36 Variant/Double

Alan Beban

I think what you are trying to do with this -

Function foo10(ParamArray x())
z = UBound(x(0)(1))
End Function

is really -

Function foo10(ParamArray x())
z = UBound(Application.Index(x(0), 1))
End Function

or depending which dimension -

z = UBound(Application.Index(x(0), 2))

Regards,
Peter T



"Alan Beban" wrote in message
...
I have the following, with the first row of the range containing the
values 33,34,35,36. When I run the sub procedure the Locals window shows
in part as below, indicating that x(0)(1) is a Variant array, but I get
a "Subscript out of range" error message at the line of foo10,
z = UBound(x(0)(1). Can someone explain the apparent anomaly?

Thanks,
Alan Beban

Sub abtest5()
x = range("a51:d54")
y = foo10(x)
End Sub

Function foo10(ParamArray x())
z = UBound(x(0)(1))
End Function

x Variant(0 to 0)
x(0) Variant/Variant/Variant(1 to 4,1 to 4)
x(0)(1) Variant(1 to 4)
x(0)(1,1) 33 Variant/Double
x(0)(1,2) 34 Variant/Double
x(0)(1,3) 35 Variant/Double
x(0)(1,4) 36 Variant/Double

Peter T wrote:
I think what you are trying to do with this -


Function foo10(ParamArray x())
z = UBound(x(0)(1))
End Function


is really -

Function foo10(ParamArray x())
z = UBound(Application.Index(x(0), 1))
End Function

or depending which dimension -

z = UBound(Application.Index(x(0), 2))

Regards,
Peter T

Thanks for responding. What I'm really trying to do is understand the
following line in the Locals window when z = UBound(x(0)(1)) gives a
"Subscript out of range" error message:

x(0)(1) Variant(1 to 4)

Thanks,
Alan Beban

"Alan Beban" wrote in message
...
Peter T wrote:
I think what you are trying to do with this -


Function foo10(ParamArray x())
z = UBound(x(0)(1))
End Function


is really -

Function foo10(ParamArray x())
z = UBound(Application.Index(x(0), 1))
End Function

or depending which dimension -

z = UBound(Application.Index(x(0), 2))

Regards,
Peter T

Thanks for responding. What I'm really trying to do is understand the
following line in the Locals window when z = UBound(x(0)(1)) gives a
"Subscript out of range" error message:

x(0)(1) Variant(1 to 4)

Thanks,
Alan Beban

Perhaps I'm missing something but I don't see any anomaly. I would expect
this line to fail (where x is the ParamArray)
z = UBound(x(0)(1))

x(0) is the first and, in your example, only element in the paramArray.
Also in the example, this sole 'element' in the paramArray is a 2d array
x(0)(1) in effect refers to the top row of the 2d array, NOT an entire
array, hence my Index example.

arr = x(0)
arr is now a 2d array, same as the array (also named x in the calling
procedure) assigned to the range.value

look at arr(1) in locals and it's type description is the same as x(0)(1),
as expected

z = Ubound(arr(1))
Above fails as expected, as does z = UBound(x(0)(1)) which is effectively
same.


To get the bounds of any arrays in a ParamArray -

Sub test1()
a = Range("A1:C4") ' 4x3
b = 123
c = Range("A1:E7") ' 7x5
res = foo1(a, b, c)
End Sub

Function foo1(ParamArray pa())
e = 0
For Each v In pa
If IsArray(v) Then
Debug.Print e; "is array", UBound(v); UBound(v, 2)
Else
Debug.Print e; "not array"
End If
e = e + 1
Next

' or
For e = 0 To UBound(pa)
If IsArray(pa(e)) Then
Debug.Print e; "is array", UBound(pa(e)); UBound(pa(e), 2)
Else
Debug.Print e; "not array"
End If
Next

End Function

Regards,
Peter T

Peter T wrote:

Perhaps I'm missing something but I don't see any anomaly. I would expect
this line to fail (where x is the ParamArray)
z = UBound(x(0)(1))

x(0) is the first and, in your example, only element in the paramArray.
Also in the example, this sole 'element' in the paramArray is a 2d array
x(0)(1) in effect refers to the top row of the 2d array, NOT an entire
array, hence my Index example.


What it comes down to is my failure to observe that the line in the
Locals window

x(0)(1) Variant(1 to 4)

did not indicate that x(0)(1) was a Variant() array. This is the first
time in the dozen or so years that I have been fooling with this stuff
that I have had occasion to recognize that a Variant variable can refer
to not only a value or an array, but a set of multiple values that do
not constitute an array.

Thanks very much for your thorough response.

Alan Beban

Peter T wrote:


Perhaps I'm missing something but I don't see any anomaly. I would expect
this line to fail (where x is the ParamArray)
z = UBound(x(0)(1))

On reflection, I guess there does remain one slight anomaly. The Locals
window suggests that x(0)(1) is of type Variant, whereas

Typename(x(0)(1)) will throw an error.

Thanks again,
Alan Beban

"Alan Beban" wrote:

Peter T wrote:


Perhaps I'm missing something but I don't see any anomaly. I would
expect
this line to fail (where x is the ParamArray)
z = UBound(x(0)(1))

On reflection, I guess there does remain one slight anomaly. The Locals
window suggests that x(0)(1) is of type Variant, whereas

Typename(x(0)(1)) will throw an error.

Thanks again,
Alan Beban

But that's the same non-anomaly as the last one :-)

it's the same as trying to do -
Redim arr(1 to 4, 1 to 4)
Typename(arr(1)) ' Subscript out of range

but could do -
TypeName(Application.Index(x(0), 1)) ' Variant()

which is where I came in!

Regards,
Peter T

Peter T wrote:
But that's the same non-anomaly as the last one :-)

it's the same as trying to do -
Redim arr(1 to 4, 1 to 4)
Typename(arr(1)) ' Subscript out of range

but could do -
TypeName(Application.Index(x(0), 1)) ' Variant()

which is where I came in!

Regards,
Peter T


I'm not sure how worthwile it is for me to be prolonging this thread,
but then not everything I do is worthwile :-)

The only thing that now remains that strikes me as anomalous is that the
Locals window seems to be indicating that x(0)(1) is a variable of the
Variant type, i.e., it shows

Expression Type
x Variant(0 to 0)
x(0) Variant/Variant/Variant(1 to 4, 1 to 4)
x(0)(1) Variant(1 to 4)

but that doesn't seem to be the case.

Regards,
Alan Beban

"Alan Beban" wrote in message
...
Peter T wrote:
But that's the same non-anomaly as the last one :-)

it's the same as trying to do -
Redim arr(1 to 4, 1 to 4)
Typename(arr(1)) ' Subscript out of range

but could do -
TypeName(Application.Index(x(0), 1)) ' Variant()

which is where I came in!

Regards,
Peter T


I'm not sure how worthwile it is for me to be prolonging this thread,
but then not everything I do is worthwile :-)

The only thing that now remains that strikes me as anomalous is that the
Locals window seems to be indicating that x(0)(1) is a variable of the
Variant type, i.e., it shows

Expression Type
x Variant(0 to 0)
x(0) Variant/Variant/Variant(1 to 4, 1 to 4)
x(0)(1) Variant(1 to 4)

but that doesn't seem to be the case.

Regards,
Alan Beban

I still don't see any anomaly in attempts to reference x(0)(1) with Ubound,
typename, vartype (cos' it's a one row of a multi-row array), but curiously
I too am starting to see something odd in Locals. In particular an
inconsistency between looking at the same array in Locals vs Watch.

Sub test()
Dim a
Dim b()
Dim c(): ReDim c(1 To 4, 1 To 3) As Variant
Dim d: ReDim d(1 To 4, 1 To 3) As Variant

a = Range("a1:c4")
b = Range("a1:c4")
c = Range("a1:c4")
d = Range("a1:c4")
res = foo(a, b, c, d)

End Sub
Function foo(ParamArray x())
Dim y
Dim z()

y = x
z = x

End Function

Looking in Locals at x(0) and x(3) both show
Variant/Variant/Variant(1 to 4, 1 to 3)

However, looking at same in Watch, x(0) and x(3) both show
Variant/Variant(1 to 4, 1 to 3)

x(1) and x(2) show the Variant/Variant in both Locals and Watch

I also compared y & z in Locals vs Watch

Locals
y - Variant/Variant(0 to 3) ' as expected
z - Variant(0 to 3) ' as expected

Watch
y - Variant/Variant(0 to 3) ' as expected
z - Variant/Variant(0 to 3) ' NOT as expected

y(n) and z(n) are all Variant/Variant(1 to 4,1 to 3) in both Locals &
Watch, ie not quite same as some of the equivalents in x

Indeed odd, not sure any of this has any implications, probably not ?

I think we can put it down to a quirk !

Regards,
Peter T