Excel VBA Get Array Dimensions: Bounds, Rank, and Size

In Excel VBA, use LBound(array, dimension) and UBound(array, dimension) to get an array’s lower and upper bounds; its element count in a dimension is UBound - LBound + 1. VBA has no single built-in function that returns an array’s number of dimensions, but you can determine the rank by checking dimensions until UBound raises an error.

The quickest way to get a dimension’s size

For an array named data, these functions report the first and last valid index along a dimension. The optional dimension argument is 1 for the first dimension, 2 for the second, and so on. If omitted, it defaults to 1.

Dim data(1 To 4, -2 To 2) As Double

Debug.Print LBound(data, 1)  ' 1
Debug.Print UBound(data, 1)  ' 4
Debug.Print UBound(data, 1) - LBound(data, 1) + 1  ' 4 elements

Debug.Print LBound(data, 2)  ' -2
Debug.Print UBound(data, 2)  ' 2
Debug.Print UBound(data, 2) - LBound(data, 2) + 1  ' 5 elements

This array has two dimensions, with four elements in the first and five in the second. It therefore contains 20 elements in total. The index values themselves are not the sizes: the second dimension runs from -2 through 2, which is five positions, not two.

To see results, run the code in the Visual Basic Editor and open its Immediate window using View > Immediate Window. On Windows, Alt+F11 opens or returns to the editor; on Mac, the equivalent is usually Option+F11, sometimes with Fn depending on the keyboard and system settings.

Bounds, element counts, and total elements

A bound is an index at an end of a dimension. A size is the number of valid indexes between those bounds, including both ends. This distinction matters whenever an array does not start at zero or one. For example, an array from 5 to 9 has five elements, because 9 − 5 + 1 = 5.

For a two-dimensional array, calculate each dimension separately. Multiply the dimension sizes to get the total number of elements in a rectangular array. For a three-dimensional array, multiply all three sizes. This multiplication is useful when the array is known to be allocated and rectangular, but it is not a substitute for checking each bound when iterating.

VBA’s default lower bound is usually zero or one depending on how the array is declared and the module’s Option Base setting. Explicit bounds, such as Dim data(1 To 10), remove that uncertainty. Always using LBound in loops makes code work with arrays that have different lower bounds:

Dim i As Long

For i = LBound(data, 1) To UBound(data, 1)
    Debug.Print data(i)
Next i

Get the number of dimensions

LBound and UBound answer questions about a specified dimension, but VBA does not provide an ArrayRank or Dimensions built-in. A common approach is to try successive dimension numbers. When the requested dimension does not exist—or the array is not allocated—UBound raises an error. The function below counts valid dimensions and returns zero if no dimension can be read.

Public Function ArrayRank(ByRef data As Variant) As Long
    Dim dimension As Long
    Dim testBound As Long

    ' Count dimensions until UBound rejects the next one.
    On Error Resume Next
    Do
        dimension = dimension + 1
        Err.Clear
        testBound = UBound(data, dimension)
        If Err.Number <> 0 Then Exit Do
    Loop
    On Error GoTo 0

    ArrayRank = dimension - 1
End Function

Pass an array to this function and it returns 1 for a one-dimensional array, 2 for a two-dimensional array, and so forth. A scalar value or an unallocated dynamic array returns zero in this pattern because there is no valid first dimension to inspect. The function uses error handling only around the bound check; keep the loop focused on that operation so an unrelated error is not mistaken for the end of the dimensions.

For example, with a declared array named data, Debug.Print ArrayRank(data) prints its dimension count in the Immediate window. A Variant argument is useful because it can receive arrays of different element types and ranks. In larger projects, consider documenting that callers must supply an array, since a scalar also produces the zero result rather than a separate “not an array” message.

Use bounds safely with dynamic arrays

A dynamic array is declared without bounds and allocated later, typically with ReDim. Before allocation, asking for its bounds raises an error. Check whether the array has been allocated before relying on LBound or UBound. VBA does not offer one universal allocation test that works identically for every array type and situation, so a small error-handled bounds check is often used when the caller cannot track allocation state.

When possible, track that state explicitly. For example, set a Boolean such as isAllocated to True after a successful ReDim, and check it before reading bounds. This is clearer than using broad error suppression throughout a procedure. If the array is resized, update the state as part of the same code path.

