Hello,


Is it possible to test if a variable is given as array?

I need to pass on a value to an UDF in VBA, but it must be given as an
array, i.e.:

MyVar = Array("A")

rather than:

MyVar = "A"


Is it possible to test if 'MyVar' is an array?


TIA,

CE

Since arrays have Ubounds and simple variables do not, we can test for this:

Sub qwerty(t As Variant)
On Error GoTo NotArray
x = UBound(t)
MsgBox (x)
Exit Sub
NotArray:
MsgBox ("clearly not an array")
End Sub


Sub routine()
Dim inputt As String
inputt = "A"
Call qwerty(inputt)
End Sub

--
Gary''s Student - gsnu200847


"Charlotte E" wrote:

Hello,


Is it possible to test if a variable is given as array?

I need to pass on a value to an UDF in VBA, but it must be given as an
array, i.e.:

MyVar = Array("A")

rather than:

MyVar = "A"


Is it possible to test if 'MyVar' is an array?


TIA,

CE

Use ISARRAY

Dim MyArray(1 To 5) As Integer, YourArray, MyCheck ' Declare array
variables.
YourArray = Array(1, 2, 3) ' Use Array function.
MyCheck = IsArray(MyArray) ' Returns True.
MyCheck = IsArray(YourArray)

"Gary''s Student" wrote:

Since arrays have Ubounds and simple variables do not, we can test for this:

Sub qwerty(t As Variant)
On Error GoTo NotArray
x = UBound(t)
MsgBox (x)
Exit Sub
NotArray:
MsgBox ("clearly not an array")
End Sub


Sub routine()
Dim inputt As String
inputt = "A"
Call qwerty(inputt)
End Sub

--
Gary''s Student - gsnu200847


"Charlotte E" wrote:

Hello,


Is it possible to test if a variable is given as array?

I need to pass on a value to an UDF in VBA, but it must be given as an
array, i.e.:

MyVar = Array("A")

rather than:

MyVar = "A"


Is it possible to test if 'MyVar' is an array?


TIA,

CE

Thanks!
--
Gary''s Student - gsnu200847


"joel" wrote:

Use ISARRAY

Dim MyArray(1 To 5) As Integer, YourArray, MyCheck ' Declare array
variables.
YourArray = Array(1, 2, 3) ' Use Array function.
MyCheck = IsArray(MyArray) ' Returns True.
MyCheck = IsArray(YourArray)

"Gary''s Student" wrote:

Since arrays have Ubounds and simple variables do not, we can test for this:

Sub qwerty(t As Variant)
On Error GoTo NotArray
x = UBound(t)
MsgBox (x)
Exit Sub
NotArray:
MsgBox ("clearly not an array")
End Sub


Sub routine()
Dim inputt As String
inputt = "A"
Call qwerty(inputt)
End Sub

--
Gary''s Student - gsnu200847


"Charlotte E" wrote:

Hello,


Is it possible to test if a variable is given as array?

I need to pass on a value to an UDF in VBA, but it must be given as an
array, i.e.:

MyVar = Array("A")

rather than:

MyVar = "A"


Is it possible to test if 'MyVar' is an array?


TIA,

CE

Use ISARRAY

That's certainly one way but there are many ways. Depends on the objective,
if you want to test for an initialized array better to use the method
suggested by GS

Regards,
Peter T


"joel" wrote in message
...
Use ISARRAY

Dim MyArray(1 To 5) As Integer, YourArray, MyCheck ' Declare array
variables.
YourArray = Array(1, 2, 3) ' Use Array function.
MyCheck = IsArray(MyArray) ' Returns True.
MyCheck = IsArray(YourArray)

"Gary''s Student" wrote:

Since arrays have Ubounds and simple variables do not, we can test for
this:

Sub qwerty(t As Variant)
On Error GoTo NotArray
x = UBound(t)
MsgBox (x)
Exit Sub
NotArray:
MsgBox ("clearly not an array")
End Sub


Sub routine()
Dim inputt As String
inputt = "A"
Call qwerty(inputt)
End Sub

--
Gary''s Student - gsnu200847


"Charlotte E" wrote:

Hello,


Is it possible to test if a variable is given as array?

