If an array is originally dim'd as: TestAy() as whatever
I can't find a way, in a subsequent macro where TestAy() is an argument,
to directly test whether or not it has been re-dim'd.

I have two macros, the first has a string array that is dim'd without
boundaries.

dim TestAy() as string

IF sub MacroOne puts values into it, I use:

redim TestAy(1 to Quantity) ' and then fill it with values.

Now, MacroTwo is called

call MacroTwo(TestAy())

sub MacroTwo(TestAy() as string)
' I've tried different ways, but keep getting subscript errors, when I try
to
' test whether or not TestAy has values without also creating and
' passing another argument showing whether or not TestAy was
' redim'd in MacroOne. Some examples follow of stuff that didn't work
' when MacroOne did NOT redim TestAy.
' NOTE: I'm looking for a direct way, if it exists, to learn. I know that
' I could redim TestAy(1) in MacroOne and then in MacroTwo test
' TestAy(1) to see what it contained.

if lbound(TestAy) 0 then ..... ' errors out if not dimmed

dim Number
number = vartype(TestAy) ' tells me it's an array but no boundaries

Dim holdARRAY
holdARRAY = Array(TestAy)
MsgBox LBound(holdARRAY(1)) 'errors out if TestAy not redim'd.

end sub

Thanks for your help.

--
Neal Z

Check for the upper boundry of the array...
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Sub Scrabble()
Dim arr() As String
Dim blnReady As Boolean

'ReDim arr(-9999 To -999)

On Error Resume Next
blnReady = UBound(arr) -999999
On Error GoTo 0
MsgBox blnReady
End Sub
'-----------

Or this from Chip Pearson...
Public Function IsArrayEmpty(Arr() As Variant) As Boolean
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''
' IsArrayEmpty
' This returns TRUE if the array is dynamic and has not
'been allocated with a Redim statement.
' Returns FALSE if the array is static or has been
'allocated with a Redim statement.
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''
Dim N As Long
On Error Resume Next
N = UBound(Arr)
If Err.Number = 0 Then
IsArrayEmpty = False
Else
IsArrayEmpty = True
End If
End Function
'-------------------



"Neal Zimm"

wrote in message
If an array is originally dim'd as: TestAy() as whatever
I can't find a way, in a subsequent macro where TestAy() is an argument,
to directly test whether or not it has been re-dim'd.

I have two macros, the first has a string array that is dim'd without
boundaries.

dim TestAy() as string
IF sub MacroOne puts values into it, I use:
redim TestAy(1 to Quantity) ' and then fill it with values.
Now, MacroTwo is called
call MacroTwo(TestAy())
sub MacroTwo(TestAy() as string)
' I've tried different ways, but keep getting subscript errors, when I try
to test whether or not TestAy has values without also creating and
' passing another argument showing whether or not TestAy was
' redim'd in MacroOne. Some examples follow of stuff that didn't work
' when MacroOne did NOT redim TestAy.
' NOTE: I'm looking for a direct way, if it exists, to learn. I know that
' I could redim TestAy(1) in MacroOne and then in MacroTwo test
' TestAy(1) to see what it contained.
if lbound(TestAy) 0 then ..... ' errors out if not dimmed
dim Number
number = vartype(TestAy) ' tells me it's an array but no boundaries
Dim holdARRAY
holdARRAY = Array(TestAy)
MsgBox LBound(holdARRAY(1)) 'errors out if TestAy not redim'd.
end sub

Thanks for your help.
--
Neal Z

Neal,

I use the following procedure in my standard library:

Public Function IsArrayAllocated(Arr As Variant) As Boolean
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''''''
' IsArrayAllocated
' Returns TRUE if the array is allocated (either a static array or a dynamic
array that has been
' sized with Redim) or FALSE if the array is not allocated (a dynamic that
has not yet
' been sized with Redim, or a dynamic array that has been Erased). Static
arrays are always
' allocated.
'
' The VBA IsArray function indicates whether a variable is an array, but it
does not
' distinguish between allocated and unallocated arrays. It will return TRUE
for both
' allocated and unallocated arrays. This function tests whether the array
has actually
' been allocated.
'
' This function is just the reverse of IsArrayEmpty.
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''

Dim N As Long
On Error Resume Next

' if Arr is not an array, return FALSE and get out.
If IsArray(Arr) = False Then
IsArrayAllocated = False
Exit Function
End If

