Part IX: Automation with Macros & VBA

Macros — Recording & Running Automated Tasks

📋 Learning Objectives

• Understand what macros are and why they automate repetitive tasks
• Enable the Developer tab and record your first macro
• Run macros via the ribbon, shortcut keys, and buttons
• Differentiate between absolute and relative recording
• Configure macro security settings and save as .xlsm
• Use the Personal Macro Workbook for cross-file macros

What Are Macros?

A macro is a recorded sequence of actions in Excel that can be replayed with a single click or shortcut key. Think of it like a TV remote's "macro button" — one press turns on the TV, sets the volume, and switches to your favourite channel. In Excel, a macro might select a range, apply bold formatting, add borders, set column widths, and freeze panes — all automatically.

Behind the scenes, when you record a macro, Excel writes VBA (Visual Basic for Applications) code that represents every action you performed. You don't need to know VBA to record macros — Excel does the coding for you.

Step 1: Enabling the Developer Tab

The Developer tab is hidden by default in Excel. You must enable it before recording macros.

1. Go to File → Options
2. Click Customize Ribbon in the left panel
3. In the right panel under "Main Tabs", check the box next to Developer
4. Click OK

Step 2: Recording a Macro

1. Click Developer → Record Macro
2. In the dialog box:
   • Macro name: e.g., FormatReport (no spaces, start with letter)
   • Shortcut key: e.g., Ctrl+Shift+F
   • Store macro in: This Workbook / New Workbook / Personal Macro Workbook
   • Description: "Applies standard header formatting to reports"
3. Click OK — recording starts (notice the blue square in the status bar)
4. Perform your actions (format cells, adjust widths, etc.)
5. Click Developer → Stop Recording

Step 3: Running Macros

Three ways to run a recorded macro:

Relative vs Absolute Recording

This is one of the most important concepts in macro recording:

Macro Security Settings

Navigate to File → Options → Trust Center → Trust Center Settings → Macro Settings:

Saving as .xlsm (Macro-Enabled Workbook)

Standard .xlsx files cannot store macros. When you save a workbook containing macros, Excel will prompt you to save as .xlsm (macro-enabled). If you save as .xlsx, all macros are stripped out permanently!

Personal Macro Workbook (PERSONAL.XLSB)

The Personal Macro Workbook is a hidden workbook that opens automatically every time Excel starts. Macros stored here are available in every workbook you open — perfect for universal formatting macros.

1. When recording, set "Store macro in" to Personal Macro Workbook
2. Excel creates PERSONAL.XLSB in: C:\Users\[Name]\AppData\Roaming\Microsoft\Excel\XLSTART\
3. To edit: View → Unhide → PERSONAL.XLSB

Solved Examples

Example 1: Record a Formatting Macro

Task: Record a macro that formats any selected range with bold headers, borders, and auto-fit columns.

Steps:

1. Select range A1:E1 (headers)
2. Developer → Record Macro → Name: FormatHeaders, Shortcut: Ctrl+Shift+H
3. Apply Bold, Font Size 12, Fill Color: Dark Green
4. Select A1:E20 → Apply All Borders
5. Select columns A:E → AutoFit Column Width
6. Developer → Stop Recording

Generated VBA Code:

VBA
Sub FormatHeaders()
    Range("A1:E1").Select
    With Selection.Font
        .Bold = True
        .Size = 12
    End With
    Selection.Interior.Color = RGB(5, 150, 105)
    Range("A1:E20").Borders.LineStyle = xlContinuous
    Columns("A:E").AutoFit
End Sub

Example 2: GST Invoice Formatter

Scenario: A Flipkart seller receives daily sales data. Record a macro to add GST columns.

Macro adds columns: Subtotal = Price × Qty, CGST (9%) = Subtotal × 0.09, SGST (9%) = same, Total = Subtotal + CGST + SGST.

VBA
Sub AddGSTColumns()
    Range("D1").Value = "Subtotal"
    Range("E1").Value = "CGST 9%"
    Range("F1").Value = "SGST 9%"
    Range("G1").Value = "Total"
    Range("D2").Formula = "=B2*C2"
    Range("E2").Formula = "=D2*0.09"
    Range("F2").Formula = "=D2*0.09"
    Range("G2").Formula = "=D2+E2+F2"
    Range("D2:G2").AutoFill Destination:=Range("D2:G4")
    Range("D2:G4").NumberFormat = "₹#,##0.00"
End Sub

Example 3–6: Quick Recording Tasks

