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
Excel
Access
Power BI
