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

VBA Programming Exercises

Learn to automate repetitive Excel tasks using VBA macros, variables, conditions, loops, worksheet objects, and error handling. Practise creating reusable macros for data entry, report formatting, calculations, and business report automation.

1. Introduction to VBA Programming

VBA stands for Visual Basic for Applications. It is the programming language built into Microsoft Office applications, including Excel. VBA allows users to automate repetitive operations and create procedures that perform tasks automatically.

For example, a sales reporting task may require formatting headings, calculating totals, highlighting important values, and preparing reports. Instead of performing each operation manually, a VBA macro can execute these steps in sequence.

VBA is particularly useful when a task follows a predictable process and needs to be repeated regularly. However, macros should be tested carefully because they can change workbook data.

Learning objective: Write, execute, and test VBA macros that automate Excel operations while keeping calculations accurate and workbook data safe.
VBA Automation Workflow
Define the Task Write and Run the VBA Macro Process Excel Data and Worksheets Validate Results Produce the Output

A reliable macro follows a defined process, validates its results, and produces the required output.

2. Set Up the VBA Environment

Before writing VBA code, enable the Developer tab in Excel and open the Visual Basic Editor.

Step 1: Enable the Developer Tab
  1. Open Microsoft Excel.
  2. Select File → Options.
  3. Choose Customize Ribbon.
  4. Enable the Developer checkbox.
  5. Click OK.
Step 2: Open the VBA Editor

Press Alt + F11 to open the Visual Basic Editor. This is where VBA modules and procedures are created.

Step 3: Insert a Module
  1. Open the Project Explorer.
  2. Select your workbook project.
  3. Choose Insert → Module.
  4. Write or paste the VBA code into the module window.
Step 4: Save the Workbook

Save a workbook containing VBA code as an Excel Macro-Enabled Workbook (.xlsm). The standard .xlsx format does not preserve VBA macros.

Important: Run macros only when you trust their source. Test your code on a practice workbook and keep a backup before executing macros that modify worksheets or overwrite data.

3. Understand VBA Program Structure

A VBA procedure is a named block of instructions. A Sub procedure can perform an operation such as displaying a message, calculating a value, or formatting a worksheet.

VBA Code
Option Explicit

Sub FirstMacro()

    MsgBox "Welcome to VBA Programming!"

End Sub
Explanation of the Code
Statement Purpose
Option Explicit Requires variables to be declared before use.
Sub FirstMacro() Begins a procedure named FirstMacro.
MsgBox Displays a message box to the user.
End Sub Marks the end of the procedure.
How to Execute the Macro
  1. Open the VBA Editor.
  2. Insert a module and paste the code.
  3. Place the cursor inside the procedure.
  4. Press F5 to run the macro.
  5. Check the message displayed by Excel.
Expected output: A message box displays “Welcome to VBA Programming!”

4. Variables and Data Types

Variables store values temporarily while a procedure is running. Declare variables using the Dim statement and select an appropriate data type for each value.

VBA Code
Option Explicit

Sub CalculateTotal()

    Dim quantity As Long
    Dim price As Double
    Dim total As Double

    quantity = 5
    price = 250

    total = quantity * price

    MsgBox "Total Amount: " & total

End Sub
Common VBA Data Types
Data Type Purpose Example
Integer Stores smaller whole numbers. Dim count As Integer
Long Stores whole numbers with a larger range. Dim rowNumber As Long
Double Stores floating-point numeric values. Dim price As Double
String Stores text. Dim employeeName As String
Boolean Stores True or False. Dim isValid As Boolean
Date Stores dates and times. Dim orderDate As Date
Worksheet References an Excel worksheet object. Dim ws As Worksheet
Practice: Change quantity to 10 and price to 150. Run the macro and verify that the total amount is 1500.

5. Conditional Statements in VBA

Conditional statements allow a macro to make decisions based on values. The If...Then...Else structure is commonly used to check sales targets, marks, attendance, and other business conditions.

VBA Code
Sub CheckSalesTarget()

    Dim sales As Double
    Dim target As Double

    sales = 75000
    target = 60000

    If sales >= target Then

        MsgBox "Sales target achieved."

    Else

        MsgBox "Sales target not achieved."

    End If

End Sub
Explanation
  1. Declare variables for actual sales and the target.
  2. Assign values to both variables.
  3. Compare sales with the target using the greater-than-or-equal operator.
  4. Display the appropriate message for the condition.
Multiple Conditions
VBA Code
Sub CheckPerformance()

    Dim marks As Double

    marks = 82

    If marks >= 75 Then

        MsgBox "Distinction"

    ElseIf marks >= 40 Then

        MsgBox "Pass"

    Else

        MsgBox "Fail"

    End If

End Sub

The conditions are evaluated from top to bottom. The first condition that evaluates to True determines which block runs.

Practice: Change marks to 65, 35, and 90. Execute the macro each time and observe the message.

6. Loops in VBA

Loops repeat a block of instructions. They are useful when processing multiple rows, filling serial numbers, formatting ranges, or checking a large number of records.

