VBA UBound & LBound Functions

UBound Description

Returns the highest subscript for a dimension of an array.

Simple UBound Examples

Sub UBound_Example()
    Dim a(3 To 10) As Integer
    MsgBox UBound(a)
End Sub

Result: 10

UBound Syntax

UBound(ArrayName, [ Dimension ])

The UBound function contains 2 arguments:

ArrayName: Name of Array variable.

Dimension: [Optional] Integer indicating which dimension’s upper bound is returned. Use 1 for the first dimension, 2 for the second, etc. 1 if ommitted.

Examples of Excel VBA UBound Function

Sub UBound_Example1()
    Dim arrValue(1 To 5, 4 To 8, 12 To 25)
    MsgBox UBound(arrValue)
    MsgBox UBound(arrValue, 1)
    MsgBox UBound(arrValue, 2)
    MsgBox UBound(arrValue, 3)
End Sub

Result: 5, 5, 8, 25

LBound Description

Returns the lowest subscript for a dimension of an array.

Simple LBound Examples

Sub LBound_Example()
    Dim a(3 To 10) As Integer
    MsgBox LBound(a)
End Sub

Result: 3

LBound Syntax

LBound(ArrayName, [ Dimension ])

The LBound function contains 2 arguments:

ArrayName: Name of Array variable.

Dimension: [Optional] Integer indicating which dimension’s lower bound is returned. Use 1 for the first dimension, 2 for the second, etc. 1 if ommitted.

Examples of Excel VBA LBound Function

Sub LBound_Example1()
    Dim arrValue(1 To 5, 4 To 8, 12 To 25)
    MsgBox LBound(arrValue)
    MsgBox LBound(arrValue, 1)
    MsgBox LBound(arrValue, 2)
    MsgBox LBound(arrValue, 3)
End Sub

Result: 1, 1, 4, 12