VBA UBound Function

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

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 UBound Function Syntax

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.

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:

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.

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.

Simply the best place to learn Excel VBA