How to check whether a variant array is unallocated?

Question

   Dim Result() As Variant

In my watch window, this appears as

Expression | Value | Type
Result     |       | Variant/Variant()

How do I check the following:

   if Result is nothing then

or

   if Result is Not Set then

This is basically what I am trying to accomplish, but the first one does not work and the second does not exist.

Installment

Answer 1

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 the array is a static array or a dynamic that has been allocated with a Redim statement. Returns FALSE if the array is a dynamic array that has not yet been sized with ReDim or that has been deallocated with the Erase statement. This function is basically the opposite of ArrayIsEmpty. For example,

Dim V() As Variant
Dim R As Boolean
R = IsArrayAllocated(V)  ' returns false
ReDim 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".

Taboo

Answer 2

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 array
Dim arrIndex() As Variant        

'Extend array
If (Not Not Result) = 0 Then
    ReDim Preserve Result(0 To 0)
Else
    ReDim Preserve Result(0 To UBound(Result) + 1)
End If

Melanosis

Thank you so much! This one is really awesome, but how does the "Not Not" prevent the type mismatch you'd get without them? – Flexed

Is this the forumpost you are referring to? – Autochthonous

this is great! but why does Not Not Result <> 0 result in a type mismatch? shouldn't it be the same? – Ingleside

Much simpler than the 20 line function in the accepted answer. – Sedlik

and the forum post says - "For anyone interested Not Arrayname reveals (or not) the inverse of a pointer to the SafeArray structure which VB uses to define arrays. Not Not Arrayname returns the actual pointer or 0 if the array is uninitialised." – Trimer

Answer 3

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 = 0
End 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.

Crate

Result is an array. The return value for IsNull(), IsEmpty() and IsMissing() right after dimension it is FALSE. And Result Is Nothing throws an error. I guess your answer would be fine if Result was not an array. – Mopes

@OP2506 - Because Result is not an object, Is Nothing will throw an error. However, IsEmpty will return true until Redim is called on the array. IsMissing will return true if nothing is passed to a parameter declared as Optional Foo As Variant which can include an array. – Crate

@Jean-François Corbett - Actually, I did. However, I notice a difference in behavior. If you declare Result() at the module level and then in the immediate window run IsEmpty( Result ) it returns true. However, if you put it in a routine and execute Debug.Print, it returns false. – Crate

@OP2506 is right. When someone challenges you like that, it never hurts to do some testing before handwaving the challenge away! Here's a copy-paste from my Immediate window: ?IsNull( Result ) False ?IsEmpty( Result ) False ?IsMissing( Result ) False – Barramunda

Answer 4

Check the LBound of the array. If you get an error then it's uninitialized.

Example which uses inline error checking:

Dim u As Long
Dim e As Long
On Error Resume Next
u = UBound(arr)
e = Err.Number
On Error GoTo 0
If e = 9 Then
    'Error 9 = subscript out of bounds.
    'The array is empty.
    Stop
Else
    'The array is not empty.
    Stop 
End If

Gitt

Not helpful unless you post the VBA code of how to check for an error – Mule

Neither LBound() nor UBound() work when the array is uninitialized, and both result in Error 9: Subscript out of range. – Leekgreen

That’s what I meant by “if you get an error” – Gitt

Answer 5

I recommend a slightly different approach because I think using language artifacts like (Not Array) = -1 to check for initialization is difficult to read and causes maintenance headaches.

If you are needing to check for array allocation, most likely it's because you're trying to make your own "vector" type: an array that grows during runtime to accommodate data as it is being added. VBA makes it fairly easy to implement a vector type, if you take advantage of the type system.

Type Vector
    VectorData() As Variant
    VectorCount As Long
End Type

Dim MyData As Vector

Sub AddData(NewData As Variant)
    With MyData
        ' If .VectorData hasn't been allocated yet, allocate it with an
        ' initial size of 16 elements.
        If .VectorCount = 0 Then ReDim .VectorData(1 To 16)

        .VectorCount = .VectorCount + 1

        ' If there is not enough storage for the new element, double the
        ' storage of the vector.
        If .VectorCount > UBound(.VectorData) Then
            ReDim Preserve .VectorData(1 To UBound(.VectorData) * 2)
        End If

        .VectorData(.VectorCount) = NewData
    End With
End Sub

' Example of looping through the vector:
For I = 1 To MyData.VectorCount
    ' Process MyData.VectorData(I)
Next

Notice how there's no need to check for array allocation in this code, because we can just check the VectorCount variable. If it's 0, we know that nothing has been added to the vector yet and therefore the array is unallocated.

Not only is this code simple and straightforward, vectors also have all the performance advantages of an array, and the amortized cost for adding elements is actually O(1), which is very efficient. The only tradeoff is that, due to how the storage is doubled every time the vector runs out of space, in the worst case 50% of the vector's storage is wasted.

Lucretialucretius

Answer 6

So I found RandomCoders approach pretty good. But keep in mind that it only works when you declare the variant as an array:

Sub test()
Dim v() As Variant

    Debug.Print (Not Not v) = 0 'returns true

    v = [A1:B5].Value

    Debug.Print (Not Not v) = 0 'returns false

End Sub

But you can also check variants this way:

Sub test2()
Dim v As Variant

    Debug.Print IsEmpty(v) 'returns true

    v = [A1:B5].Value

    Debug.Print IsEmpty(v) 'returns false

End Sub

Streptomycin

Note that the code in this answer can run only in Excel (v = [A1:B5].Value), but the question was not specific to Excel. – Vulcanize