The VBA Ubound function
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.
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 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 " &amp;amp; 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.