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

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, "FortisC...@gmail.com" <FortisC...@gmail.com>
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

On Mar 14, 6:02 pm, JP <jp2...@earthlink.net> 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, "FortisC...@gmail.com" <FortisC...@gmail.com>
> 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

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)




<(E-Mail Removed)> wrote in message
news:70359399-c6de-4685-848c-(E-Mail Removed)...
> 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

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)
>
>
>
>
> <(E-Mail Removed)> wrote in message
> news:70359399-c6de-4685-848c-(E-Mail Removed)...
> > 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
>