I need to pass on a value to an UDF in VBA, but it must be given as an
array, i.e.:

MyVar = Array("A")

rather than:

MyVar = "A"


Is it possible to test if 'MyVar' is an array?


TIA,

CE

if you want to test for an initialized array better to use the method

In my standard library of functions, I use the following function to
test whether a variable is an array and if so whether it has been
allocated. The function will work with any type of array with any
number of dimensions.

Function IsArrayAllocated(Arr As Variant) As Boolean
On Error Resume Next
IsArrayAllocated = IsArray(Arr) And _
Not IsError(LBound(Arr, 1)) And _
LBound(Arr, 1) <= UBound(Arr, 1)
End Function

' Call with
Dim B As Boolean
B = IsArrayAllocated(V)
If B = True Then
Debug.Print "V is an array"
Else
Debug.Print "V is not an allocated array"
End If

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




On Sat, 18 Apr 2009 15:06:13 +0100, "Peter T" <peter_t@discussions
wrote:

Use ISARRAY

That's certainly one way but there are many ways. Depends on the objective,
if you want to test for an initialized array better to use the method
suggested by GS

Regards,
Peter T


"joel" wrote in message
...
Use ISARRAY

Dim MyArray(1 To 5) As Integer, YourArray, MyCheck ' Declare array
variables.
YourArray = Array(1, 2, 3) ' Use Array function.
MyCheck = IsArray(MyArray) ' Returns True.
MyCheck = IsArray(YourArray)

"Gary''s Student" wrote:

Since arrays have Ubounds and simple variables do not, we can test for
this:

Sub qwerty(t As Variant)
On Error GoTo NotArray
x = UBound(t)
MsgBox (x)
Exit Sub
NotArray:
MsgBox ("clearly not an array")
End Sub


Sub routine()
Dim inputt As String
inputt = "A"
Call qwerty(inputt)
End Sub

--
Gary''s Student - gsnu200847


"Charlotte E" wrote:

Hello,


Is it possible to test if a variable is given as array?

I need to pass on a value to an UDF in VBA, but it must be given as an
array, i.e.:

MyVar = Array("A")

rather than:

MyVar = "A"


Is it possible to test if 'MyVar' is an array?


TIA,

CE

I am probably missing something but why not simply -

Function IsArrayAllocated(V As Variant) As Boolean
On Error Resume Next
IsArrayAllocated = (LBound(V, 1) <= UBound(V, 1))
End Function

IOW, in what scenario would the bounds check return true but IsArray(V)
return false.

Regards,
Peter T

"Chip Pearson" wrote in message
...

if you want to test for an initialized array better to use the method

In my standard library of functions, I use the following function to
test whether a variable is an array and if so whether it has been
allocated. The function will work with any type of array with any
number of dimensions.

Function IsArrayAllocated(Arr As Variant) As Boolean
On Error Resume Next
IsArrayAllocated = IsArray(Arr) And _
Not IsError(LBound(Arr, 1)) And _
LBound(Arr, 1) <= UBound(Arr, 1)
End Function

' Call with
Dim B As Boolean
B = IsArrayAllocated(V)
If B = True Then
Debug.Print "V is an array"
Else
Debug.Print "V is not an allocated array"
End If

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




On Sat, 18 Apr 2009 15:06:13 +0100, "Peter T" <peter_t@discussions
wrote:

Use ISARRAY

That's certainly one way but there are many ways. Depends on the
objective,
if you want to test for an initialized array better to use the method
suggested by GS

Regards,
Peter T


"joel" wrote in message
...
Use ISARRAY

Dim MyArray(1 To 5) As Integer, YourArray, MyCheck ' Declare array
variables.
YourArray = Array(1, 2, 3) ' Use Array function.
MyCheck = IsArray(MyArray) ' Returns True.
MyCheck = IsArray(YourArray)

"Gary''s Student" wrote:

Since arrays have Ubounds and simple variables do not, we can test for
this:

Sub qwerty(t As Variant)
On Error GoTo NotArray
x = UBound(t)
MsgBox (x)
Exit Sub
NotArray:
MsgBox ("clearly not an array")
End Sub


Sub routine()
Dim inputt As String
inputt = "A"
Call qwerty(inputt)
End Sub

