How to use LBound function in VBA?

In VBA, the LBound function returns the smallest available subscript for the indicated dimension of an array. This is particularly useful when you’re dealing with arrays whose lower boundary isn’t necessarily 0, or when working with dynamically allocated arrays where the lower boundary could have been set to a different number using the Option Base statement or the ReDim statement. Here’s the syntax for the LBound function:

LBound(arrayName, [dimension])
arrayName: The name of the array variable for which you want to find the lower boundary. dimension (optional): A numeric expression that specifies the dimension of the array you want. If this parameter is omitted, 1 is assumed, which is the first dimension. ### Example of using LBound:

Sub ExampleOfLBound()

    Dim myArray() As Integer
    Dim i As Integer
    ' Use ReDim to initially create an array from 1 to 5
    ReDim myArray(1 To 5)
    ' Loop from the lower boundary to the upper boundary of the array
    For i = LBound(myArray) To UBound(myArray)
        ' Populate the array with the square of the index
        myArray(i) = i * i
        ' Print the value to the Immediate Window (Ctrl + G to view)
        Debug.Print "Element " & i & ": " & myArray(i)
    Next i
End Sub
If you run the above macro, it will output the squared values of the indices 1 through 5 in the Immediate Window in the VBA editor: Element 1: 1 Element 2: 4 Element 3: 9 Element 4: 16 Element 5: 25 Remember, LBound is most valuable when the lower bound of the array is not known or when the array isn’t necessarily starting at 0. If you’re working with multidimensional arrays, you can specify which dimension’s lower boundary you want to get:

' For a 2-dimensional array
lowerBoundFirstDimension = LBound(my2DArray, 1)
lowerBoundSecondDimension = LBound(my2DArray, 2)
By using LBound and UBound together, you can ensure that your code will work with arrays of any base without hard-coding the indices.

Unlock Your Potential


Basic - Advanced


Access Basic - Advanced

Power BI

Power BI Basic - Advanced

Help us grow the project

Leave a Reply

Your email address will not be published. Required fields are marked *