MODULES▼
LESSONS▼
This module introduces two important VBA concepts: testing conditions, and doing something repeatedly (looping).
Choose what you want to learn from the list of lessons above.
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
'do something useful
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 = ""
'do something useful
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
'do something useful
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 <> ""
'do something useful
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
'do something useful
ActiveCell.Offset(1, 0).Select
Loop While ActiveCell.Value <> ""
This page contains reference material for the code used in this module.
Comparison Operators
The table below shows a list of comparison operators that you can use in logical tests:
| Operator | Description |
|---|---|
| = | Is equal to |
| <> | Is not equal to |
| > | Is greater than |
| < | Is less than |
| >= | Is greater than or equal to |
| <= | Is less than or equal to |
Logical Operators
The table below shows a list of logical operators you can use in VBA:
| Operator | Description |
|---|---|
| Or | Combines two logical tests. Returns True when either or both logical tests returns True. |
| And | Combines two logical tests. Returns True when both logical tests return True. |
| Not | Changes True to False and vice versa. |
| Xor | Combines two logical tests. Returns True when only one of the logical tests returns True. |
| Eqv | Combines two logical tests. Returns True when both logical tests return the same result. |
Try doing one or more of the following exercises for this module:
Exercise 3.01 Exercise 3.02 Exercise 3.03There is currently no test for this module.