VBA Array Length: Count Elements and Find Bounds

In VBA, calculate an array’s length as UBound(array) - LBound(array) + 1. The expression works for a one-dimensional array with any lower bound; for a multidimensional array, specify which dimension you want to count.

What VBA Means by Array Length

VBA arrays do not have a built-in Length property. To count the elements in an array dimension, find its lower and upper bounds and include both endpoints in the count. For example, an array whose indexes run from 1 through 5 contains five elements, not four: 5 − 1 + 1 = 5.

This distinction matters because array indexes are not always zero-based or one-based. A declared array might use indexes from 0 to 4, 1 to 5, or even 10 to 14. Each has five elements, but the upper bound alone is not necessarily the element count. The formula accounts for both ends of the index range.

Use the same calculation whether the array is fixed-size or dynamic, provided it has been allocated and you are counting one dimension. The function names are built into VBA, so no worksheet function or special reference is needed.

The Basic UBound and LBound Calculation

LBound returns the smallest valid subscript in a dimension, and UBound returns the largest. Both accept an optional dimension number. When the array is one-dimensional, the dimension defaults to 1, so this expression is sufficient:

UBound(values) - LBound(values) + 1

For a concrete example, this procedure creates an array with subscripts 5 through 9. The result is 5 because the indexes are 5, 6, 7, 8, and 9.

Save a copy of the workbook before running a macro. VBA macros cannot be undone with Ctrl+Z, even when a particular example only prints a result in the Immediate window.

Public Sub CountFixedArray()
    Dim values(5 To 9) As Long
    Dim elementCount As Long

    ' Count both endpoints of the index range.
    elementCount = UBound(values) - LBound(values) + 1
    Debug.Print elementCount
End Sub

In the Visual Basic Editor, Debug.Print writes the result to the Immediate window. If that window is not visible, use View > Immediate Window in the editor. The macro does not place the answer in a worksheet cell.

Lower Bounds and Option Base

The lower bound is why UBound(array) is not a general substitute for an element count. If an array starts at 0, its upper bound happens to equal its element count, but that coincidence does not hold for an array starting at 1 or another value. Subtracting LBound makes the calculation reliable across these cases.

In a VBA module, the optional Option Base statement can set the default lower bound used when a declaration omits an explicit lower bound. The default is 0; a module can specify Option Base 1. This setting is local to its module and does not change an array whose bounds are written explicitly, such as Dim values(5 To 9) As Long.

For code that other people may maintain, explicit bounds are often easier to read than relying on a module setting. Regardless of how the array was declared, checking its actual bounds with LBound and UBound avoids assumptions about where indexing begins.

Counting Elements in a Multidimensional Array

A multidimensional array has a separate lower and upper bound for each dimension. Pass the dimension number as the second argument to both functions. For an array declared with three rows and five columns, dimension 1 has three elements and dimension 2 has five.

Public Sub CountArrayDimensions()
    Dim grid(1 To 3, 0 To 4) As String
    Dim rowCount As Long
    Dim columnCount As Long
    Dim totalElements As Long

    ' Dimension 1 is rows; dimension 2 is columns.
    rowCount = UBound(grid, 1) - LBound(grid, 1) + 1
    columnCount = UBound(grid, 2) - LBound(grid, 2) + 1
    totalElements = rowCount * columnCount

    Debug.Print rowCount
    Debug.Print columnCount
    Debug.Print totalElements
End Sub

The output values are 3, 5, and 15. Multiplying the dimension counts gives the number of positions in a rectangular array when you need the total number of elements. It does not tell you how many positions contain nonblank text or nonzero numbers; that is a different question about the values stored in the array.

For arrays with more dimensions, repeat the bounds calculation with the relevant dimension number. To find the total number of positions, multiply the counts for all dimensions. Keep the dimension order straight: in a worksheet range array, the first dimension conventionally represents rows and the second represents columns.

Dynamic Arrays and Unallocated Arrays

A dynamic array is declared with empty parentheses and receives bounds later through ReDim. Once allocated, the standard length formula applies. Before allocation, however, calling LBound or UBound raises an error, so code should not assume that every declared dynamic array is ready to count.

For a known dynamic array, this pattern treats a bounds error as an indication that no usable bounds are available. It clears the error before checking and turns normal error handling back on afterward.

