Test an array to see if it is populated without an error

[Rich J]

I have a routine that calculates an assigned number based on the date that is
entered. I have recently changed the code so that it now calculates the
number without me having to give a starting number for each year. This means
that the code loops starting from 2007 to calculate the beginning number for
each year after. I created an array to store as many years after 2007 as the
years increase. It is a one dimensional array.
My question: Is there a simple way to tell whether the array has been filled
or if it is empty on the first time through the routine without getting an
error?

Y would be the year of the date needed. I did not include all of the code.
I created the Iserror routine to try to determine this but I think there must
be a better way.
Once the array has been filled and the program running it will save a lot of
time not to have to recalculate each year again.

Function WDXFind(Y)
YY = 2007 ' starting year
WDX = 1 ' 1/8/2007 starting point for calculating assigned number
for 2007
N = 1 <- dimension
i = 0 <- index

WDXTF = False
On Error Resume Next
If IsError(UBound(WDXHold)) Then
WDXTF = True
End If

If WDXTF Or UBound(WDXHold) < Y - YY Then <- The resume Next lets the
program continue on the first run through. The UBound(WDXHold) gives
"Superscript of Range" error message.

Do Until YY = Y
..
..
ReDim Preserve WDXHold(N)
WDXHold(i) = WDX
N = N + 1 <- dimension starting at 1
i = i + 1 <- index starting at 0
YY = YY + 1 <- increases the year to calculate next
Loop
WDXFind = WDX
Else
WDXFind = WDXHold(Y - 2008)

End If
End Function

 

[Gary Keramidas]

not sure if this will help or not. if you run this with and without the
commented line you will see the difference. what you can do, is add a watch for
arr and step through the code and see how arr is changed as you step through the
code.
someone else may have some other ideas.

Option Base 1
Sub test()
Dim arr As Variant
' arr = Array("2007", "2008")
If IsEmpty(arr) Then
MsgBox "array empty"
Else
MsgBox UBound(arr) & " elements"
End If
End Sub

 

[Bob Phillips]

Gary,

Try this

Sub test()
Dim arr As Variant
arr = Array("2007", "2008")
Erase arr
If IsEmpty(arr) Then
MsgBox "array empty"
Else
MsgBox UBound(arr) & " elements"
End If
End Sub


--
---
HTH

Bob


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

 

[Gary Keramidas]

ok, i see.

--


Gary

Gary,

Try this

Sub test()
Dim arr As Variant
arr = Array("2007", "2008")
Erase arr
If IsEmpty(arr) Then
MsgBox "array empty"
Else
MsgBox UBound(arr) & " elements"
End If
End Sub


--
---
HTH

Bob


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

 

[Rich J]

Thanks guys. I experimented with the code and where the Dim arr is used and
then changed to an array in the next line, I'm not sure how to use that. My
Dim statement is Dim arr() as Integer. When the parentheses are there the
ISEmpty(arr) function does not work. When I try to use the ReDim statement
when Dim arr is specified I get an error msg.

What I ended up doing is putting a simple flag inside the loop where the
array is populated. FLAG = TRUE
I test FLAG ahead of the loop and code does not see UBound(arr) unless it
has already gone at least once thru the loop.

Sub test()
Dim arr As Variant
arr = Array("2007", "2008")

 

[Bob Phillips]

That is exactly the point I was making, if you use IsEmpty, it only works on
an array variable that has not been worked on. Loading it and emptying means
IsEmpty won't work, as I showed, Diming it as a particular type also doesn't
work.

This is how to do it properly

Sub TestArrays()
Dim ary1 As Variant
Dim ary2() As Long
Dim ary3 As Variant

ReDim ary3(1 To 2)
ary3(1) = "some value"

MsgBox IsItEmpty(ary1, "ary1")
MsgBox IsItEmpty(ary2, "ary2")
MsgBox IsItEmpty(ary3, "ary3")

'now do it properly
MsgBox IsArrayAllocated(ary1, "ary1")
MsgBox IsArrayAllocated(ary2, "ary2")
MsgBox IsArrayAllocated(ary3, "ary3")

End Sub

Function IsItEmpty(inArray As Variant, arrayName As String) As String
If IsEmpty(inArray) Then
IsItEmpty = "IsEmpty thinks " & arrayName & " is empty"
Else
IsItEmpty = "IsEmpty thinks " & arrayName & " is NOT empty"
End If
End Function


Function IsArrayAllocated(inArray As Variant, arrayName As String) As String
Dim fAllocated As Boolean
On Error Resume Next
fAllocated = Not (IsError(LBound(inArray))) And _
IsArray(inArray) And _
(LBound(inArray) <= UBound(inArray))
If fAllocated Then
IsArrayAllocated = "IsArrayAllocated thinks " & arrayName & " is
allocated"
Else
IsArrayAllocated = "IsArrayAllocated thinks " & arrayName & " is NOT
allocated"
End If
End Function



--
---
HTH

Bob


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

 

[Rich J]

I revisited my problem and the solution was very simple.

At the top I had
Dim Arr()
which gives a variable size array

When testing if it had been filled I used
If IsEmpty(Arr) Then and this gave an error

It should have been

If IsEmpty(Arr()) Then

 

[Chip Pearson]

If IsEmpty(Arr()) Then

That won't return an accurate result in all cases. I use

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

to handle any sort of array.


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

 

[Rich J]

Thanks Chip, yeah I found out after I ran the routine several times that it
did not work every time.