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.


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



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



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)




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




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.


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



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



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.


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



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



and array size: