VBA UBound in Excel — Array Size with UBound and LBound (and the Off-by-One)

TL;DR — In VBA you do not count an array, you ask it. UBound(a) returns the highest valid index; LBound(a) returns the lowest. Arrays are zero-based by default, so Dim a(9) holds ten elements indexed 0 to 9 — UBound is 9, not 10. That off-by-one is the single most common loop bug. The loop that always survives is For i = LBound(a) To UBound(a), and the true length is UBound(a) - LBound(a) + 1. There is no .Length or .Count on an array.

Sub LoopSafely()
    Dim a() As String
    a = Split("red,green,blue", ",")   ' Split returns a 0-based array

    Dim i As Long
    For i = LBound(a) To UBound(a)      ' works no matter the bounds
        Debug.Print i, a(i)            ' 0 red / 1 green / 2 blue
    Next i

    Debug.Print "count = " & (UBound(a) - LBound(a) + 1)   ' 3
End Sub

UBound is how you read an array's shape, and it is the partner of ReDim: you size the array, then you ask what size it ended up. Get the bounds wrong and you either skip the last element or run off the end into run-time error 9. Because different sources hand you arrays with different starting indexes, the habit of asking — rather than assuming 0 or 1 — is what keeps loops correct.

What you'll learn

• What UBound and LBound return, and why an array has no .Length property
• Why VBA arrays are zero-based by default, and where the off-by-one bug comes from
• The loop that always works: For i = LBound(a) To UBound(a)
• The true array length: UBound(a) - LBound(a) + 1
• Getting the size of each dimension of a 2D array with UBound(a, 2)
• Why UBound on an empty dynamic array raises error 9, and how to guard it

UBound and LBound: ask, do not count

UBound (upper bound) returns the largest index you can legally use; LBound (lower bound) returns the smallest. Together they describe the array's shape. VBA arrays deliberately have no .Length or .Count — the bounds are the source of truth, and you read them at run time:

Dim a(1 To 5) As Long
Debug.Print LBound(a)   ' 1
Debug.Print UBound(a)   ' 5

Why ask instead of remember? Because the array you are looping over was often sized somewhere else — by a ReDim, by Split, by reading a range — and hard-coding the size you think it has is exactly how loops drift out of sync with reality.

Zero-based by default: where the off-by-one lives

Unless you say otherwise, a VBA array starts at index 0. So Dim a(9) does not give you nine elements — it gives you ten, indexed 0 through 9:

Dim a(9) As Long        ' ten elements: a(0) .. a(9)
Debug.Print LBound(a)   ' 0
Debug.Print UBound(a)   ' 9  <- not 10

This is the root of the classic bug. For i = 1 To UBound(a) silently skips a(0); For i = 0 To 10 runs one step too far and hits Subscript out of range. You can force 1-based arrays for a whole module with Option Base 1, or declare explicit bounds with Dim a(1 To 10) — but the real fix is to stop guessing the start at all.

The loop that always works

Write the loop in terms of the array's own bounds and it is correct no matter how the array was declared or where it came from:

Dim i As Long
For i = LBound(a) To UBound(a)
    ' ... use a(i) ...
Next i

This one habit removes the entire family of off-by-one errors. It works for a 0-based Split result, a 1-based Range.Value array, an Option Base 1 module, and an explicit Dim a(5 To 12) alike. Never hard-code 0 or 1 as the start of an array loop when LBound will tell you the truth for free.

Array length: UBound minus LBound plus one

Because an array can start at any index, its length is not simply UBound + 1. The formula that is always right is:

Dim n As Long
n = UBound(a) - LBound(a) + 1

For a 0-based array of ten, that is 9 - 0 + 1 = 10; for Dim a(1 To 10), it is 10 - 1 + 1 = 10. Use the full formula and it does not matter which base the array uses. (People search for a "VBA array length" property; there is not one — this expression is the answer.)

