Microsoft Office Tutorials and References
In Depth Information
Controlling Code Execution
Sub CloseInactive()
Dim Book as Workbook
For Each Book In Workbooks
If Book.Name <> ActiveWorkbook.Name Then Book.Close
Next Book
End Sub
A common use for the For Each-Next construct is to loop through all cells in a range. The
next example of For Each-Next is designed to be executed after the user selects a range of
cells. Here, the Selection object acts as a collection that consists of Range objects because
each cell in the selection is a Range object. The procedure evaluates each cell and uses the VBA
UCase function to convert its contents to uppercase. (Numeric cells are not affected.)
Sub MakeUpperCase()
Dim Cell as Range
For Each Cell In Selection
Cell.Value = UCase(Cell.Value)
Next Cell
End Sub
VBA provides a way to exit a For-Next loop before all the elements in the collection are
evaluated. Do this with an Exit For statement. The example that follows selects the first negative
value in Row 1 of the active sheet:
Sub SelectNegative()
Dim Cell As Range
For Each Cell In Range(“1:1”)
If Cell.Value < 0 Then
Exit For
End If
Next Cell
End Sub
This example uses an If-Then construct to check the value of each cell. If a cell is negative, it’s
selected, and then the loop ends when the Exit For statement is executed.
Controlling Code Execution
Some VBA procedures start at the top and progress line by line to the bottom. Macros that you
record, for example, always work in this fashion. Often, however, you need to control the flow of
your routines by skipping over some statements, executing some statements multiple times, and
testing conditions to determine what the routine does next.
Search JabSto ::

Custom Search