Lesson 10: Mastering If...Then...Else Statements

❮ Previous Next Lesson ❯

In Excel VBA 365, decision making is fundamental to creating dynamic and responsive macros. The If...Then...Else statement is the cornerstone of conditional logic, allowing your VBA programs to make decisions and execute different code blocks based on specific conditions.

10.1 Understanding Conditional Logic in VBA

Conditional statements evaluate whether a condition is True or False and then direct program flow accordingly. To implement effective decision-making in your VBA macros, you'll need to combine If...Then..Else statements with conditional operators and logical operators.

Conditional Operators

These operators compare values and return Boolean results (True/False):

Table 10.1: VBA Conditional Operators
Operator	Syntax Example	Description
=	`x = y`	True if x equals y
>	`x > y`	True if x is greater than y
<	`x < y`	True if x is less than y
>=	`x >= y`	True if x is greater than or equal to y
<=	`x <= y`	True if x is less than or equal to y
<>	`x <> y`	True if x is not equal to y

Logical Operators

These operators combine or modify Boolean expressions:

Table 10.2: VBA Logical Operators
Operator	Syntax Example	Description
And	`x > 5 And x < 10`	True only if both conditions are true
Or	`x = 5 Or x = 10`	True if either condition is true
Xor	`x = 5 Xor y = 10`	True if either condition is true but not both
Not	`Not (x = y)`	Reverses the logical result (True becomes False and vice versa)

10.2 Practical Example: Grading System

Let's implement a practical grading system that demonstrates the power of If...Then...Else statements. This program will:

Generate a random test score between 0 and 100
Display the score in cell A1
Determine the corresponding letter grade
Display the grade in cell A2

We'll use the Rnd function to generate random numbers. To create random integers between 0 and 100, we use Int(Rnd*100). Here's how it works:

When Rnd = 0.6543, then Rnd*100 = 65.43
Int(65.43) = 65
The statement Cells(1,1).Value = mark places 65 into cell A1

Here's the complete code for our grading system:

Private Sub CommandButton1_Click()
    Dim mark As Integer
    Dim grade As String
    
    ' Generate random mark between 0-100
    mark = Int(Rnd * 100)
    
    ' Display mark in cell A1
    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"
    Else
        grade = "Invalid Score"
    End If
    
    ' Display grade in cell A2
    Cells(2, 1).Value = grade
End Sub

Excel VBA If Then Else output example showing grade calculation — Figure 10.1: The Output - Random score with corresponding grade

10.3 Additional Examples

Example 1: Simple If Statement

The most basic form of conditional logic:

' Check if a value is positive
If Cells(1, 1).Value > 0 Then
    MsgBox "The value is positive"
End If

Example 2: If-Else Statement

Adding an alternative path when the condition is false:

' Check if a cell is empty
If IsEmpty(Cells(1, 1)) Then
    MsgBox "Cell is empty"
Else
    MsgBox "Cell contains: " & Cells(1, 1).Value
End If

Example 3: Nested If Statements

Complex decision-making with multiple levels:

' Determine discount level based on purchase amount
Dim purchaseAmount As Double
purchaseAmount = Cells(1, 1).Value

If purchaseAmount > 1000 Then
    If purchaseAmount > 5000 Then
        discount = 0.15 ' 15% discount
    Else
        discount = 0.1 ' 10% discount
    End If
ElseIf purchaseAmount > 500 Then
    discount = 0.05 ' 5% discount
Else
    discount = 0 ' No discount
End If

Cells(1, 2).Value = "Discount: " & Format(discount, "0%")

10.4 Best Practices for If...Then...Else

Order conditions carefully: Place the most likely or restrictive conditions first for better performance
Use proper indentation: Makes complex logic easier to read and maintain
Consider Select Case: For multiple conditions against a single variable, Select Case might be cleaner (covered in next lesson)
Add comments: Explain complex conditions for future reference
Test edge cases: Always test the boundaries of your conditions

Common Mistakes to Avoid

Using = (assignment) instead of = (equality comparison)
Forgetting the End If statement
Overlapping conditions that create unreachable code
Not accounting for all possible cases (especially with numeric ranges)
Creating overly complex nested conditions that are hard to debug

Pro Tip: You can write a simple If statement on one line without End If when there's only one action:
If x > 10 Then MsgBox "Value is large"

Summary

If...Then...Else is VBA's primary decision-making structure
Combine with conditional operators (=, >, <, >=, <=, <>) for comparisons
Use logical operators (And, Or, Xor, Not) to combine conditions
ElseIf allows testing multiple conditions sequentially
Always include an End If for multi-line If statements
Proper indentation improves code readability
Consider Select Case as an alternative for multiple conditions (next lesson)
Test your conditions thoroughly, especially boundary values

🔗 Related Resources

❮ Previous Lesson Next Lesson ❯

Contact: Facebook Page