VBA UBound and LBound Functions
UBound and LBound are array functions in VBA. Let us look at these, one by one.
What is UBound function in VBA
UBound and LBound are array functions in VBA. Let us look at these one by one.
The UBound is an array function that returns the largest available subscript (or upper limit) for the indicated dimension in an array.
Syntax:
LBound(arrayname, [ dimension ])
We will show you examples of one, two, and three dimensional arrays with UBound function.
The examples will also cover how to get array size by using both functions.
An example of using UBound with one dimensional array
In this example, we declared a one-dimensional array. Then we assigned values to a few array elements.
Finally, we used a message box to display the value returned by UBound function.
VBA code:
Sub ubound_ex() Dim arr_ub(10) As String 'Creating array elements arr_ub(0) = "Red" arr_ub(1) = "Green" arr_ub(2) = "Black" arr_ub(3) = "Blue" MsgBox "Value returned by UBound: " & UBound(arr_ub) End Sub
Result:
Using two-dimensional array with UBound example
Now we declared a 2-D array and then used the UBound function.
In UBound, we passed both arguments. See what UBound returned:
Sub ubound_ex() Dim arr_ub(1 To 5, 1 To 4) As String 'Creating array elements arr_ub(1, 1) = "Red" arr_ub(2, 1) = "Green" arr_ub(3, 1) = "Black" arr_ub(4, 1) = "Blue" MsgBox "Value returned by 2nd-dimension UBound: " & UBound(arr_ub, 2) End Sub
Result:
As we specified 2, that tells UBound to return the largest subscript of the 2nd dimension in the array – so it returned 4.
If we omitted the second argument i.e.
MsgBox "Value returned by 2nd-dimension UBound: " & UBound(arr_ub)
Result:
So, it returned the first dimension’s subscript.
Using UBound with 3-D array
Similarly, you may use the UBound with three-dimensional array and get the largest subscript for the specified dimension.
See the code and outputs as we specify different dimensions in UBound:
Sub ubound_ex() Dim arr_ub(1 To 5, 1 To 3, 1 To 2) As String 'Creating array elements arr_ub(1, 1, 1) = "Red" arr_ub(2, 1, 1) = "Green" arr_ub(3, 1, 2) = "Black" MsgBox "Upper limit of 3rd Dimension: " & UBound(arr_ub, 3) End Sub
Output:
For:
MsgBox "Upper limit of 2nd Dimension: " & UBound(arr_ub, 2)
For UBound 1 or omitting the second argument i.e.
MsgBox "Upper limit of First Dimension: " & UBound(arr_ub)
LBound function in VBA
The LBound function returns the smallest subscript (lowest limit) of the array.
In the optional argument, you may specify which dimension to get the lower limit.
Syntax:
LBound(arrayname, [ dimension ])
An example of using LBound with array
We declared a one-dimensional array and used the LBound to get lower subscript as below:
VBA code:
Sub lbound_ex() Dim arr_lb(3 To 8) As String 'Creating array elements arr_lb(3) = "Python" arr_lb(4) = "Java" arr_lb(5) = "C++" MsgBox "Lower limit of array: " & LBound(arr_lb) End Sub
Output:
Using LBound with 2-D array
Getting the lower subscript for the first and second dimensions of the array as follows:
Sub lbound_ex() Dim arr_lb(3 To 8, 1 To 3) As String 'Creating array elements arr_lb(3, 1) = "Python" arr_lb(4, 2) = "Java" arr_lb(5, 3) = "C++" MsgBox "Lower limit of 2nd-dimension: " & LBound(arr_lb, 2) End Sub
Output:
If you specify 1 or omit second arguments, it returns 3 (lower subscript for the first dimension).
Using LBound and UBound to get number of elements in the array
The following example returns the array size by using both LBound and UBound functions.
Code:
Sub lbound_ex() Dim arr_flowers(2 To 7) As String Dim arrSize As Integer 'Creating array elements arr_flowers(2) = "Red Rose" arr_flowers(3) = "Daisy" arr_flowers(4) = "Pink Rose" arr_flowers(5) = "White Rose" arr_flowers(6) = "Sun Flower" 'Display Array Length arrSize = UBound(arr_flowers) - LBound(arr_flowers) + 1 MsgBox "The Array has " & arrSize & " Elements." End Sub
Result:
You may learn more about how to get array size in VBA. There you can also see 2-D array size example and how it works.
An example of using LBound and UBound with Excel cells/Range
In this example, we will create a 2-D array by using Excel cell’s data.
Then we used UBound and LBound to get the array size.
VBA Code:
Sub lbound_ubound_ex() 'Creating a 2D array Dim arr_products(3 To 6, 2 To 3) As String Dim x As Integer, y As Integer Dim i As Integer, j As Integer, k As Integer 'Assigning cell values as array element values For x = 3 To 6 For y = 2 To 3 arr_products(x, y) = Cells(x, y).Value Next y Next x 'Displaying whole array in Message Box For i = 3 To UBound(arr_products) Str_Prods = Str_Prods & arr_products(i, 2) & " " & arr_products(i, 3) & vbNewLine Next i 'Display cells/element MsgBox Str_Prods 'Using UBound and LBound to get array length i = UBound(arr_products, 1) - LBound(arr_products, 1) + 1 j = UBound(arr_products, 2) - LBound(arr_products, 2) + 1 k = i * j 'Display array length MsgBox "2-D Array has " & k & " elements." End Sub
Outputs:
and array size: