Do you need to find the lower limit and upper limit of a given array in excel VBA? In this post, you will learn how to find the lower limit and upper limit and values of a given array using LBound and UBound in VBA for your projects.
The VBA LBound and UBound functions return the size of an array. Use LBound function to find the lower limit of an array and the UBound function to find the upper limit.
These functions are returns only the array index not the values stored in the array
LBound Syntax
LBound(Array, [ Dimension ]) – returns lower limit of given array
Array: Name of Array variable arrValue
Dimension: [Optional] Integer, default 1 if committed.
UBound Syntax
UBound(Array, [ Dimension ]) – returns upper limit of given array
Array: Name of Array variable arrValue
Dimension: [Optional] Integer, default 1 if committed.
Example 1: If you assigned the below array in VBA
Sub LBoundUBound_1()
Dim arrValue(1 To 25)
Debug.Print "----------- OUTPUT -----------------"
Debug.Print "LBound Return :" & LBound(arrValue)
Debug.Print "UBound Return :" & UBound(arrValue)
End Sub
Copy and Paste the above VBA script into your editor and run the script
You can see the output in the immediate window. It returns the lower limit 1 and the upper limit 25.
Example 2: If assigned the values 35 to the lower limit 1 and 55 to the upper limit 25.
Sub LBoundUBound_1()
Dim arrValue(1 To 25)
arrValue(1) = 35
arrValue(25) = 55
Debug.Print "----------- OUTPUT -----------------"
Debug.Print "LBound Index :" & LBound(arrValue) & "; Value :" & arrValue(1)
Debug.Print "UBound Index :" & UBound(arrValue) & "; Value :" & arrValue(25)
End Sub
Copy and Paste the above VBA script into your editor and run the script
You can see the output in the immediate window. It returns the lower limit 1 and the upper limit 25 along with the assigned values to the respective indexes.
Multi-dimensional array
how to use LBound and UBound functions in VBA for the multi-dimensional arrays.
Declaring arrValue multi-dimensional array size of 10 rows and 2 columns
Sub LBoundUBound_2()
'Declaring variables
Dim arrValue(1 To 25, 1 To 2) As String, numOfRow As Integer, numOfCol As Integer, numOfElements As Integer
'Compute Number of Rows, Columns using UBound and LBound function, to find the Number of elements just multiply both numOfRow and numOfCol variable
numOfRow = UBound(arrValue, 1) - LBound(arrValue, 1) + 1
numOfCol = UBound(arrValue, 2) - LBound(arrValue, 2) + 1
numOfElements = numOfRow * numOfCol
'output to print the number of elements
Debug.Print "Array has " & numOfElements & " element(s)."
End Sub
Copy and Paste the above VBA script into your editor and run the script
Conclusions
As an excel user, you definitely use these functions when you automate your reports or create dashboards. In market research when you create a list of countries or categories to display in a control you need these functions to find the lower and upper limit of the array.
Let me know in the comments if you need any articles in excel VBA for your reports or projects.
