We are going to learn about programming FOR EACH NEXT loops. First, we are going to learn what loops are and why they are necessary. Then, we are going to learn how to use a FOR EACH NEXT LOOP.
Types of VBA loops
In Excel VBA they are two types of FOR LOOPS: FOR NEXT and FOR EACH NEXT. Our focus will be on the FOR EACH NEXT loop in this article.
The For Each loop in Excel VBA is used to iterate over a collection of objects, such as cells in a range or items in an array. The syntax is as follows:
For Each item in collection
[statements to be executed]
Next item
Here, item is a variable that holds each item in the collection as the loop iterates over it. A collection is the object to be looped through.
Example:
Sub LoopExample()
Dim rng As Range
Set rng = Range("A1:A5")
For Each cell In rng
cell.Value = "Hello World"
Next cell
End Sub
This code sets a range rng to the cells A1 to A5, and then uses a For Each loop to iterate through each cell in the range, setting the value of each cell to “Hello World”.
Now let’s see how to use the For Each Next loop.
The ribbon
Follow the steps below:
Right-click anywhere on the ribbon and select “Customize the Ribbon” from the drop down menu.
A dialog box will open. Click on the DEVELOPER check box (it is under “Customize the Ribbon Main Tabs”).
The developer tap is now visible and is present next to the View tab on the top menu bar. Click on Developer tab and select “View Code”.
A new window (Visual Basic Editor) will open which would have a dialog box in the center. You will write the code in the dialog box.
The code
Write the following line of code in the dialog box.
Sub my_for_loop3()
For Each sht In ActiveWorkbook.Worksheets
MsgBox sht.Name
Next sht
End Sub
After writing the code, close the window by clicking on the cross(x) icon on the upper right side of the screen.
The result
This is the result. That’s it! You have successfully used a For Next Each loop.
This is a simple example of how to use the FOR EACH NEXT loop to iterate through a collection of worksheets. This type of loop is especially useful for working with collections of objects, making your code more readable and easier to manage.
