VBA Array Count: Count Elements With LBound and UBound

To count elements in a one-dimensional VBA array, use UBound(arrayName) - LBound(arrayName) + 1. For example, an array whose bounds are 1 and 5 contains 5 elements; subtracting the lower bound matters because VBA arrays do not always start at zero or one.

What “array count” means in VBA

VBA arrays do not have a built-in Count property. Instead, their lower and upper bounds describe the index range, and the number of elements is the size of that range. The calculation is upper bound minus lower bound plus one: the extra one includes both endpoints.

This is different from counting values that meet a condition. An array can contain five elements even if some are blank, zero, or repeated. If you need to count only numbers, nonblank entries, or unique values, you need to examine the elements and apply that condition; the bounds alone only tell you the array’s capacity or current index range.

For a normal one-dimensional array, use this pattern:

Dim scores(1 To 5) As Long
Dim elementCount As Long

' Count every index in the array's bounds.
elementCount = UBound(scores) - LBound(scores) + 1
Debug.Print elementCount

The result is 5. The code works whether the lower bound is 0, 1, or another valid value, provided the array has been allocated.

Why both LBound and UBound matter

UBound returns the greatest valid index, while LBound returns the smallest. A common shortcut is UBound(arrayName) + 1, which assumes the first index is zero. That assumption is not safe for every VBA array.

For example, if an array runs from index 3 through index 7, it has five elements, not eight. The correct calculation is 7 – 3 + 1. Similarly, an array from 0 through 4 contains five elements. Using both bounds makes the calculation portable across those cases.

Use LBound and UBound with the array variable itself, not a worksheet range. A range has its own properties, such as Range.Count; that is a different object model from a VBA array.

Bounds, Option Base, and the Array function

VBA arrays can have different lower bounds. An explicit declaration such as Dim items(1 To 10) As String makes the bounds clear. With a declaration such as Dim items(10) As String, the lower bound is normally zero, unless the module specifies Option Base 1. Option Base affects arrays declared without an explicit lower bound in that module; it does not override bounds written explicitly in a declaration.

The built-in Array function creates a zero-based array. Because it returns a Variant containing an array, assign its result to a Variant variable:

Dim colors As Variant
Dim colorCount As Long

' Array creates a zero-based array with three values.
colors = Array("Red", "Green", "Blue")
colorCount = UBound(colors) - LBound(colors) + 1
Debug.Print colorCount

This prints 3. If you use a lower-bound assumption with arrays from different sources—for example, arrays created by Array, declared in a module with Option Base, or redimensioned at runtime—you can get an off-by-one result. Checking both bounds avoids that problem.

Counting a dynamic array

A dynamic array is declared with empty parentheses and receives its bounds later through ReDim. Count it after it has been allocated. The following example creates four elements indexed from 0 to 3:

Dim entries() As String
Dim entryCount As Long

' Allocate four elements, with indexes 0 through 3.
ReDim entries(0 To 3)
entryCount = UBound(entries) - LBound(entries) + 1
Debug.Print entryCount

The output is 4. A dynamic array that has not yet been allocated has no usable bounds. Calling LBound or UBound on it raises a run-time error, so do not treat an unallocated array as if it had a count of zero without checking for that situation.

One practical approach is to use a small helper when an array may be unallocated. This version returns zero if querying the bounds raises an error:

Private Function SafeArrayCount(ByRef values As Variant) As Long
    Dim lowerIndex As Long
    Dim upperIndex As Long

    ' An unallocated array has no bounds to read.
    On Error GoTo NoBounds
    lowerIndex = LBound(values)
    upperIndex = UBound(values)
    SafeArrayCount = upperIndex - lowerIndex + 1
    Exit Function

NoBounds:
    SafeArrayCount = 0
End Function

This helper is convenient for controlled cases where the argument is expected to be an array. It handles errors from reading the bounds, but it should not be used to hide unrelated programming errors or to validate arbitrary input. If the array is required, it is often better to make allocation a clear part of the procedure’s logic and handle an unallocated array explicitly.

Counting elements in a multidimensional array

A multidimensional array has a separate lower and upper bound for each dimension. Pass the dimension number to LBound and UBound. For a rectangular array, multiply the number of elements in each dimension to get the total number of slots.

Dim grid(1 To 3, 0 To 4) As Double
Dim rowCount As Long
Dim columnCount As Long
Dim totalSlots As Long