Ex 3: Macro to freeze top row and apply filter to row 1. Ex 4: Macro to insert current date in selected cell with Date function. Ex 5: Macro to sort column A ascending (A→Z). Ex 6: Macro to set print area to used range and landscape orientation.

Example 7: Relative Reference — Data Entry Template

Task: Record with relative references to enter "Name", "Amount", "Date" headers and move down.

VBA
Sub DataTemplate()
    ActiveCell.Value = "Name"
    ActiveCell.Offset(0, 1).Value = "Amount (₹)"
    ActiveCell.Offset(0, 2).Value = "Date"
    ActiveCell.Offset(1, 0).Select
End Sub

Examples 8–12: Business Scenarios

Ex 8: Macro to highlight negative values red in selection. Ex 9: Macro to remove duplicates from column A. Ex 10: Auto-save backup copy with timestamp in filename. Ex 11: Macro to merge and center title row across A1:F1. Ex 12: Macro to convert text-formatted numbers to actual numbers using Paste Special → Multiply by 1.

Practice Exercises

1. Enable the Developer tab on your Excel installation and take a screenshot of the ribbon showing the Developer tab.
2. Record a macro called BasicFormat that applies Arial font size 11, cell color light yellow, and all borders to the selection.
3. Record the same formatting macro using Relative References. Compare the VBA code generated.
4. Create a button on your worksheet and assign the BasicFormat macro to it.
5. Save a workbook with macros as both .xlsx and .xlsm. Document what happens to the macros.
6. Store a macro in PERSONAL.XLSB, close Excel, reopen, and verify the macro is available in a new blank workbook.

MCQ Quiz

VBA Fundamentals — Your First Code

📋 Learning Objectives

• Navigate the VBA Editor (VBE) and its components
• Write and run Sub procedures
• Use MsgBox and InputBox for user interaction
• Manipulate cells using Range and Cells objects
• Use With...End With for cleaner code

The VBA Editor (VBE)

Press Alt+F11 to open the VBA Editor. It has four key areas:

Inserting a Module

VBA code is stored in modules. To create one: In the VBE, click Insert → Module. A new module (Module1) appears in the Project Explorer under your workbook.

Sub Procedures

Every macro is a Sub procedure — a block of code that performs actions:

VBA
Sub MyFirstMacro()
    ' This is a comment - Excel ignores this line
    MsgBox "Hello, Excel!"
End Sub

Run it by pressing F5 or clicking the green ▶ Run button. A dialog box appears with "Hello, Excel!"

MsgBox — Displaying Messages

MsgBox displays information and can include buttons and icons:

VBA
' Simple message
MsgBox "Report generated successfully!"

' With title
MsgBox "Saved!", vbInformation, "Status"

' With Yes/No buttons
Dim result As VbMsgBoxResult
result = MsgBox("Delete all data?", vbYesNo + vbCritical, "Warning")
If result = vbYes Then
    Range("A2:E100").ClearContents
End If

InputBox — Getting User Input

VBA
Sub GetStudentName()
    Dim studentName As String
    studentName = InputBox("Enter student name:", "CBSE Report Card")
    If studentName <> "" Then
        Range("A1").Value = studentName
        MsgBox "Welcome, " & studentName & "!"
    End If
End Sub

Range Object — Manipulating Cells

The Range object is the most-used object in VBA. It represents cells:

VBA
' Set a value
Range("A1").Value = "Employee Name"

' Read a value
Dim salary As Double
salary = Range("B5").Value

' Using Cells(row, column) - great for loops
Cells(1, 1).Value = "Name"       ' Same as Range("A1")
Cells(3, 2).Value = 45000       ' Same as Range("B3")

' Select and format a range
Range("A1:D1").Font.Bold = True
Range("A1:D1").Interior.Color = RGB(5, 150, 105)

' Clear contents
Range("A2:D100").ClearContents

Worksheets and Workbooks Objects

VBA
' Reference a specific sheet
Worksheets("Sales").Range("A1").Value = "Monthly Sales"

' Add a new sheet
Worksheets.Add.Name = "Summary"

' Reference another workbook
Workbooks("Data.xlsx").Worksheets("Sheet1").Range("A1").Value

' Active objects
ActiveWorkbook.Save
ActiveSheet.Name = "Report"
ActiveCell.Value = "Hello"

With...End With Statement

Instead of repeating the object reference, use With:

VBA
' Without With (repetitive)
Range("A1").Font.Bold = True
Range("A1").Font.Size = 14
Range("A1").Font.Color = vbWhite
Range("A1").Interior.Color = RGB(5,150,105)