' Attempt to get the UBound of the array. If the array has not been
allocated,
' an error will occur. Test Err.Number to see if an error occurred.
N = UBound(Arr, 1)
If (Err.Number = 0) Then
''''''''''''''''''''''''''''''''''''''
' Under some circumstances, if an array
' is not allocated, Err.Number will be
' 0. To acccomodate this case, we test
' whether LBound <= Ubound. If this
' is True, the array is allocated. Otherwise,
' the array is not allocated.
'''''''''''''''''''''''''''''''''''''''
If LBound(Arr) <= UBound(Arr) Then
' no error. array has been allocated.
IsArrayAllocated = True
Else
IsArrayAllocated = False
End If
Else
' error. unallocated array
IsArrayAllocated = False
End If

End Function

The function supports both single-dimensional and multi-dimensional arrays.

You would then call the function with code like the following:

Dim Arr() As Long
If IsArrayAllocated(Arr) = True Then
Debug.Print "Array has been Redim'd"
Else
Debug.Print "Array hasn't been Redim'd or was Erased."
End If



This and about 20 other array-related utility functions can be found at
http://www.cpearson.com/excel/VBAArrays.htm You can download code and
examples from
http://www.cpearson.com/Zips/modArraySupport.zip


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"Neal Zimm" wrote in message
...
If an array is originally dim'd as: TestAy() as whatever
I can't find a way, in a subsequent macro where TestAy() is an argument,
to directly test whether or not it has been re-dim'd.

I have two macros, the first has a string array that is dim'd without
boundaries.

dim TestAy() as string

IF sub MacroOne puts values into it, I use:

redim TestAy(1 to Quantity) ' and then fill it with values.

Now, MacroTwo is called

call MacroTwo(TestAy())

sub MacroTwo(TestAy() as string)
' I've tried different ways, but keep getting subscript errors, when I
try
to
' test whether or not TestAy has values without also creating and
' passing another argument showing whether or not TestAy was
' redim'd in MacroOne. Some examples follow of stuff that didn't work
' when MacroOne did NOT redim TestAy.
' NOTE: I'm looking for a direct way, if it exists, to learn. I know
that
' I could redim TestAy(1) in MacroOne and then in MacroTwo test
' TestAy(1) to see what it contained.

if lbound(TestAy) 0 then ..... ' errors out if not dimmed

dim Number
number = vartype(TestAy) ' tells me it's an array but no boundaries

Dim holdARRAY
holdARRAY = Array(TestAy)
MsgBox LBound(holdARRAY(1)) 'errors out if TestAy not redim'd.

end sub

Thanks for your help.

--
Neal Z

Thanks Jim, I'll try the upper boundary test. It's interesting that both your
and Chip's example require a test, with the "on error...." syntax. It kinda
confirms my
suspicion that there is no "direct" way, ( my term) to do the test.
--
Neal Z


"Jim Cone" wrote:

Check for the upper boundry of the array...
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Sub Scrabble()
Dim arr() As String
Dim blnReady As Boolean

'ReDim arr(-9999 To -999)

On Error Resume Next
blnReady = UBound(arr) -999999
On Error GoTo 0
MsgBox blnReady
End Sub
'-----------

Or this from Chip Pearson...
Public Function IsArrayEmpty(Arr() As Variant) As Boolean
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''
' IsArrayEmpty
' This returns TRUE if the array is dynamic and has not
'been allocated with a Redim statement.
' Returns FALSE if the array is static or has been
'allocated with a Redim statement.
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''
Dim N As Long
On Error Resume Next
N = UBound(Arr)
If Err.Number = 0 Then
IsArrayEmpty = False
Else
IsArrayEmpty = True
End If
End Function
'-------------------



"Neal Zimm"

wrote in message
If an array is originally dim'd as: TestAy() as whatever
I can't find a way, in a subsequent macro where TestAy() is an argument,
to directly test whether or not it has been re-dim'd.

I have two macros, the first has a string array that is dim'd without
boundaries.

dim TestAy() as string
IF sub MacroOne puts values into it, I use:
redim TestAy(1 to Quantity) ' and then fill it with values.
Now, MacroTwo is called
call MacroTwo(TestAy())
sub MacroTwo(TestAy() as string)
' I've tried different ways, but keep getting subscript errors, when I try
to test whether or not TestAy has values without also creating and
' passing another argument showing whether or not TestAy was
' redim'd in MacroOne. Some examples follow of stuff that didn't work
' when MacroOne did NOT redim TestAy.
' NOTE: I'm looking for a direct way, if it exists, to learn. I know that
' I could redim TestAy(1) in MacroOne and then in MacroTwo test
' TestAy(1) to see what it contained.
if lbound(TestAy) 0 then ..... ' errors out if not dimmed
dim Number
number = vartype(TestAy) ' tells me it's an array but no boundaries
Dim holdARRAY
holdARRAY = Array(TestAy)
MsgBox LBound(holdARRAY(1)) 'errors out if TestAy not redim'd.
end sub

Thanks for your help.
--
Neal Z

Thanks Chip, It's interesting that both your and Jim C's example still
require a test, with the "on error...." syntax. I'm still queasy about using
the on error ....
method. I need to read up more about it. I'm afraid something else will
error out and the error won't be caught.
Your answer kinda confirms my suspicion that there is no "direct" way, (
my term) to do the test. But I like the idea of your function and will use
it.
Thanks for your time, I use your web site all the time. Happy New Year.
Regards,
Neal Z


"Chip Pearson" wrote:

Neal,

I use the following procedure in my standard library:

Public Function IsArrayAllocated(Arr As Variant) As Boolean
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''''''
' IsArrayAllocated
' Returns TRUE if the array is allocated (either a static array or a dynamic
array that has been
' sized with Redim) or FALSE if the array is not allocated (a dynamic that
has not yet
' been sized with Redim, or a dynamic array that has been Erased). Static
arrays are always
' allocated.
'
' The VBA IsArray function indicates whether a variable is an array, but it
does not
' distinguish between allocated and unallocated arrays. It will return TRUE
for both
' allocated and unallocated arrays. This function tests whether the array
has actually
' been allocated.
'
' This function is just the reverse of IsArrayEmpty.
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''

Dim N As Long
On Error Resume Next

' if Arr is not an array, return FALSE and get out.
If IsArray(Arr) = False Then
IsArrayAllocated = False
Exit Function
End If

' Attempt to get the UBound of the array. If the array has not been
allocated,
' an error will occur. Test Err.Number to see if an error occurred.
N = UBound(Arr, 1)
If (Err.Number = 0) Then
''''''''''''''''''''''''''''''''''''''
' Under some circumstances, if an array
' is not allocated, Err.Number will be
' 0. To acccomodate this case, we test
' whether LBound <= Ubound. If this
' is True, the array is allocated. Otherwise,
' the array is not allocated.
'''''''''''''''''''''''''''''''''''''''
If LBound(Arr) <= UBound(Arr) Then
' no error. array has been allocated.
IsArrayAllocated = True
Else
IsArrayAllocated = False
End If
Else
' error. unallocated array
IsArrayAllocated = False
End If

