VBA LBound

LBound in VBA stands for "Lower Bound." It will extract the lowest number of an array. Arrays are used to store lists of values in programming, and each value has a position number. LBound tells you the number of the first position in that list. If an array starts at position 1, LBound will return 1.

For example, if the array says "Dim ArrayCount (2 to 10) as String," then using the LBound function, we can find the least number of the array length, i.e., 2.

Below is the syntax of the LBound function. It is very simple and easy. It has only two parameters.

=LBOUND(Array name )

• Array Name: This is the first argument. For this parameter, we need to specify the array's name used to define the array.
• : It is not required if the array is a single dimension. By default, it takes one argument, or we need to supply the dimension number.

So, using these functions, we can find the minimum length of an array.

Here’s how to use the LBound function in VBA step by step, with a simple example:

Step 1: As a first step, we define an array with a lower and upper bound.

Example: Dim myArray(1 To 5) As Integer

Step 2:  Use the code LBound(arrayName) to get the lowest index of the array.

Example: Dim firstIndex As Integer

firstIndex = LBound(myArray)

Step 3: You can use MsgBox firstIndex to display the result, which will show 1 (the first index in this case).

The entire code can be seen below.

Sub LBoundExample()

  Dim myArray(1 To 5) As Integer

  Dim firstIndex As Integer

  firstIndex = LBound(myArray)

  MsgBox "The first index is: " & firstIndex

End Sub

Let us look at some examples on how to use the LBOUND function in VBA. 

Example #1

Look at the below code.

Code:

Sub LBound_Example1()  

  Dim Count(2 To 5) As Integer   

  MsgBox LBound(Count)

End Sub

In the above code, we have defined the array as integer and the array size as 2 to 5. Next, we have assigned the VBA message box to show the lowest length of the array by using the LBound function.

When we run the code, we will get the below result in a message box.

Output:

Since our array starts from 2, the LBound function determines the lowest length of the array as 2.

Example #2

Look at the below code.

Code:

Sub LBound_Example2()  

  Dim Count(5) As Integer  

  MsgBox LBound(Count)

End Sub

In the above code, we have not decided on the lowest limit. Rather, we just supplied the array length as 5 now. So, let us run the code and see the lowest length of the value.

Output:

It has returned the result as 0 because when we don't decide the start and endpoint of an array, rather provide the static number, for example, "Count (5), i.e., in this case, array value starting from 0, not from 1. So, we can store a total of 6 values in it now.

Count (0), Count(1), Count(2), Count(3), Count(4), Count(5).

Example #3

Now, we will use the data range and decide the lower bound from the range of data. For example, look at the below data image.

We will decide on the smallest and highest row size from this range.

First, define the variable as a variant.

Code:

Sub LBound_Example3()  

  Dim Rng As Variant

End Sub

Step 1: For this “Rng” variant variable, set the range reference value as “Range (“B2: B5”).Value.”

Code:

Sub LBound_Example3()  

  Dim Rng As Variant  

  Rng = Range("B2:B5").Value

End Sub

Step 2: For this range, we will find the lowest and highest array length. Next, open the message box and LBound function and supply the variable name.

Code:

Sub LBound_Example3()  

  Dim Rng As Variant  

  Rng = Range("B2:B5").Value  

  MsgBox LBound(Rng)

End Sub

Now, run the VBA code and see the lowest value from the length.

Output:

Step 3: Now, change the variable reference from B2: B5 to A2: B5.

We will find the lower and upper bound values for this range.

Code:

Sub LBound_Example3()  

  Dim Rng As Variant  

  Rng = Range("A2:B5").Value

End Sub

Step 4: Since we have more than one dimension array, we must also supply the dimension number.

Code:

Sub LBound_Example3()  

  Dim Rng As Variant  

  Rng = Range("A2:B5").Value  

  MsgBox LBound(Rng, 1)

End Sub

Step 5: To find the first column first lower bound above code will help similarly. Likewise, finding the upper bound in this first column below the code will help.

Code:

Sub LBound_Example3()  

  Dim Rng As Variant  

  Rng = Range("A2:B5").Value  

  MsgBox LBound(Rng, 1) & vbNewLine & UBound(Rng, 1)

End Sub

Step 6: It will find the first column's lower length and upper length. Similarly, write one more message box in the next line but dimension from 1 to 2.

Code:

Sub LBound_Example3()  

  Dim Rng As Variant  

  Rng = Range("A2:B5").Value  

  MsgBox LBound(Rng, 1) & vbNewLine & UBound(Rng, 1)  

  MsgBox LBound(Rng, 2) & vbNewLine & UBound(Rng, 2)

End Sub

Run the code and see the result in the message box.

Output:

For the first dimension, the lower bound is 1, and the upper bound is 4.

Click on "OK" to get the next dimension limits.

Output:

The second dimension's lower limit is 1, and the upper limit is 2.

Interested in learning more about VBA coding in detail? You can check out our Advanced VBA course here. 

• The LBound function returns the minimum length from the array.
• When the array length is static, i.e., a single number, the array always starts from the number 0, not from 1.
• In the case of a multi-dimensional array, we need to specify the dimension number.
• For dynamic arrays, LBound can only be used after the array is initialized with ReDim or assigned values.