The VBA LBound function, short for Lower Bound, is a built-in function used to return the smallest subscript for a specified array or collection. It is commonly used in VBA programming to determine the lower bound of an array or collection, which is the first index or position of an array.
VBA LBound Function – Purpose, Syntax and Arguments
Purpose:
The main purpose of the LBound function is to determine the starting point or lower bound of an array or collection in a VBA program. It is helpful in cases where you need to loop or manipulate data in an array, as it allows you to know the beginning index of the array.
Syntax:
LBound (array, [dimension])
The LBound function takes in two arguments: array and dimension, where array is a required argument and dimension is an optional argument.
Arguments:
- array: The array for which you want to determine the lower bound.
- dimension: (Optional) An integer value specifying the dimension for which you want to return the lower bound. If omitted, the function will return the lower bound of the first dimension.
Example:
Let’s say we have an array named numArray with values from 1 to 5. The lower bound of this array, by default, will be 0. To change this and get the actual lower bound, we can use the LBound function in the following way:
Sub LBoundExample() Dim numArray(1 To 5) As Integer Dim lowerBound As Integer lowerBound = LBound(numArray) MsgBox "The lower bound is " & lowerBound End Sub
In this example, the value of lowerBound will be 1, which is the first index of the array. This can be helpful if we need to loop through the numArray from the start.
Remarks:
- The LBound function only returns the lower bound of the first dimension of an array. If the specified array is a multidimensional array, you will need to use another LBound function to return the lower bound of other dimensions.
- The LBound function is frequently used with the UBound function, which returns the upper bound or last index of an array.
- The lower bound of an array can be set using the Option Base statement. By default, the lower bound of an array is 0, but it can be changed to 1 or any other value using the Option Base statement.
Important Notes:
- The LBound function is a useful tool for working with arrays in VBA. Knowing the lower bound of an array can help in looping through arrays, accessing specific elements, and performing other operations.
- Always make sure to specify the correct dimension when using the LBound function, especially when working with multidimensional arrays. Providing the wrong dimension can result in errors or incorrect results.
Understanding VBA LBound Function with Examples
Example 1: Basic Syntax of LBound
Dim list(3 To 10) As Integer Print LBound(list)
Output: 3
The LBound function is used to find the lower bound of an array, which is the index number of the first element in the array. In this example, the array “list” has been declared with a range of 3 to 10, with 3 being the lower bound. The LBound function returns the value of 3, as it is the starting index of the array.
Example 2: Using LBound with Multi-dimensional Arrays
Dim matrix(1 To 3, 1 To 3) As Integer Print LBound(matrix, 1)
Output: 1
In this example, we have a multi-dimensional array “matrix” with two dimensions, 1 to 3. The LBound function is used to find the lower bound of the first dimension of the array, which is 1. This means the first element in the first dimension of the array is at index 1.
Example 3: Using LBound with Dynamic Arrays
Dim list() As Integer ReDim list(1 To 5) Print LBound(list)
Output: 1
Dynamic arrays are those that are not given a fixed size during declaration. In this example, the array “list” has been declared with the ReDim statement, with a lower bound of 1 and upper bound of 5. The LBound function returns the lower bound of 1, despite not being explicitly specified in the declaration. This is because the default starting index of an array is always 0, unless specified otherwise.
Example 4: Finding the Lower Bound of an Empty Array
Dim list() As Integer Print LBound(list)
Output: 0
If an array is declared but not initialized with any elements, the LBound function returns the default lower bound value of 0. This is because there are no elements to determine the lower bound of the array.
Example 5: Using LBound with Strings
Dim name As String name = "John" Print LBound(name)
Output: 1
Contrary to arrays, the LBound function does not work with strings to find the starting index. In this example, the string “name” has been declared with the name “John”. The LBound function returns a value of 1, which is the starting index of the first character in the string.
Example 6: Using LBound to Loop Through an Array
Dim list(1 To 5) As Integer
For i = LBound(list) To UBound(list)
list(i) = i
Next i
Print list(2)
Output: 2
The LBound function is often used in conjunction with the UBound function to loop through an array. In this example, a for loop is used with LBound and UBound to assign values to each element in the array. Here, the value of 2 is assigned to the 2nd element in the array, which can be seen by printing the 2nd index of the array.
Example 7: Using LBound with User-defined Data Types
Type student
name As String
age As Integer
End Type
Dim roster(2) As student
Print LBound(roster)
Output: 0
The LBound function can also be used for user-defined data types. In this example, a user-defined data type “student” is declared with two elements, “name” and “age”. An array “roster” is then declared with a range of 2, which will hold two student records. The LBound function returns the default lower bound value of 0, as there are no elements in the array yet.
Example 8: Using LBound with User-defined Arrays
Dim employees() As employee ReDim employees(1 To 5) Print LBound(employees)
Output: 1
Similar to user-defined data types, LBound can also be used with user-defined arrays. In this example, an array of type “employee” is declared and then resized with the ReDim statement. The LBound function returns a value of 1, which is the lower bound specified in the ReDim statement.
Example 9: Using LBound with Functions
Function findMax(list() As Integer)
Dim maximum As Integer
maximum = list(LBound(list))
For i = LBound(list) + 1 To UBound(list)
If list(i) > maximum Then
maximum = list(i)
End If
Next i
findMax = maximum
End Function
Dim numbers(1 To 5) As Integer
For i = 1 To 5
numbers(i) = i * 10
Next i
Print findMax(numbers)
Output: 50
The LBound function can also be used in functions to define the starting index for looping through arrays. In this example, the function “findMax” takes in an array and uses the LBound function to initialize the maximum value. The function then loops through the array, starting at the lower bound and compares each element to the maximum value. The LBound function allows for the flexibility of using different array sizes in the function.
Example 10: Using LBound with Named Parameters
Sub printNames(firstNames() As String, Optional ByVal count As Integer)
For i = LBound(firstNames) To UBound(firstNames)
Print firstNames(i)
Next i
End Sub
Dim names(1 To 3) As String
names(1) = "John"
names(2) = "Jane"
names(3) = "James"
printNames firstNames:=names, count:=3
Output: John Jane James
When using named parameters, the LBound function can be used to define the starting index for the array. In this example, the sub procedure “printNames” takes in a dynamic array of first names and an optional parameter “count”. The LBound function is used in the for loop to start at the lower bound of the array, and the value of the parameter “count” is used as the upper bound for the loop. This allows for easier manipulation of array sizes without directly specifying the range in the for loop.
Conclusion
The LBound function in VBA is a versatile tool for finding the starting index of arrays. It can be used with different types of arrays, including dynamic, multi-dimensional, and user-defined arrays. It is often used in conjunction with the UBound function to loop through an array or to define the lower bound in functions. Knowing how to use the LBound function is essential for efficiently handling and manipulating arrays in VBA programs.
