VBA UBound and LBound Functions

UBound and LBound are array functions in VBA.

Let us look at these one by one.

Syntax:

We will show you examples of one, two, and three-dimensional arrays with the 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 2^nd 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

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:

For UBound 1 or omitting the second argument i.e.

LBound function in VBA

In the optional argument, you may specify which dimension to get the lower limit.

Syntax:

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:

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:

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 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: