About Us

Matthew Pfluger

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
TESTING FOR EMPTY ARRAY	JingleRock	Excel Programming	4	August 28th 07 12:50 AM
Set array element to empty	Raul	Excel Programming	2	March 2nd 05 03:53 PM
Empty Array	ExcelMonkey[_190_]	Excel Programming	4	March 1st 05 04:10 PM
Array Empty After Sub Called	ExcelMonkey[_190_]	Excel Programming	2	March 1st 05 09:21 AM
Is the array empty?	Otto Moehrbach[_6_]	Excel Programming	4	June 13th 04 03:35 AM

Menu

About Us