Excel VBA Lesson 12: Mastering If...Then...Else Statements in Excel VBA
Continue learning classic Excel VBA with the same shared lesson template and cleaner visual style.
In this lesson, you'll learn how to create powerful decision-making structures in Excel VBA using If..Then...ElseIf statements. These control structures enable your programs to make decisions based on specific conditions, using conditional and logical operators to determine program flow.
12.1 Conditional and Logical Operators
Conditional operators are essential tools that allow your VBA programs to compare values and make decisions. These operators work similarly to mathematical operators but return Boolean (True/False) results. Logical operators combine multiple conditions for more complex decision-making.
| Operator | Meaning | Example |
|---|---|---|
| = | Equal to | If x = 5 Then |
| > | Greater than | If x > 10 Then |
| < | Less than | If x < 0 Then |
| >= | Greater than or equal to | If x >= 100 Then |
| <= | Less than or equal to | If x <= 50 Then |
| <> | Not equal to | If x <> "Yes" Then |
| Operator | Meaning | Example |
|---|---|---|
| And | Both conditions must be true | If x > 0 And x < 100 Then |
| Or | Either condition must be true | If x = "Yes" Or x = "Y" Then |
| Xor | One condition must be true but not both | If x > 0 Xor y > 0 Then |
| Not | Negates the condition | If Not x = 0 Then |
12.2 Implementing If...Then...Else Statements
The If...Then...Else structure is fundamental to controlling program flow in VBA. The complete syntax includes multiple conditional branches:
If condition1 Then
' Code to execute if condition1 is True
ElseIf condition2 Then
' Code to execute if condition2 is True
ElseIf condition3 Then
' Code to execute if condition3 is True
Else
' Code to execute if no conditions are True
End If
Note: Every If statement must conclude with End If. The Else clause is optional and can be omitted when not needed.
Practical Example: Grade Calculator
This example demonstrates how to use If...Then...Else statements to create a grade calculator that:
- Generates a random test score between 0-100
- Determines the corresponding letter grade
- Displays both the score and grade in an Excel worksheet
The Complete VBA Code
Private Sub CommandButton1_Click()
Dim mark As Integer
Dim grade As String
' Generate random mark between 0-100
mark = Int(Rnd * 100)
Cells(1, 1).Value = mark
' Determine grade based on mark
If mark < 20 And mark >= 0 Then
grade = "F"
ElseIf mark < 30 And mark >= 20 Then
grade = "E"
ElseIf mark < 40 And mark >= 30 Then
grade = "D"
ElseIf mark < 50 And mark >= 40 Then
grade = "C-"
ElseIf mark < 60 And mark >= 50 Then
grade = "C"
ElseIf mark < 70 And mark >= 60 Then
grade = "C+"
ElseIf mark < 80 And mark >= 70 Then
grade = "B"
ElseIf mark <= 100 And mark > 80 Then
grade = "A"
End If
' Display the grade
Cells(2, 1).Value = grade
End Sub
Program Output
Summary
✅ In This Lesson, You Learned:
- If...Then...Else statements enable decision-making in VBA code
- Conditions can be simple comparisons or complex logical expressions
- The Rnd function generates random numbers for testing
- Proper indentation improves code readability
- Always include End If to terminate If blocks