Quick Jump
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.

Let’s get right to it! Oh, and feel free to subscribe to my email list for more Mastering VBA content.


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 strCountries and populated its elements with names of countries.

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:

UBound(NameOfArray, [Dimension])

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.


Application Ideas

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!


Comments

It’s your turn! Leave a comment below, subscribe to my email list, share this article on social media and follow me on Google+ and Twitter for more great VBA content.


wellsrPRO

Coming Fall 2017

Auto-Import Macros Directly from wellsr.com

Absolutely FREE when you join the waitlist


About Ryan Wells


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.