Chapter 13: Macros & VBA Basics
Macros & VBA Basics — the final major chapter in our journey from beginner to advanced Excel user.
Macros and VBA (Visual Basic for Applications) are Excel’s way of automating repetitive tasks. If you find yourself doing the same steps over and over (formatting reports, cleaning data, creating monthly summaries, emailing results, etc.), macros can save you hours every week.
Today I’ll explain everything very slowly, step by step, like a patient teacher sitting next to you in Airoli (maybe with some nice hot chai on the side while we code!). We’ll use our familiar tea shop workbook as the example.
Important notes before we start (2026 reality):
- Macros are stored in .xlsm files (not .xlsx)
- Always save your file as Excel Macro-Enabled Workbook (.xlsm)
- VBA is not enabled by default on many computers → you may need to enable it in File → Options → Trust Center → Trust Center Settings → Macro Settings → “Enable VBA macros (not recommended; potentially dangerous code can run)” or “Disable all macros with notification”
Let’s begin!
1. Recording Macros (The Easiest Way to Start)
Recording = Excel watches what you do and writes VBA code for you.
Example task: Every month you want to:
- Format the header row (bold, light blue fill, center)
- Auto-fit all columns
- Add a total row with SUM
- Apply green conditional formatting to Total > ₹10,000
Step-by-step – Record your first macro
- Open your TeaSales.xlsm workbook (or save as .xlsm now).
- Go to View tab → Macros group → Record Macro (or Developer tab → Record Macro — if Developer tab is not visible: File → Options → Customize Ribbon → check Developer)
- In the dialog box:
- Macro name: FormatMonthlyReport (no spaces, start with letter)
- Shortcut key: Ctrl + Shift + F (optional – very convenient)
- Store macro in: This Workbook
- Description: “Formats header, autofits columns, adds total, conditional formatting”
- Click OK → recording starts (small square icon appears in status bar)
- Now do the steps slowly & exactly:
- Select row 1 (headers) → Home → Bold, Font size 12, Fill color light blue, Center align, Merge A1:F1 if you want title
- Select entire table (Ctrl + A inside table) → Home → Format as Table → choose style (optional)
- Click Total Row checkbox in Table Design tab
- Select Total column → Conditional Formatting → Highlight Cells Rules → Greater Than → 10000 → Light Green Fill
- Select all columns → double-click column borders to AutoFit
- When finished → Developer tab → Stop Recording (or click the square in status bar)
- Test it:
- Go to a messy new sheet → paste raw data → make it a table
- Developer tab → Macros → select FormatMonthlyReport → Run → All your steps happen instantly!
Tip: Keep recordings short & clean. If you make a mistake during recording → stop, delete the macro, and record again.
2. Editing VBA Code (Gentle First Look)
Every recorded macro creates VBA code you can read and improve.
Open the VBA Editor:
- Alt + F11 (fastest way)
- Or Developer tab → Visual Basic
You’ll see:
- Project Explorer (left) → VBAProject (your file) → Modules → Module1 (or similar)
- Double-click Module1 → code window opens
Example of what a recorded macro looks like (yours will be similar):
Sub FormatMonthlyReport()
'
' FormatMonthlyReport Macro
' Formats header, autofits columns, adds total, conditional formatting
'
Rows("1:1").Select
Selection.Font.Bold = True
With Selection.Interior
.Color = RGB(0, 176, 240) ' light blue
End With
Selection.HorizontalAlignment = xlCenter
ActiveSheet.ListObjects("TeaSales").TableStyle = "TableStyleMedium9"
ActiveSheet.ListObjects("TeaSales").ShowTotals = True
Range("E:E").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="10000"
Selection.FormatConditions(1).Interior.Color = RGB(146, 208, 80)
Cells.Select
Selection.Columns.AutoFit
End Sub
Simple edits you can try (don’t be afraid — you can always Undo or re-record):
- Change color → replace RGB(0, 176, 240) with vbYellow or RGB(255, 255, 153)
- Add a message at the end:
vba
MsgBox "Report formatted successfully!", vbInformation - Make it work on active sheet without selecting everything (cleaner):
- Replace messy .Select lines with direct references:
vbaWith ActiveSheet.ListObjects(1) ' assumes first table
.TableStyle = "TableStyleMedium2"
.ShowTotals = True
End WithWith ActiveSheet.ListObjects(1).ListColumns("Total").DataBodyRange
.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="10000"
.FormatConditions(1).Interior.Color = vbGreen
End WithActiveSheet.UsedRange.Columns.AutoFit
- Save → Alt + Q to exit VBA editor → test with Alt + F8 → Run
3. Assigning Macros to Buttons (Make It User-Friendly)
Best way: Add a button on the sheet so anyone can click it.
Steps:
- Developer tab → Controls group → Insert → Button (Form Control) (top-left button)
- Draw the button on your sheet → dialog appears → assign macro FormatMonthlyReport → OK
- Right-click button → Edit Text → change to “Format Report”
- Format button (right-click → Format Control → Colors, font, etc.)
Now anyone clicks the button → macro runs instantly!
Alternative: Assign to Quick Access Toolbar or shape (Insert → Shapes → rectangle → right-click → Assign Macro)
4. Simple VBA Loops and Automation (First Real Coding)
For Next Loop – Repeat action for each row/item
Example – Color-code rows based on City (Mumbai = light blue, Thane = light green)
- Open VBA Editor (Alt + F11) → Insert → Module
- Paste this simple code:
Sub ColorByCity()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sales") ' change to your sheet name
Dim tbl As ListObject
Set tbl = ws.ListObjects("TeaSales") ' your table name
Dim row As ListRow
For Each row In tbl.ListRows
Select Case row.Range.Cells(1, 6).Value ' 6 = column F = City
Case "Mumbai"
row.Range.Interior.Color = RGB(221, 235, 247) ' light blue
Case "Thane"
row.Range.Interior.Color = RGB(226, 240, 217) ' light green
Case "Navi Mumbai"
row.Range.Interior.Color = RGB(255, 242, 204) ' light yellow
Case Else
row.Range.Interior.ColorIndex = xlNone
End Select
Next row
MsgBox "Color coding complete!", vbInformation
End Sub
- Run it (F5 inside editor or via Macros dialog)
- Assign to a button: “Color Rows by City”
Do…Loop example – Keep adding rows until total reaches target
Sub AddRowsUntilTarget()
Dim target As Double
target = 100000 ' target total sales
Do While Application.Sum(Range("E:E")) < target
ActiveSheet.ListObjects(1).ListRows.Add
' You can add more code here to fill new row
Loop
MsgBox "Target reached or exceeded!"
End Sub
Important beginner tips:
- Always test on a copy of your file
- Press F8 in VBA editor → step through code line by line (great for learning)
- Use F9 to set breakpoints
- Comment lines with ‘ (single quote)
Quick Summary Table – Chapter 13
| Topic | How / Shortcut | Tea Shop Example Tip |
|---|---|---|
| Record Macro | Developer → Record Macro | Format report, add total, conditional formatting |
| Edit VBA | Alt + F11 → find Module | Remove .Select, add MsgBox, change colors |
| Assign to Button | Insert → Form Button → Assign Macro | “Run Monthly Cleanup” button on sheet |
| Simple Loop (For Each) | For Each row In table.ListRows | Color rows by City, process each sale |
| Do While Loop | Do While condition … Loop | Keep adding rows until sales target is reached |
Homework / Practice (Do This Today!)
- Record a macro: “CleanAndFormat” → remove duplicates, format headers, add total row
- Open VBA editor → add a MsgBox “All done!” at the end
- Create a button on your Sales sheet → assign the macro
- Write a simple macro: HighlightHighSales → loop through Total column → if >15000 then make cell red
- Test → run → fix errors → run again
Congratulations! 🎉 You’ve now completed the full journey: from opening Excel → to building dashboards → to writing your own automation code. You’re at a level where you can confidently use Excel in any job, small business, or personal project.
If you want:
- more VBA examples (email sending, PDF export, auto-backup)
- debugging help
- next steps (Power Automate, Office Scripts, Python in Excel)
just tell me — we can continue exploring together.
You’ve done fantastic work — be proud! 💪☕ What would you like to do next? 😊
