IsArray on Variant/Object/Range returns true??

  • Thread starter Thread starter QuantDev
  • Start date Start date
Q

QuantDev

Any idea on how I can check if the variant contains an array,
considered IsArray returns true for ranges?

thanks
 
Hi QuantDev,
Any idea on how I can check if the variant contains an array,
considered IsArray returns true for ranges?
Click to expand...

If the variable is truly a Variant, then it cannot contain a reference to a
Range object. When you do this:

Dim v As Variant

v = Range("A1:A10")

The variable v is being filled with the *values* from the range A1:A10. So
the IsArray function is correct - the Variant v really does contain an array
at that point.

Maybe I'm not understanding your issue - what is it you are trying to
accomplish?

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
Function TestArray(a)
TestArray = True
On Error Resume Next
If IsError(UBound(a)) Then
TestArray = False
End If
End Function

Stan Scott
New York City
 
Check for a range first

if typename(varr) is "Range" then
' it's a range
elseif isarray(varr) then
' it's an array
 
Dim v as Variant
set v = Range("A1:A10")

v will hold a range reference.

I assume he has a generalized function that is passed in a variant which may
contain a range. This would be a common need in handling a parmarray

--
Regards,
Tom Ogilvy

Jake Marx said:
Hi QuantDev,
Any idea on how I can check if the variant contains an array,
considered IsArray returns true for ranges?
Click to expand...

If the variable is truly a Variant, then it cannot contain a reference to a
Range object. When you do this:

Dim v As Variant

v = Range("A1:A10")

The variable v is being filled with the *values* from the range A1:A10. So
the IsArray function is correct - the Variant v really does contain an array
at that point.

Maybe I'm not understanding your issue - what is it you are trying to
accomplish?

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
Click to expand...
 
Thanks for the correction, Tom. I guess I typically use variables of type
Object to do that, so I wasn't even aware (or forgot) you could do the same
thing with a Variant.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Tom said:
Dim v as Variant
set v = Range("A1:A10")

v will hold a range reference.

I assume he has a generalized function that is passed in a variant
which may contain a range. This would be a common need in handling
a parmarray


Jake Marx said:
Hi QuantDev,
Any idea on how I can check if the variant contains an array,
considered IsArray returns true for ranges?
Click to expand...

If the variable is truly a Variant, then it cannot contain a
reference to a Range object. When you do this:

Dim v As Variant

v = Range("A1:A10")

The variable v is being filled with the *values* from the range
A1:A10. So the IsArray function is correct - the Variant v really
does contain an array at that point.

Maybe I'm not understanding your issue - what is it you are trying to
accomplish?

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
Click to expand...
Click to expand...
 
Stan Scott said:
Function TestArray(a)
TestArray = True
On Error Resume Next
If IsError(UBound(a)) Then
TestArray = False
End If
End Function
Click to expand...

I suppose it would return false for 0 dimensioned arrays.
 
Tom Ogilvy said:
Check for a range first

if typename(varr) is "Range" then
' it's a range
elseif isarray(varr) then
' it's an array

--
Click to expand...

Thanks Tom.

As far as you know, am I going to get "true" for isarray for others
variant/object/* combinations?
thx
 
Jake Marx said:
Thanks for the correction, Tom. I guess I typically use variables of type
Object to do that, so I wasn't even aware (or forgot) you could do the same
thing with a Variant.
Click to expand...

Even if you declare it as an object, IsArray will still return true.

The problem here is that it looks like a range is automatically
converted to a variant array whenever a variant is expected.
 
for example:

? isarray(thisworkbook.Sheets)
False
? isarray(Activesheet.Shapes)
False
? isarray(Range("A1"))
False
? isarray(Range("A1:A10"))
True

I can't think of another object that returns an array for the value (or
default) attribute. That doesn't mean there are not any, but I can't think
of one.
 
Back
Top