For Each Loop VBA – Excel

Today we will learn about an important type of loop which is For Each loop in VBA Excel. Loops are a special type of control structure that is used in Iterating through a set of statements. For Each loop is one of them. In this article, we would learn the implementation of For Each loop along with examples.

So let us begin learning.

For Each Loop

A For Each loop is used to execute a set of statements for each element of an Array or Collection. We do not need a counter variable and it automatically executes for all the items in the Collection or Array. We cannot use it to iterate through an Array containing User Defined Data Types.

The For Each element executes only when there is at least one element in the Array or Collection. All the sets of statements inside loop are executed for each element. If an array has three elements then the looping iterates the block of statements three times.

Syntax and Terms

Here we got the syntax to use a For Each loop in VBA.

For Each item In group
'statement1
'statement2
'statement3
Next item

The following terms are explained as follows.

• For Each – In – These are the keywords to start the For Each loop and are a part of the syntax.
• item – This is a variant or object that contains each element of the collection or array through each iteration. It can be a variant, a generic object, or a specific object variable. It would contain the element for which the current iteration is being executed in the Collection.
• group – We supply the actual Array or Collection.
• Next item – This indicates that the current iteration of For Each loop has been completed. Now it VBA checks if there are more elements in the group then the next iteration would begin. If the loop has matured then its execution would be completed.

In order to use a For Each loop with arrays, we need to declare the item variable as a variant.

Examples to Learn For Each Loop

In this section of the blog, we would learn about some examples of implementation through VBA Sub Procedures.

Example 1 – Print Elements of Array

In this example, we would take a Students_Marks array of Integer type that would contain marks of three students in Maths.

We would initialize the array with marks and then print the marks using the MsgBox function.

Use the following Sub Procedure to implement the intended work.

Sub exPrintArrayElements()
'print the elements of array using For Each loop
'initialize the marks array
studentMarks = Array(34, 25, 63)
'declare a variant
Dim marks As Variant
'starting for each loop
    For Each marks In studentMarks
        'marks is item variable containing the each element of studentMarks array
        MsgBox marks
    Next marks
End Sub

• Press Alt and F11 keys to open VBE.
• Go to the Insert tab and insert a new module.
• Paste the code into the module.
• Close the VBE and press Alt and F8 keys to open the list of available macros.
• Select the exPrintArrayElements and then click on the Run button to run this Sub Procedure.

Example 2 – Refer to all Worksheets in the Worksheets Collection

In this example, we would refer to each worksheet of the Worksheet collection using the For Each loop.

Let us suppose we have a workbook containing three worksheets as follows.

We wish to access each worksheet of our workbook and then change the names from Sheet1, Sheet2, and Sheet3 to today’s date, tomorrow’s date, and the day after tomorrow’s date. For example, if today is 4-2-2023 then the new names of Worksheets would be 4-2-2023, 5-2-2023, and 6-2-2023.

We use the following code to do this.

Sub exChangeSheetNamesToDates()

'change the names from Sheet1,Sheet2 and Sheet3 to
'today's date, tomorrows date and day after tomorrows's date
'declare an increment variable to increase date value to next date
Dim incr As Integer
'declare a worksheet to hold each worksheet of collection
Dim ws As Worksheet
Dim start As Date
'set start to todays date, Date is a function that returs system's date
start = Date
    For Each ws In Sheets
        ws.Name = CDate(start + incr)
        incr = incr + 1
    Next ws

End Sub

This brings us to an end.

Thank you for reading.