VBA For Each Loop

What is Excel VBA For Each Loop?

The VBA For Each Loop in Excel is a control structure that allows you to iterate through a collection of objects or elements, such as worksheets, cells, or files, without explicitly defining the start and end points of the loop. It simplifies looping through collections and is particularly useful when working with Excel workbooks and their various elements.

Let us look at an example. We have a VBA subroutine named “FormatSheetNames” designed to streamline the management of worksheet names within an Excel workbook. It employs a For Each Loop to iterate through all the worksheets in the active workbook. As it traverses each sheet, it performs a crucial task: checking if any sheet name contains spaces.

The conditional check is facilitated by the InStr function, which scans the sheet names for spaces. When a space is identified within a sheet name, the code acts by substituting that space with an underscore.

What is Excel VBA For Each Loop?

The VBA For Each Loop is used to iterate through collections of objects or elements without specifying any start and end points.
VBA For Each Loop inherently moves forward through a collection. To iterate VBA For Each Loop Backward, employ a standard For Loop with a decrementing index variable.
VBA For Each Loop is useful for automating tasks involving Excel workbooks, such as modifying sheets, cells, charts, or other elements.
VBA For Each Loop is mainly for collections, but you can iterate through a VBA For Each Loop Array using a standard For Loop with an index variable.

Syntax

For Each element In Collection

‘ Code to be executed for each element.

Next element

The syntax of the For Each Loop in VBA is as follows:

element: This variable represents the current element in the collection.
Collection: This is the collection of objects or elements you want to loop through.

How to Use For Each Loop in VBA?

Here are the steps to use a For Each VBA Loop.

Step 1: In the Excel workbook, press ALT + F11 to open the Visual Basic for Applications (VBA) editor.

Step 2: Inside the VBA editor, go to Insert > Module to insert a new module where you can write your VBA code.

Step 3: Declare any variables you’ll need within the loop to represent the objects or elements you want to loop through.

Step 4: Create a For Each Loop within the module. Define the collection of objects or elements you want to loop through and specify the actions to be performed on each element.

Step 5: Write the logic within the loop to execute the desired actions on each element. You can access and manipulate the properties and methods of the current element within the loop.

Step 6: Close the VBA editor and return to your Excel workbook. Execute the VBA code by pressing ALT + F8, selecting the macro containing the For Each Loop, and clicking “Run.”

Step 7: Review the results in your Excel workbook after executing the code. The loop should have applied the specified actions to each element in the collection.

Examples

Example #1 – Insert Same Text in All the Sheets

In this example, we will understand how to insert the same text into the sheets’ top-left cell within an Excel workbook using VBA.

Suppose we have an Excel workbook with multiple worksheets and want to insert text in cell A1 in all the worksheets. We can achieve this using the following procedure:

Step 1: In the new module, we will first define a Subroutine (Sub) named InsertTextInAllSheets.

Step 2: Here, we declare a variable ws of type Worksheet. This variable will be used to represent each worksheet in the loop.

Step 3: Next, we declare another variable, textToInsert, as a string. This variable will store the text we want to insert into each sheet.

Step 4: Now, we assign the string “This text was inserted.” to the “textToInsert” variable. It is the text that will be inserted into each sheet. You can replace this with any text you want.

Step 5: Now, we start the VBA For Each Loop. It will iterate through each sheet in the workbook (ThisWorkbook).

Step 6: Inside the loop, we use ws (the current worksheet) to access its cells. Here, we set the cell’s value in the first row and first column (cell A1) to the text stored in the “textToInsert” variable.

Step 7: Finally, we end the loop using “Next ws.” It tells VBA to move to the next sheet in the workbook.

Step 8: Save the macro and click on Run. Once you execute it, this code inserts the same text in the first cell of all sheets in the active workbook.

Here is the full code:

Sub InsertTextInAllSheets()

Dim ws As Worksheet

Dim textToInsert As String

textToInsert = “This text was inserted.”

For Each ws In ThisWorkbook.Sheets

ws.Cells(1, 1).Value = textToInsert

Next ws

End Sub

Example #2 – Hide All the Sheets

In this example, we will explore a VBA code that hides all the worksheets within an Excel workbook using a For Each Loop. Suppose we have four worksheets and we want to hide them; It can be achieved using the following steps:

Step 1: In the new module, first, we define a Subroutine named HideAllSheets.

Step 2: Here, we declare a variable ws to represent worksheets.

Step 3: Now, we insert a VBA For Each Loop that will iterate through all sheets in the workbook.

Step 4: Inside the loop, we set the Visible property of each worksheet (ws) to xlSheetHidden. It hides the sheet.