' With With (clean!)
With Range("A1")
    .Font.Bold = True
    .Font.Size = 14
    .Font.Color = vbWhite
    .Interior.Color = RGB(5, 150, 105)
End With

Solved Examples (15)

Example 1: Salary Calculator

VBA
Sub SalaryCalculator()
    Dim basic As Double, hra As Double, da As Double, gross As Double
    basic = InputBox("Enter Basic Salary (₹):")
    hra = basic * 0.4    ' 40% HRA
    da = basic * 0.12   ' 12% DA
    gross = basic + hra + da
    MsgBox "Basic: ₹" & basic & vbCrLf & _
           "HRA: ₹" & hra & vbCrLf & _
           "DA: ₹" & da & vbCrLf & _
           "Gross: ₹" & gross, vbInformation, "Salary Slip"
End Sub

Example 2: Write Data Table to Sheet

VBA
Sub CreateStudentTable()
    ' Headers
    Range("A1").Value = "Roll No"
    Range("B1").Value = "Name"
    Range("C1").Value = "Marks"
    Range("D1").Value = "Grade"
    ' Data
    Range("A2").Value = 101: Range("B2").Value = "Aarav Sharma"
    Range("C2").Value = 92: Range("D2").Value = "A+"
    Range("A3").Value = 102: Range("B3").Value = "Priya Patel"
    Range("C3").Value = 87: Range("D3").Value = "A"
    ' Format headers
    With Range("A1:D1")
        .Font.Bold = True
        .Interior.Color = RGB(5, 150, 105)
        .Font.Color = vbWhite
    End With
End Sub

Example 3: GST Calculator with InputBox

VBA
Sub GSTCalculator()
    Dim amount As Double, gstRate As Double
    amount = InputBox("Enter amount (₹):")
    gstRate = InputBox("Enter GST rate (5/12/18/28):")
    Dim cgst As Double, sgst As Double, total As Double
    cgst = amount * (gstRate / 200)
    sgst = cgst
    total = amount + cgst + sgst
    MsgBox "Amount: ₹" & amount & vbCrLf & _
           "CGST (" & gstRate / 2 & "%): ₹" & Format(cgst, "#,##0.00") & vbCrLf & _
           "SGST (" & gstRate / 2 & "%): ₹" & Format(sgst, "#,##0.00") & vbCrLf & _
           "Total: ₹" & Format(total, "#,##0.00"), vbInformation, "GST Bill"
End Sub

Example 4: Confirmation Before Delete

VBA
Sub ConfirmDelete()
    Dim ans As VbMsgBoxResult
    ans = MsgBox("This will delete all data in Sheet1. Continue?", _
                 vbYesNo + vbCritical, "⚠️ Confirm Delete")
    If ans = vbYes Then
        Worksheets("Sheet1").Cells.ClearContents
        MsgBox "All data cleared.", vbInformation
    Else
        MsgBox "Operation cancelled."
    End If
End Sub

Examples 5–15 (Summary)

Ex 5: Copy range A1:D10 from Sheet1 to Sheet2. Ex 6: Rename active sheet using InputBox. Ex 7: Count filled rows in column A using Cells(Rows.Count, 1).End(xlUp).Row. Ex 8: Create a new workbook and copy summary data. Ex 9: Format currency columns with ₹ symbol. Ex 10: Toggle gridlines on/off. Ex 11: Add a timestamp to cell A1 with Now. Ex 12: Set zoom to 120% for all sheets. Ex 13: Protect sheet with password from InputBox. Ex 14: Insert a row above current cell. Ex 15: Multi-sheet summary — read A1 from all sheets into a new sheet.

Practice Exercises

1. Write a Sub that displays your name, college, and favorite subject using MsgBox with vbInformation.
2. Create a VBA program that asks for length and breadth via InputBox and calculates area of a rectangle.
3. Write VBA to populate A1:A12 with month names (January to December) using Cells().
4. Use With...End With to format range B2:B10 — font Calibri, size 11, bold, blue color.
5. Write VBA to create a new worksheet named "Analysis" and write "Report Date:" in A1 with today's date in B1.
6. Create a simple ₹ to $ converter: InputBox for ₹ amount, multiply by 0.012, show result in MsgBox.
7. Write VBA that reads employee name from A2 and salary from B2, calculates 10% bonus, writes to C2.
8. Create a MsgBox with Yes/No/Cancel buttons that asks "Save changes?" and shows different messages based on choice.

MCQ Quiz

VBA Variables & Data Types

📋 Learning Objectives

