Macros & VBA

What Are Macros?

A macro is a recorded or written sequence of actions that Excel can replay with a single click. Macros are written in VBA (Visual Basic for Applications) — a programming language built into Office.

When to use macros:

Repetitive formatting that takes 20 minutes every day → macro does it in 2 seconds
Monthly reports that require the same 15 steps → one-click macro
Custom functions Excel doesn't have built in
Automating file saves, email sending, or data transformations

Setting Up: Enable Developer Tab

The Developer tab isn't visible by default:

File → Options → Customize Ribbon
In the right list, check Developer
Click OK

The Developer tab now appears in the ribbon with buttons for Macros, Visual Basic, and Record Macro.

Recording a Macro

The easiest way to start — Excel records your actions as VBA code.

Step-by-Step

Developer → Record Macro (or Alt + T + M + R)
Set:
- Macro name: FormatReport (no spaces)
- Shortcut key: Ctrl + Shift + F (optional keyboard shortcut)
- Store macro in: This Workbook (for project use) or Personal Macro Workbook (for all workbooks)
- Description: "Formats the monthly payroll report"
Click OK — recording begins
Perform the actions: select header row, bold, fill with blue, set font white, etc.
Developer → Stop Recording (or click the blue square in the status bar)

Running a Macro

Developer → Macros → select → Run
Keyboard shortcut you assigned
A button on the sheet (covered below)

The VBA Editor

Developer → Visual Basic (or Alt + F11) opens the VBA editor.

Structure of the VBA editor:
┌─────────────────────────────────────────────────────────┐
│  [Menus: File Edit View Insert Format Debug Run Tools]  │
├────────────────┬────────────────────────────────────────┤
│ Project        │                                        │
│ ├ VBAProject   │  [Code Window — where you write VBA]  │
│ │ ├ Sheet1     │                                        │
│ │ ├ Sheet2     │  Sub FormatReport()                   │
│ │ └ ThisWorkbook│      ' your code here                 │
│ └ Modules      │  End Sub                              │
│    └ Module1   │                                        │
├────────────────┴────────────────────────────────────────┤
│  [Immediate Window — test single lines of code]         │
└─────────────────────────────────────────────────────────┘

Modules

Macros live in Modules. Insert a new module: Insert → Module. A module is just a text file containing VBA Sub procedures.

VBA Fundamentals

Sub Procedures (Macros)

Sub HelloWorld()
    MsgBox "Hello, World!"
End Sub

Sub FormatHeaders()
    ' Select the first row
    Rows(1).Select
    ' Apply formatting
    With Selection.Font
        .Bold = True
        .Color = RGB(255, 255, 255)  ' White
    End With
    With Selection.Interior
        .Color = RGB(37, 61, 105)    ' Navy #253D69
    End With
End Sub

Variables

Dim employeeName As String
Dim salary As Long
Dim taxRate As Double
Dim isManager As Boolean

employeeName = "Priya Sharma"
salary = 75000
taxRate = 0.18
isManager = True

' Declare multiple on one line:
Dim lastRow As Long, lastCol As Integer

Common Data Types

Type	Use
`String`	Text
`Long`	Large integer (use instead of Integer for safety)
`Double`	Decimal number
`Boolean`	True/False
`Date`	Date and time
`Variant`	Any type (flexible but slower)
`Range`	Excel cell range object
`Worksheet`	Sheet object
`Workbook`	Workbook object

Referencing Cells and Ranges

' By cell address:
Range("A1").Value = "Hello"
Range("A1:C10").Clear

' By row and column number:
Cells(1, 1).Value = "Hello"   ' Row 1, Column 1 = A1
Cells(2, 3).Value = 100       ' Row 2, Column 3 = C2

' Active cell:
ActiveCell.Value = "Current"

' Named range:
Range("Salary").Interior.Color = RGB(200, 255, 200)

' Using a variable:
Dim rng As Range
Set rng = Range("A1:C10")
rng.Font.Bold = True

Finding Last Row

One of the most common patterns in VBA:

Dim lastRow As Long
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
' Starts from the bottom of column A, goes up to find last used row

' Dynamic range using lastRow:
Range("A2:C" & lastRow).Select

Loops

' For loop:
Dim i As Long
For i = 1 To 10
    Cells(i, 1).Value = i * 100
Next i

' For Each — loop over a range:
Dim cell As Range
For Each cell In Range("C2:C11")
    If cell.Value > 80000 Then
        cell.Interior.Color = RGB(0, 200, 0)   ' Green
    End If
Next cell

' Do While loop:
Dim row As Long
row = 2
Do While Cells(row, 1).Value <> ""   ' While column A is not empty
    Cells(row, 2).Value = Cells(row, 3).Value * 1.10   ' Apply 10% raise
    row = row + 1
