How to check if a variant array is unallocated?

Question

Dim Result() As Variant 

In the viewport, it looks like

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

How to check the following:

  if Result is nothing then 

or

  if Result is Not Set then 

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

Answer 1

Chip Pearson created a useful module called modArraySupport , which contains a set of functions for checking such things. In your case, you would like to use IsArrayAllocated .

 Public Function IsArrayAllocated(Arr As Variant) As Boolean 

This function returns TRUE or FALSE, indicating whether the specified array is selected (not empty). Returns a TRUE array - this is a static array or dynamic object that has been allocated using the Redim operator. Returns FALSE if the array is a dynamic array that has not yet been measured with ReDim or has been freed using the Erase statement. This function is basically the opposite of ArrayIsEmpty. For instance,

 Dim Result() As Variant Dim R As Boolean R = IsArrayAllocated(Result) ' returns false ReDim V(1 To 10) R = IsArrayAllocated(Result) ' returns true 

The technique is mainly used to check the boundaries of the array (as suggested by @Tim Williams), BUT with an additional trick.

To check in your nearest window:

 ?IsArrayAllocated(Result) 

Testing in the Watch window: there may be ways to do this; for example, add a clock to R and in the "Clock Type" section, select "Break when the value changes."

Answer 2

To avoid error handling, I used this seen on the forum a long time ago and have been successfully using since:

 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 expand the size of an array from an unset array in 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 

Answer 3

In the nearest window you can use the following:

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

The first is just for completeness. Since Result is not an object, Result Is Nothing will throw an error. Empty for options that have not been initialized , including arrays that have not been defined. .

(Update) When performing an additional check, I found that IsEmpty will never return true in the 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 a function is passed to or for sub values. While you cannot declare Optional Foo() As Variant , you can have something like ParamArray Foo() As Variant , in which case if nothing is passed, IsMissing will return true.

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

 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 laid out by Jean-Francois Corbett) will return false if the array is specified and then deleted.

Answer 4

Check out the LBound array. If you receive an error message, it is not initialized.

Answer 5

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

If you need to check the distribution of the array, this is most likely due to the fact that you are trying to create your own "vector" type: an array that grows at run time to accommodate data as it is added. VBA simplifies the implementation of a vector type if you use a 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 

Note that there is no need to check the distribution of the array in this code, because we can just check the VectorCount variable. If it is 0, we know that nothing has been added to the vector, and therefore the array is unallocated.

This code is not only simple and understandable, but also has all the advantages of array performance, and the amortized cost of adding elements is actually O (1), which is very effective. The only compromise is that due to the fact that the storage doubles every time the vector runs out of space, in the worst case, 50% of the vector storage is lost.

Answer 6

I think line 4 of the accepted answer should say:

 ReDim Result(1 To 10) 

Instead:

 ReDim V(1 To 10) 

I believe in line 5:

 R = IsArrayAllocated(Result) ' returns true 

will return FALSE, as in line 3:

 R = IsArrayAllocated(Result) ' returns false 

because Result is not what is revised on line 4.

In line 4, it tries to change the dimension of the array V, but it is not even declared.