• Declare variables with Dim, Private, and Public
• Choose appropriate data types for different scenarios
• Use Option Explicit to enforce declarations
• Work with constants, arrays, and type conversion functions

Declaring Variables with Dim

Variables store data values. Always declare them with Dim:

VBA
Dim studentName As String
Dim marks As Integer
Dim percentage As Double
Dim isPassed As Boolean
Dim examDate As Date

studentName = "Rahul Verma"
marks = 456
percentage = marks / 5     ' 91.2
isPassed = (percentage >= 33)
examDate = #3/15/2025#

VBA Data Types

Option Explicit

Add Option Explicit at the very top of every module. This forces you to declare all variables, catching typos:

VBA
Option Explicit

Sub Example()
    Dim totalSales As Double
    totalSales = 50000
    ' totlSales = 50000  ← This typo would cause a compile error!
End Sub

Constants

VBA
Const GST_RATE As Double = 0.18
Const COMPANY_NAME As String = "Reliance Retail"
Const MAX_STUDENTS As Long = 500
Const PI As Double = 3.14159265

Variable Scope

Arrays

VBA
' Static array
Dim marks(1 To 5) As Integer
marks(1) = 85
marks(2) = 92
marks(3) = 78
marks(4) = 95
marks(5) = 88

' Dynamic array
Dim students() As String
Dim n As Long
n = Cells(Rows.Count, 1).End(xlUp).Row
ReDim students(1 To n)

' 2D array for table data
Dim table(1 To 10, 1 To 3) As Variant

Type Conversion Functions

Solved Examples

Example 1: Student Marks Processor

VBA
Sub ProcessMarks()
    Dim subjects(1 To 5) As String
    Dim marks(1 To 5) As Integer
    Dim total As Long, avg As Double, i As Integer
    subjects(1) = "Hindi":   marks(1) = 82
    subjects(2) = "English": marks(2) = 91
    subjects(3) = "Maths":   marks(3) = 95
    subjects(4) = "Science": marks(4) = 88
    subjects(5) = "SST":     marks(5) = 79
    total = 0
    For i = 1 To 5
        Cells(i + 1, 1).Value = subjects(i)
        Cells(i + 1, 2).Value = marks(i)
        total = total + marks(i)
    Next i
    avg = total / 5
    Cells(7, 1).Value = "Average"
    Cells(7, 2).Value = Format(avg, "0.00")
End Sub

Example 2: Dynamic Array from Sheet Data

VBA
Sub ReadToArray()
    Dim lastRow As Long, i As Long
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Dim names() As String
    ReDim names(1 To lastRow - 1)
    For i = 2 To lastRow
        names(i - 1) = Cells(i, 1).Value
    Next i
    MsgBox "Loaded " & UBound(names) & " names into array."
End Sub

Examples 3–12: EMI calculator with Double, Boolean pass/fail checker, Date difference calculator for employee tenure, Constant-based tax slab calculator, 2D array for 5×3 marks table, ReDim Preserve to grow array, Type conversion for string-to-number cleanup, Module-level variable counter, Public variable shared across modules, Array-based Indian state capital quiz.

MCQ Quiz

VBA Loops & Control Structures

📋 Learning Objectives

• Use If...Then...ElseIf for decision-making
• Use Select Case for multi-branch logic
• Master For...Next, For Each, Do While, Do Until loops
• Apply loops to real worksheet data processing

If...Then...Else

VBA
Sub CheckPassFail()
    Dim marks As Integer
    marks = Range("B2").Value
    If marks >= 90 Then
        Range("C2").Value = "Distinction"
    ElseIf marks >= 75 Then
        Range("C2").Value = "First Class"
    ElseIf marks >= 60 Then
        Range("C2").Value = "Second Class"
    ElseIf marks >= 33 Then
        Range("C2").Value = "Pass"
    Else
        Range("C2").Value = "Fail"
    End If
End Sub

Select Case

VBA
Sub AssignGSTRate()
    Dim category As String, rate As Double
    category = Range("A2").Value
    Select Case category
        Case "Food Grains", "Milk"
            rate = 0      ' Exempt
        Case "Packaged Food"
            rate = 5
        Case "Electronics"
            rate = 18
        Case "Luxury", "Automobile"
            rate = 28
        Case Else
            rate = 12    ' Default
    End Select
    Range("B2").Value = rate & "%"
End Sub

For...Next Loop

VBA
Sub FillSerialNumbers()
    Dim i As Long
    For i = 1 To 100
        Cells(i + 1, 1).Value = i
    Next i
End Sub

