The VBA UBound Function

Related Function:
VBA LBound


The VBA UBound function returns the highest subscript for a dimension of a supplied array.

The syntax of the function is:

UBound( ArrayName, [Dimension] )

Where the function arguments are:

ArrayName -

The array for which you want to find the highest subscript.

[Dimension] -

An optional integer, specifying the dimension of the array, for which you require the highest subscript.

If [Dimension] is omitted, it takes on the default value 1.

VBA UBound Function Examples

Example 1 - One-Dimensional Array

' Return the highest subscript of a one-dimensional array.
Dim prices(0 to 10) As Double
Dim pricesUB As Integer
pricesUB = UBound( prices )
' Now the integer pricesUB has the value 10.

After running the above VBA code, the variable pricesUB has the value 10.

Example 2 - Two-Dimensional Array

' Return the highest subscripts of each dimension a 2-d array.
Dim costs(0 to 10, 0 to 100) As Double
Dim costsUB1 As Integer
Dim costsUB2 As Integer
costsUB1 = UBound( costs, 1 )
costsUB2 = UBound( costs, 2 )
' Now, costsUB1 = 10 and costsUB2 = 100.

After running the above VBA code, the variable costsUB1 has the value 10 and the variable costsUB2 has the value 100.