This page provides a brief summary of what you've learned in this module.
You can click here to download the
example code shown below.
If Statements
You can write a basic If statement to
perform an action if a condition is met.
If Range("A1").Value = "" Then Exit Sub
You can write a block If statement to
perform multiple actions if a condition is met.
If Range("A1").Value = "" Then
Range("A1").Interior.Color = rgbPink
Range("A1").Select
Exit Sub
End If
You can use the Else clause to perform
actions if the condition is not met.
If Range("A1").Value = "" Then
Range("A1").Interior.Color = rgbPink
Range("A1").Select
Exit Sub
Else
Range("A1").ClearFormats
End If
You can use ElseIf to test multiple
conditions in the same block If statement.
If Range("A1").Value > Range("B1").Value Then
Range("C1").Value = "Player 1 wins"
ElseIf Range("B1").Value > Range("A1").Value Then
Range("C1").Value = "Player 2 wins"
Else
Range("C1").Value = "Draw"
End If
You can nest If statements.
If IsNumeric(Range("A1").Value) Then
If Range("A1").Value < 0 Then
Range("A2").Value = "Negative number"
Exit Sub
End If
Else
Range("A2").Value = "Not a number"
Exit Sub
End If
Logical Tests
You can combine conditions using the Or
operator.
If Range("A1").Value < 0 _
Or Range("A1").Value > 10 Then
Exit Sub
End If
You can combine conditions using the And
operator.
If Month(Now) = 12 And Day(Now) = 25 Then
Range("A1").Value = "Tis the season"
End If
You can use the Not operator to change
True to False and vice versa.
If Not IsDate(Range("A1").Value) Then
Range("A2").Value = "Not a date"
Exit Sub
End If
Select Case Statements
You can write a Select Case statement to
assign a value to one of a set of discrete categories.
Select Case Month(Date)
Case 1
Range("A1").Value = "Jan"
Case 2
Range("A1").Value = "Feb"
Case 3
Range("A1").Value = "Mar"
Case 4
Range("A1").Value = "Apr"
Case 5
Range("A1").Value = "May"
Case 6
Range("A1").Value = "Jun"
Case 7
Range("A1").Value = "Jul"
Case 8
Range("A1").Value = "Aug"
Case 9
Range("A1").Value = "Sep"
Case 10
Range("A1").Value = "Oct"
Case 11
Range("A1").Value = "Nov"
Case 12
Range("A1").Value = "Dec"
Case Else
Range("A1").Value = "Unknown"
End Select
You can test multiple values in each case.
Select Case Month(Date)
Case 12, 1, 2
Range("A1").Value = "Winter"
Case 3, 4, 5
Range("A1").Value = "Spring"
Case 6, 7, 8
Range("A1").Value = "Summer"
Case 9, 10, 11
Range("A1").Value = "Autumn"
Case Else
Range("A1").Value = "Unknown"
End Select
You can test a range of values in each case.
Select Case Month(Date)
Case 1 To 3
Range("A1").Value = "Quarter 1"
Case 4 To 6
Range("A1").Value = "Quarter 2"
Case 7 To 9
Range("A1").Value = "Quarter 3"
Case 10 To 12
Range("A1").Value = "Quarter 4"
Case Else
Range("A1").Value = "Unknown"
End Select
Conditional Functions
You can use the IIf function to return a
value based on a logical test.
Range("A2").Value = IIf( _
Expression:=Range("A1").Value < 0, _
TruePart:="Negative", _
FalsePart:="Positive")
You can use the Switch function to test
multiple conditions in the same expression.
Range("A2").Value = Switch( _
Range("A1").Value < 3, "Rubbish", _
Range("A1").Value < 6, "Average", _
Range("A1").Value < 8, "Good", _
True, "Great")
Conditional Loops
You can use a Do - Loop
statement to execute instructions repeatedly, with an Exit Do statement to stop the loop.
Range("A1").Select
Do
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = "" Then Exit Do
Loop
You can use a Do Until - Loop
statement to automatically exit the loop when the condition
returns True.
Range("A1").Select
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
Loop
You can use a Do - Loop Until
statement to test the condition at the end of the loop.
Range("A1").Select
Do
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Value = ""
You can use a Do While - Loop
statement to automatically exit the loop when the condition
returns False.
Range("A1").Select
Do While ActiveCell.Value
<> ""
ActiveCell.Offset(1, 0).Select
Loop
You can use a Do - Loop While
statement to test the condition at the end of the loop.
Range("A1").Select
Do
ActiveCell.Offset(1, 0).Select
Loop While ActiveCell.Value
<> ""
