Excel VBA: Check Whether a Dynamic Array Is Allocated

No—VBA arrays are not objects, so If myArray Is Nothing is not a valid way to test one. To check a dynamic array, try reading its bounds under error handling; use Is Nothing for object variables, not arrays.

Why an array cannot be Nothing

In VBA, Nothing means an object variable currently refers to no object. It is used with object types such as Range, Workbook, or Collection. An array is a different kind of value: it is a sequence of elements, not an object reference. Consequently, the object comparison operator Is cannot test whether an array exists.

For example, this is appropriate for a worksheet range:

If targetRange Is Nothing Then
    ' No Range object has been assigned.
End If

But writing If numbers Is Nothing Then for an array is a compile-time error. The useful question is usually whether a dynamic array has been allocated with ReDim, whether a Variant currently contains an array, or whether the array has elements in a particular range. Those are separate checks and call for different tests.

Check whether a dynamic Variant array is allocated

A dynamic array declared with empty parentheses has no bounds until it is dimensioned. For an allocated array, LBound returns its lower index. For an unallocated dynamic array, asking for its bounds raises an error, so a small error-handling function can turn that condition into a Boolean result. This version is intended for a Variant that contains an array:

Private Function VariantArrayIsAllocated(ByRef value As Variant) As Boolean
    Dim lowerBound As Long

    If Not IsArray(value) Then Exit Function

    On Error GoTo NotAllocated
    lowerBound = LBound(value, 1)
    VariantArrayIsAllocated = True
    Exit Function

NotAllocated:
    VariantArrayIsAllocated = False
End Function

IsArray first rules out values such as Empty or a number. The call to LBound(value, 1) then checks whether the first dimension has usable bounds. The local variable receives the bound so the function performs an actual bounds query; its numeric value is not otherwise needed. If the array is unallocated, the error handler returns False. If it is allocated, the function returns True.

For instance, if items is declared as a Variant and later assigned an array, call the helper like this:

Sub CheckVariantArrayExample()
    Dim items As Variant

    items = Array("red", "green", "blue")

    If VariantArrayIsAllocated(items) Then
        Debug.Print "Allocated; lower bound: "; LBound(items)
    Else
        Debug.Print "Not an allocated array"
    End If
End Sub

The example prints that the array is allocated and reports a lower bound of 0, because the VBA Array function normally creates a zero-based array. An array built with ReDim items(1 To 3) instead has a lower bound of 1.

Checking a typed dynamic array

A typed dynamic array, such as Long(), is not the same declaration as a Variant containing an array. A helper specifically declared for the array’s element type avoids relying on a Variant parameter to accept that typed array. Here is a version for a dynamic array of Long values:

Private Function LongArrayIsAllocated(ByRef values() As Long) As Boolean
    Dim lowerBound As Long

    On Error GoTo NotAllocated
    lowerBound = LBound(values, 1)
    LongArrayIsAllocated = True
    Exit Function

NotAllocated:
    LongArrayIsAllocated = False
End Function

Use it with a matching declaration, such as Dim values() As Long. It returns False before the array has been dimensioned and after a dynamic array has been erased; it returns True after a successful ReDim. If the element type is String, Double, or another type, define a corresponding helper with that element type. VBA does not provide one universal array-allocation test that can be safely substituted for every possible declaration and element type.

For a quick one-off check, you can also put the bounds probe directly in the procedure that owns the array. Use a local error handler and keep the error-handling scope small. A reusable function is easier to read when the check appears in several places, while an inline check can be convenient for a single test.

IsArray, IsEmpty, and bounds test different things

Several VBA checks sound as if they answer the same question, but they describe different states:

IsArray(value) asks whether a Variant currently contains an array, or whether an expression is an array. It does not establish that a dynamic array has been dimensioned.
IsEmpty(value) checks whether a Variant has the special Empty value. It is useful for an unassigned Variant, but it is not a general test for an unallocated typed array.
LBound and UBound retrieve array bounds. For a dynamic array with no allocation, the bounds query raises an error, which is why the allocation helper uses error handling.
Is Nothing checks whether an object variable refers to an object. It does not apply to an array variable.

For example, a Variant declared with Dim data As Variant begins as Empty, so IsEmpty(data) is initially True. After assigning data = Array(10, 20), it contains an array and IsArray(data) is True. These checks describe the Variant’s current contents; a bounds check is the more direct test when the question is whether an array has valid dimensions.

What ReDim and Erase change

