VBA For Each Loop

A For Each loop is used to execute a statement or a group of statements for each element in an array or collection.

A For Each loop is similar to a “For” Loop, but the loop is run for each element in an array or collection. In this type of loop, the step counter will be absent because it is mostly used with arrays or File system objects in order to recursively operate.

Syntax

For Each element In group

For Each element In group

[Statement 1]

[Statement 2]

[Statement n]

[Exit For]

[Statement 8]

[Statement 9]

Next

In VBA,You can build a cycle through a set of collections using the “For Each” loop.

Here are some examples of collections using the For Each loop in the excel VBA, such as:

  • A collection of all the open Workbooks.
  • A collection of all worksheets in a workbook.
  • A collection of all the cells in a range of selected cells.
  • A collection of all the charts or shapes in the workbook.

By using the “For Each” loop, you can go through each of the objects in a collection and perform some action on it, such as if you were to go through all of the worksheets in a workbook and protect them, or if you were to go through all of the cells in a selection and change their formatting.

With the “For Each” loop (also known as the “For Each-Next” loop), you do not need to know how many items exist in a collection as the “For Each” loop will automatically go through each object and perform the specified action.

For example, if you wanted to read all the worksheets name in a workbook, the code would be the same whether the workbook had 1 or 20 worksheets.

Example

Suppose you have a workbook where you want to protect all the worksheets. Such as:

Sub VBAForEachLoop_ReadWorksheetsName()

Dim sht As Worksheet

For Each sht In ThisWorkbook.Worksheets

MsgBox sht.Name

Next

End Sub

In the code above, the variable ‘sht’ is denoted as a worksheet object, which tells VBA that ‘sht’ should be interpreted as a worksheet object in the code.

Use the Statement ‘For Each’ to browse through each ws (which is a worksheet object) in the collection of all the worksheets in the active workbook.

Unlike other loops in which we have tried to protect all the worksheets in a workbook, this one does not need to be concerned about how many worksheets are in the workbook, as we don’t need to count them to run the loop.

For Each loop ensures that all objects are analyzed one by one.

Now, to go Through all the Open Workbooks and Read all the Workbooks Name

It can be useful to save all of the workbooks at once when working with multiple workbooks at the same time.

The code below can do this for us:

Sub VBAForEachLoop_ReadOpenWorkbookName()

Dim wb As Workbook

For Each wb In Workbooks

MsgBox wb.Name

Next wb

End Sub

Note that in the above code, you will get a MsgBox that will show you all the workbooks name which are opened.

Example

Sub VBAForEachLoop_ReadColorFromArray()

Dim ArrayColors As Variant

Dim Iteam As Variant

Dim strColorName As String

ArrayColors = Array(“Red”, “Green”, “Blue”)

For Each Iteam In ArrayColors

strColorName = strColorName & vbNewLine & Iteam

Next

MsgBox strColorName

End Sub

Executes the above code, you will get the following output, such as:

MsgBoxColor