Loop through Array with VBA UBound

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

Make powerful macros with our free VBA Developer Kit

Tutorials like this can be complicated. That’s why we created our free VBA Developer Kit and our Big Book of Excel VBA Macros to supplement this tutorial. Grab them below and you’ll be writing powerful macros in no time.

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!

That’s all for this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form below.