The VBA UBound (Upper Bound) function returns the upper limit of an array as opposed to the LBound (Lower Bound) that returns the lower limit of an array. Both functions (UBound and LBound) can be combined to determine the size of an array.
The syntax of the UBound function is: UBound( ArrayName, [Dimension] )
The UBound function has two arguments which are “ArrayName" and “Dimension"
It is a required argument referring to the array for which you want to find the highest subscript.
It is an optional integer that specifies the dimension of the array. When omitted in the code, it
takes a default value 1.
Reminder on Arrays
An array is a special type of data type that can store multiple objects of the same type. An array can be considered as a container than can store more than one value at the same time. Arrays can be One-dimensional or Multi-dimensional (Two-dimensional, Three-dimensional etc.). The values within an array are called elements or items. Each item in an array is assigned an index position or an index. The index represents the position of the element within the array; it is placed in line relatively to the other elements of the array.
Note that array indexes start counting from zero (0) meaning that the first element of an array will have as index 0, the second element has index 1, and so on.
Let’s consider for example an array of strings called seasons that will store the four seasons[Spring, Summer, Fall, Winter]. In this One-dimensional array, the index of Spring = 0, Summer = 1, Fall = 2, Winter = 3. The Upper bound index of this array is 3 representing the index of the last item of the array.
In VBA, arrays are declared like other variables using keyword Dim.
If I want to declare the array called seasons above, the syntax
will be as follows:
Dim seasons(3) As String.
Above, the Upper Bound = 3, Number of elements = 4 because counting starts from 0
Dim seasons(0 to 3) As String.
Examples with VBA UBound Function
In the following lines we are going to apply the usage of
the Ubound function with a step by
step tutorial for beginners. It is advisable for you follow the steps and do it
While assuming that you know how to use the VBA/Excel Editor (If not, refer to the following article: How to create a procedure in Excel/VBA from the same author), let’s start:
Activity Number 1: One-Dimensional Array
For a start we shall create a procedure called “MyFirstArray" in the Module1 of the VBA Editor and write the following code:
Dim seasons(0 To 3) As String
Dim HighestIndex As Integer
HighestIndex = UBound(seasons)
MsgBox "The Highest Index is " & HighestIndex
After running the above VBA code, you should have the following:
Activity Number 2: Two-Dimensional Array
Let’s imagine an array called “prices" that has to store the prices of two goods (Good A and Good B). The code will be as follows:
Dim prices(0 To 10, 0 To 100) As Double
Dim PricesGoodA As Interger
Dim PricesGoodB As Interger
PricesGoodA = UBound(prices, 1)
PricesGoodB = UBound(prices, 2)
Note the use of an additional argument in the multi dimensional array indicating the column number in the array. A Two-dimensional array has 2 columns.
After running the code, the variable PricesGoodA has the value 10 and the variable PricesGoodB has the value 100.