How to check whether a variant array is unallocated?

vba variant
jason m picture jason m · Mar 30, 2011 · Viewed 32.7k times · Source 
   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.

Answer

RandomCoder picture RandomCoder · Aug 25, 2017

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

Related questions

Excel VBA: Variants in Array Variables

A question on variants. Im aware that variants in Excel vba are both the default data type and also inefficient (from the viewpoint of overuse in large apps). However, I regularly use them for storing data in arrays that have …

Read More
How to return the number of dimensions of a (Variant) variable passed to it in VBA

Does anyone know how to return the number of dimensions of a (Variant) variable passed to it in VBA?

Read More
Convert Variant Array to String

I am trying to take a variant variable and convert it into a string so that I can run a split function on the data. However, whenever I try to redefine the variant I get a type mismatch error. I …

Read More