6.1 For Loop
VBA Code
Sub FillSerialNumbers()

    Dim i As Long

    For i = 1 To 10

        Cells(i, 1).Value = i

    Next i

End Sub

This macro writes numbers from 1 to 10 into cells A1 to A10 of the active worksheet.

6.2 For Each Loop
VBA Code
Sub FormatAllWorksheetHeaders()

    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets

        ws.Range("A1:C1").Font.Bold = True
        ws.Columns("A:C").AutoFit

    Next ws

End Sub

The For Each loop visits every worksheet in the workbook containing the code and formats the specified columns.

Important: The first macro writes to A1:A10 on the active worksheet. The second macro changes every worksheet in ThisWorkbook. Test both on a copy of your workbook before using them on important data.
How a VBA Loop Works
Initialize Counter Condition True? No Finish Yes Perform Action Increment counter and repeat

A loop checks its condition, executes the instructions, advances the counter, and stops when the condition is false.

7. Work with Excel Worksheets and Cells

VBA provides objects such as Workbook, Worksheet, Range, and Cells to interact with Excel. These objects allow macros to read data, write values, apply formatting, and perform calculations.

7.1 Write Data into Cells
VBA Code
Sub WriteEmployeeDetails()

    Range("A1").Value = "Employee Name"
    Range("B1").Value = "Department"

    Range("A2").Value = "Amit"
    Range("B2").Value = "Sales"

End Sub
7.2 Read a Cell Value
VBA Code
Sub ReadEmployeeName()

    Dim employeeName As String

    employeeName = CStr(Range("A2").Value)

    MsgBox "Employee Name: " & employeeName

End Sub
7.3 Use Worksheet Variables
VBA Code
Sub WriteUsingWorksheetObject()

    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Sheet1")

    ws.Range("A1").Value = "Sales Report"
    ws.Range("A2").Value = 50000

End Sub

Replace Sheet1 with the actual worksheet name in your workbook. Using a worksheet variable helps make the target of the operation explicit.

Important: Unqualified references such as Range("A1") generally refer to the active worksheet. Use a worksheet object when the macro must work with a specific worksheet.

8. Automate Excel Report Formatting

Formatting is a common repetitive task in Excel. VBA can apply consistent heading styles, font colours, background colours, alignment, and column widths.

VBA Code
Sub FormatSalesReport()

    Dim ws As Worksheet

    Set ws = ActiveSheet

    With ws.Range("A1:C1")

        .Font.Bold = True
        .Font.Color = RGB(255, 255, 255)
        .Interior.Color = RGB(31, 78, 121)
        .HorizontalAlignment = xlCenter

    End With

    ws.Columns("A:C").AutoFit

    MsgBox "Sales report formatting completed."

End Sub
Explanation of Formatting Properties
Property Purpose
Font.Bold Makes the text bold.
Font.Color Changes the font colour.
Interior.Color Changes the cell background colour.
HorizontalAlignment Controls horizontal alignment.
Columns("A:C").AutoFit Adjusts column widths to fit their contents.
Practice: Create a worksheet with Employee Name, Department, and Salary headings in A1:C1. Run the macro and observe the formatting.

9. Process Data Using VBA

A macro can read rows, validate values, apply business rules, and write the results into a worksheet. The following exercise uses a sales dataset to calculate the status of each record.

Sample Dataset
Employee Sales (₹) Sales Status
Amit 75000 Target Achieved
Neha 42000 Below Target
Priya 68000 Target Achieved
Omkar 55000 Target Achieved

Assume the Employee heading is in A1 and Sales is in B1. The macro will write Sales Status into column C.

VBA Code
Option Explicit

Sub CheckEmployeeSales()

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim salesValue As Double

    Set ws = ActiveSheet

    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

    ws.Range("C1").Value = "Sales Status"

    For i = 2 To lastRow

        If Len(Trim(CStr(ws.Cells(i, "B").Value))) = 0 Then

            ws.Cells(i, "C").Value = "Missing Sales"

        ElseIf IsNumeric(ws.Cells(i, "B").Value) Then

            salesValue = CDbl(ws.Cells(i, "B").Value)

            If salesValue >= 50000 Then
                ws.Cells(i, "C").Value = "Target Achieved"
            Else
                ws.Cells(i, "C").Value = "Below Target"
            End If

        Else

            ws.Cells(i, "C").Value = "Check Sales Data"

        End If

    Next i

    MsgBox "Sales status processing completed."

End Sub
Step-by-Step Explanation
  1. Assign the active worksheet to the ws variable.
  2. Find the last used row in column B.
  3. Set the heading for the output column.
  4. Loop through the data rows beginning at row 2.
  5. Check whether the sales cell is blank.
  6. Verify that the sales value is numeric.
  7. Compare valid sales with the target of ₹50,000.
  8. Write the result into column C.
Important: Run the macro on a practice copy. It writes values into column C and assumes that row 1 contains headings.

10. Error Handling in VBA

Error handling helps a procedure respond to unexpected runtime errors. It is useful when a macro works with worksheets, external files, calculations, or data that may not be in the expected format.