End Function

The function supports both single-dimensional and multi-dimensional arrays.

You would then call the function with code like the following:

Dim Arr() As Long
If IsArrayAllocated(Arr) = True Then
Debug.Print "Array has been Redim'd"
Else
Debug.Print "Array hasn't been Redim'd or was Erased."
End If



This and about 20 other array-related utility functions can be found at
http://www.cpearson.com/excel/VBAArrays.htm You can download code and
examples from
http://www.cpearson.com/Zips/modArraySupport.zip


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"Neal Zimm" wrote in message
...
If an array is originally dim'd as: TestAy() as whatever
I can't find a way, in a subsequent macro where TestAy() is an argument,
to directly test whether or not it has been re-dim'd.

I have two macros, the first has a string array that is dim'd without
boundaries.

dim TestAy() as string

IF sub MacroOne puts values into it, I use:

redim TestAy(1 to Quantity) ' and then fill it with values.

Now, MacroTwo is called

call MacroTwo(TestAy())

sub MacroTwo(TestAy() as string)
' I've tried different ways, but keep getting subscript errors, when I
try
to
' test whether or not TestAy has values without also creating and
' passing another argument showing whether or not TestAy was
' redim'd in MacroOne. Some examples follow of stuff that didn't work
' when MacroOne did NOT redim TestAy.
' NOTE: I'm looking for a direct way, if it exists, to learn. I know
that
' I could redim TestAy(1) in MacroOne and then in MacroTwo test
' TestAy(1) to see what it contained.

