Conditional Statements in VBA
Learn how to make decisions in Excel VBA using If, If...Else, ElseIf, Nested If, And, Or, and practical worksheet examples.
1. What Are Conditional Statements?
Conditional statements allow a program to make decisions based on whether a condition is True or False. VBA executes different blocks of code depending on the result of the condition.
For example, a macro can check whether a student has passed, whether sales have reached a target, or whether a product is eligible for a discount.
True or False.
2. Comparison Operators
Comparison operators compare values and produce a Boolean result. They are commonly used inside If statements.
| Operator | Meaning | Example |
|---|---|---|
= |
Equal to | marks = 75 |
<> |
Not equal to | marks <> 0 |
> |
Greater than | salary > 30000 |
< |
Less than | age < 18 |
>= |
Greater than or equal to | marks >= 40 |
<= |
Less than or equal to | quantity <= 10 |
3. Simple If Statement
Use a simple If statement when code should execute only if a condition is True.
Syntax
If condition Then
' Statements to execute
End If
Example 1: Check passing marks
Sub CheckPass()
Dim marks As Integer
marks = 75
If marks >= 40 Then
MsgBox "Student has passed."
End If
End Sub
Since 75 is greater than or equal to 40, the condition is True.
4. If...Else Statement
Use If...Else when one block should execute if the condition is True and another block should execute if it is False.
Syntax
If condition Then
' True block
Else
' False block
End If
Example 2: Pass or fail
Sub CheckResult()
Dim marks As Integer
marks = 32
If marks >= 40 Then
MsgBox "Pass"
Else
MsgBox "Fail"
End If
End Sub
5. If...ElseIf...Else Statement
Use ElseIf when multiple conditions must be checked in sequence. VBA executes the first matching branch and skips the remaining branches.
Syntax
If condition1 Then
' First block
ElseIf condition2 Then
' Second block
ElseIf condition3 Then
' Third block
Else
' Default block
End If
Example 3: Student grade
Sub CalculateGrade()
Dim marks As Integer
marks = 85
If marks >= 90 Then
MsgBox "Grade A+"
ElseIf marks >= 75 Then
MsgBox "Grade A"
ElseIf marks >= 60 Then
MsgBox "Grade B"
ElseIf marks >= 40 Then
MsgBox "Grade C"
Else
MsgBox "Fail"
End If
End Sub
| Marks | Result |
|---|---|
| 95 | Grade A+ |
| 82 | Grade A |
| 65 | Grade B |
| 45 | Grade C |
| 30 | Fail |
6. Nested If Statement
A Nested If is an If statement placed inside another If statement. It is useful when a second condition should be checked only after the first condition is satisfied.
Example 4: Eligibility check
Sub CheckEligibility()
Dim age As Integer
Dim qualification As String
age = 22
qualification = "Graduate"
If age >= 18 Then
If qualification = "Graduate" Then
MsgBox "Eligible for the application."
Else
MsgBox "Graduation is required."
End If
Else
MsgBox "Minimum age requirement is not met."
End If
End Sub
Explanation: VBA checks the qualification only after the age condition is satisfied.
7. Logical Operators: And, Or, Not
Logical operators combine or reverse conditions.
| Operator | Meaning | Example |
|---|---|---|
And |
Both conditions must be True. | age >= 18 And age <= 60 |
Or |
At least one condition must be True. | day = "Saturday" Or day = "Sunday" |
Not |
Reverses a Boolean condition. | Not isEligible |
Example 5: Check marks range
Sub ValidateMarks()
Dim marks As Integer
marks = 78
If marks >= 0 And marks <= 100 Then
MsgBox "Valid marks."
Else
MsgBox "Marks must be between 0 and 100."
End If
End Sub
Example 6: Weekend check
Sub CheckWeekend()
Dim dayName As String
dayName = "Sunday"
If dayName = "Saturday" Or dayName = "Sunday" Then
MsgBox "It is a weekend."
Else
MsgBox "It is a weekday."
End If
End Sub
And and
Or operators do not guarantee short-circuit
evaluation. Do not rely on the second condition being skipped.
8. Practical Example: Check Excel Cell Values
Suppose column A contains student names and column B contains marks. The macro below checks the marks in cell B2 and writes the result into cell C2.
| A | B | C |
|---|---|---|
| Rahul | 85 | Pass |
| Priya | 32 | Fail |
Option Explicit
Sub CheckExcelMarks()
Dim marksValue As Variant
marksValue = ThisWorkbook.Worksheets("Sheet1").Range("B2").Value
If IsError(marksValue) Then
MsgBox "Cell B2 contains an Excel error.", vbExclamation
Exit Sub
End If
If Not IsNumeric(marksValue) Then
MsgBox "Cell B2 must contain numeric marks.", vbExclamation
Exit Sub
End If
If CDbl(marksValue) < 0 Or CDbl(marksValue) > 100 Then
MsgBox "Marks must be between 0 and 100.", vbExclamation
Exit Sub
End If
If CDbl(marksValue) >= 40 Then
ThisWorkbook.Worksheets("Sheet1").Range("C2").Value = "Pass"
Else
ThisWorkbook.Worksheets("Sheet1").Range("C2").Value = "Fail"
End If
End Sub
This example validates the cell value before deciding whether
the student has passed. Ensure the workbook contains a
worksheet named Sheet1.
9. Practical Example: Calculate a Discount
Apply a 10% discount when the purchase amount is at least 5,000.
Sub CalculateDiscount()
Dim purchaseAmount As Double
Dim discount As Double
Dim finalAmount As Double
purchaseAmount = 6500
If purchaseAmount >= 5000 Then
discount = purchaseAmount * 0.1
Else
discount = 0
End If
finalAmount = purchaseAmount - discount
MsgBox "Purchase Amount: " & purchaseAmount & vbCrLf & _
"Discount: " & discount & vbCrLf & _
"Final Amount: " & finalAmount
End Sub
- Purchase Amount: 6500
- Discount: 650
- Final Amount: 5850
10. Common Mistakes to Avoid
-
Forgetting
End Ifin a multiline If statement. -
Using
=when the intended comparison is>=or<=. - Checking a broad condition before a more specific condition.
- Using nested If statements without properly closing each block.
- Comparing text values without considering spelling and spaces.
- Processing cell values before checking for Excel errors or invalid data.
11. Practice Exercises
Write VBA macros to solve the following tasks.
- Check whether a number is positive.
- Check whether a number is even or odd.
- Check whether a student has passed or failed.
- Assign grades using ElseIf based on marks.
- Find the largest of two numbers using If...Else.
- Find the largest of three numbers using Nested If.
- Check whether an age is between 18 and 60 using And.
- Check whether a day is Saturday or Sunday using Or.
- Calculate a discount based on the purchase amount.
- Read marks from B2 and write Pass or Fail into C2.
Important Points to Remember
- Use
If...Thenfor a single condition. - Use
If...Elsefor two possible paths. - Use
ElseIfto check multiple conditions. - Use Nested If when one decision depends on another.
- Use
And,Or, andNotfor logical conditions. - Validate worksheet values before performing calculations.
Summary
Conditional statements help VBA programs make decisions. Simple If executes code when a condition is True; If...Else handles two paths; ElseIf checks multiple conditions; and Nested If handles dependent decisions. Logical operators combine conditions, making it possible to build useful Excel automation.
The next topic, Select Case, provides another way to handle multiple possible values and conditions.