' With Step
Sub FillEvenNumbers()
    Dim i As Long, row As Long
    row = 2
    For i = 2 To 100 Step 2
        Cells(row, 1).Value = i
        row = row + 1
    Next i
End Sub

For Each...Next Loop

VBA
Sub HighlightNegatives()
    Dim cell As Range
    For Each cell In Range("B2:B100")
        If IsNumeric(cell.Value) And cell.Value < 0 Then
            cell.Interior.Color = RGB(255, 200, 200)
            cell.Font.Color = vbRed
        End If
    Next cell
End Sub

Do While / Do Until Loops

VBA
' Process rows until empty cell found
Sub ProcessUntilEmpty()
    Dim row As Long
    row = 2
    Do While Cells(row, 1).Value <> ""
        ' Calculate total in column D
        Cells(row, 4).Value = Cells(row, 2).Value * Cells(row, 3).Value
        row = row + 1
    Loop
    MsgBox "Processed " & (row - 2) & " rows."
End Sub

Nested Loops — Multiplication Table

VBA
Sub MultiplicationTable()
    Dim i As Long, j As Long
    For i = 1 To 10
        Cells(1, i + 1).Value = i   ' Column headers
        Cells(i + 1, 1).Value = i   ' Row headers
        For j = 1 To 10
            Cells(i + 1, j + 1).Value = i * j
        Next j
    Next i
End Sub

Exit Statements

VBA
' Exit For — search for a value and stop when found
Sub FindEmployee()
    Dim i As Long, searchName As String
    searchName = InputBox("Enter employee name:")
    For i = 2 To 1000
        If Cells(i, 1).Value = searchName Then
            MsgBox "Found at row " & i & "! Salary: ₹" & Cells(i, 3).Value
            Exit For
        End If
    Next i
End Sub

Practical: Zomato Order Processor

VBA
Sub ProcessZomatoOrders()
    Dim lastRow As Long, i As Long
    Dim onTime As Long, delayed As Long
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastRow
        If Cells(i, 4).Value <= 30 Then
            Cells(i, 5).Value = "On Time ✅"
            Cells(i, 5).Interior.Color = RGB(209, 250, 229)
            onTime = onTime + 1
        Else
            Cells(i, 5).Value = "Delayed ❌"
            Cells(i, 5).Interior.Color = RGB(254, 202, 202)
            delayed = delayed + 1
        End If
    Next i
    MsgBox "On Time: " & onTime & vbCrLf & "Delayed: " & delayed
End Sub

Practice Exercises

1. Write a For loop to fill cells A1:A20 with squares (1, 4, 9, 16...).
2. Use For Each to count how many cells in B2:B50 contain the word "Pending".
3. Write a Do While loop that reads names from column A until it finds "END".
4. Use Select Case to assign Indian tax slabs (0-2.5L=0%, 2.5-5L=5%, 5-10L=20%, 10L+=30%).
5. Nested loop: Create a 12×12 multiplication table starting at A1.
6. Write a loop to find the maximum value in column C (without using WorksheetFunction.Max).
7. Process a Flipkart order list: loop through rows, apply 10% discount if Amount > ₹1000, write discounted price in next column.
8. Write a loop with Exit For that searches column A for a specific employee ID.

MCQ Quiz

VBA UserForms — Building Interactive Interfaces

📋 Learning Objectives

• Create and design UserForms with Toolbox controls
• Set control properties (Name, Caption, Font, etc.)
• Write event procedures for buttons and form interaction
• Validate form input before saving to worksheet
• Navigate between records (Previous, Next, First, Last)

Creating a UserForm

In VBE: Insert → UserForm. A blank form canvas appears with the Toolbox panel.

Toolbox Controls

Setting Properties

Select a control, then modify properties in the Properties Window (F4):

Event Procedures

VBA
' CommandButton Click — Save data to worksheet
Private Sub cmdSave_Click()
    Dim nextRow As Long
    nextRow = Worksheets("Students").Cells(Rows.Count, 1).End(xlUp).Row + 1
    With Worksheets("Students")
        .Cells(nextRow, 1).Value = txtRollNo.Value
        .Cells(nextRow, 2).Value = txtName.Value
        .Cells(nextRow, 3).Value = cmbClass.Value
        .Cells(nextRow, 4).Value = CDbl(txtMarks.Value)
        If optMale.Value Then
            .Cells(nextRow, 5).Value = "Male"
        Else
            .Cells(nextRow, 5).Value = "Female"
        End If
    End With
    MsgBox "Student record saved!", vbInformation
    ' Clear form
    txtRollNo.Value = ""
    txtName.Value = ""
    txtMarks.Value = ""
    txtRollNo.SetFocus
