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.
The If...Then
statement is used for conditional control. The syntax is:
IfCondition
Then ' statements go here . . . ElseIfAnotherCondition
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