Description

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 and to check if it is empty.

Syntax

The syntax of the UBound function is: UBound( ArrayName, [Dimension] )

Arguments

The UBound function has two arguments which are “ArrayName” and “Dimension”

ArrayName

 It is a required argument referring to the array for which you want to find the highest subscript.

Dimension

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

 or

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 yourself.

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:

Sub MyFirstArray()
Dim seasons(0 To 3) As String
Dim HighestIndex As Integer
HighestIndex = UBound(seasons)
MsgBox "The Highest Index is " & HighestIndex
End Sub

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:

Sub MySecondArray()
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)
	Debug.Print PricesGoodA
	Debug.Print PricesGoodB
End Sub

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.