Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test an array to see if it is populated without an error
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 -- Gary "Rich J" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test an array to see if it is populated without an error
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" <GKeramidasATmsn.com wrote in message ... 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 -- Gary "Rich J" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test an array to see if it is populated without an error
ok, i see.
-- Gary "Bob Phillips" wrote in message ... 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" <GKeramidasATmsn.com wrote in message ... 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 -- Gary "Rich J" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test an array to see if it is populated without an error
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") "Gary Keramidas" wrote: ok, i see. Gary "Bob Phillips" wrote in message ... 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" <GKeramidasATmsn.com wrote in message ... 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 -- Gary "Rich J" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test an array to see if it is populated without an error
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" wrote in message ... 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") "Gary Keramidas" wrote: ok, i see. Gary "Bob Phillips" wrote in message ... 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" <GKeramidasATmsn.com wrote in message ... 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 -- Gary "Rich J" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test an array to see if it is populated without an error
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test an array to see if it is populated without an error
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test an array to see if it is populated without an error
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
First populated cell in row array/ Last populated cell in row arra | Excel Worksheet Functions | |||
Add dimesion to Array already populated | Excel Programming | |||
Test Initialization of an Array | Excel Programming | |||
Test for dups in Array | Excel Programming | |||
Array Test | Excel Programming |