if lbound(TestAy) 0 then ..... ' errors out if not dimmed

dim Number
number = vartype(TestAy) ' tells me it's an array but no boundaries

Dim holdARRAY
holdARRAY = Array(TestAy)
MsgBox LBound(holdARRAY(1)) 'errors out if TestAy not redim'd.

end sub

Thanks for your help.

--
Neal Z

Neal,

Actually, there is another way to determine whether an array is actually
allocated, without using On Error. I don't use it for two reasons: (1) I
don't know why it works, and (2) it is probably unsupported by Microsoft.
However, it does work, just don't ask me why. I would be very wary of using
it in production code.

Dim A() As Long
If Not Not A Then
Debug.Print "allocated"
Else
Debug.Print "unallocated"
End If
ReDim A(1 To 3)
If Not Not A Then
Debug.Print "allocated"
Else
Debug.Print "unallocated"
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"Neal Zimm" wrote in message
...
Thanks Chip, It's interesting that both your and Jim C's example still
require a test, with the "on error...." syntax. I'm still queasy about
using
the on error ....
method. I need to read up more about it. I'm afraid something else will
error out and the error won't be caught.
Your answer kinda confirms my suspicion that there is no "direct" way, (
my term) to do the test. But I like the idea of your function and will use
it.
Thanks for your time, I use your web site all the time. Happy New Year.
Regards,
Neal Z


"Chip Pearson" wrote:

Neal,

I use the following procedure in my standard library:

Public Function IsArrayAllocated(Arr As Variant) As Boolean
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''''''
' IsArrayAllocated
' Returns TRUE if the array is allocated (either a static array or a
dynamic
array that has been
' sized with Redim) or FALSE if the array is not allocated (a dynamic
that
has not yet
' been sized with Redim, or a dynamic array that has been Erased). Static
arrays are always
' allocated.
'
' The VBA IsArray function indicates whether a variable is an array, but
it
does not
' distinguish between allocated and unallocated arrays. It will return
TRUE
for both
' allocated and unallocated arrays. This function tests whether the array
has actually
' been allocated.
'
' This function is just the reverse of IsArrayEmpty.
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''

Dim N As Long
On Error Resume Next

' if Arr is not an array, return FALSE and get out.
If IsArray(Arr) = False Then
IsArrayAllocated = False
Exit Function
End If

