How to use UBound function in VBA?

In VBA (Visual Basic for Applications), the UBound function is used to retrieve the highest available index number of an array (the upper boundary of the array). This function is particularly useful when you’re working with arrays of unknown size or when you want to iterate through the entire array without explicitly knowing the number of elements it contains.

Basic syntax


UBound(arrayName, [dimension])

arrayName is the name of the array variable.
dimension is optional. It specifies which dimension’s upper bound you want if you’re working with a multi-dimensional array. The first dimension is 1, the second is 2, and so on. If omitted, the default dimension is 1.

UBound with a one-dimensional array:

VBA


Sub UBoundExample()
    Dim myArray() As Integer
    Dim i As Integer
    
    ' Let's imagine you have populated the array earlier in your code
    ' and you don't know its exact size.
    ' For demonstration purposes, let's populate it with some sample data:
    myArray = Array(1, 2, 3, 4, 5)
    
    ' Using UBound to get the maximum index of the array
    For i = 0 To UBound(myArray)
        Debug.Print myArray(i)
    Next i
End Sub

UBound with a two-dimensional array

VBA


Sub UBoundExampleMultiDimensional()
    Dim myArray(1 To 3, 1 To 5) As Integer
    Dim i As Integer, j As Integer
    
    ' Populate the 2-dimensional array with some values.
    ' Normally, you'd have some logic to do this, but for this example, we'll just assign directly.
    myArray(1, 1) = 11
    myArray(1, 2) = 12
    ' ... other elements would be populated here.

    ' Using UBound to iterate through the first dimension
    For i = LBound(myArray, 1) To UBound(myArray, 1)
        ' Using UBound again to iterate through the second dimension
        For j = LBound(myArray, 2) To UBound(myArray, 2)
            Debug.Print myArray(i, j)
        Next j
    Next i
End Sub

In this example, UBound(myArray, 1) gives the upper bound of the first dimension and UBound(myArray, 2) gives the upper bound of the second dimension. We also use LBound to get the lower bound of the array dimensions, which is typically 0 for zero-based arrays and 1 for arrays declared with a specific lower bound. Remember that arrays in VBA can have their lower index set to a number other than zero using the To keyword in the declaration, as demonstrated with the two-dimensional array above (Dim myArray(1 To 3, 1 To 5)). Be sure to account for this when using loops to iterate over an array’s elements.

facebooklinkedin