In Excel VBA, IsEmpty does not reliably tell you whether an array has been allocated. For a dynamic array, test whether it is an array and whether LBound can read its bounds; use IsEmpty for a Variant that may contain the special value Empty.
What “empty array” means in VBA
People use “empty array” to describe several different states, and VBA treats them differently. A dynamic array declared with empty parentheses has not yet been given bounds. An allocated array has bounds, even if its elements contain no useful values. A Variant can also contain the special value Empty, which is not the same thing as an array with no elements.
For example, Dim values() As Variant declares a dynamic array but does not allocate its storage. Calling ReDim values(0 To 2) allocates three elements. Those elements may initially contain Empty values, but the array itself is allocated. This distinction matters because VBA will often raise run-time error 9, “Subscript out of range,” if code asks for the bounds of an unallocated dynamic array.
VBA does not provide one universal “is this array empty?” test that answers every possible meaning. First decide whether you need to know if a variable is an array, if it has been allocated, or if its elements contain blank or Empty values. Each question needs a different check.
Use a safe allocation check
A practical check is to use IsArray to establish that the value is an array, then attempt LBound under error handling. If the lower bound can be read, the array is allocated. This helper returns False for a non-array or an unallocated dynamic array:
Public Function IsArrayAllocated(ByRef arr As Variant) As Boolean
Dim lowerBound As Long
' Reject values that are not arrays.
If Not IsArray(arr) Then Exit Function
' An unallocated dynamic array raises an error at LBound.
On Error Resume Next
lowerBound = LBound(arr)
IsArrayAllocated = (Err.Number = 0)
Err.Clear
On Error GoTo 0
End Function
Call the function with an array variable or a Variant that contains an array. For example, with Dim values() As Variant, a call to IsArrayAllocated(values) returns False before a ReDim and True after one. The function checks whether the first dimension has a readable lower bound; it does not decide whether the array contains meaningful data.
The helper uses On Error Resume Next only around the bound check, clears the handled error, and then restores normal error handling. Keep that scope narrow. Leaving error suppression active across unrelated code can hide bugs that should be reported.
Why IsEmpty is not an array test
IsEmpty is designed to test whether a Variant is in the Empty state. It can be useful for a Variant variable that has not been assigned a value, or for a worksheet cell value returned as Empty. It is not a dependable way to detect whether a dynamic array has been allocated. An array can be allocated while its elements are Empty, and a Variant containing an array is not itself an Empty Variant.
There is an additional type distinction: Dim items() As String declares a typed array, not a Variant. IsEmpty is intended for Variants, so using it as a general-purpose test on differently typed arrays is the wrong approach. Use an allocation check for array storage and inspect individual elements separately when their contents matter.
Likewise, IsArray alone answers only whether the expression is an array. It does not promise that a dynamic array has been allocated or that it has any usable elements. Treating IsArray as an allocation test can lead directly to an error when later code calls LBound or UBound.
Allocate, erase, and resize dynamic arrays
A dynamic array gets bounds when code uses ReDim. For instance, ReDim values(1 To 3) creates three elements indexed 1, 2, and 3. The chosen lower bound depends on the bounds given in ReDim and on the module’s array settings; do not assume every array starts at zero. LBound and UBound report the actual bounds.
Erase behaves differently for dynamic and fixed-size arrays. For a dynamic array, it releases the allocated storage, so a subsequent bounds check will fail until the array is allocated again. For a fixed-size array, Erase resets its elements to their default values but does not remove its declared bounds. This is another reason to ask whether you mean “unallocated” or “contains only blank-looking values.”
ReDim Preserve can retain existing values while changing the size of a dynamic array, subject to VBA’s rules: it can change only the upper bound of the last dimension. A normal ReDim does not preserve existing contents. Neither operation creates a general-purpose zero-element array by using a negative upper bound; such bounds are not a safe way to represent “nothing.”
Handle bounds without causing another error
Do not call UBound on a dynamic array before checking that it is allocated. A tempting condition such as “if the array is not allocated or its upper bound is below its lower bound” is unsafe if it evaluates a bound for an unallocated array. VBA’s logical And and Or operators do not short-circuit: both sides are evaluated. Put checks in separate If statements instead.
Once allocation is confirmed, the bounds can be examined safely. This example prints the range of indexes for an allocated array. Save a copy of the workbook before running a macro; macros cannot be undone with Ctrl+Z.
Sub ReportArrayBounds()
Dim values() As Variant
Dim firstIndex As Long
Dim lastIndex As Long
' Allocate three elements for this example.
ReDim values(0 To 2)
If IsArrayAllocated(values) Then
firstIndex = LBound(values)
lastIndex = UBound(values)
Debug.Print firstIndex, lastIndex
Else
Debug.Print "Array is not allocated"
End If
End Sub
With the bounds shown, the Immediate window prints 0 and 2. The procedure deliberately allocates the array before asking for its bounds. In real code, remove or replace the example ReDim and make the allocation check around the array whose state you actually need to examine. The helper function must be available in the project for this example to compile.
Arrays returned from worksheet ranges
Excel range values introduce a common source of confusion. With multiple cells, an assignment such as data = Range("A1:A3").Value normally places a two-dimensional Variant array in data, with dimensions representing rows and columns. Even though the worksheet range is one column wide, the returned array is still two-dimensional. Use LBound(data, 1) and UBound(data, 1) for rows, and dimension 2 for columns.
A range consisting of one cell is different: its Value is returned as a scalar Variant rather than a two-dimensional array. Code that assumes every range read produces an array can therefore fail when the selected range shrinks to one cell. Check IsArray before treating a range result as an array, and handle the scalar case separately.
Blank cells inside a multi-cell range do not mean the returned array is unallocated. They are values at particular array positions. Depending on how the data is read and represented, a blank cell value may be Empty; test the individual element if the goal is to skip blank worksheet cells. If the goal is to test whether the range result is an array, use IsArray, then check bounds only after confirming it is an array.
Choosing a suitable representation for no items
If a procedure may have zero results, it is often clearer to track the item count or use a collection than to rely on a special empty-array state. A Collection can exist with a Count of zero, so the caller can test the count directly. For an array, keep a separate count or return a clearly documented status alongside the array. This makes “no matching records” explicit rather than depending on bounds behavior.
Use a dynamic array when indexed access or efficient bulk processing is useful. Use a collection when the number of items changes and a simple count is important. If a function returns a Variant array, document what it returns when there are no matches and make the caller handle both that case and any scalar or error result the function may produce. Consistency at the function boundary prevents many downstream checks from becoming ambiguous.
- Use
IsArrayto identify an array value. - Use a guarded
LBoundcall to determine whether a dynamic array has been allocated. - Use
IsEmptyto examine a Variant’s Empty state or an individual value, not to test array allocation. - Use
Countfor a collection, or a separate item count for an array, when the real question is whether there are results.
Common errors and debugging checks
Run-time error 9 at UBound or LBound commonly means the dynamic array was never allocated, was erased, or the requested dimension does not exist. In a multidimensional array, specify the dimension you mean; for example, a two-dimensional range array has different bounds for its row and column dimensions. A typo in an index can also cause error 9 even when the array itself is allocated.
If an array works in one branch but fails in another, follow every path that assigns, resizes, or erases it. A branch that returns early may leave a dynamic array unallocated. Check the state immediately before the failing bounds call, and avoid assuming that a prior check remains valid if intervening code can erase or replace the array.
Finally, distinguish an empty array from an array of empty elements. A three-element array whose values have not been set still has three valid indexes. If the task is to find populated entries, loop through those indexes and check the element values according to their data type; allocation alone cannot answer that question.
Frequently Asked Questions
Does IsEmpty work on a VBA array?
Not as a general array-allocation check. IsEmpty tests whether a Variant is Empty. Use IsArray and a guarded bounds check to determine whether a dynamic array is allocated.
Does IsArray mean the array has elements?
No. It indicates that the value is an array, but does not by itself establish that a dynamic array has been allocated or contains usable items. Check bounds safely, then use a count or inspect elements if you need to know whether there is data.
What happens when I use UBound on an unallocated array?
VBA raises run-time error 9, “Subscript out of range.” Check allocation before calling UBound or LBound, and remember that VBA does not short-circuit And and Or conditions.
Does Erase make every array unallocated?
No. Erase deallocates a dynamic array, but resets the values in a fixed-size array while leaving its bounds intact. The outcome depends on how the array was declared.
Why does a one-cell range sometimes not return an array?
Excel returns a scalar value for a single-cell range, while a multi-cell range normally returns a two-dimensional array. Check the result with IsArray and handle both shapes when the range size can vary.
Related
- Excel Split Cell Based on Character: Methods That Work
- Excel Split Cell at a Specific Character?
- Excel: Specify Which Duplicate to Remove?
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA For Next Loop: Syntax, Examples, and Common ErrorsA VBA For...Next loop repeats a block of code for a counter that moves through a specified range.…
- Excel VBA String Limit: Maximum Length and Cell CapsA variable-length VBA String can hold up to approximately 2 billion characters in theory, but available memory usually…
- Running Macros in Google Sheets: How to Run and Manage ThemTo run a macro in Google Sheets, open the spreadsheet in a web browser, choose Extensions > Macros,…
- Excel VBA Get Workbook Name: Methods and ExamplesIn Excel VBA, use ThisWorkbook.Name to get the name of the workbook that contains the running code, or…
- Excel VBA: Find the Minimum Value in a Worksheet RangeIn Excel VBA, use Application.WorksheetFunction.Min to get the smallest number in a range. For example, if the values…
- Excel VBA Array Allocation: Declare, Size, and ResizeIn Excel VBA, declare a dynamic array with empty parentheses, then allocate it with ReDim; for example, use…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.