VBA Function: UBound

The VBA function UBound returns the highest available index for the specified dimension of an array.

Usage:

UBound(array)

or

UBound(array, dimension)

Examples of Usage

Using the UBound function to retrieve the indices of the 2 dimensions of the array:

Sub example()

    Dim myArray(10, 4)

    'Maximum index of the first dimension
    MsgBox UBound(myArray) 'Returns: 10

    'Maximum index of the second dimension
    MsgBox UBound(myArray, 2) 'Returns: 4

End Sub

Using the UBound function to get the number of values in an array obtained by the Split function:

Sub example()

    link = "www.excel-pratique.com"

    'Splitting the string into an array
    myArray = Split(link, ".")

    'Number of elements in the array (knowing that an array starts at 0)
    number = UBound(myArray) + 1

    'Displaying this number
    MsgBox number 'Returns: 3

End Sub