End Sub

' UserForm Initialize — load default values
Private Sub UserForm_Initialize()
    cmbClass.AddItem "Class 9"
    cmbClass.AddItem "Class 10"
    cmbClass.AddItem "Class 11"
    cmbClass.AddItem "Class 12"
    cmbClass.ListIndex = 0  ' Select first item
    optMale.Value = True
End Sub

Form Validation

VBA
Private Sub cmdSave_Click()
    ' Validate empty fields
    If Trim(txtName.Value) = "" Then
        MsgBox "Name cannot be empty!", vbExclamation
        txtName.SetFocus: Exit Sub
    End If
    ' Validate numeric
    If Not IsNumeric(txtMarks.Value) Then
        MsgBox "Marks must be a number!", vbExclamation
        txtMarks.SetFocus: Exit Sub
    End If
    ' Validate range
    If CDbl(txtMarks.Value) < 0 Or CDbl(txtMarks.Value) > 100 Then
        MsgBox "Marks must be 0–100!", vbExclamation
        txtMarks.SetFocus: Exit Sub
    End If
    ' All valid — save data...
End Sub

Record Navigation

VBA
Dim currentRow As Long

Private Sub cmdNext_Click()
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    If currentRow < lastRow Then
        currentRow = currentRow + 1
        LoadRecord currentRow
    End If
End Sub

Private Sub LoadRecord(row As Long)
    txtRollNo.Value = Cells(row, 1).Value
    txtName.Value = Cells(row, 2).Value
    cmbClass.Value = Cells(row, 3).Value
    lblRecordNum.Caption = "Record: " & (row - 1)
End Sub

Solved Examples (Summary)

Ex 1-3: Simple form with Label + TextBox + Save button; ComboBox for Indian states dropdown; OptionButton for gender selection. Ex 4-6: CheckBox for selecting subjects; SpinButton linked to age TextBox; ListBox showing saved records. Ex 7-9: Date validation using IsDate(); PAN number format validation using Like "?????####?"; Multi-field form clearing. Ex 10-12: Search form — enter Roll No and load matching record; Delete record confirmation; Export form data to new sheet.

Practice Exercises

1. Create a UserForm with TextBoxes for Name, Age, City and a Save button that writes to Sheet1.
2. Add a ComboBox with all 28 Indian states. Load the list in UserForm_Initialize.
3. Add validation: Name must not be empty, Age must be 18-65, City must not be empty.
4. Add Previous/Next buttons to navigate between saved records.
5. Add a Search TextBox and Search button to find records by name.
6. Create a form with CheckBoxes for skills (Excel, Word, PowerPoint, Tally) and store selected skills as comma-separated string.

MCQ Quiz

VBA Error Handling & Debugging

📋 Learning Objectives

• Understand runtime, compile, and logical errors
• Implement On Error GoTo, Resume Next, GoTo 0
• Use the Err object for error details
• Master debugging tools: breakpoints, Immediate Window, stepping

Types of Errors

On Error GoTo ErrorHandler

VBA
Sub SafeDivision()
    On Error GoTo ErrorHandler
    
    Dim num As Double, den As Double, result As Double
    num = InputBox("Enter numerator:")
    den = InputBox("Enter denominator:")
    result = num / den
    MsgBox "Result: " & result
    Exit Sub
    
ErrorHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical
End Sub

On Error Resume Next

VBA
Sub ConvertTextToNumbers()
    Dim cell As Range
    On Error Resume Next  ' Skip cells that can't convert
    For Each cell In Range("A2:A100")
        cell.Value = CDbl(cell.Value)
        If Err.Number <> 0 Then
            cell.Interior.Color = vbYellow  ' Flag problem cells
            Err.Clear
        End If
    Next cell
    On Error GoTo 0  ' Reset error handling
End Sub

Err Object

Debugging Tools

Debug.Print — Your Best Friend

VBA
Sub ProcessWithLogging()
    Dim i As Long, lastRow As Long
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Debug.Print "Processing started. Rows: " & lastRow
    For i = 2 To lastRow
        Debug.Print "Row " & i & ": " & Cells(i, 1).Value
        ' Process each row...
    Next i
    Debug.Print "Processing complete!"
End Sub

Output appears in the Immediate Window (Ctrl+G). This is essential for debugging loops.

Professional Error Handling Pattern

