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.
The syntax for the `UBound` function is as follows:
UBound(arrayName, [dimension])
Where:
- `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.
Here’s an example of using `UBound` with a one-dimensional array:
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
Here’s an example of using `UBound` with a two-dimensional array, where we specify which dimension we’re interested in:
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.