Master Advanced Excel & VBA

Learn Excel formulas, data analysis, dashboards, VBA programming, and business automation through structured practical lessons.

COURSE SYLLABUS
1. Excel Basics
Introduction to Advanced Excel Workbook, Worksheets, Rows & Columns Data Types & Cell Formatting Cell References & Keyboard Shortcuts
2. Advanced Excel Formulas
Advanced Formulas Introduction IF, AND, OR & IFERROR Excel Tables XLOOKUP INDEX & MATCH SUMIFS & COUNTIFS
3. Pivot Analytics
Pivot Analytics Introduction Pivot Tables Calculated Fields & Items Timelines & Slicers Text Functions & Data Cleaning
4. Excel Dashboards
Interactive Dashboards Introduction Data Validation Conditional Formatting Combo Charts Sparklines
5. Excel Data Tools
Data Integration Introduction Importing Text Files Power Query Worksheet Protection Formula Auditing
6. VBA Fundamentals
VBA Fundamentals Introduction Macro Recorder VBE Workspace VBA Objects & Properties Variables & Data Types MsgBox & InputBox If...Then...Else Select Case Loops & Dynamic Ranges
7. VBA Automation
VBA Automation Introduction Subroutines & Arguments User-Defined Functions Workbook Events Worksheet Events Form Controls & Buttons
8. VBA UserForms
UserForms Introduction UserForm Design Toolbox Controls Input Validation CRUD Operations with UserForms
9. VBA Optimization & Security
Optimization Introduction Error Handling Debugging Tools Performance Optimization VBA Project Security
10. Corporate Capstone Project
Capstone Project Introduction Data Consolidation Data Cleaning & Analysis Automated PDF Reports Dashboard Automation
11. Excel Practice & Assignments
Practice Introduction Excel Formula Exercises Pivot Table Exercises Dashboard Exercises VBA Programming Exercises Final Assignments

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.

Start program Condition True or False? False Execute False block True Execute True block
Remember: A condition is an expression that evaluates to 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
Output: Student has passed.

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
Output: Fail

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
95Grade A+
82Grade A
65Grade B
45Grade C
30Fail
Important: Check conditions in the correct order. Here, the highest marks range is checked first.

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
Note: VBA's 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
Output:
  • Purchase Amount: 6500
  • Discount: 650
  • Final Amount: 5850

10. Common Mistakes to Avoid

  • Forgetting End If in 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.

  1. Check whether a number is positive.
  2. Check whether a number is even or odd.
  3. Check whether a student has passed or failed.
  4. Assign grades using ElseIf based on marks.
  5. Find the largest of two numbers using If...Else.
  6. Find the largest of three numbers using Nested If.
  7. Check whether an age is between 18 and 60 using And.
  8. Check whether a day is Saturday or Sunday using Or.
  9. Calculate a discount based on the purchase amount.
  10. Read marks from B2 and write Pass or Fail into C2.

Important Points to Remember

  • Use If...Then for a single condition.
  • Use If...Else for two possible paths.
  • Use ElseIf to check multiple conditions.
  • Use Nested If when one decision depends on another.
  • Use And, Or, and Not for 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.