Resizing also affects what the bounds mean. A plain ReDim can create or resize a dynamic array, while ReDim Preserve keeps existing values when it resizes. In a multidimensional array, ReDim Preserve can change only the upper bound of the last dimension; it cannot preserve values while changing an earlier dimension. Check the bounds after resizing if subsequent logic depends on the new size.

Arrays returned from worksheet ranges

Range values are a frequent source of confusion. Assigning a multi-cell range’s Value or Value2 to a Variant produces a two-dimensional array, even when the selected range is only one row or one column. For a normal worksheet range, the first dimension represents rows and the second represents columns. Inspect both rather than assuming a one-dimensional list.

Sub InspectRangeArray()
    Dim values As Variant
    Dim rowCount As Long
    Dim columnCount As Long

    ' A multi-cell range returns a two-dimensional Variant array.
    values = ThisWorkbook.Worksheets("Sheet1").Range("A1:C3").Value2
    rowCount = UBound(values, 1) - LBound(values, 1) + 1
    columnCount = UBound(values, 2) - LBound(values, 2) + 1

    Debug.Print rowCount, columnCount  ' 3 rows, 3 columns
End Sub

For this example, the result is three rows and three columns. A single-cell range is different: assigning its value to a Variant gives a scalar, not a two-dimensional array. Consequently, calling UBound(values, 1) in that case raises an error. If code may receive either one cell or multiple cells, handle the single-cell case separately before treating the result as an array.

Save a copy of the workbook before running a macro. VBA macros cannot be undone with Ctrl+Z, and code that writes to cells or changes data can have lasting effects.

Common errors and misconceptions

Using UBound as the element count: It returns the highest index, not the number of items. Subtract LBound and add one.
Assuming every array begins at zero: Explicit bounds and Option Base can change the lower bound. Query it instead of hard-coding it.
Leaving out the dimension argument: The default is dimension 1. For a two-dimensional array, it does not report the column bound; use dimension 2 for that.
Assuming a one-cell range is an array: Its value is a scalar Variant. A range with multiple cells returns an array.
Calling bounds on an unallocated array: The call fails because no valid bounds exist yet. Allocate it first or handle the error deliberately.
Confusing array dimensions with worksheet dimensions: The array’s row and column indexes describe its in-memory structure. They are not necessarily worksheet row numbers or column letters.

These checks also help prevent loop errors. A loop from zero to UBound(data) fails for an array whose lower bound is one, while a loop from one to UBound(data) fails for a zero-based array. Using both bound functions makes the loop’s limits match the actual array.

Choosing the right check for your task

Use LBound and UBound directly when you already know the array’s rank and need its limits or the size of a particular dimension. Use the formula UBound - LBound + 1 when you need a count. Use a rank-checking function only when the rank is genuinely unknown at runtime, such as when a procedure accepts arrays in different shapes.

If the array shape is part of a procedure’s contract, it is often simpler to state that contract and validate it than to make every procedure infer the shape. For example, code that processes a range matrix can require a two-dimensional array and check its rank before accessing dimension 2. That makes failures easier to diagnose and avoids treating a scalar or an unallocated array as valid input.

Remember that rank and size answer different questions. A one-dimensional array can have a thousand elements, while a two-dimensional array can contain only two. Likewise, a two-dimensional array may have one row and many columns; it remains two-dimensional. Check the actual bounds whenever behavior depends on shape, rather than inferring shape from the amount of data.

Frequently Asked Questions

How do I get the size of an array in VBA?
For a known dimension, calculate UBound(array, dimension) - LBound(array, dimension) + 1. This counts all valid indexes, including the lower and upper bounds.

How do I find the number of dimensions in a VBA array?
There is no built-in rank function. Try UBound with dimension numbers starting at 1 and stop when it raises an error, as in the ArrayRank example above.

Does UBound return the array length?
Not necessarily. It returns the upper index. It equals the length only in some zero-based cases; the general count is upper bound minus lower bound plus one.

How do I get the number of rows and columns from a range array?
For a multi-cell range stored in a Variant, calculate the size of dimension 1 for rows and dimension 2 for columns. Treat a single-cell range separately because its value is a scalar.

Why does UBound give an error?
The array may be unallocated, the requested dimension may not exist, or the value may be a scalar rather than an array. Check which case applies before using the bound.

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.