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 [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.
See More: VBA Editor
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:
