Microsoft Office Tutorials and References
In Depth Information
Chapter 8. Control Statements
Chapter 8. Control Statements
We conclude our discussion of the VBA language with a discussion of the main VBA control
statements , which are statements that affect the flow of control (or flow of execution) in a program.
8.1 The If...Then Statement
The If...Then statement is used for conditional control. The syntax is:
If Condition Then
' statements go here . . .
ElseIf AnotherCondition Then
' more statements go here . . .
Else
' more statements go here . . .
End If
Note that we may include more than one
ElseIf part and that both the ElseIf part(s) and the Else part are optional. We can also
squeeze all parts of this statement onto a single line, which is generally only a good idea when the
ElseIf and Else parts are missing. As an example, the following code deletes the current
selection in the active worksheet if it contains more than one cell:
If Selection.Count > 1 Then Selection.Delete
The following example changes the color of the current selection based upon its location—
selected cells in odd-numbered rows are colored red, those in even-numbered rows are colored
blue:
Dim oCell As Range
For Each oCell In Selection.Cells
If (oCell.Row Mod 2) = 1 Then
' odd
oCell.Interior.ColorIndex = 3 ' red
Else
' even
oCell.Interior.ColorIndex = 5 ' blue
End If
Next
8.2 The For Loop
The For...Next statement provides a method for repeatedly looping through a block of code
(that is, one or more lines of code). This loop is naturally referred to as a For loop. The basic
syntax is:
For counter = start To end
' block of code goes here . . .
 
 
 
Search JabSto ::




Custom Search