Loop Through an Array in Excel VBA Macros

Add to Favorites

I'll show you how to loop through an array in VBA and macros in Excel.  This is a fairly simple concept but it can be a little tricky.

First, I'm assuming you already know what an array is and how to create and array in VBA [LINK to Article]. Now, that you know that, let's get to looping though arrays.

There are two main ways to loop through all of the elements of an array in VBA and both of those will be covered below.

Create the Array

First, we need to create an array, and then we can loop through it.

myarray = Array("red", "green", "blue")

Loop through Array Method 1 - LBound and UBound Method

We use this code to loop though the array:

For i = LBound(myarray) To UBound(myarray)

    MsgBox "Array value: " & myarray(i)

Next i

The MsgBox part of this code is simply what creates output so that we can see that this loop is working.

The actual loop is just this part:

For i = LBound(myarray) To UBound(myarray)

                'inside the loop

Next i

To get this loop to work with your array, you simply need to replace myarray with the name of your array.  If you want a more detailed explanation of everything, continue reading.

There are a few things going on here, so let's start first with LBound and UBound.

LBound is a function in VBA that will find the lowest point of the array.  This generates a number equal to the lowest index key for the array.  The array that you want this to work on is put within this function, in this case, myarray.

UBound is a function in VBA that will find the highest point of the array.  It outputs a number equal to the highest index key for the array. The array that you want this to work on is put within this function, in this case, myarray.

Now, we use these two functions within a basic For Next loop.

The For Next loop does this:

It sets a variable, in this case i (but it could be any name you want), equal to the LBound function's output.  Then, it says that this loop should continue running up to when it gets to the number provided by the UBound function, which will be the last index number in the array.

You need to close the loop with Next and then the variable i so that it will continue the loop, adding 1 each time.

Accessing Data from the Array within the Loop

You need to be able to get the data from the array variable and here is how you do it using this method:

myarray(i)

While inside the loop, you put the name of your array and then inside parenthesis you put i or whichever variable you used in the loop in place of i; this will get the output from the array within the loop.

Download the accompanying workbook and test out the loop to see how it works.  The result of running this loop is that three message boxes will open and show you the data that is stored in the array variable.  This loop is contained in the macro titled ArrayCreateLoop_1.

Loop through Array Method 2 - For Each ...Next Loop Method

 Using the For Each ...Next method, we use this code to loop through the array:

For Each element In myarray

    MsgBox "Array value: " & element

Next element

The name of the array is myarray and, in the middle of the loop, we have a line that starts with MsgBox that will output the contents of the array into a message box.

All you need to do to make this loop work for you is to change myarray to the name of your array variable.

If you want to learn more about the loop, keep reading.

This is the actual loop:

For Each element In myarray

                'inside the loop

Next element

This loop will go through every part of the array and put it into a variable that we supply, in this case, the element variable.

You can name this variable whatever you want, you don't have to call it "element".  If you rename this, make sure to rename it in both paces, after where it says "For Each" and after "Next" - the "Next" part of the loop is what makes this a loop and tells the code to keep going through the variable until it is finished.

Accessing Data from the Array within the Loop

This is very easy to do using this method, simply use the variable that we used in the For Each loop, in this case element.

element

Anywhere you put this in the loop, the contents of the array will appear.  You can see above how this was used in the line that starts with Msgbox.

Download the accompanying workbook and test out the loop to see how it works.  The result of running this loop is that three message boxes will open and show you the data that is stored in the array variable.  This loop is contained in the macro titled ArrayCreateLoop_2.

Notes

Both of these methods will work just fine for looping though a basic one dimensional array.  For basic arrays, I would use the For Each loop since its syntax is quite a bit easier to understand and use.

However, don't forget the LBound UBound method because you will often come across it when working with macros in Excel and it works great with more complex array functions.

Also, don't forget to download the accompanying workbook so you can look at the code and play with it.


Downloadable Files: Excel File

Similar Content on TeachExcel
Loop through All Worksheets in Excel using VBA and Macros
Tutorial: Ill show you how to loop through all of the worksheets in a workbook in Excel using VBA an...
Excel VBA - Create an Array - 3 ways
Tutorial: Ill show you three different ways to create an array in Excel VBA and Macros and how to ge...
How to Add a New Line to a Message Box (MsgBox) in Excel VBA Macros
Tutorial: I'll show you how to create a message box popup window in Excel that contains text on mult...
Loop through a Range of Cells in Excel VBA/Macros
Tutorial: How to use VBA/Macros to iterate through each cell in a range, either a row, a column, or ...
Logical Operators in Excel VBA Macros
Tutorial: Logical operators in VBA allow you to make decisions when certain conditions are met. They...
Select Cells in Excel using Macros and VBA
Tutorial: This is actually a very easy thing to do and only requires a couple lines of code. Below I...