EDUCBA

Home VBA VBA Resources VBA Array Functions VBA LBound
 

VBA LBound

Madhuri Thakur
Article byMadhuri Thakur

VBA LBound

Excel VBA LBound

VBA LBound is used for knowing the lower limits of an array and UBound is used to determine the upper limit of an array and when these both functions are used together we get the size of an array. When we use multiple values in a single variable it is known as an array. Every array has its own size and range. There is a lower range and there is an upper range. The functions which are used to determine the size of an array is called LBound.

 

 

Syntax:

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

LBound (Array Name, Dimension)

Out of those two arguments we know that the array name is the name of the array variable and dimension is a long type argument which is provided to the function where we will find the lower limit like for the first dimension we use 1 and for second dimension we use 2 and so on.

How to Use the LBound Function in VBA?

It takes two arguments to use the LBound function. The first argument is the name of the array variable while the second argument is the dimension of the array. The value returned by the function is long which is the smallest available limit provided in the array.

We will learn how to use the LBound Function using the VBA code in Excel.

You can download this VBA LBound Excel Template here – VBA LBound Excel Template

Example #1

Let us first use a very basic example on how we can evaluate the lowest limit of an array using the LBound function. For this, follow the below steps:

Step 1: Insert a new module inside Visual Basic Editor (VBE). Click on Insert tab > select Module.

Insert Module Example1-1

Step 2: Insert a new subprocedure.

Code:

Sub Example1()

End Sub

VBA LBound Example 1-2

Step 3: Now let us declare an array.

Code:

Sub Example1()

Dim A(1 To 5) As Integer

End Sub

Example 1-3 (Declare Aarray)

Step 4: Now Declare a variable where we will store the output of the lower limit of the array.

Code:

Sub Example1()

Dim A(1 To 5) As Integer
Dim B As Long

End Sub

Example 1-4 (Lower Limit)

Step 5: Now we will use the LBound function to store the lower limit of the array and store the value in B.

Code:

Sub Example1()

Dim A(1 To 5) As Integer
Dim B As Long
B = LBound(A, 1)

End Sub

VBA LBound Example 1-5

Step 6: Now we can use the Msgbox function to display.

Code:

Sub Example1()

Dim A(1 To 5) As Integer
Dim B As Long
B = LBound(A, 1)
MsgBox B

End Sub

Msgbox function Example 1-6

Step 7: Run this code by hitting the F5 key or press the Run button which is placed on the topmost ribbon of VBE.

VBA LBound Example 1-7

Example #2

Let us see another example where we provide the dimension starting from 0 to any range. For this, follow the below steps:

Step 1: In the same module let us declare another subprocedure.

Code:

Sub Example2()

Dim A(0 To 10) As Integer

End Sub

VBA LBound Example 2-1

Step 2: Now, declare an array variable.

Code:

Sub Example2()

Dim A(0 To 10) As Integer

End Sub

VBA LBound Example 2-2

Step 3: And we will declare another variable as long so that we can store the value from the result.

Code:

Sub Example2()

Dim A(0 To 10) As Integer
Dim B As Long

End Sub

VBA LBound Example 2-3

Step 4: Now we will use LBound function with lower bound as 0 in dimension.

Code:

Sub Example2()

Dim A(0 To 10) As Integer
Dim B As Long
B = LBound(A, 0)

End Sub

VBA LBound Example 2-4

Step 5: Now we will use the Msgbox function to display the result as shown below.

Code:

Sub Example2()

Dim A(0 To 10) As Integer
Dim B As Long
B = LBound(A, 0)
MsgBox B

End Sub

Msgbox function Example 2-5

Step 6: Run this code by hitting F5 or Run button.

VBA LBound Example 2-6

We encountered this error because 0 can be only base when the array is not multidimensional.

Example #3

Now let us try to skip the lower range in the array and see the result in this example. For this, follow the below steps:

Step 1: In the same module let us declare another subprocedure.

Code:

Sub Example3()

End Sub

VBA LBound Example 3-1

Step 2: Now let us declare an array variable.

Code:

Sub Example3()

Dim A(5) As Integer

End Sub

Example 3-2 (Declare Array)

Step 3: And we will declare another variable as long so that we can store the value from the result.

Code:

Sub Example3()

Dim A(5) As Integer
Dim B As Long

End Sub

VBA LBound Example 3-3

Step 4: Now we will use LBound function with lower bound as 0 in dimension.

Code:

Sub Example3()

Dim A(5) As Integer
Dim B As Long
B = LBound(A)

End Sub

VBA LBound Example 3-4

Step 5: Now we will use the Msgbox function to display the result as shown below.

Code:

Sub Example3()

Dim A(5) As Integer
Dim B As Long
B = LBound(A)
MsgBox B

End Sub

Msgbox function Example 3-5

Step 6: Execute the above code by hitting the F5 or Run button.

VBA LBound Example 3-6

Example #4

Now let us try in another example where we will provide an array with multiple sizes. For this, follow the below steps:

Step 1: Now in the same module we can begin with subprocedure.

Code:

Sub Example4()

Dim A(1 To 10, 5 To 15, 10 To 20) As Integer

End Sub

VBA LBound Example 4-2

Step 2: Now we can use simple msgbox function to use Lbound function as follows.

Code:

Sub Example4()

Dim A(1 To 10, 5 To 15, 10 To 20) As Integer
MsgBox LBound(A, 3)

End Sub

Msgbox function Example 4-3

Step 3: Execute the above code by hitting the F5 or Run button.

VBA LBound Example 4-4

Explanation of VBA LBound:

L Bound function as discussed above is used to identify the lower limit of the array.  Like we saw if the array is A[ 1 to 10] and we use l bound (a,1) we will get the output as 1. Because 1 is the lower limit of the array. If we do not provide the dimension we will get the output as 0 or 1 because it totally depends upon the option base.

Things to Remember

There are few things which we need to remember about L Bound function in VBA and they are as follows:

  • LBound function is one of the functions which is used with UBound function to determine the size of the array.
  • LBound function is used to determine the lowest limit of an array.
  • This function takes two arguments.
  • Dimension as an argument is mandatory when the array is a multidimensional array.

Recommended Articles

This is a guide to the VBA LBound. Here we discuss how to use the LBound Function in Excel VBA to determine the size of the array length along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA Resize
  2. VBA Replace String
  3. VBA Login
  4. VBA Month