VBA LBound Function

The Excel VBA LBound function returns a Long numeric value of the lower bounds (smallest subscript) of a VBA Array. This function is useful as in VBA you can define an array to start at any lower bound instead of 0 as in most programming languages.

VBA Reference – Array Functions

VBA LBound Function Syntax

LBound ( arrayname, [ dimension ])

Parameters

arrayname
The name of the array variable (Variant data type).
dimension
Optional. The index number of the dimension for which the lower bounds are to be returned.

Example usage of VBA LBound function

Using LBound with single dimension arrays

In below example we will create 2 static VBA Arrays. The first array will be defined without explicitly setting it’s lower bounds. The second array will be defined with both lower and upper bounds.

 
Dim arr1(8) As Variant 'Array of size 9 -> 0 to 8
Debug.Print LBound(arr1) 'Result: 0
    
Dim arr2(2 To 10) As Variant 'Array of size 9 -> 2 to 10
Debug.Print LBound(arr2) 'Result: 2

LBound for multi-dimension arrays

In case dealing with multi-dimensional arrays you can use the dimension parameter to define for which dimension you want to obtains the VBA Array lower bounds:

 
Dim arr1(8, 1) As Variant '2 Dimensional array of size 9x2. 0-8x0-1
Debug.Print LBound(arr1,1) 'Result: 0
Debug.Print LBound(arr1,2) 'Result: 0

Dim arr2(2 To 10, 1 To 2) As Variant '2 Dimensional array of size 9x2. 2-10x1-2
Debug.Print LBound(arr2,1) 'Result: 2
Debug.Print LBound(arr2,2) 'Result: 1

Changing the default Array lower bounds

To reduce confusion around what is the lower bound of an array you can use the Options Base directive to set the default VBA Array default lower bounds. Let us revisit the code above:

Options Base 1 'Added directive. Now all arrays by default start subscript at 1

Sub Test()
  Dim arr1(8) As Variant 'Array of size 8 -> 1 to 8
  Debug.Print LBound(arr1) 'Result: 1. Changed to default of 1 from the previous 0 as declared implicitly.
    
  Dim arr2(2 To 10) As Variant 'Array of size 9 -> 2 to 10
  Debug.Print LBound(arr2) 'Result: 2. This hasn't changed as was set explicitly.
End Sub

Analyst Cave