' Count the bounds in each dimension.
rowCount = UBound(grid, 1) - LBound(grid, 1) + 1
columnCount = UBound(grid, 2) - LBound(grid, 2) + 1
totalSlots = rowCount * columnCount
Debug.Print totalSlots

The first dimension contains 3 positions and the second contains 5, so the array has 15 slots. In VBA, a two-dimensional array is not automatically a worksheet-style table with a special count property; it is still an array indexed by each dimension.

VBA does not provide a simple built-in function that reports the number of dimensions for every array. If your procedure knows the array has two dimensions, specify dimensions 1 and 2 as above. If the number of dimensions can vary, you need a deliberate dimension-checking routine; probing dimensions by calling UBound also requires error handling because an invalid dimension raises an error.

Array size is not the same as populated values

The bounds calculation counts every valid index, whether or not your code has put meaningful data there. For example, a newly redimensioned array of strings has the same element count as after it is filled, even though its elements initially contain default values. For numeric types, those defaults are generally zero; for strings, they are empty strings.

If you want to count entries that satisfy a condition, loop through the array. This example counts nonempty strings in a one-dimensional array:

Dim labels(1 To 4) As String
Dim index As Long
Dim nonemptyCount As Long

' Count values that are not empty strings.
labels(1) = "North"
labels(2) = ""
labels(3) = "South"
labels(4) = "East"

For index = LBound(labels) To UBound(labels)
    If Len(labels(index)) > 0 Then
        nonemptyCount = nonemptyCount + 1
    End If
Next index
Debug.Print nonemptyCount

This prints 3, while the bounds formula returns 4. Decide whether “count” means available array positions or qualifying values before choosing the calculation.

Array count versus Count properties and worksheet functions

A frequent source of confusion is that VBA collections and ranges do have a Count property, but arrays do not. For a Collection, use its Count property. For a worksheet range, Range.Count counts cells in the range. Neither is the general method for counting the bounds of a VBA array.

Excel’s COUNT worksheet function is also not an array-size function: it counts numeric values in a range or applicable value set. It may ignore text or empty cells, which is different from counting every index. If you have transferred a worksheet range into a VBA array, use the array’s bounds to count positions, or loop through it if you need a value-based count.

For a one-dimensional array, the basic bounds expression is usually the clearest option. Use a loop when you need a conditional count, and multiply dimension sizes when you need the total slots in a multidimensional array.

Common errors and practical safeguards

Assuming the lower bound is zero: Use UBound - LBound + 1 unless you have a specific reason to rely on a known lower bound.
Reading bounds before allocation: A dynamic array must be redimensioned before LBound or UBound can be queried.
Forgetting the dimension argument: For a multidimensional array, UBound(arrayName) refers to the last index of the first dimension. Supply the dimension number when you need another dimension.
Confusing elements with meaningful data: Bounds count slots, not nonblank, nonzero, or unique values.
Using the wrong count API: Collection.Count and Range.Count apply to those objects, not ordinary arrays.

When writing a loop, use the array’s bounds as well as for its count. A loop from LBound(values) through UBound(values) remains correct for a non-zero lower bound; a loop that starts at 0 or 1 may skip items or address an invalid index.

Frequently Asked Questions

Does VBA have an array Count property?
No. For a one-dimensional array, calculate UBound(arrayName) - LBound(arrayName) + 1. Collections and ranges have separate count properties, but those do not apply to a standard VBA array.

How do I count an array that starts at zero?
Use the same bounds formula. For an array indexed from 0 through 4, the result is 4 – 0 + 1, or 5 elements.

What happens if I use UBound on an unallocated array?
VBA raises a run-time error because the dynamic array does not yet have bounds. Allocate it with ReDim first or handle that case explicitly.

How do I get the total count in a two-dimensional array?
Calculate the size of each dimension with its own LBound and UBound, then multiply those sizes. For a 3-by-5 array, the total is 15 slots.

Does array count tell me how many cells are filled?
No. It reports the number of valid index positions. To count filled or qualifying entries, loop through the elements and test each value.

These examples are VBA code for Excel on Windows. Before running a macro that changes a workbook, save a separate copy: VBA macros cannot be undone with Ctrl+Z. On Mac, VBA code concepts and array bounds work the same way, though the editor and shortcut for opening it may differ from Windows.

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.