VBA
Sub RobustImport()
    On Error GoTo ErrorHandler
    
    Application.ScreenUpdating = False
    Dim filePath As String, wb As Workbook
    filePath = Application.GetOpenFilename("Excel Files (*.xlsx),*.xlsx")
    If filePath = "False" Then Exit Sub  ' User cancelled
    
    Set wb = Workbooks.Open(filePath)
    ' ... process data ...
    wb.Close SaveChanges:=False
    
CleanUp:
    Application.ScreenUpdating = True
    Exit Sub
    
ErrorHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical
    Debug.Print Now & " ERROR: " & Err.Number & " - " & Err.Description
    Resume CleanUp
End Sub

Solved Examples (Summary)

Ex 1-3: Safe file open with error handling; Division-by-zero trap; Type mismatch handler for InputBox. Ex 4-6: Sheet-not-found handler with On Error Resume Next; Workbook-already-open detection; Logging errors to a "Log" worksheet. Ex 7-10: Custom error with Err.Raise for invalid GST rates; Multi-file import with per-file error handling (continue on failure); Debug.Print timing for performance testing; Complete cleanup pattern with Application settings restoration.

Practice Exercises

1. Write a Sub with error handling that opens a file specified by InputBox. Handle "file not found" gracefully.
2. Create a loop that converts text-to-number in column A, flagging and skipping non-numeric cells.
3. Add breakpoints and use F8 to step through a For loop. Watch variable values in Locals Window.
4. Write an error logger that saves error details (timestamp, error number, description, sub name) to a "Log" sheet.
5. Use Debug.Print to track execution flow through an If...ElseIf...Else structure — print which branch executes.
6. Create a Sub that deliberately raises error 1004 (Application-defined error) and handles it with a user-friendly message.

MCQ Quiz

Report Automation — One-Click Report Generation

📋 Learning Objectives

• Automate report generation from raw data to finished output
• Use Workbook_Open for auto-executing code
• Build summary sheets from multiple data sheets
• Auto-format reports and export to PDF
• Create navigation menus with VBA buttons

Workbook_Open Event

Code in the ThisWorkbook module (not a regular module) runs automatically when the file opens:

VBA
' In ThisWorkbook module
Private Sub Workbook_Open()
    Worksheets("Dashboard").Activate
    MsgBox "Welcome! Report data last updated: " & _
           Format(Worksheets("Dashboard").Range("A1").Value, "dd-MMM-yyyy"), _
           vbInformation, "Sales Report System"
End Sub

Creating Summary from Multiple Sheets

VBA
Sub CreateMonthlySummary()
    On Error GoTo ErrorHandler
    Application.ScreenUpdating = False
    
    Dim ws As Worksheet, summaryWs As Worksheet
    Dim summaryRow As Long
    
    ' Create or clear Summary sheet
    On Error Resume Next
    Set summaryWs = Worksheets("Summary")
    On Error GoTo ErrorHandler
    If summaryWs Is Nothing Then
        Set summaryWs = Worksheets.Add
        summaryWs.Name = "Summary"
    Else
        summaryWs.Cells.ClearContents
    End If
    
    ' Headers
    summaryWs.Range("A1").Value = "Division"
    summaryWs.Range("B1").Value = "Total Sales (₹)"
    summaryWs.Range("C1").Value = "Transactions"
    summaryWs.Range("D1").Value = "Average Sale (₹)"
    summaryRow = 2
    
    ' Loop through all data sheets
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Summary" And ws.Name <> "Dashboard" Then
            Dim lastRow As Long
            lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
            summaryWs.Cells(summaryRow, 1).Value = ws.Name
            summaryWs.Cells(summaryRow, 2).Value = _
                Application.WorksheetFunction.Sum(ws.Range("D2:D" & lastRow))
            summaryWs.Cells(summaryRow, 3).Value = lastRow - 1
            summaryWs.Cells(summaryRow, 4).Value = _
                Application.WorksheetFunction.Average(ws.Range("D2:D" & lastRow))
            summaryRow = summaryRow + 1
        End If
    Next ws
    
CleanUp:
    Application.ScreenUpdating = True
    MsgBox "Summary created with " & (summaryRow - 2) & " divisions.", vbInformation
    Exit Sub
ErrorHandler:
    MsgBox "Error: " & Err.Description, vbCritical
    Resume CleanUp
End Sub

Auto-Formatting Reports

