In Excel VBA, we use the For Loop to iterate through an array (For Next Loop and For Each Loop). Also, in order to use both as the loop’s counter, you must be aware of the array’s size (upper and lower bounds). For the most part, a for loop will begin with the first element of the array and loop all the way to the last. So, we will demonstrate how to use Excel VBA For Loop with Array with 5 practical examples.
How to Open VBA Macro Editor in Excel
VBA is a programming language that may be used for a variety of tasks, and different types of users can use it for those tasks. Using the Alt + F11 keyboard shortcut, you can launch the VBA editor. In the last section, we will generate VBA code that makes it very easy to perform Vlookup with multiple criteria in Excel. Therefore, you can follow the simple steps accordingly to open the VBA editor.
Steps:
- Firstly, we will open the Developer tab.
- Then, we will select the Visual Basic.
- Here, the Visual Basic window will open.
- After that, from the Insert option, we will choose the new Module to write the VBA code.
Excel VBA For Loop with Array: 5 Examples
This article will demonstrate how to perform Loop Array in Excel VBA by giving 5 practical examples with explanations. Here, we will apply the Nested For Loop, apply For Loop for a multidimensional array, then employ For Each Loop, use For Next Loop for part array and an entire array, and finally add data into another worksheet using For Loop array. Now, you can go through a sample video of our work on Loop Array in Excel VBA.
Example 1: Using Nested For Loop Through with Array for Finding Combination Values
Here, we will demonstrate an output value where we have used a nested for loop with the array. Our goal is to show all the possible combined values of two numbers where the sum of these two numbers is 90.
- Now, copy the following code and paste it into the above Module. After that, click on Run to see the output.
Sub Array_with_Nested_ForLoop()
Dim MyArray(5) As Integer
'Declaring Array Element
MyArray(0) = 20
MyArray(1) = 30
MyArray(2) = 40
MyArray(3) = 50
MyArray(4) = 60
MyArray(5) = 70
'Using For Loop
Dim Combination_Value As String
Combination_Value = "Combination Value of arrays which show Total = '90' : "
For x = LBound(MyArray) To UBound(MyArray)
For j = LBound(MyArray) To UBound(MyArray)
'Applying If statement
If MyArray(x) + MyArray(j) = 90 And x <> j Then
Combination_Value = Combination_Value + "," + CStr(MyArray(x)) _
+ "+" + CStr(MyArray(j))
End If
Next j
Next x
'Showing Result in MsgBox
MsgBox (Combination_Value)
End SubVBA Breakdown
- Firstly, this code starts with the Sub statement and the name of the subroutine, “Array_with_Nested_ForLoop“.
Sub Array_with_Nested_ForLoop()- Secondly, we declare an integer array “MyArray” with 6 The next 6 lines populate the “MyArray” array with 6 integer values.
Dim MyArray(5) As Integer
MyArray(0) = 20
MyArray(1) = 30
MyArray(2) = 40
MyArray(3) = 50
MyArray(4) = 60
MyArray(5) = 70- After that, we declare a string variable “Combination_Value” and initialize it with a starting string value.
Dim Combination_Value As String
Combination_Value = "Combination Value of arrays which show = 90: "- Then, we will apply a For loop to go through the “MyArray” array elements from the lower bound to the upper bound. The “x” variable is used as the loop counter.
For x = LBound(MyArray) To UBound(MyArray)- Now, we will apply a nested For loop to go through the “MyArray” array elements from the lower bound (i.e., the first element) to the upper bound (i.e., the last element). The “j” variable is used as the loop counter.
For j = LBound(MyArray) To UBound(MyArray)- Within the inner For loop, an If statement is used to check if the sum of the current element of the “MyArray” array with the index “x” and the current element of the “MyArray” array with the index “j” is equal to 90 and if “x” is not equal to “j“. If the condition in the If statement is met, then the two elements are concatenated with a “+” symbol and added to the “Combination_Value” string by using the CStr After the inner For loop completes, the outer For loop moves on to the next element of the “MyArray” array.
If MyArray(x) + MyArray(j) = 90 And i <> j Then
Combination_Value = Combination_Value + "," + CStr(MyArray(x)) _
+ "+" + CStr(MyArray(j))
End If
Next j
Next x- So, a MsgBox statement shows the output of the final value of the “Combination_Value” string.
MsgBox (Combination_Value)- Finally, this VBA macro ends with an End
End Sub
Finally, you will get the final output of all the possible two combined numbers whose sum is 90.
Read More: How to Use Excel VBA Nested For Loop
Example 2: Applying For Loop to Loop Through Multidimensional Array in Excel VBA
Here, we will demonstrate an output of some names of multiple age groups based on their particular range of ages on MsgBox by applying “For loop” with the multidimensional array.
- Now, copy the following code and paste it into the above Module. After that, click on Run to see the output.
Sub ForLoop_MultidimensionalArray()
Dim MyArray(3, 1) As Variant
'Populating the array
MyArray(0, 0) = "Ronin"
MyArray(0, 1) = 8
MyArray(1, 0) = "Maddison"
MyArray(1, 1) = 15
MyArray(2, 0) = "John"
MyArray(2, 1) = 13
MyArray(3, 0) = "Jill"
MyArray(3, 1) = 16
'Declaring variable for showing the list of names at under 15
Dim Under15 As String
Under15 = "Abobe 8 and under 14 participants Name : "
'Declaring variable for showing the list of names at under 20
Dim Under20 As String
Under20 = "Abobe 14 and under 17 participants Name: "
For i = LBound(MyArray, 1) To UBound(MyArray, 1)
If MyArray(i, 1) > 8 And MyArray(i, 1) < 14 Then
Under15 = Under15 + CStr(MyArray(i, 0)) + ", "
ElseIf MyArray(i, 1) > 14 And MyArray(i, 1) < 17 Then
Under20 = Under20 + CStr(MyArray(i, 0)) + ", "
End If
Next i
MsgBox (Under15)
MsgBox (Under20)
End SubVBA Breakdown
- Firstly this code starts with the Sub statement and the name of the subroutine, “ForLoop_MultidimensionalArray()“.
Sub ForLoop_MultidimensionalArray()- The code declares a two-dimensional array named MyArray with 4 rows and 2 columns. The Variant data type is used here to allow the array to hold values of different data types.
Dim MyArray(3, 1) As Variant- The code then populates the array with values. The first index of the array (MyArray(x, 0)) holds the name of a participant, and the second index (MyArray(x, 1)) holds their age.
MyArray(0, 0) = "Ronin"
MyArray(0, 1) = 8
MyArray(1, 0) = "Maddison"
MyArray(1, 1) = 15
MyArray(2, 0) = "John"
MyArray(2, 1) = 13
MyArray(3, 0) = "Jill"
MyArray(3, 1) = 16- The code declares two variables Under15 and Under20 as strings, which will later hold the names of participants under the age of 15 and 20, respectively:
Dim Under15 As String
Under15 = "Under 15 participants Name : "
Dim Under20 As String
Under20 = "Under 20 participants Name: "- The code then uses a nested For loop to iterate through each row of the array and check if the age of the participant falls within a certain range. In this case, if the age of the participant is greater than 8 and less than 14, their name is added to the Under15 Similarly, if their age is greater than 14 and less than 17, their name is added to the Under20 string.
For i = LBound(MyArray, 1) To UBound(MyArray, 1)
If MyArray(i, 1) > 8 And MyArray(i, 1) < 14 Then
Under15 = Under15 + CStr(MyArray(i, 0)) + ", "
ElseIf MyArray(i, 1) > 14 And MyArray(i, 1) < 17 Then
Under20 = Under20 + CStr(MyArray(i, 0)) + ", "
End If
Next i- Therefore, the code displays the names of participants under the age of 15 and 20 in separate message boxes:
MsgBox (Under15)
MsgBox (Under20)- Finally, this VBA macro ends with an End Sub.
End Sub
Therefore, you will see the output of the above 8 and under 14 category participants “John”.
Example 3: Employing For Each Loop with Array to Show All the Values
In the section, we will create an array of some names and we would like to show each array item by using “For each loop” with an array.
- Then, copy the following code and paste it into the above Module. After that, click on Run to see the output in the immediate window.
Sub ForEach_Loop_Array()
'Declaring a variable as an array
Dim MyString(4) As String
'Populating the array
MyString(1) = "Robin"
MyString(2) = "Ronin"
MyString(3) = "John"
MyString(4) = "Maddison"
'Declaring a variant to hold the array element
Dim Name As Variant
'Using For Each loop
For Each Name In MyString
'Showing each name in the debug window
Debug.Print Name
Next Name
End SubVBA Breakdown
- Firstly, the code starts with the Sub statement and the name of the subroutine, “ForEach_Loop_Array“.
Sub ForEach_Loop_Array()- Then, we declare a variable “MyString” as an array of 4 strings. The array is populated with 4 names.
Dim MyString(4) As String
MyString(1) = "Robin"
MyString(2) = "Ronin"
MyString(3) = "John"
MyString(4) = "Maddison"- After that, we declare a variant variable “Name” to hold each element of the “MyString” array.
Dim Name As Variant- Now, we will apply a For Each loop to go through each element of the “MyString” array. The “Name” variable is used to hold the value of each element in turn.
For Each Name In MyString- Within the loop, the print statement is used to output the value of the “Name” variable to the Immediate window. Now, the Next statement moves to the next element in the array until all elements have been analyzed. Therefore, when the code is performed, the For Each loop revolves around each element of the “MyString” array, assigns the value of each element to the “Name” variable, and outputs each “Name” to the Immediate window using the Debug.Print statement.
Debug.Print Name
Next Name- Finally, this VBA macro ends with an End Sub command.
End Sub
So, at the end of this section, you can see the output of all the names which we included in the array items.
Read More: Excel VBA to Use For Loop with Two Variables
Example 4: Using For Next Loop with Array in Excel VBA
In this example, we will demonstrate two cases to give you a clear idea to use Excel VBA For Loop with Array.
Case 1: Loop Through Part of Array
In the part of the section, we want to show part of the array item by using “For next loop” with an array.
- Then, copy the following code and paste it into the above Module. After that, click on Run to see the output in the immediate window.
Sub ForNextLoop_Through_Part_Array()
'Declaring a variant array
Dim MyString(1 To 4) As String
'Populating the array
MyString(1) = "Robin"
MyString(2) = "Ronin"
MyString(3) = "John"
MyString(4) = "Madison"
'Declaring an integer
Dim j As Integer
'Using For Nextloop
For j = 2 To 3
'Showing the name in the debug window
Debug.Print MyString(j)
Next j
End SubVBA Breakdown
- Firstly this code starts with the Sub statement and the name of the subroutine, “ForNext_Loop_Through_Part_Array“.
Sub ForNextLoop_Through_Part_Array()- Then we declare a variable “MyString” as an array of 4 strings, with indexes from 1 to 4. The array is populated with 4 names.
Dim MyString(1 To 4) As String
MyString(1) = "Robin"
MyString(2) = "Ronin"
MyString(3) = "John"
MyString(4) = "Madison- After that, we declare an integer variable “j” that will be used as a loop counter in the For Next loop.
- Now, we will apply a For Next loop to go through the “MyString” array elements from index 2 to 3. The “j” variable is used as the loop counter.
Dim j As Integer
For j = 2 To 3- Within the loop, the Print statement is used to output the value of the element of the “MyString” array with the index “j” to the Immediate window. Now, the Next statement moves to the next element in the loop until the specified range has been analyzed. Therefore, when the code is performed, the For Next loop goes through the elements of the “MyString” array with the indexes 2 and 3 and outputs the value of each element to the Immediate window using the Debug.Print statement.
Debug.Print MyString(j)
Next j- Finally, this VBA macro ends with an End Sub command.
End Sub
Therefore, you can see the output of the first two names which we included in the array of items.
Case 2: Loop Through an Entire Array
In this section, you will see the output, where we will show each array item by using the “For next loop” through the entire array.
- Then, copy the following code and paste it into the above Module. After that, click on Run to see the output in the immediate window.
Sub ForNextLoop_Through_Entire_Array()
'Declaring a variant array
Dim MyString() As String
'Initializing the array
ReDim MyString(1 To 4)
'Populating the array
MyString(1) = "Robin"
MyString(2) = "Ronin"
MyString(3) = "John"
MyString(4) = "Maddison"
'Declaring an integer
Dim j As Integer
'For Nextloop from the lower bound of the array to _
the upper bound of the array - the entire array
For j = LBound(MyString) To UBound(MyString)
'Showing the name in the immediate window
Debug.Print MyString(j)
Next j
End Sub
Finally, you will get the output of all the array items in the immediate window.
Example 5: Using For Loop with an Array and Add Data to Another Sheet
Here, we would like to add data to another sheet whose MPG is greater than 20. Therefore, you will see the output for cars with an MPG greater than 20. So, follow this section with great care, and see the video at the end of this section where we will show you how to do this.
- Then, copy the following code and paste it into the above Module. After that, click on Run to see the output in the immediate window.
Sub Add_Data_into_AnotherSheet()
'Declaring array region
MyArray = Sheet2.Cells(4, 2).CurrentRegion
reportrow = 5
'Using For loop
For Row = 2 To UBound(MyArray)
'Using If statement
If MyArray(Row, 1) > 20 Then
For Column = 1 To UBound(MyArray, 2)
Sheet3.Cells(reportrow, Column) = MyArray(Row, Column)
Next
reportrow = reportrow + 1
End If
Next
End SubVBA Breakdown
- Firstly this code starts with the Sub statement and the name of the subroutine, Add_Data_into_AnotherSheet()“.
Sub Add_Data_into_AnotherSheet()- Then, we will declare MyArray as a variable and assign data from sheet2 into this. And we put a value in a variable.
MyArray = Sheet2.Cells(4, 2).CurrentRegion
reportrow = 5- After that, we will apply For loop to go through each row, and using the If statement, we will check the value which is greater than 20. Now, for column operation, we will apply a For loop again to go through each column for adding the data from sheet2 into sheet3.
For Row = 2 To UBound(MyArray)
'Using If statement
If MyArray(Row, 1) > 20 Then
For Column = 1 To UBound(MyArray, 2)
Sheet3.Cells(reportrow, Column) = MyArray(Row, Column)
Next
reportrow = reportrow + 1
End If
Next- Finally, this VBA macro ends with an End Sub command.
End SubFinally, in this section, you will see the output on the right side where we have demonstrated how to add data from “sheet2” to “sheet3” where miles per gallon is greater than 20, by using “For next loop” with an array. So, you can follow the above video for a better understanding, as it contains all the steps.
Frequently Asked Questions
1. With Excel VBA, how do I loop through an array?
Use the For Loop in VBA to iterate through an array (For Next). Also, in order to use both as the loop’s counter, you must be aware of the array’s size (upper and lower bounds). For the most part, the for loop will begin with the first element of the array and loop all the way to the end.
2. In VBA, can an array be sent to a function?
In VBA, arrays can be passed to procedures (Subs, Functions, and Properties) and returned by Functions and Properties (Property Get only).
Things to Remember
While using an array in VBA, you have to enter the cell reference according to the array region. It will not be the same as the general cell reference in Excel.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice it yourself.
Conclusion
In this article, we’ve covered 5 examples of how to perform For Loop Array in Excel VBA. We sincerely hope you enjoyed and learned a lot from this article. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.
Related Articles
- Excel VBA: Running a Backwards For Loop
- How to Continue Excel VBA For Loop
- Excel VBA with For Loop Decrement
- Excel VBA: For Loop Skip to Next Iteration
- Excel VBA: How to Exit a For Loop
