How to check whether a variant array is unallocated?

To avoid error handling, I used this, seen on a forum long time ago and used sucessfully since then:

If (Not Not Result) <> 0 Then 'Means it is allocated

or alternatively

If (Not Not Result) = 0 Then 'Means it is not allocated

I used this mainly to extend array size from unset array this way

'Declare arrayDim arrIndex() As Variant        'Extend arrayIf (Not Not Result) = 0 Then    ReDim Preserve Result(0 To 0)Else    ReDim Preserve Result(0 To UBound(Result) + 1)End If

You can use the following in the immediate window:

?Result Is Nothing?IsNull( Result )?IsEmpty( Result )?IsMissing( Result )

The first is simply for completeness. Since Result is not an object, Result Is Nothing will throw an error. Empty is for variants that have not been initialized including arrays which have not been dimensioned..

(Update) In doing some additional checking, I have discovered that IsEmpty will never return true on a declared array (whether Redim'd or not) with only one exception. The only exception I found is when the array is declared at the module level and not as Public and then only when you check it in the immediate window.

Missing if for optional values passed to a function or sub. While you cannot declare Optional Foo() As Variant, you could have something like ParamArray Foo() As Variant in which case if nothing is passed, IsMissing would return true.

Thus, the only way to determine if the array is initialized is to write a procedure that would check:

Public Function IsDimensioned(vValue As Variant) As Boolean    On Error Resume Next    If Not IsArray(vValue) Then Exit Function    Dim i As Integer    i = UBound(Bar)    IsDimensioned = Err.Number = 0End Function

Btw, it should be noted that this routine (or the library posted by Jean-François Corbett) will return false if the array is dimensioned and then erased.

Chip Pearson made a useful module called modArraySupport that contains a bunch of functions to test for things like this. In your case, you would want to use IsArrayAllocated.

Public Function IsArrayAllocated(Arr As Variant) As Boolean

This function returns TRUE or FALSE indicating whether the specified array is allocated (not empty). Returns TRUE of thearray is a static array or a dynamic that has been allocated with a Redim statement. Returns FALSE if the array is a dynamic array thathas not yet been sized with ReDim or that has been deallocated with the Erase statement. This function is basically the opposite ofArrayIsEmpty. For example,

Dim V() As VariantDim R As BooleanR = IsArrayAllocated(V)  ' returns falseReDim V(1 To 10)R = IsArrayAllocated(V)  ' returns true

The technique used is basically to test the array bounds (as suggested by @Tim Williams) BUT with an extra gotcha.

To test in your immediate window:

?IsArrayAllocated(Result)

Testing in Watch window: there are may ways to do this; for example, add a watch on R and under "Watch Type" select "Break When Value Changes".