Loop Through an Array in Excel VBA Macros
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.
Question? Ask it in our Excel Forum
Excel VBA Course - From Beginner to Expert
200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Tutorial: Ill show you how to loop through all of the worksheets in a workbook in Excel using VBA an...
Tutorial: Ill show you three different ways to create an array in Excel VBA and Macros and how to ge...
Tutorial: I'll show you how to create a message box popup window in Excel that contains text on mult...
Tutorial: How to use VBA/Macros to iterate through each cell in a range, either a row, a column, or ...
Tutorial: How to make a drop down menu list using data validation and dynamic arrays in Excel 365. ...
Tutorial: This is actually a very easy thing to do and only requires a couple lines of code. Below I...
Subscribe for Weekly Tutorials
BONUS: subscribe now to download our Top Tutorials Ebook!
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)