Excel VBA Lesson 12: Mastering If...Then...Else Statements in Excel VBA

❮ Previous Lesson Next Lesson ❯

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.

Table 12.1: Conditional Operators in VBA
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

Table 12.2: Logical Operators in VBA
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

Excel VBA If Then Else Example Output — Figure 12.1: Sample output of the grade calculator program

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

🔗 Related Resources

❮ Previous Lesson Next Lesson ❯