2D arrays: a bound per dimension

For a multi-dimensional array, UBound and LBound take a second argument — which dimension you are asking about. UBound(a, 1) is the first dimension (rows), UBound(a, 2) is the second (columns):

Dim g(1 To 3, 1 To 4) As Long   ' 3 rows, 4 columns
Debug.Print UBound(g, 1)        ' 3  (rows)
Debug.Print UBound(g, 2)        ' 4  (columns)

Dim r As Long, c As Long
For r = LBound(g, 1) To UBound(g, 1)
    For c = LBound(g, 2) To UBound(g, 2)
        ' ... use g(r, c) ...
    Next c
Next r

A bare UBound(g) with no dimension number returns the first dimension. This matters most when you read a sheet into an array, because Range.Value always hands you a 1-based 2D array — asking UBound(a, 1) and UBound(a, 2) is how you discover how many rows and columns you actually got.

Guard the empty dynamic array

A dynamic array that has been declared but never ReDim-ed (or one that has been Erase-d) has no bounds at all, and calling UBound on it raises run-time error 9, Subscript out of range:

Dim a() As Long
Debug.Print UBound(a)   ' error 9 - the array has no size yet

If there is any chance the array is empty — say it came from a Filter that matched nothing — guard it before you loop. A small helper keeps the calling code clean:

Function IsAllocated(a As Variant) As Boolean
    On Error Resume Next
    IsAllocated = (LBound(a) <= UBound(a))
    On Error GoTo 0
End Function

Then If IsAllocated(a) Then For i = LBound(a) To UBound(a) .... This is the array cousin of guarding an object with Is Nothing before you use it.

How ExcelMaster helps

UBound is a small function with one big job — telling a loop exactly where to stop — and the mistakes around it (assuming 0 or 1, forgetting the array is empty, hard-coding a length) are precisely the ones that make a macro skip the last row or crash on an edge case.

ExcelMaster lets you describe the task — "pull the used range into an array, total each column, write the totals below" — and it loops from LBound to UBound on the right dimension, computes the length correctly, and guards an array that might have come back empty, so the macro is right on the first row, the last row, and the empty case. You keep the workbook and the code.

Frequently asked questions

What does UBound do in VBA?

UBound returns the highest valid index of an array — the largest number you can use inside the brackets. For Dim a(1 To 5), UBound(a) is 5; for a zero-based Dim a(9), it is 9. Pair it with LBound, which returns the lowest index, to loop over an array without hard-coding its size.

How do I get the length of an array in VBA?

There is no .Length or .Count property on a VBA array. Compute the count as UBound(a) - LBound(a) + 1. The full formula is needed because a VBA array can start at index 0, 1, or any explicit lower bound, so UBound + 1 is only correct for zero-based arrays.

Why do I get Subscript out of range with UBound?

Usually because the array is empty — a dynamic array that was declared with Dim a() but never ReDim-ed, or one that was Erase-d, has no bounds, so UBound raises run-time error 9. Guard it with a small IsAllocated helper before you loop. You also see error 9 when a loop runs past UBound, for example For i = 0 To 10 on a ten-element zero-based array.

Are VBA arrays zero-based or one-based?

Zero-based by default: Dim a(9) holds ten elements indexed 0 to 9. You can make a module one-based with Option Base 1, or set explicit bounds with Dim a(1 To 10). Arrays returned by Split and Array are zero-based; an array read from Range.Value is one-based. Because it varies, loop with LBound to UBound instead of assuming.

How do I find the size of a 2D array in VBA?

Pass the dimension number as the second argument: UBound(a, 1) for the first dimension (rows) and UBound(a, 2) for the second (columns), with LBound(a, 1) and LBound(a, 2) for the lower bounds. This is how you read the shape of a 2D array returned by Range.Value.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-09-24.

Related guides: VBA ReDim · VBA 2D Arrays · VBA Array · VBA For Loop · VBA Split