Public Sub CountIfAllocated()
    Dim values() As Long
    Dim elementCount As Long

    ' Allocate the array before counting it.
    ReDim values(1 To 4)

    Err.Clear
    On Error Resume Next
    elementCount = UBound(values) - LBound(values) + 1

    If Err.Number <> 0 Then
        elementCount = 0
        Err.Clear
    End If

    On Error GoTo 0
    Debug.Print elementCount
End Sub

As written, the array is allocated with four elements, so the result is 4. If the ReDim line is removed, the bounds call fails and the procedure reports 0. Use this kind of error handling narrowly around the bounds operation: On Error Resume Next can conceal unrelated errors if it is left active across more code than necessary.

Arrays Returned from Excel Ranges

Reading a multi-cell range through its Value property commonly produces a two-dimensional Variant array. For example, the range A1:C5 has five rows and three columns, with the resulting array indexed from 1 to 5 in its first dimension and 1 to 3 in its second. The dimensions can be counted rather than assumed:

Public Sub CountRangeValues()
    Dim data As Variant
    Dim rowCount As Long
    Dim columnCount As Long

    ' Read a multi-cell range into a Variant array.
    data = Range(A1:C5).Value
    rowCount = UBound(data, 1) - LBound(data, 1) + 1
    columnCount = UBound(data, 2) - LBound(data, 2) + 1

    Debug.Print rowCount
    Debug.Print columnCount
End Sub

In actual VBA, the range address must be a string. The assignment line should therefore be written as data = Range("A1:C5").Value. The counts are 5 rows and 3 columns. This range example is useful when a macro should adapt to a range whose size changes, rather than hard-coding its dimensions.

There is an important exception: a single-cell range’s Value is a scalar value, not a two-dimensional array. Calling UBound on that scalar will fail. If code may read either one cell or multiple cells, account for the different return shapes before treating the result as an array.

Array Length Compared with Capacity

In VBA, the bounds describe the valid indexes currently allocated for an array. They do not expose a separate capacity value like some programming languages provide. If a dynamic array has bounds 1 through 100, its current dimension contains 100 positions, whether all, some, or none of those positions have meaningful values.

This also separates array size from the number of occupied entries. An array can have 10 allocated elements while only 6 contain non-empty strings. Counting non-empty strings requires examining the values, not just subtracting the bounds. Similarly, a two-dimensional array’s total position count is the product of its dimension sizes, whereas the count of qualifying records may require checking a condition in each row.

Collections are a related but different VBA structure. A Collection exposes a Count property, while an array uses bounds. If the code needs a resizable set of items with a direct item count, a Collection may fit better; if it needs indexed, compact data or a block of worksheet values, an array is often appropriate.

Common Mistakes and How to Avoid Them

Using only UBound: this gives the greatest index, not a reliable count when the lower bound can vary. Subtract LBound and add 1.
Forgetting the dimension argument: in a two-dimensional array, the default dimension is 1. Specify 2 to count columns, or the appropriate number for another dimension.
Counting an unallocated dynamic array: allocate it first or handle the bounds error deliberately.
Assuming every range result is an array: a one-cell range returns a scalar, so code that uses array bounds needs to account for it.
Confusing allocated slots with filled values: bounds count positions, not nonblank cells, nonzero values, or records that pass a test.

When looping through an array, use its actual lower and upper bounds if the indexing base is not guaranteed. A loop that always starts at 0 can fail for a one-based array; one that always starts at 1 can skip the first value or fail for a zero-based array.

Frequently Asked Questions

How do I get the length of an array in VBA?
For one dimension, use UBound(array) - LBound(array) + 1. This counts every valid subscript, including both the lower and upper bounds.

Does UBound return the number of elements?
Not in every case. It returns the largest index in the selected dimension. It equals the count only when the lower bound is zero; otherwise, subtract the lower bound and add 1.

How do I count columns in a two-dimensional array?
Use UBound(array, 2) - LBound(array, 2) + 1. Dimension 2 is commonly the column dimension for arrays read from multi-cell Excel ranges.

What happens if I call UBound on an unallocated array?
VBA raises an error because the dynamic array has no valid bounds yet. Use ReDim to allocate it first, or handle the bounds error if an unallocated state is expected.

Can VBA arrays have a lower bound other than zero or one?
Yes. You can specify bounds such as 5 To 9 in an array declaration or when using ReDim. The LBound-and-UBound formula still counts the elements correctly.

Is array length the same as the number of non-empty values?
No. Array length is the number of allocated positions in a dimension. To count only non-empty values, inspect the elements and count those that meet the condition.

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.