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

"Chip Pearson" wrote:

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" wrote in message
...
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

"Rich J" wrote:

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