Microsoft Office Tutorials and References
In Depth Information
For…Each…Next
You don’t always need to start counting from the number 1 in a For…Next loop; you can pretty much
count from any number to any number. Suppose you want to hide rows 6, 7, and 8. A For…Next loop
to accomplish that task could look like this:
Sub ForNextExample3()
‘Declare your Integer or Long variable.
Dim intCounter As Integer
‘Open the For loop structure.
For intCounter = 6 To 8
‘Enter the command(s)that will be repeated.
Rows(intCounter).Hidden = True
‘Loop to the next iteration.
Next intCounter
End Sub
for…Each…next
The For…Each…Next loop executes an action for a fixed number of times just as the For…Next
construct does, but unlike For…Next , For…Each…Next does not keep a count along the way of how
many iterations it performs. The count of iterations is not important with For…Each…Next because
the objective is to execute an action for however many objects exist in a specified VBA collection.
Maybe there will be hundreds of iterations to occur; maybe there will be none.
Suppose that as part of your workbook project’s design, a particularly lengthy macro will run faster
and less confusingly for the user if all other Excel workbooks are closed. Naturally, you can never
know in advance if the user will have 10 other workbooks open in addition to yours, or if your
workbook is the only open workbook. A For…Each…Next loop would be the perfect way to save and
close all other workbooks that might be open, such as with this example:
Sub CloseWorkbooks()
‘Declare your object variable.
Dim wb As Workbook
‘Open the For loop structure.
For Each wb In Workbooks
‘Enter the command(s)that will be repeated.
If wb.Name <> ThisWorkbook.Name Then
wb.Save
wb.Close
End If
‘Loop to the next iteration.
Next wb
End Sub
Notice that an object variable is declared for Workbook , and the Workbooks collection is being
evaluated with an If structure for the presence of any and all workbooks that are named differently than
your workbook. The code will complete its mission with the same result of your workbook being the
only one that’s open, regardless of whether it was the only one open from the start, or whether 50
other workbooks had also been open at the time.
Search JabSto ::




Custom Search