ReDim gives a dynamic array bounds and storage. For example, ReDim scores(1 To 5) allocates five Long elements if scores was declared as Long(). A later ReDim scores(1 To 10) changes its size and, unless Preserve is used, clears the existing elements. ReDim Preserve retains existing values where the rules for that array’s dimensions permit it.

Erase behaves differently depending on the declaration. Erasing a dynamic array releases its allocated array storage, so a subsequent bounds check fails until it is dimensioned again. Erasing a fixed-size array does not remove its declared bounds: it resets the elements to their default values. For example, numeric elements become zero, strings become empty strings, and object elements become Nothing. That last detail is about each object element, not the array variable itself.

This distinction helps explain why checking whether an element is empty is not a reliable substitute for checking allocation. A legitimately allocated numeric array may contain only zeros, and an allocated string array may contain only empty strings. Allocation concerns the array’s bounds, not whether its elements happen to hold meaningful data.

Multidimensional arrays and bounds

For a multidimensional array, LBound(value, 1) checks the first dimension. If that succeeds, the array has been dimensioned; you can query other dimensions separately when you need their bounds. For example, an array declared with two dimensions can be inspected with LBound(matrix, 1), UBound(matrix, 1), LBound(matrix, 2), and UBound(matrix, 2).

Do not assume every array starts at zero or one. The lower bound can depend on how the array was created and on the module’s Option Base setting. An explicit declaration such as ReDim values(1 To 4) makes the intended bounds clear. Code that loops through an array should generally use its actual bounds rather than hard-coding an assumed starting index:

Dim i As Long

For i = LBound(values) To UBound(values)
    Debug.Print values(i)
Next i

That loop works for either a zero-based or one-based, one-dimensional array, provided it has been allocated. For an unallocated dynamic array, check allocation first or handle the bounds error before entering the loop.

Object arrays are a special case

An array can hold object references, but the array container is still not itself a single object reference. You cannot use objectArray Is Nothing to check whether the array has been allocated. You can check the array’s bounds using an appropriate typed-array helper, and you can check individual elements with Is Nothing after confirming that the array is allocated and that the index is in range.

For example, an allocated array of Range references may have some elements that have not been assigned. An element-level test looks like If ranges(i) Is Nothing Then. It answers whether that particular element refers to a Range object, not whether the array exists. Keep these checks separate to avoid trying to index an unallocated array or an invalid index.

Common mistakes and a safe workflow

The most common mistake is treating a dynamic array like a Range variable and testing it with Is Nothing. Another is assuming IsArray alone proves that bounds are available, or using IsEmpty to test a typed array. A third is checking whether the first element is blank: the array may be allocated with a blank first element, or its lower bound may not be zero.

A reliable sequence is:

Identify the declaration: object variable, Variant, fixed array, or dynamic typed array.
For an object variable, use If objectVariable Is Nothing Then.
For a Variant, use IsArray to identify array contents, then probe a bound under error handling if allocation is the question.
For a typed dynamic array, use a bounds probe in the owning procedure or a helper with a matching array type.
Only access elements after confirming allocation and using valid bounds.

When changing a workbook macro to add these checks, save a copy of the workbook first. Running a VBA macro can change or delete data, and macro actions cannot be undone with Ctrl+Z. On Windows, open the VBA editor with Alt+F11; on Mac, the editor shortcut is Option+F11 on many keyboards, though the exact key combination can depend on the keyboard and function-key settings. The VBA language behavior described here applies to desktop Excel VBA; Excel for the web does not run VBA macros.

Frequently Asked Questions

Can I use IsArray to check whether a dynamic array has been allocated?
IsArray identifies array values, but allocation is best checked by attempting LBound under error handling. Use both when a Variant might contain a non-array value.

What error does LBound raise for an unallocated dynamic array?
A bounds query on an unallocated dynamic array raises a runtime error, commonly error 9, “Subscript out of range.” A narrowly scoped error handler lets your code interpret that condition as “not allocated.”

Does Erase make every array unallocated?
No. Erase deallocates a dynamic array, but a fixed-size array retains its declared bounds and has its elements reset to default values.

How do I check whether a Range variable is Nothing?
For a Range object variable named targetRange, use If targetRange Is Nothing Then. This tests the object reference and is different from testing an array of Range references.

Can a dynamic array have zero elements but still be allocated?
Ordinary VBA array bounds describe one or more indexed elements; attempting to create a zero-length array with a reversed range such as 0 To -1 raises an error. Do not use a zero-length-array assumption as an allocation test; inspect the bounds and handle the unallocated case.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.