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 loops in VBA. 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.
For Each Loop can loop through all the set collection of objects or items. A collection 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 boring and time-consuming task to do. You can do this using for each loop.
How to use For Each Loop in VBA?
Example #1 – Insert Same Text in All the Sheets
We will see how to use FOR EACH in VBA with 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 actually performing this activity in each worksheet, not in the same worksheet. Follow the below steps to write the VBA code.
Step 1: Start the excel macro.
Sub For_Each_Example1() End Sub
Step 2: Since we are referring to the worksheets declare the variable as “Worksheet”.
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.
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.
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 sheet in excel.
Step 1: Start the macro with your name.
Sub For_Each_Example2() End Sub
Step 2: Declare the variable as “Ws”.
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.
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 to be visible. So we need to tell which sheet to not hide.
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 VBA. 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 F5 key or manually then, it will hide all the worksheet except the one named as “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 just need to change the code from xlSheetVeryHidden to xlSheetVisible.
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 IF condition because we are unhiding all the sheets. If you don’t want to unhide any specific sheet then you can use 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 code 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.
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 the protected sheets in the workbook. We just need to put the word Unprotect and password.
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 collection of objects.
- It will consider all the specified objects in the specified workbook.
- While declaring the variable we need to which object we are referring to. For example Worksheet, Workbook, Chart, etc.
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 –