# 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 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**

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: