VBA For Each Loop

Excel VBA For Each Loop

VBA For Each Loop goes through all the collection of objects or items and perform a similar set of activities . It will take into consideration of all the available specified objects and perform instructed activity in each object.

In VBA, it is mandatory to understand the loopsLoopsA VBA loop in excel is an instruction to run a code or repeat an action multiple times.read more. A loop allows you to conduct the same kind of activity for many cells or objects in excel. In today’s article, we are going to concentrate on For Each Loop mechanism.

Syntax

For Each Loop can loop through all the set collection of objects or items. A group is nothing but “All the opened workbooks,” “All the worksheets in a workbook,” “All the collection of shapes and charts in the workbook.”

Let’s look at the syntax.

For Each Object In Collection
           
     What to Do?

Next Object

For example, You have 10 sheets in your workbook, and you want to hide all the worksheets except the one you are in. Can you hide manually? Yes, you can, but what if you have 100 sheets like that? Isn’t that a tedious and time-consuming task to do? You can do this using for each loop.

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA For Each Loop (wallstreetmojo.com)

How to use For Each Loop in VBA? (Examples)

Example #1 – Insert Same Text in All the Sheets

We will see how to use FOR EACH in VBA withVBA WithIn VBA, the With statement is used to gain access to all of an object's properties and methods.read more a simple example. Assume you have 5 worksheets in a workbook, and you want to insert the word “Hello” in all the worksheets in cell A1.

We can do this with FOR EACH LOOP. One thing you need to remember here is we are performing this activity in each worksheet, not in the same worksheet. Follow the below steps to write the VBA codeWrite The VBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more.

Step 1: Start the excel macroExcel MacroA macro in excel is a series of instructions in the form of code that helps automate manual tasks, thereby saving time. Excel executes those instructions in a step-by-step manner on the given data. For example, it can be used to automate repetitive tasks such as summation, cell formatting, information copying, etc. thereby rapidly replacing repetitious operations with a few clicks. read more.

Code:

Sub For_Each_Example1()

End Sub

Step 2: Since we are referring to the worksheets, declare the variable as “Worksheet.”

Code:

Sub For_Each_Example1()

  Dim Ws As Worksheet

End Sub

Step 3: Now, using FOR EACH LOOP, we need to refer each worksheet in the active workbook.

Code:

Sub For_Each_Example1()

   Dim Ws As Worksheet

   For Each Ws In ActiveWorkbook.Worksheets

   Next Ws

End Sub

Step 4: Now, write what we want to do in each worksheet. In each worksheet, we need to put the word “Hello” in cell A1.

Code: 

Sub For_Each_Example1()

   Dim Ws As Worksheet

   For Each Ws In ActiveWorkbook.Worksheets
   Ws.Range("A1").Value = "Hello"
   Next Ws

End Sub

Step 5: Now run this code manually through option or press shortcut key F5. It doesn’t matter how many sheets you have; it will insert the Word “Hello” in all the worksheets.

Example #2 – Hide All the Sheets

As told earlier in the post, what if you have hundreds of sheets to hide except the one you are in. Using For each loop, we can hide all the sheets in excel.

Step 1: Start the macro with your name.

Code:

Sub For_Each_Example2()

End Sub

Step 2: Declare the variable as “Ws.”

Code:

Sub For_Each_Example2()

  Dim Ws As Worksheet
 
End Sub

Step 3: Now, in each worksheet, what you need to do is hide the sheet.

Code:

Sub For_Each_Example2()

  Dim Ws As Worksheet

  For Each Ws In ActiveWorkbook.Worksheets
  Ws.Visible = xlSheetVeryHidden
  Next Ws

End Sub

Step 4: But if you run the above code, it will try to hide all the sheets, but excel needs at least one sheet visible. So we need to tell which sheet not to hide.

Code:

Sub For_Each_Example2()

  Dim Ws As Worksheet

  For Each Ws In ActiveWorkbook.Worksheets

  If Ws.Name <> "Main Sheet" Then
  Ws.Visible = xlSheetVeryHidden
  End If
  Next Ws

End Sub

The operator symbol <> means not equal to in VBA.

So code says when you are looping through all the worksheets in the active workbook, hide only if the sheet name is not equal to the sheet name of Main Sheet.

This can be done by using the IF statement in VBAIF Statement In VBAIF OR is not a single statement; it is a pair of logical functions used together in VBA when we have more than one criteria to check, and when we use the if statement, we receive the true result if either of the criteria is met.read more. Write the code as IF Ws.Name <> “Main Sheet” Then hide or if it is equal to the sheet name “Main Sheet,” then don’t hide.

Step 5: Now run the code using the F5 key or manually. Then, it will hide all the worksheets except the one named “Main Sheet.”

Example #3 – Unhide All the Sheets

We have seen how to hide all sheets except the one we are in. Similarly, we can unhide all the worksheets as well.

We need to change the code from xlSheetVeryHidden to xlSheetVisible.

Code:

Sub For_Each_Example3()

Dim Ws As Worksheet

For Each Ws In ActiveWorkbook.Worksheets
Ws.Visible = xlSheetVisible
Next Ws

End Sub

Here we don’t need the IF condition because we are unhiding all the sheets. If you don’t want to unhide any specific sheet, then you can use the IF condition and supply the sheet name.

Example #4 – Protect and UnProtect All the Sheets

Protect All Sheets: We can protect all the sheets in the workbook with just a piece of code. All the principle is the same only thing we need to do here is instead of Ws. Visible, we need to put the code Ws. Protect and type the password.

Code:

Sub For_Each_Example4()

  Dim Ws As Worksheet

  For Each Ws In ActiveWorkbook.Worksheets
    Ws.Protect Password:="Excel@2019"
  Next Ws

End Sub

Unprotect All the Sheets: On a similar note, using VBA, we can also unprotect all sheetsUsing VBA, We Can Also Unprotect All SheetsVBA unprotect sheet helps you unprotect a protected sheet. A protected sheet secures the data and information on a sheet if anyone tries to edit, access, change or manipulate it, usually protected by a password. You can unprotect a sheet using VBA coding.read more protected in the workbook. We need to put the word Unprotect and password.

Code:

Sub For_Each_Example6()

  Dim Ws As Worksheet

  For Each Ws In ActiveWorkbook.Worksheets
  Ws.Unprotect Password:="Excel@2019"
  Next Ws

End Sub

Things to Remember

• Each is for the collection of objects.
• It will consider all the specified objects in the specified workbook.
• While declaring the variable, we need to which object we refer to—for example, Worksheet, Workbook, Chart, etc.

Recommended Articles

This has been a guide to VBA For Each Loop. Here we learn how to use VBA For Each Loop to Insert Text, Hide or Unhide sheets and Protect and Unprotect Workbook in excel along with practical examples and a downloadable template. Below you can find some useful excel VBA articles –

• List of VBA Functions
• Do Until Loop in VBA
• For Next Loop in VBA
• Not Function in Excel VBA