VBA Code
Sub SafeDivision()

    On Error GoTo ErrorHandler

    Dim numerator As Double
    Dim denominator As Double
    Dim result As Double

    numerator = 100
    denominator = 0

    If denominator = 0 Then

        MsgBox "Denominator cannot be zero."
        Exit Sub

    End If

    result = numerator / denominator

    MsgBox "Result: " & result

    Exit Sub

ErrorHandler:

    MsgBox "Unexpected error: " & Err.Description

End Sub
Important Error Handling Statements
  • On Error GoTo ErrorHandler directs a runtime error to the named error-handling section.
  • Err.Description provides a description of the current runtime error.
  • Exit Sub ends the procedure before the error handler section is reached during normal execution.

The denominator check prevents division by zero in this example. The error handler provides additional protection against other runtime errors; it does not replace validation of input values.

11. VBA Automation Workflow for Reports

A report automation macro can combine several operations into one reusable procedure. For example, it can prepare headings, calculate totals, format the report, and display a completion message.

Automated Reporting Process
1. Prepare Source Data 2. Validate Records 3. Calculate and Format 4. Review and Save the Report Validate the results before sharing the final workbook.
Report Automation Checklist
  • Confirm that the source worksheet exists.
  • Check that the expected columns contain valid data.
  • Calculate totals and summary values.
  • Apply consistent headings and number formats.
  • Check the final report against the source data.
  • Save the workbook after reviewing the output.

12. Practical VBA Programming Exercises

Complete the following exercises in Excel. Write each macro in a separate procedure, run it with sample data, and verify the output before moving to the next exercise.

No. Exercise Concept Expected Result
1 Display your name and today's date. MsgBox and Date Display both values in a message box.
2 Calculate the total price using quantity and price. Variables Display the calculated amount.
3 Check whether marks meet the passing threshold. If...Then...Else Display Pass or Fail.
4 Write serial numbers from 1 to 50 in column A. For loop Populate cells A1:A50.
5 Format the heading row of a worksheet. Range and formatting Bold headings with adjusted columns.
6 Identify sales values above ₹50,000. Conditions and cells Display the status for each record.
7 Apply formatting to every worksheet. For Each loop Format the selected headings in each sheet.
8 Count non-empty employee names in column A. Worksheet functions Display the count of non-empty names.
9 Display a warning if a required cell is blank. Validation Show a warning for missing data.
10 Create a reusable sales report formatting macro. Automation Produce a consistently formatted report.

13. Mini Project — Sales Report Automation

Build a VBA macro that checks sales records, calculates each employee's sales status, formats the report headings, and displays a completion message.

Sample Data
Employee Department Sales (₹) Status
Amit Sales 75000 Achieved
Neha Sales 42000 Below Target
Priya Sales 68000 Achieved
Omkar Sales 55000 Achieved
Project Requirements
  1. Read the employee and sales details from the worksheet.
  2. Check whether sales values are missing or invalid.
  3. Use ₹50,000 as the sales target.
  4. Write the status into the output column.
  5. Format the heading row and adjust column widths.
  6. Display a completion message after processing.
  7. Test the macro with both valid and invalid data.
Evaluation Checklist
  • ☐ The macro executes without an unhandled error.
  • ☐ Sales statuses are calculated correctly.
  • ☐ Missing or invalid sales values are handled.
  • ☐ The report headings are formatted consistently.
  • ☐ The source data is preserved where required.
  • ☐ The workbook is saved in .xlsm format.
Final task: Complete the macro, test it on a practice workbook, and compare its output with the expected sales statuses before submitting the project.

14. Common VBA Programming Mistakes

Mistake Why It Is a Problem Recommended Improvement
Using undeclared variables Typing errors can create unexpected variables. Use Option Explicit and declare variables.
Using the wrong worksheet Data may be written to an unintended sheet. Reference the required worksheet explicitly.
Using an incorrect loop range Rows may be skipped or unintended cells changed. Check loop boundaries and the last used row.
Ignoring invalid data Unexpected values may cause incorrect results. Validate input before performing calculations.
Not testing the macro Errors may go unnoticed until the report is used. Test different input values and edge cases.
Saving as .xlsx VBA code is not preserved in the standard format. Use the .xlsm format for macro-enabled workbooks.

15. Important Points

  • VBA automates repetitive Excel operations using macros.
  • Use Sub and End Sub to define procedures.
  • Declare variables with Dim and choose suitable data types.
  • Use If...Then...Else for decision-making.
  • Use For and For Each loops to process repeated tasks.
  • Use Range and Cells to read and write worksheet data.
  • Specify the worksheet when the target sheet matters.
  • Validate values and handle runtime errors appropriately.
  • Save workbooks containing VBA code in .xlsm format.
  • Test macros on sample data and maintain backups before making important workbook changes.

16. Summary

In this module, you learned how to set up the VBA environment, write procedures, declare variables, use conditional statements and loops, interact with worksheet cells, format reports, process records, and handle runtime errors.

You also practised building a sales report automation workflow and explored common programming mistakes. These skills provide a foundation for automating repetitive reporting tasks in Excel.

Complete the practical exercises and Sales Report Automation mini project before moving to the Final Assignments module.