--
Gary''s Student - gsnu200847


"Charlotte E" wrote:

Hello,


Is it possible to test if a variable is given as array?

I need to pass on a value to an UDF in VBA, but it must be given as
an
array, i.e.:

MyVar = Array("A")

rather than:

MyVar = "A"


Is it possible to test if 'MyVar' is an array?


TIA,

CE

I am probably missing something but why not simply -

I think you're right. I've had my version in my ArrayUtilities module
for so long that I don't even think about it.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Sat, 18 Apr 2009 16:50:37 +0100, "Peter T" <peter_t@discussions
wrote:

I am probably missing something but why not simply -

Function IsArrayAllocated(V As Variant) As Boolean
On Error Resume Next
IsArrayAllocated = (LBound(V, 1) <= UBound(V, 1))
End Function

IOW, in what scenario would the bounds check return true but IsArray(V)
return false.

Regards,
Peter T

"Chip Pearson" wrote in message
.. .

if you want to test for an initialized array better to use the method

In my standard library of functions, I use the following function to
test whether a variable is an array and if so whether it has been
allocated. The function will work with any type of array with any
number of dimensions.

Function IsArrayAllocated(Arr As Variant) As Boolean
On Error Resume Next
IsArrayAllocated = IsArray(Arr) And _
Not IsError(LBound(Arr, 1)) And _
LBound(Arr, 1) <= UBound(Arr, 1)
End Function

' Call with
Dim B As Boolean
B = IsArrayAllocated(V)
If B = True Then
Debug.Print "V is an array"
Else
Debug.Print "V is not an allocated array"
End If

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




On Sat, 18 Apr 2009 15:06:13 +0100, "Peter T" <peter_t@discussions
wrote:

Use ISARRAY

That's certainly one way but there are many ways. Depends on the
objective,
if you want to test for an initialized array better to use the method
suggested by GS

Regards,
Peter T


"joel" wrote in message
...
Use ISARRAY

Dim MyArray(1 To 5) As Integer, YourArray, MyCheck ' Declare array
variables.
YourArray = Array(1, 2, 3) ' Use Array function.
MyCheck = IsArray(MyArray) ' Returns True.
MyCheck = IsArray(YourArray)

"Gary''s Student" wrote:

Since arrays have Ubounds and simple variables do not, we can test for
this:

Sub qwerty(t As Variant)
On Error GoTo NotArray
x = UBound(t)
MsgBox (x)
Exit Sub
NotArray:
MsgBox ("clearly not an array")
End Sub


Sub routine()
Dim inputt As String
inputt = "A"
Call qwerty(inputt)
End Sub

--
Gary''s Student - gsnu200847


"Charlotte E" wrote:

Hello,


Is it possible to test if a variable is given as array?

I need to pass on a value to an UDF in VBA, but it must be given as
an
array, i.e.:

MyVar = Array("A")

rather than:

MyVar = "A"


Is it possible to test if 'MyVar' is an array?


TIA,

CE

Peter,
I proposes something similar to this a while back but
never got a reaction...
'--
Function Scrabble(V As Variant) As Boolean
On Error Resume Next
'lower limit for a double
Scrabble = UBound(V, 1) -1.79769313486232E+307
End Function
--
Jim Cone
Portland, Oregon USA


"Peter T"
<peter_t@discussions
wrote in message
I am probably missing something but why not simply -

Function IsArrayAllocated(V As Variant) As Boolean
On Error Resume Next
IsArrayAllocated = (LBound(V, 1) <= UBound(V, 1))
End Function

IOW, in what scenario would the bounds check return true but IsArray(V)
return false.
Regards,
Peter T

In any case, Peter T's code is about 1 or 2 % faster than my function,
over 1,000,000 loops.
--
Jim Cone
Portland, Oregon USA



"Jim Cone"

wrote in message
Peter,
I proposed something similar to this a while back but
never got a reaction...
'--
Function Scrabble(V As Variant) As Boolean
On Error Resume Next
'lower limit for a double
Scrabble = UBound(V, 1) -1.79769313486232E+307
End Function
--
Jim Cone
Portland, Oregon USA