' Attempt to get the UBound of the array. If the array has not been
allocated,
' an error will occur. Test Err.Number to see if an error occurred.
N = UBound(Arr, 1)
If (Err.Number = 0) Then
''''''''''''''''''''''''''''''''''''''
' Under some circumstances, if an array
' is not allocated, Err.Number will be
' 0. To acccomodate this case, we test
' whether LBound <= Ubound. If this
' is True, the array is allocated. Otherwise,
' the array is not allocated.
'''''''''''''''''''''''''''''''''''''''
If LBound(Arr) <= UBound(Arr) Then
' no error. array has been allocated.
IsArrayAllocated = True
Else
IsArrayAllocated = False
End If
Else
' error. unallocated array
IsArrayAllocated = False
End If

End Function

The function supports both single-dimensional and multi-dimensional
arrays.

You would then call the function with code like the following:

Dim Arr() As Long
If IsArrayAllocated(Arr) = True Then
Debug.Print "Array has been Redim'd"
Else
Debug.Print "Array hasn't been Redim'd or was Erased."
End If



This and about 20 other array-related utility functions can be found at
http://www.cpearson.com/excel/VBAArrays.htm You can download code and
examples from
http://www.cpearson.com/Zips/modArraySupport.zip


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"Neal Zimm" wrote in message
...
If an array is originally dim'd as: TestAy() as whatever
I can't find a way, in a subsequent macro where TestAy() is an
argument,
to directly test whether or not it has been re-dim'd.

I have two macros, the first has a string array that is dim'd without
boundaries.

dim TestAy() as string

IF sub MacroOne puts values into it, I use:

redim TestAy(1 to Quantity) ' and then fill it with values.

Now, MacroTwo is called

call MacroTwo(TestAy())

sub MacroTwo(TestAy() as string)
' I've tried different ways, but keep getting subscript errors, when I
try
to
' test whether or not TestAy has values without also creating and
' passing another argument showing whether or not TestAy was
' redim'd in MacroOne. Some examples follow of stuff that didn't work
' when MacroOne did NOT redim TestAy.
' NOTE: I'm looking for a direct way, if it exists, to learn. I know
that
' I could redim TestAy(1) in MacroOne and then in MacroTwo test
' TestAy(1) to see what it contained.

if lbound(TestAy) 0 then ..... ' errors out if not dimmed

dim Number
number = vartype(TestAy) ' tells me it's an array but no boundaries

Dim holdARRAY
holdARRAY = Array(TestAy)
MsgBox LBound(holdARRAY(1)) 'errors out if TestAy not redim'd.

end sub

Thanks for your help.

--
Neal Z

Neal,
Here one way:

Private Sub CommandButton1_Click()
Dim Test() As Variant

Erase Test()

MsgBox "Array is initailised : " & CStr(IsArrayInitailised(Test()))

ReDim Test(1 To 3)

MsgBox "Array is initailised : " & CStr(IsArrayInitailised(Test()))

End Sub

Private Function IsArrayInitailised(argArray() As Variant) As Boolean
IsArrayInitailised = Not ((Not argArray) = -1)
End Function

But read the recent thread "Test for uninitialised array" in
"microsoft.public.vb.general.discussion" for why this is maybe not good.
And an alternative.

NickHK

"Neal Zimm" wrote in message
...
If an array is originally dim'd as: TestAy() as whatever
I can't find a way, in a subsequent macro where TestAy() is an argument,
to directly test whether or not it has been re-dim'd.

I have two macros, the first has a string array that is dim'd without
boundaries.

dim TestAy() as string

IF sub MacroOne puts values into it, I use:

redim TestAy(1 to Quantity) ' and then fill it with values.

Now, MacroTwo is called

call MacroTwo(TestAy())

sub MacroTwo(TestAy() as string)
' I've tried different ways, but keep getting subscript errors, when I
try
to
' test whether or not TestAy has values without also creating and
' passing another argument showing whether or not TestAy was
' redim'd in MacroOne. Some examples follow of stuff that didn't work
' when MacroOne did NOT redim TestAy.
' NOTE: I'm looking for a direct way, if it exists, to learn. I know
that
' I could redim TestAy(1) in MacroOne and then in MacroTwo test
' TestAy(1) to see what it contained.

if lbound(TestAy) 0 then ..... ' errors out if not dimmed

dim Number
number = vartype(TestAy) ' tells me it's an array but no boundaries

Dim holdARRAY
holdARRAY = Array(TestAy)
MsgBox LBound(holdARRAY(1)) 'errors out if TestAy not redim'd.

end sub

Thanks for your help.

--
Neal Z

Hi Chip. This is just an fun observation. I can't add much, but thought
you might find this interesting.
Three negative numbers are assigned to each array, and are used again in
each loop.
However, the order in which they are assigned is in reverse order in which
they are erased.
In other words, in the code below, A is erased last, so it will be the first
one assigned in the next loop. All 3 outputs are the same in each loop.
However, if you erase them in order A, B, then C, then C will be allocated
first in the next loop. The 3 group of numbers will cycle back and forth.
Again, I don't know why either, but just an observation. :)

Sub Demo()
Dim A() As Long
Dim B() As String
Dim C() As Double
Dim j As Long

For j = 1 To 4
ReDim A(1 To 3)
ReDim B(1 To 3)
ReDim C(1 To 3)

Debug.Print Not A
Debug.Print Not B
Debug.Print Not C
Debug.Print "= = = = = "

Erase C
Erase B
Erase A
Next j
Debug.Print "= = = = = = = = = ="
End Sub

--
Dana DeLouis


"Chip Pearson" wrote in message
...
Neal,

Actually, there is another way to determine whether an array is actually
allocated, without using On Error. I don't use it for two reasons: (1) I
don't know why it works, and (2) it is probably unsupported by Microsoft.
However, it does work, just don't ask me why. I would be very wary of
using it in production code.

Dim A() As Long
If Not Not A Then
Debug.Print "allocated"
Else
Debug.Print "unallocated"
End If
ReDim A(1 To 3)
If Not Not A Then
Debug.Print "allocated"
Else
Debug.Print "unallocated"
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

<snip