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