"Peter T"
<peter_t@discussions
wrote in message
I am probably missing something but why not simply -

Function IsArrayAllocated(V As Variant) As Boolean
On Error Resume Next
IsArrayAllocated = (LBound(V, 1) <= UBound(V, 1))
End Function

IOW, in what scenario would the bounds check return true but IsArray(V)
return false.
Regards,
Peter T

Sorry for a late reply, guys...

But, at least to me, it was worth it: Lots of suggestions and, not least,
great learning too :-)


Thanks for all the responses.... :-)))

Hi Jim,

First I can't claim that as "my" function. The basic method to test for the
existence of (say) UBound w/out error is long established.

In my first reply I had forgotten about the scenario of LBound(v) = 0 and
UBound(v) = -1, that would pass the sole UBound check but give incorrect
result without checking UBound = LBound. That was catered for in Chip's
original function which I adapted.

Your UBound(V, 1) -1.79769313486232E+307 removes the requirement to check
both bounds. If you conceived that it is indeed an original and I think
reliable method. So the accolade goes to you :-)

I wonder though what the lowest theoretical UBound actually is, much more
than that lowest double. From light testing it appears to be the lowest
long, so maybe(?) your function could be changed to

Scrabble = UBound(V, 1) = -2 ^ 31

Regards,
Peter T


"Jim Cone" wrote in message
In any case, Peter T's code is about 1 or 2 % faster than my function,
over 1,000,000 loops.


Peter,
I proposed something similar to this a while back but
never got a reaction...
'--
Function Scrabble(V As Variant) As Boolean
On Error Resume Next
'lower limit for a double
Scrabble = UBound(V, 1) -1.79769313486232E+307
End Function
--
Jim Cone
Portland, Oregon USA



"Peter T"
<peter_t@discussions
wrote in message
I am probably missing something but why not simply -

Function IsArrayAllocated(V As Variant) As Boolean
On Error Resume Next
IsArrayAllocated = (LBound(V, 1) <= UBound(V, 1))
End Function

IOW, in what scenario would the bounds check return true but IsArray(V)
return false.
Regards,
Peter T

Peter,
Yes, I get an overflow error trying to dimension an array using numbers
smaller than -2147483648 (lower limit for a Long).

The fastest times are for...
Scrabble = (UBound(V, 1) = -2147483647) 'last digit 7 not 8

-2147483648 is automatically converted to a double by Excel
and is a smidgen slower.

-2 ^ 31 is slower yet.

However, as a practical matter, any function that works is fast enough. <g
--
Jim Cone
Portland, Oregon USA




"Peter T" <peter_t@discussions
wrote in message
Hi Jim,
First I can't claim that as "my" function. The basic method to test for the
existence of (say) UBound w/out error is long established.

In my first reply I had forgotten about the scenario of LBound(v) = 0 and
UBound(v) = -1, that would pass the sole UBound check but give incorrect
result without checking UBound = LBound. That was catered for in Chip's
original function which I adapted.

Your UBound(V, 1) -1.79769313486232E+307 removes the requirement to check
both bounds. If you conceived that it is indeed an original and I think
reliable method. So the accolade goes to you :-)

I wonder though what the lowest theoretical UBound actually is, much more
than that lowest double. From light testing it appears to be the lowest
long, so maybe(?) your function could be changed to

Scrabble = UBound(V, 1) = -2 ^ 31

Regards,
Peter T


"Jim Cone"

wrote in message
In any case, Peter T's code is about 1 or 2 % faster than my function,
over 1,000,000 loops.


Peter,
I proposed something similar to this a while back but
never got a reaction...
'--
Function Scrabble(V As Variant) As Boolean
On Error Resume Next
'lower limit for a double
Scrabble = UBound(V, 1) -1.79769313486232E+307
End Function
--
Jim Cone
Portland, Oregon USA



"Peter T"
<peter_t@discussions
wrote in message
I am probably missing something but why not simply -

Function IsArrayAllocated(V As Variant) As Boolean
On Error Resume Next
IsArrayAllocated = (LBound(V, 1) <= UBound(V, 1))
End Function

IOW, in what scenario would the bounds check return true but IsArray(V)
return false.
Regards,
Peter T