Loop

If Statements

Dim salary As Long
salary = Cells(2, 3).Value

If salary > 90000 Then
    MsgBox "Senior employee"
ElseIf salary > 70000 Then
    MsgBox "Mid-level employee"
Else
    MsgBox "Junior employee"
End If

With Statement

Avoids repeating the object name for multiple properties:

' Without With:
Range("A1").Font.Bold = True
Range("A1").Font.Size = 14
Range("A1").Font.Color = RGB(37, 61, 105)

' With With (cleaner):
With Range("A1").Font
    .Bold = True
    .Size = 14
    .Color = RGB(37, 61, 105)
End With

Working with Worksheets

' Reference sheets:
Sheets("Sheet1").Activate
Worksheets("Payroll").Range("A1").Value = "Hello"
ThisWorkbook.Sheets(1).Name = "Data"     ' Rename first sheet

' Add and delete sheets:
Sheets.Add After:=Sheets(Sheets.Count)   ' Add after last sheet
Sheets("OldSheet").Delete

' Loop over all sheets:
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
    MsgBox ws.Name
Next ws

A Complete Practical Macro

Monthly Report Formatter

Sub FormatMonthlyReport()
    Dim ws As Worksheet
    Dim lastRow As Long
    
    Set ws = ThisWorkbook.Sheets("Payroll")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Format header row
    With ws.Rows(1)
        .Font.Bold = True
        .Font.Color = RGB(255, 255, 255)
        .Interior.Color = RGB(37, 61, 105)   ' Navy #253D69
        .Font.Size = 11
    End With
    
    ' AutoFit all columns
    ws.Columns.AutoFit
    
    ' Format salary column (C) as currency
    ws.Range("C2:C" & lastRow).NumberFormat = "₹#,##0.00"
    
    ' Highlight high earners
    Dim cell As Range
    For Each cell In ws.Range("C2:C" & lastRow)
        If cell.Value > 90000 Then
            cell.Interior.Color = RGB(198, 255, 221)   ' Mint #C6FFDD
        End If
    Next cell
    
    ' Add a total row
    ws.Cells(lastRow + 2, 1).Value = "Total"
    ws.Cells(lastRow + 2, 1).Font.Bold = True
    ws.Cells(lastRow + 2, 3).Formula = "=SUM(C2:C" & lastRow & ")"
    ws.Cells(lastRow + 2, 3).Font.Bold = True
    ws.Cells(lastRow + 2, 3).NumberFormat = "₹#,##0.00"
    
    MsgBox "Report formatted successfully! " & (lastRow - 1) & " employees processed."
End Sub

Copy Data Between Sheets

Sub CopyFilteredData()
    Dim sourceWs As Worksheet, destWs As Worksheet
    Dim sourceLastRow As Long, destRow As Long
    
    Set sourceWs = ThisWorkbook.Sheets("All Employees")
    Set destWs = ThisWorkbook.Sheets("Finance Team")
    
    sourceLastRow = sourceWs.Cells(sourceWs.Rows.Count, "A").End(xlUp).Row
    destRow = 2   ' Start at row 2 in destination (row 1 = header)
    
    ' Copy header
    sourceWs.Rows(1).Copy destWs.Rows(1)
    
    ' Loop and copy Finance employees only
    Dim i As Long
    For i = 2 To sourceLastRow
        If sourceWs.Cells(i, 2).Value = "Finance" Then   ' Col B = Department
            sourceWs.Rows(i).Copy destWs.Rows(destRow)
            destRow = destRow + 1
        End If
    Next i
    
    MsgBox "Finance employees copied: " & (destRow - 2)
End Sub

Custom Functions (UDFs)

Write your own Excel functions:

' In a Module:
Function TaxableIncome(salary As Double, exemption As Double) As Double
    TaxableIncome = salary - exemption
End Function

Function GetInitials(fullName As String) As String
    Dim parts() As String
    parts = Split(fullName, " ")
    Dim initials As String
    Dim p As Integer
    For p = 0 To UBound(parts)
        initials = initials & Left(parts(p), 1) & "."
    Next p
    GetInitials = UCase(initials)
End Function

Use them like any Excel function:

=TaxableIncome(C2, 250000)   → C2 - 250000
=GetInitials("Priya Sharma") → "P.S."

Adding Buttons to Trigger Macros

Method 1: Button from Form Controls

Developer → Insert → Form Controls → Button (rectangle icon)
Draw the button on the sheet
"Assign Macro" dialog appears → select your macro
Right-click the button → Edit Text to label it

Method 2: Shape as Button

Insert → Shapes → Rounded Rectangle
Right-click the shape → Assign Macro
Format it with colours, text

Method 3: Quick Access Toolbar

File → Options → Quick Access Toolbar
Choose Commands From: Macros
Select your macro → Add
It appears in the toolbar at the top of Excel

