LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 38
Default Test an array to see if it is populated without an error

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
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
First populated cell in row array/ Last populated cell in row arra Skyscan Excel Worksheet Functions 7 May 29th 08 05:20 PM
Add dimesion to Array already populated ExcelMonkey Excel Programming 7 January 20th 08 08:11 PM
Test Initialization of an Array Neal Zimm Excel Programming 7 December 27th 06 04:58 PM
Test for dups in Array Perico[_2_] Excel Programming 5 July 14th 05 09:51 AM
Array Test VBA Dabbler[_2_] Excel Programming 9 March 30th 05 09:53 PM


All times are GMT +1. The time now is 08:55 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"