Microsoft Office Tutorials and References
In Depth Information
Exiting a For… Loop
One of Excel’s oddities is that you can hide any number of worksheets at the same time, but if you
have multiple worksheets that are hidden, you can unhide only one worksheet at a time. With this
macro as another example of a For…Each…Next loop, you can quickly unhide all worksheets at once:
Sub UnhideSheets()
‘Declare your object variable.
Dim ws As Worksheet
‘Open a For Ech loop.
For Each ws In Worksheets
‘Command(s) to be executed.
ws.Visible = xlSheetVisible
‘Loop to the next iteration.
Next ws
End Sub
Exiting a for… loop
Suppose your macro requires that you determine whether a particular workbook named Test.xlsx
happens to be open, and if so, you must close it. You might compose a macro with a loop that looks
like this:
Sub CloseOneWorkbook()
‘Declare your object variable.
Dim wb As Workbook
‘Open a For Each loop.
For Each wb In Workbooks
‘Command(s) to be executed.
If wb.Name = “Test.xlsx” Then
wb.Save
wb.Close
End If
‘Loop to the next iteration.
Next wb
End Sub
Strictly speaking, the macro will work. But think for a moment — what if a few dozen workbooks
are open? In this case, you’d want the loop to do its job only up to the point of encountering the
Test.xlsx workbook.
In the preceding CloseOneWorkbook example, even if the Test.xlsx workbook is found to be open and
then closed, the loop will still continue its appointed rounds after that by unnecessarily evaluating
each open workbook. This would be a waste of time and system resources. Instead, you should insert
the Exit For statement to stop the looping process in a For…Next or For…Each…Next loop when a
condition has been met and dealt with, and cannot be met thereafter.
Here is an example of how that macro should look, with the Exit For statement placed
immediately before the End If statement:
Sub CloseOneWorkbookFaster()
‘Declare your object variable.
Dim wb As Workbook
For Each wb In Workbooks
‘Command(s) to be executed.
Search JabSto ::




Custom Search