VBA UBound Function

The Excel VBA UBound function returns a Long numeric value of the upper bounds (largest subscript) of a VBA Array. In VBA there is no function that returns the array size hence you need to use VBA UBound together with VBA LBound functions to calculate the array size.

VBA Reference – Array Functions

VBA UBound Function Syntax

UBound ( arrayname, [ dimension ])

Parameters

arrayname
The name of the array variable (Variant data type).
dimension
Optional. The index number of the dimension for which the upper bounds are to be returned.

Example usage of VBA UBound function

Using UBound with single dimension arrays

In below example we will create 2 static VBA Arrays. The first array will be defined without explicitly setting it’s upper bounds. The second array will be defined with both lower and upper bounds.

 
Dim arr1(8) As Variant 'Array of size 9 -> 0 to 8
Debug.Print UBound(arr1) 'Result: 8. Notice this is different from the array size!
    
Dim arr2(2 To 10) As Variant 'Array of size 9 -> 2 to 10
Debug.Print UBound(arr2) 'Result: 10. Notice this is different from the array size!

UBound for multi-dimension arrays

In case dealing with multi-dimensional arrays you can use the dimension parameter to define for which dimension you want to obtains the VBA Array upper bounds:

 
Dim arr1(8, 1) As Variant '2 Dimensional array of size 9x2. 0-8x0-1
Debug.Print UBound(arr1,1) 'Result: 8
Debug.Print UBound(arr1,2) 'Result: 1

Dim arr2(2 To 10, 1 To 2) As Variant '2 Dimensional array of size 9x2. 2-10x1-2
Debug.Print UBound(arr2,1) 'Result: 10
Debug.Print UBound(arr2,2) 'Result: 2

Calculating VBA Array size

When starting using VBA Arrays there is a lot of confusion around calculating the size of array. Below an example of how to calculate an array size using the VBA UBound and VBA LBound functions.

Dim arr1(8) As Variant 'Array of size 7 -> 1 to 8
Debug.Print UBound(arr1) - LBound(arr1) + 1 'Result: 7. Correct array size!

Debug.Print UBound(arr1) 'Result: 8. Incorrect array size!

But I don’t want to waste time on this calculation each time I want to use VBA Arrays and don’t want to define a dedicated function for this. Is there a way out? Yes! Using the Option Base directive you can set all explicitly declared array lower bounds (LBound) to be 1 by default hence their upper bounds will be equal to the actual size of the array.

Option Base 1

Sub Test()
  Dim arr1(8) As Variant 'Array of size 8 -> 1 to 8
  Debug.Print UBound(arr1) - LBound(arr1) + 1 'Result: 8. Correct array size!
  Debug.Print UBound(arr1)                    'Result: 8. Correct array size!

End Sub

Analyst Cave