Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is the array empty?
Hi
I am using a macro to load an array with data from excel. This is time series data. As I loop through all the years and months, there are some months that don't exist in a specific year (eg for 2005, the data starts from April, whereas my array loops over all months). So when I look up this array in the local variables, each element of the array shows up as EMPTY. this causes a problem cos at the end of all this, I'm trying to do : worksheetfunction.average(array) and it fails when I the array is empty. Is there anyway to determine if the array is empty, cos then I can use an if statement to circumvent. I've tried using lbound, ubound, isempty etc etc...nothing seems to be working. Suggestions are deeply appreciated. Thanks Chet |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is the array empty?
I found this on another board, does it help?
If IsError(Application.Match("*", (myArray), 0)) Then MsgBox "Empty array" Else MsgBox "Something, somewhere" End If --JP On Mar 14, 6:58*pm, " wrote: Hi I am using a macro to load an array with data from excel. This is time series data. As I loop through all the years and months, there are some months that don't exist in a specific year (eg for 2005, the data starts from April, whereas my array loops over all months). So when I look up this array in the local variables, each element of the array shows up as EMPTY. this causes a problem cos at the end of all this, I'm trying to do : worksheetfunction.average(array) and it fails when I the array is empty. Is there anyway to determine if the array is empty, cos then I can use an if statement to circumvent. I've tried using lbound, ubound, isempty etc etc...nothing seems to be working. Suggestions are deeply appreciated. Thanks Chet |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is the array empty?
On Mar 14, 6:02 pm, JP wrote:
I found this on another board, does it help? If IsError(Application.Match("*", (myArray), 0)) Then MsgBox "Empty array" Else MsgBox "Something, somewhere" End If --JP On Mar 14, 6:58 pm, " wrote: Hi I am using a macro to load an array with data from excel. This is time series data. As I loop through all the years and months, there are some months that don't exist in a specific year (eg for 2005, the data starts from April, whereas my array loops over all months). So when I look up this array in the local variables, each element of the array shows up as EMPTY. this causes a problem cos at the end of all this, I'm trying to do : worksheetfunction.average(array) and it fails when I the array is empty. Is there anyway to determine if the array is empty, cos then I can use an if statement to circumvent. I've tried using lbound, ubound, isempty etc etc...nothing seems to be working. Suggestions are deeply appreciated. Thanks Chet WOW! Yeah it seems to be working, i'll run a few loops to test it. I guess the " * " is the generic for any non empty element right? so it tests if the array has ANYTHING non empty and then gives the corresponding msg. Thanks a ton for this one. Truly Chet |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is the array empty?
I use the following function in my standard code library. You pass in a
variable and it returns True if that variable is an allocated array. It returns False if the variable is not an array, is an unallocated or Erase'd array, or is an array whose LBound UBound. Function IsArrayAllocated(V As Variant) As Boolean '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''' ' IsArrayAllocated ' Returns True if V is a static array or an allocated ' dynamic array. Returns False if V is: ' - not an array, or ' - an Erase'd or unallocated dynamic array, or ' - an array whose LBound is UBound (e.g., failure ' of Split). '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''' On Error Resume Next IsArrayAllocated = IsArray(V) And _ Not IsError(LBound(V)) And _ (LBound(V) <= UBound(V)) End Function You can use it in code like Dim V As Variant ' or V() As whatever ' do something with V If IsArrayAllocated(V) = True Then Debug.Print "V is an allocated array" Else Debug.Print "V is not an allocated array" End If -- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) wrote in message ... Hi I am using a macro to load an array with data from excel. This is time series data. As I loop through all the years and months, there are some months that don't exist in a specific year (eg for 2005, the data starts from April, whereas my array loops over all months). So when I look up this array in the local variables, each element of the array shows up as EMPTY. this causes a problem cos at the end of all this, I'm trying to do : worksheetfunction.average(array) and it fails when I the array is empty. Is there anyway to determine if the array is empty, cos then I can use an if statement to circumvent. I've tried using lbound, ubound, isempty etc etc...nothing seems to be working. Suggestions are deeply appreciated. Thanks Chet |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is the array empty?
Excellent function, Chip. Thanks.
Matthew Pfluger "Chip Pearson" wrote: I use the following function in my standard code library. You pass in a variable and it returns True if that variable is an allocated array. It returns False if the variable is not an array, is an unallocated or Erase'd array, or is an array whose LBound UBound. Function IsArrayAllocated(V As Variant) As Boolean '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''' ' IsArrayAllocated ' Returns True if V is a static array or an allocated ' dynamic array. Returns False if V is: ' - not an array, or ' - an Erase'd or unallocated dynamic array, or ' - an array whose LBound is UBound (e.g., failure ' of Split). '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''' On Error Resume Next IsArrayAllocated = IsArray(V) And _ Not IsError(LBound(V)) And _ (LBound(V) <= UBound(V)) End Function You can use it in code like Dim V As Variant ' or V() As whatever ' do something with V If IsArrayAllocated(V) = True Then Debug.Print "V is an allocated array" Else Debug.Print "V is not an allocated array" End If -- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) wrote in message ... Hi I am using a macro to load an array with data from excel. This is time series data. As I loop through all the years and months, there are some months that don't exist in a specific year (eg for 2005, the data starts from April, whereas my array loops over all months). So when I look up this array in the local variables, each element of the array shows up as EMPTY. this causes a problem cos at the end of all this, I'm trying to do : worksheetfunction.average(array) and it fails when I the array is empty. Is there anyway to determine if the array is empty, cos then I can use an if statement to circumvent. I've tried using lbound, ubound, isempty etc etc...nothing seems to be working. Suggestions are deeply appreciated. Thanks Chet |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
TESTING FOR EMPTY ARRAY | Excel Programming | |||
Set array element to empty | Excel Programming | |||
Empty Array | Excel Programming | |||
Array Empty After Sub Called | Excel Programming | |||
Is the array empty? | Excel Programming |