VBA UBound Function

The VBA UBound function is a built-in function that determines the upper bound of an array in Visual Basic for Applications (VBA). This function is commonly used to determine the size of an array, which is the maximum index number for the array. The UBound function can also be used to find the size of an array’s last dimension, in multi-dimensional arrays. This function can be very useful in manipulating array data and looping through arrays in VBA code.

VBA UBound Function – Purpose, Syntax and Arguments

Purpose:

The main purpose of the UBound function is to return the upper bound of a given array or the last index number of a specified dimension of an array. This information can then be used in array manipulation, such as looping through an array to perform certain actions on its elements. By using the UBound function, we can easily determine the size of the array and use that information in our code to make it more dynamic and adaptable to different array sizes.

Syntax:

The syntax for the UBound function is:

UBound (arrayname, [ dimension ])

– arrayname: This is a required argument that specifies the name of the array for which we want to determine the upper bound.
– dimension: This is an optional argument that specifies which dimension of the array we want to find the upper bound for. If this argument is omitted, the function will return the size of the last dimension of the array.

Arguments:

• arrayname: The name of the array for which we want to determine the upper bound.
• dimension: Optional. Specifies which dimension of the array we want to find the upper bound for.

Example:

Let’s say we have an array named myArray with five elements and we want to determine its upper bound. We can use the UBound function in the following way:

Dim myArray(5) As Integer
Dim upperBound As Integer
upperBound = UBound(myArray)
MsgBox "The upper bound of myArray is " & upperBound

In this example, the UBound function will return the value 4, representing the index of the last element in the array (since arrays in VBA are zero-based). The upperBound variable will then be assigned this value, which we can use in our code for further array manipulation.
If we want to find the upper bound of a specific dimension of a multi-dimensional array, we can provide the dimension argument in the function. For example, if we have a 2-dimensional array named my2DArray with 3 rows and 5 columns, we can determine the upper bound for the second dimension (columns) using the following code:

Dim my2DArray(3, 5) As Integer
Dim upperBound As Integer
upperBound = UBound(my2DArray, 2)
MsgBox "The upper bound for the second dimension of my2DArray is " & upperBound

In this case, the UBound function will return the value 4, indicating the last index number for the columns in the my2DArray array.

Remarks and Important Notes:

• The UBound function can only be used with arrays. Attempting to use it with any other data type will result in an error.
• When specifying a specific dimension in multi-dimensional arrays, the ‘dimension’ argument is one-based, meaning the first dimension is 1, not 0.
• If the array is not initialized or has no elements, the UBound function will return -1.
• The UBound function only returns an upper bound value and not the actual number of elements in the array. To get the number of elements in an array, we can use the ‘LBound’ function.

In conclusion, the VBA UBound function is a powerful tool for working with arrays in VBA. It allows us to easily determine the size of an array and use that information in our code to make it more flexible and dynamic. By understanding the syntax, arguments, and important notes of this function, we can effectively use it in our VBA projects to manipulate array data.

Understanding VBA UBound Function with Examples

Example 1: Using UBound with an Array

Arrays are a collection of data that are grouped together under one variable. The UBound function in VBA (Visual Basic for Applications) is used to determine the upper limit of an array, which means the highest index number of the elements in an array. This is especially useful when working with large arrays, as it allows for efficient looping through the elements. Let’s take a look at an example:

Dim myArray(10) As String
myArray(0) = "Apple"
myArray(1) = "Banana"
myArray(2) = "Orange"
myArray(3) = "Grapes"
myArray(4) = "Watermelon"
myArray(5) = "Pineapple"
myArray(6) = "Mango"
myArray(7) = "Strawberry"
myArray(8) = "Blueberry"
myArray(9) = "Raspberry"
myArray(10) = "Blackberry"
Dim count As Integer
count = UBound(myArray)
MsgBox "The upper limit of myArray is: " & count

1. The first line of code declares a new array named myArray with a size of 10.
2. The next 11 lines of code assign values to each element of the array using its index number.
3. The Dim count As Integer line declares a new variable named count that will hold the result of the UBound function.
4. The next line assigns the value of the UBound function to the count variable. This means that the value of count will be 10, since arrays in VBA start at index 0.
5. The last line uses the MsgBox function to display the result in a message box.

When running this code, the message box will display: The upper limit of myArray is: 10. This means that the highest index of the elements in the myArray variable is 10, which is the size of the array minus 1.

Example 2: Using UBound with a Dynamic Array

Dynamic arrays are arrays whose size can be changed at runtime, meaning it can grow or shrink depending on the data that is being stored in it. When working with dynamic arrays, it is necessary to use the ReDim statement to resize the array. Let’s see how we can use UBound with a dynamic array:

Dim myDynamicArray() As Integer
ReDim myDynamicArray(5)
myDynamicArray(0) = 150
myDynamicArray(1) = 200
myDynamicArray(2) = 300
myDynamicArray(3) = 400
myDynamicArray(4) = 500
myDynamicArray(5) = 600
Dim count As Integer
count = UBound(myDynamicArray)
MsgBox "The upper limit of myDynamicArray is: " & count

1. The first line of code declares a new dynamic array named myDynamicArray.
2. The ReDim statement resizes the array to a size of 5, meaning it now contains 5 elements with index numbers 0 to 4.
3. The next 6 lines of code assign values to each element of the array using its index number.
4. Similarly to the previous example, the count variable is used to store the result of the UBound function.
5. The MsgBox function displays the result in a message box, which in this case will be The upper limit of myDynamicArray is: 5.

As you can see, the UBound function works the same way with both static and dynamic arrays, as long as the array has been initialized and contains elements with assigned values.

Example 3: Using UBound with Nested Arrays

Nested arrays, also known as multidimensional arrays, are arrays that contain other arrays as its elements. In order to access the elements within a nested array, you need to specify the index number for each dimension. This is where the UBound function comes in handy. Let’s look at an example:

Dim myNestedArray(2,2) As Integer
myNestedArray(0,0) = 10
myNestedArray(0,1) = 20
myNestedArray(1,0) = 30
myNestedArray(1,1) = 40
myNestedArray(2,0) = 50
myNestedArray(2,1) = 60
Dim count As Integer
count = UBound(myNestedArray, 2)
MsgBox "The upper limit of myNestedArray is: " & count

1. In this example, we declare a nested array named myNestedArray with 3 rows and 2 columns.
2. The next 6 lines of code assign values to each element of the nested array using its index numbers for each dimension.
3. The count variable is again used to store the result of the UBound function, but this time we need to specify the second dimension of the array (which is the columns) as the second argument of the UBound function.
4. The MsgBox function displays the result in a message box, which in this case will be The upper limit of myNestedArray is: 1. This means that the highest index of the columns in the myNestedArray variable is 1.

It is important to note that when using UBound with nested arrays, the first dimension has an index of 0, while the second dimension has an index of 1.

Conclusion

The UBound function is a very useful tool when working with arrays in VBA. It allows you to quickly determine the upper limit of an array, which can then be used in loops to efficiently iterate through its elements. It is easy to use and can be applied to both static and dynamic arrays, as well as nested arrays. By understanding the UBound function, you can improve the performance of your VBA code and make your array operations more efficient.