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.
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.
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!
Get these macros delivered right to your spreadsheet for free
Access tutorials and import my macros without ever leaving ExcelShow me - It's free
About Ryan Wells
Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.Follow