Step 5: Finally, we end the loop using “Next ws” and move on to the next sheet.

Step 6: Save the macro and click on run. Once you execute the macro, this code hides all the sheets in the active workbook.

Here is the full code:

Sub HideAllSheets()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Sheets

ws.Visible = xlSheetHidden

Next ws

End Sub

Example #3 – Unhide All the Sheets

In this example, we will understand how to unhide all the worksheets within an Excel workbook. It is achieved by utilizing a VBA For Each Loop, allowing us to make previously hidden sheets visible again.

Step 1: In the new module, define a Subroutine named UnhideAllSheets.

Step 2: Here, we declare a variable ws to represent worksheets.

Step 3: Now, provide a VBA For Each Loop, which will iterate through all sheets in the workbook.

Step 4: Inside the loop, we set the Visible property of each worksheet (ws) to xlSheetVisible. This property makes the sheet visible again.

Step 5: Finally, we end the loop using “Next ws” and move on to the next sheet.

Step 6: Save the macro and click Run. Once you execute the macro, this code unhides all the sheets in the active workbook.

Here is the full code:

Sub UnhideAllSheets()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Sheets

ws.Visible = xlSheetVisible

Next ws

End Sub

Example #4 – Protect and UnProtect All the Sheets

In this example, we will see how to protect all the worksheets in an Excel workbook with a specified password, enhancing the security of the workbook.

Step 1: Start by defining a Subroutine named ProtectUnprotectSheets in the new module.

Step 2: Next, we declare a variable ws to represent worksheets.

Step 3: Here, we declare a variable password as a string to store the password for protecting the sheets.

Step 4: Now, we assign “mypassword” to the password variable.

Step 5: We will provide VBA For Each Loop that iterates through all sheets in the workbook.

Step 6: Inside the loop, we use the Protect method to protect each worksheet (ws) with the specified password.

Step 7: Use “ws.Unprotect Password:=password” to unprotect the sheets.

Step 8: Finally, we end the loop using “Next ws” and move on to the next sheet.

Step 9: Save the VBA macro and click on Run. Once you execute the macro, this code protects all the sheets in the active workbook with a specified password.

Here is the full code:

Sub ProtectUnprotectSheets()

Dim ws As Worksheet

Dim password As String

password = “mypassword”

For Each ws In ThisWorkbook.Sheets

ws.Protect Password:=password

‘ To unprotect: ws.Unprotect Password:=password

Next ws

End Sub

Important Things To Note

If a specific condition is met, you can exit VBA For Each Loop prematurely using the Exit For statement.
If you anticipate potential errors while looping through elements, consider adding error handling in VBA using On Error Resume Next to handle exceptions gracefully.
Excel VBA provides various collections to loop through, including Worksheets, Cells, Charts, and more. Choose the appropriate collection based on your task.
Ensure you declare variables with the correct data types to avoid mismatch errors in VBA.
In VBA, the “VBA For Each Loop Break” doesn’t exist as a dedicated ‘break’ statement for VBA For Each Loop. Instead, you can achieve an early exit based on a condition by utilizing ‘Exit For.’
Use “Continue For” to skip the current iteration and proceed to the next element in the collection in a VBA For Each Loop Skip to Next.

Frequently Asked Questions (FAQs)

1. How to end a For Each Loop in VBA?

You can use the Exit For statement to end a VBA For Each Loop prematurely. This statement is typically used within an If condition to check for a specific condition, and when that condition is met, the loop is terminated.

2. How to continue a For Each Loop in VBA?

You can use the Continue For statement to continue VBA For Each Loop without processing the current element and move to the next one. It skips the remaining code for the current element and proceeds to the next element in the collection.

3. What is the difference between a For Loop and a For Each Loop in VBA?

VBA For Loop and VBA For Each Loop are both used for iteration, but they have key differences:

• VBA For Loop: A For Loop is used when you know the iteration’s starting and ending points and want to increment or decrement a variable (usually a counter) until a certain condition is met.

• VBA For Each Loop: A For Each Loop is used when you want to iterate through a collection of objects or elements (e.g., worksheets, cells) without specifying the start and end points explicitly. It automatically iterates through all elements in the collection.

4. What is one advantage of using a For Each Loop in VBA?

One advantage of using VBA For Each Loop is that it simplifies the process of iterating through collections of objects. You don’t need to manage index variables or calculate the number of iterations; instead, the loop automatically goes through all elements in the collection.

This can lead to cleaner and more readable code, especially when working with complex Excel workbooks containing multiple sheets, charts, or other elements.

VBA For Each Loop