Is the array empty?

[FortisChet]

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

 

[JP]

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

 

[FortisChet]

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

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

 

[Chip Pearson]

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)

 

[Matthew Pfluger]

Excellent function, Chip. Thanks.

Matthew Pfluger