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

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.

#### Discover how this Nuclear Engineer Mastered Excel VBA

And why you should, too

##### Your time is valuable. It's time to become a VBA expert.

#### 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.*