VBA
Sub FormatReport()
    Dim lastRow As Long, lastCol As Long
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    
    ' Header formatting
    With Range(Cells(1, 1), Cells(1, lastCol))
        .Font.Bold = True
        .Font.Size = 12
        .Font.Color = vbWhite
        .Interior.Color = RGB(5, 150, 105)
        .HorizontalAlignment = xlCenter
    End With
    
    ' Borders for all data
    With Range(Cells(1, 1), Cells(lastRow, lastCol)).Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    
    ' Auto-fit columns
    Columns("A:" & Chr(64 + lastCol)).AutoFit
    
    ' Currency format for numeric columns
    Range(Cells(2, 2), Cells(lastRow, 4)).NumberFormat = "₹#,##0.00"
    
    ' Page setup
    With ActiveSheet.PageSetup
        .Orientation = xlLandscape
        .FitToPagesWide = 1
        .FitToPagesTall = False
        .PrintTitleRows = "$1:$1"
    End With
End Sub

Exporting to PDF

VBA
Sub ExportToPDF()
    Dim filePath As String
    Dim fileName As String
    fileName = "Sales_Report_" & Format(Date, "yyyy-MM-dd") & ".pdf"
    filePath = ThisWorkbook.Path & "\" & fileName
    
    ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=filePath, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        OpenAfterPublish:=True
    
    MsgBox "PDF exported to: " & filePath, vbInformation
End Sub

' Export ALL sheets to one PDF
Sub ExportAllSheetsPDF()
    Dim filePath As String
    filePath = ThisWorkbook.Path & "\Full_Report_" & Format(Date, "yyyyMMdd") & ".pdf"
    ThisWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=filePath
    MsgBox "All sheets exported to PDF!", vbInformation
End Sub

Emailing Reports (Outlook Integration Overview)

VBA
' Requires: Tools → References → Microsoft Outlook XX.0 Object Library
Sub EmailReport()
    Dim outlookApp As Object, mail As Object
    Set outlookApp = CreateObject("Outlook.Application")
    Set mail = outlookApp.CreateItem(0)
    With mail
        .To = "manager@company.com"
        .Subject = "Monthly Sales Report - " & Format(Date, "MMMM yyyy")
        .Body = "Dear Sir/Madam," & vbCrLf & vbCrLf & _
                "Please find attached the monthly sales report." & vbCrLf & _
                "Regards," & vbCrLf & "Finance Team"
        .Attachments.Add ThisWorkbook.Path & "\Sales_Report.pdf"
        .Display  ' Use .Send to send directly
    End With
End Sub

Creating Navigation Menus

VBA
Sub CreateNavigationMenu()
    Dim ws As Worksheet, btn As Button
    Dim topPos As Long
    topPos = 50
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Menu" Then
            Set btn = Worksheets("Menu").Buttons.Add(50, topPos, 200, 30)
            btn.Caption = ws.Name
            btn.OnAction = "GoToSheet"
            topPos = topPos + 40
        End If
    Next ws
End Sub

Sub GoToSheet()
    Dim sheetName As String
    sheetName = Application.Caller.Caption
    Worksheets(sheetName).Activate
End Sub

Scheduling Discussion

VBA macros can be triggered automatically using Windows Task Scheduler:

1. Create a VBScript (.vbs) that opens the workbook (which triggers Workbook_Open)
2. Schedule the .vbs file in Task Scheduler at the desired time (e.g., 6:00 AM daily)
3. The macro runs, generates reports, exports PDFs, and closes Excel

This enables fully unattended report generation — reports are ready before employees arrive.

Solved Examples (Summary)

Ex 1-4: Workbook_Open that refreshes pivot table data; Summary sheet builder from 4 regional sheets; Complete report formatter (headers, borders, widths, page setup); PDF export with timestamped filename. Ex 5-8: Multi-sheet PDF export; Email with attachment using Outlook; Navigation menu with colored buttons; Dashboard auto-updater that reads from data sheets. Ex 9-12: Print area setter with header/footer; Backup copy creator (Save As with date); Data consolidator from multiple workbooks; Complete one-click monthly report system.

Practice Exercises

1. Write Workbook_Open code that displays a welcome message and navigates to the Dashboard sheet.
2. Create a VBA Sub that loops through all sheets and creates a summary table (Sheet Name, Row Count, Column Count).
3. Build a complete report formatter: headers, borders, auto-fit, currency format, page setup (landscape, fit to 1 page wide).
4. Export the active sheet to PDF with filename format: "ReportName_YYYYMMDD.pdf" in the same folder.
5. Create a navigation menu sheet with buttons linking to each worksheet in the workbook.
6. Build a "one-click" macro that calls FormatReport, then ExportToPDF, then shows completion message with file path.

MCQ Quiz