How to Get the Array Length in VBA

To count the elements in a VBA array, subtract its lowest index from its highest index and add one: UBound(array) - LBound(array) + 1. The + 1 includes both endpoint indexes. This works when an array starts at zero, one, or another declared lower bound. UBound by itself gives only the highest index, which is not necessarily the number of elements. Use COUNTA only when you want to count nonempty values rather than measure array capacity.

Use UBound and LBound to Get an Array’s Length in VBA

For example, Dim names(5 To 9) As String declares five elements, at indexes 5 through 9. Its length is UBound(names) - LBound(names) + 1, or 9 - 5 + 1 = 5. The explicit bounds make clear why the upper bound alone is not the count. This complete VBA procedure uses Debug.Print and & to join the label to the calculated value: Sub ShowArrayLength(): Dim names(5 To 9) As String: Debug.Print "Array length: " & (UBound(names) - LBound(names) + 1): End Sub. Debug.Print sends the calculated value to the VBA editor’s Immediate window; the supplied runtime evidence covers the separate Python arithmetic example below, not execution of this VBA procedure.

If several procedures need the same calculation, put a helper in a standard module and call it wherever needed: Public Function ArrayLength(ByRef values As Variant) As Long: ArrayLength = UBound(values) - LBound(values) + 1: End Function. Pass an array that has already been allocated; the helper assumes its bounds can be read and does not handle an unallocated dynamic array. The Variant parameter can receive the array, while UBound and LBound preserve its actual lower and upper bounds, including a nonzero lower bound.

The same formula applies to arrays returned by Array, such as a Variant assigned Array("Glen", "Yumi", "Myla"). Calculate its length from its bounds instead of assuming a particular lower bound. VBA’s default lower bound can be affected by Option Base, while an explicit Array or declaration may have its own behavior; asking LBound avoids baking that assumption into the calculation.

Run this Python arithmetic example to see the same result; it is not execution of the VBA procedure:

lower_bound = 5
upper_bound = 9
length = upper_bound - lower_bound + 1
print(f"Array length: {length}")

Array length: 5

Handle Dynamic and Uninitialized Arrays

A dynamic array is declared without bounds, for example Dim values() As String, and receives bounds later with ReDim values(1 To 4). Once allocated, use the same UBound and LBound formula. A fixed-size array has bounds at declaration; a dynamic array’s bounds depend on its current allocation.

An unallocated dynamic array has no usable bounds: calling LBound or UBound on it causes a run-time error. IsEmpty checks whether a Variant has the special Empty value; it does not detect whether a dynamic array has been allocated. When code may receive an unallocated array, place the LBound and UBound calls in a narrow error-handling section and treat their bounds error as the unallocated case. Keep other work outside that section so an unrelated failure is not mistaken for an allocation check. Once allocated with ReDim, the usual formula applies. The procedure above uses a fixed-size array, so it always has bounds.

Use COUNTA to Count Nonempty Array Values

In Excel VBA, WorksheetFunction.CountA(values) counts nonempty values in an array according to Excel’s COUNTA rules. It can be convenient when the question is how many entries contain values. It is not a general substitute for array length: an allocated array can contain slots without values, so the count of nonempty entries can be lower than the number of indexes. When every element is populated, both counts may match; when you need capacity or the number of available positions, use the bounds formula instead.

COUNTA is a worksheet function call, not a VBA bound operation, and the array must be in a form the worksheet function can accept. There is no need to impose a 30-element cap when using this method. Choose UBound and LBound for the array’s element count, including arrays with nonzero lower bounds; choose COUNTA only when nonempty values are the quantity you intend to measure.