When your procedure needs to loop through all of the objects of a collection or elements in an array (arrays are covered in Chapter 7), the For Each...Next loop should be used. This loop does not require a counter variable. Visual Basic can determine how many times the loop should execute.
Let's take, for example, a collection of worksheets. To remove a worksheet from a workbook, you have to first select it and then choose Edit | Delete Sheet. To leave only one worksheet in a workbook, you have to use the same command several times, depending on the total number of worksheets. Because each worksheet is an object in a collection of worksheets, you can speed up the process of deleting worksheets by using the For Each.Next loop. This loop looks like this:
For Each element In Group statement1 statement2 statementN Next [element]
In the above syntax, element is a variable to which all the elements of an array or collection will be assigned. This variable has to be of the Variant data type for an array and an Object data type for a collection. Group is the name of a collection or an array.
Now let's use the For Each.Next loop to remove some worksheets.
1. Insert a new module into the current project and rename it ForEachNextLoop.
2. Type the following procedure in the ForEachNextLoop module:
Sub RemoveSheets() Dim mySheet As Worksheet Application.DisplayAlerts = False Workbooks.Add
Worksheets("Sheet2").Select For Each mySheet In Worksheets
ActiveWindow.SelectedSheets.Delete Next mySheet End Sub
Visual Basic will open a new workbook and delete all the sheets except for Sheetl. Notice that the variable mySheet represents an object in a collection of worksheets. Instead of declaring an object variable in a generic way as type Object, your procedure will perform better when you declare object variables according to their specific type. In this particular case, instead of Dim mySheet As Object, you can use the following declaration: Dim mySheet As Worksheet. The first instruction, Application.DisplayAlerts = False, makes sure that Excel does not display alerts and messages while the procedure is running. If you omit this statement, Excel will ask you to confirm the deletion of the selected worksheet. Next, the procedure opens a new workbook and selects Sheet2. The For Each...Next loop steps through each worksheet (starting from the selected Sheet2) and deletes it. When the procedure ends, the workbook has only one sheet—Sheetl.
Here's another example that checks whether a certain sheet is part of a workbook:
Sub IsSuchSheet() Dim mySheet As Worksheet Dim counter As Integer counter = 0
For Each mySheet In Worksheets
If mySheet.name = "Sheet2" Then counter =counter + 1 End If Next mySheet
If counter = 1 Then
MsgBox "This workbook contains Sheet2."
MsgBox "Sheet2 was not found." End if
Was this article helpful?