Error Handling

Sub SafeMacro()
    On Error GoTo ErrorHandler
    
    ' Your main code here
    Range("A1").Value = 100 / 0   ' This will cause an error
    
    Exit Sub
    
ErrorHandler:
    MsgBox "An error occurred: " & Err.Description
    ' Err.Number has the error code
    ' Resume Next → skip the error and continue
    ' Resume → retry the failed line (only if you've fixed it in the handler)
End Sub

Saving Macro-Enabled Files

Regular .xlsx files cannot contain macros. When you add a macro, save as:

Excel Macro-Enabled Workbook (.xlsm) — standard choice
Excel Binary Workbook (.xlsb) — faster for large files with macros

File → Save As → change file type to .xlsm

Security note: When opening .xlsm files, Excel shows a security warning. Click "Enable Content" to allow macros to run. Never enable macros in files from unknown sources.

Practical Examples

Example 1: One-Click Report Export

Sub ExportToPDF()
    Dim filename As String
    filename = "Payroll_Report_" & Format(Now(), "YYYY-MM-DD") & ".pdf"
    
    ThisWorkbook.Sheets("Payroll").ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=Environ("USERPROFILE") & "\Desktop\" & filename, _
        Quality:=xlQualityStandard
    
    MsgBox "Report exported to Desktop as: " & filename
End Sub

Example 2: Validate and Highlight Data Issues

Sub FindDataIssues()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim issueCount As Long
    
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    issueCount = 0
    
    ' Clear previous highlights
    ws.Range("A2:E" & lastRow).Interior.ColorIndex = xlNone
    
    Dim i As Long
    For i = 2 To lastRow
        Dim hasIssue As Boolean
        hasIssue = False
        
        If ws.Cells(i, 1).Value = "" Then hasIssue = True  ' Missing name
        If ws.Cells(i, 3).Value <= 0 Then hasIssue = True  ' Invalid salary
        If Not IsDate(ws.Cells(i, 4).Value) Then hasIssue = True  ' Bad date
        
        If hasIssue Then
            ws.Range("A" & i & ":E" & i).Interior.Color = RGB(255, 200, 200)  ' Light red
            issueCount = issueCount + 1
        End If
    Next i
    
    MsgBox issueCount & " rows with data issues found and highlighted."
End Sub

Common Mistakes

1. Not using Option Explicit

' Without Option Explicit — typos create new variables silently
Slary = 75000   ' Should be Salary — but no error!
MsgBox Salary   ' Shows 0 (empty new variable)

' Add to top of every module:
Option Explicit
' Now all variables must be declared; typos cause compile errors

2. Selecting cells unnecessarily

' Slow and brittle:
Range("A1").Select
Selection.Value = "Hello"

' Fast and clean:
Range("A1").Value = "Hello"

3. Hard-coding the last row

' Breaks when data grows:
For i = 2 To 100

' Use dynamic last row:
Dim lastRow As Long
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To lastRow

4. No error handling

Without error handling, a runtime error shows a cryptic dialog and breaks the macro mid-way, leaving the workbook in a partially modified state.

Practice Exercises

Record a macro that formats a header row (bold, navy background, white text) and save it with shortcut Ctrl+Shift+H.
Write a Sub that finds the last row of data in column A and displays the count in a MsgBox.
Write a For Each loop that turns cells in column C red if the value is below 50,000, and green if above 90,000.
Create a UDF named FullBandName that takes a salary and returns: "Band A" if > 90000, "Band B" if > 75000, "Band C" otherwise.
Write a macro that copies rows where column B = "Finance" from Sheet1 to a new sheet named "Finance_Report".

Summary

In this chapter you learned:

Record a macro: Developer → Record Macro → perform steps → Stop Recording
VBA Editor: Alt+F11 — modules live here; write Sub procedures in modules
Sub structure: Sub Name() ... End Sub; variables declared with Dim
Data types: String, Long, Double, Boolean, Range, Worksheet
Referencing cells: Range("A1"), Cells(row, col), ActiveCell
Dynamic last row: Cells(Rows.Count, "A").End(xlUp).Row
Loops: For i = 1 To n, For Each cell In Range(...), Do While
With statement: group multiple property assignments on one object
Custom functions (UDFs): use Function instead of Sub; callable from worksheet
Error handling: On Error GoTo ErrorHandler prevents cryptic runtime errors
Save as .xlsm to preserve macros; .xlsx strips them silently
Option Explicit at the top of every module catches variable typos at compile time

Congratulations! You have now completed the full Excel tutorial series — from the interface and basic data entry through formulas, lookups, pivot tables, charts, automation, and VBA. You have the foundation to handle professional data work, build dashboards, and automate repetitive tasks in Excel.