The For Each…Next statement in VBA (Visual Basic for Applications) is used to loop through each element in a collection or array. It’s particularly useful when you want to perform the same action on every item in a collection without manually indexing each element. Here’s how you use it:

Syntax:

For Each element In group
    [statements]
    [Exit For]
    [statements]
Next element

• element: This is a variable that represents the current element in the group or collection.
• group: This is the collection or array that you want to loop through.
• statements: These are the lines of code that you want to execute for each element in the group.
• Exit For: This statement is optional. It immediately exits the For Each loop.

Example:

Let’s say you have a collection of cells in an Excel worksheet and you want to change the background color of each cell.

Dim cell As Range
For Each cell In Worksheets("Sheet1").Range("A1:A10")
    cell.Interior.Color = RGB(255, 255, 0) ' Yellow color
Next cell

In this example, the loop will iterate over each cell in the range A1:A10 of “Sheet1”. For each cell in this range, it sets the interior color (background color) to yellow.

Key Points:

• Collection or Array: The For Each…Next loop works on collections of objects (like Excel cells, slides in PowerPoint, etc.) or arrays.
• Readability: It improves the readability of your code, especially when dealing with collections.
• Not for Every Situation: While it’s very useful for collections, in some cases (like when you need to use the index of an array in your calculations), a traditional For…Next loop might be more appropriate.

Remember to ensure that the collection or array you are looping through is not empty to avoid runtime errors.

Switch the language

Spanish

Follow us on Social Media

facebooklinkedin

Unlock Your Potential

Excel

Access

Power BI

Help us grow the project