Introduction | Example | Tutorial | Applications | Comments
Introduction - VBA UBound
Use the VBA UBound function to loop through all the elements in an array. The VBA UBound function returns the maximum subscript, or index, of a particular dimension of an array. In most cases, this essentially yields the size of your array.
Whenever you make an array in VBA, you undoubtedly want to eventually loop through each item in that array. The beauty of ubound is that once you know the size of your array, you know how many elements you need to loop through. Place the ubound function in the upper bound of your For Loop, and you’re ready to loop through your VBA array.
Looping through the elements in an array is an essential skill, but it just scratches the surface of what you can do with VBA arrays. To become really good at using arrays, you’ll need to grab a copy of our comprehensive VBA Arrays Cheat Sheet with over 20 pre-built macros and dozens of tips designed to make it easy for you to handle arrays.
Let’s get right to it!
Example - VBA UBound
Sub vba_ubound() Dim strCountries(4) As String Dim i As Integer strCountries(0) = "United States" strCountries(1) = "India" strCountries(2) = "United Kingdom" strCountries(3) = "Germany" strCountries(4) = "Canada" For i = LBound(strCountries) To UBound(strCountries) 'looping through array here MsgBox strCountries(i) Next i End Sub
Tutorial - VBA UBound
In our example, we created an array of strings called
After that, we used a For Loop to loop from the smallest element (lower bound) to the largest element (upper bound) of the array. In this example, that means we looped from element 0 to element 4.
Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I'm giving away my personal macro library for free. This powerful gift lets you automatically import all my macros directly into your spreadsheet with just one click.
You have to use your imagination in this example because it’s obvious how many elements we had in our array. Imagine, however, that you had a vba array with variable or dynamic dimensions and you populated the array based on contents from your spreadsheet or a userform. In that scenario, the UBound functions becomes more important.
I know we’re not here to talk about the LBound function, but since I used it, it deserves a brief mention. Suffice it to say that by default LBound returns a value of 0. If you change your starting subscript, LBound will return a different value. Think of LBound as the opposite of UBound.
So what exactly does UBound do and how does it work? Oh, I’m glad you asked! UBound returns the highest subscript in your vba array. In other words, it determines the upper bound of your array. That’s what makes it so useful for including in a For Loop.
VBA UBound Syntax
The VBA UBound function accepts two arguments, the second of which is optional:
If you have a multidimensional array, UBound lets you choose which dimension you want to find the max subscript for. That’s what the optional second argument is for.
Let’s go through a few more examples
More UBound Examples
Pretend you have an array which you’ve defined with the following dimensions:
Dim arr(-1 To 2, 5) As String
This array has 2 dimensions AND its first dimension starts from -1 instead of the default of 0. First, we want to find the upper bound of the first dimension. We can type:
msgbox UBound(arr, 1)
The VBA UBound function yields a result of 2.
Next, we want to find the upper bound of the second dimension. We can type:
msgbox UBound(arr, 2)
The UBound function yields a result of 5. It’s pretty simple. The LBound function behaves the same way.
VBA Loop Through Multidimensional Array
We’ll end this tutorial with an example that loops through each dimension of a multidimensional array by using the LBound and UBound VBA functions.
Sub VBALoopThroughArray() Dim arr(3, -5 To 5) As String Dim i As Integer, j As Integer 'Populate array here with your own code For i = LBound(arr, 1) To UBound(arr, 1) For j = LBound(arr, 2) To UBound(arr, 2) 'Place your array handling code here MsgBox arr(i, j) Next j Next i End Sub
This example loops through each item of your two dimensional array and displays each item in a message box.
The ability to effortlessly navigate arrays is an important skill if you want to Master VBA. I do it all the time in my nuclear engineering job. I regularly write VBA scripts that populate two dimensional arrays with control blades or fuel assembly maps.
I’ll loop through each element in my array, look for a match, then exit the For Loop with the
Exit For command.
If you think hard about it, I’m sure you can think of similar applications. Using UBound takes the guess work out of your macro. You know what the max subscript of your array is, even if it’s dynamically defined!
It’s your turn! Leave a comment below, subscribe to my email list, share this article on social media and follow me on Twitter for more great VBA content. Check out our VBA Q&A community if you still have questions or want to learn more.