VBA LBound

What is Excel VBA LBOUND Function?

VBA LBOUND stands for “Lower Bound,” i.e., it is an array function that is used to find the starting position of an array. We have two array functions, i.e., UBOUND and LBOUND.

These two functions are used to find the size of an array, i.e., the starting and the ending position of an array. Finding the positions of an array helps us initiate the loop and exit it based on the size of an array. For example, look at the following code.Dim MyArray (2 to 10) as String

We have defined an array “MyArray” variable, and we can use the LBOUND to find the position of the array, i.e., 2.

Syntax of Excel VBA LBOUND Function

The following is the syntax of the VBA LBOUND function.

LBound( Array Name, [Dimension])

• Array Name: Here, we must provide the array name for which we are finding the lowest subscript, i.e., the lowest position of an array.
• [Dimension]: This is an optional argument. Here, we can use the integer value to determine the dimension of the array. If we omit this argument, one will be the default value.

How to Use the VBA LBOUND Function?

Let us show you the step-by-step approach to using the VBA LBOUND function.  Follow the steps below.

Step 1: Start the sub-procedure by naming the macro.

Step 2: Define a variable of the String VBA data type.

Step 3: To use the LBOUND function, we must make the defined variable as an array variable. Hence, assign a size to the defined variable after the variable name within the parenthesis as follows.

Step 4: Now, we can use the LBOUND function to find the lowest position of the array variable. The VBA LBOUND function returns an output; hence, define another variable to assign the VBA LBOUND function return value to the variable.

Step 5: Assign the VBA LBOUND function to the second variable declared as Integer.

Step 6: Inside the VBA LBOUND function, pass the array variable name, i.e., “MyVariable.”

Note: Ignore the LBOUND dimension argument because the array defined is one dimension. Hence, by default, it takes one as dimension argument input.

Step 7: At last, let’s showcase the value of the LBOUND output value variable in a message box.

The complete code is given below for your reference.

Sub VBA_LBOUND()

    Dim MyVariable(1 To 6) As String

    Dim Lbound_Value As Integer

    Lbound_Value = LBound(MyVariable)

    MsgBox Lbound_Value

End Sub

When you execute this code, you will get the following result.

The result of the VBA LBOUND function is one because the array variable starts with the lowest range of 1. Now, let’s change the array size from “1 to 6” to “3 to 6.”

Sub VBA_LBOUND()

    Dim MyVariable(3 To 6) As String

    Dim Lbound_Value As Integer

    Lbound_Value = LBound(MyVariable)

    MsgBox Lbound_Value

End Sub

When you execute this code, you get

Now, the lowest range is 3. Hence, the VBA LBOUND function retrieved the array starting range as 3.

Examples of VBA LBOUND Function

Example #1 – Find the Lowest Position of the Array

When we declare an array variable, we usually assign a size to it. In this example, let us find the starting position of the array variable.

For example, look at the following code.

The typical size of an array variable declared in VBA thus is “1 to 8”. However, the size of the array is different.

Sub VBA_LBOUND_EX1()

    Dim MyVariable(8) As String

    Dim Lbound_Value As Integer

    Lbound_Value = LBound(MyVariable)

    MsgBox Lbound_Value

End Sub

Let’s use the VBA LBOUND function to find the starting position of the array variable.

Now, execute the code. You will see the following starting position of the array variable.

We have got the result 0. It is because when defining the array variable, we have not entered the starting position; instead, we stated only the final size of the size, i.e., 8.

When we state only the final size of the array, the array’s starting position will be 0, not one, and the total size of the array will be actual size + 1, i.e., 8 + 1 = 9. Hence, the lowest bound of an array will be 0.

Example #2 – VBA LBOUND 2D Array

Working with multi-dimensional arrays is an advanced technique; hence, finding the lower bound of an array also differs in the VBA LBOUND function.

First, let’s look at how to define a multi-dimensional array. For example, look at the following data in Excel.

We have nine rows and two columns of data (excluding headers). Since the data is spread over more than 1 column, we use a multi-dimensional array. Define the array and assign it as multi-dimensional.

Now, let us use the VBA LBOUND function to find the lowest bound of the first dimension.

Sub VBA_LBOUND_EX2()

    ‘Define an array variable and assign

    Dim MyData (2 To 10, 1 To 10) As String

    Dim Lbound_Range As Integer

    ‘We have used the dimension argument as 1 to look at the first dimension VBA LBOUND range.

    Lbound_Range = LBound(MyData, 1)

    MsgBox Lbound_Range

End Sub

As shown, inside the VBA LBOUND function, we have used the dimension argument as 1 to look only into one dimension of the array. Let’s execute this code.

The array variable MyData has two dimensions, i.e., 2 to 10 and 1 to 10.

Since we are looking at one dimension with the LBOUND function, the lowest bound value is 2. Hence, the same has been returned by the LBOUND function.

Let us change the dimension argument in the code from one to two.

Sub VBA_LBOUND_EX2()

    ‘Define an array variable and assign

    Dim MyData (2 To 10, 1 To 10) As String

    Dim Lbound_Range As Integer

    ‘We have used the dimension argument as 2 to look at the first dimension VBA LBOUND range

    Lbound_Range = LBound(MyData, 2)

    MsgBox Lbound_Range

End Sub

Execute the code and see what happens.

In the second dimension, the array’s lowest bound is 1; hence, the same has been returned by the VBA LBOUND function.

Example #3 – VBA LBOUND and VBA UBOUND Together in a Loop

The power of an array function in VBA can be best exploited when we use it in conjunction with other functions inside the loop. Let’s explore more on two array functions, VBA LBOUND and VBA UBOUND.

Assume we need to loop through five integers, we must define a variable with an array size of 5 as follows.

Define another variable to initiate the loop.

Next, initiate the loop.

We have given the hard-coded start and end boundaries for the loop, i.e., 1 to 5, which is equal to the array variable size. However, if we increase or decrease the size of an array, we need to manually change the FOR NEXT loop start and end as well.

Hence, instead of giving the hardcoded numbers, we can use the VBA LBOUND UBOUND functions to determine the lowest bound and highest bound of an array.

Now, inside the loop, we can write the code as per our requirements. The following is the complete code for your reference.

Sub VBA_LBOUND_EX3()

    ‘Define an array variable with a size of 5

    ‘Assign the integer data type since we need to loop through these values

    Dim MyInteger(1 To 5) As Integer

    ‘Define a variable to initiate the loop

    Dim k As Long

    ‘Initiate the loop

    ‘Use the VBA LBOUND UBOUND functions to determine the start and end of loop

    For k = LBound(MyInteger) To UBound(MyInteger)

        MsgBox k

    Next k

End Sub

This code now will show integer values from 1 to 5 in a message box separately one after the other.

Important Things to Note

• When the array length is static, i.e., if we do not provide the complete size (1 to 10) then the array always starts from 0 and not 1.
• The default value for the dimension argument of VBA LBOUND function is 1. If we want to go to other dimensions then we need to specifically mention those dimension numbers like 2, 3, or 4.
• If the array is not sized or empty, then the default value will be 0.
• VBA LBOUND function does not work with string values and the default value will be 1.

Frequently Asked Questions (FAQs)

Recommended Articles

This has been a guide to VBA LBOUND Function. Here we learn How to Use the VBA LBOUND Function using syntax along with examples. You can learn more from the following articles –

• VBA Split String into Array
• VBA String Array
• VBA Split Function