4.4Macros & VBA programming
The essentials in 30 seconds
- Record, then re-read: a macro memorizes a sequence of actions in VBA — you replay the weekly report, formatted identically, in one click.
- Read the editor (Alt+F11) to progress: change a range, a value, a color in the generated code — short and focused macros, tested on a copy.
- Wassim's reflex: a macro doesn't undo (Ctrl+Z useless) and can be malicious — save the workbook as .xlsm, trusted sources only.
Running mini-case — Wassim replays his Monday formatting in one click
Wassim, an accountant in Algiers, formats the same report every Monday (frozen panes, bold headers, amounts in dinars, AutoFilter). He records a « FormatReport » macro, opens the code with Alt+F11, spots the lines and adjusts the range. Saved as .xlsm, one click replays everything — tested first on a copy, because a macro doesn't undo. Fourth milestone of the running thread: the repetitive interface work is automated, carefully.
Continuation of cases 4.1-4.3 — Wassim automates what neither Power Query nor the formulas do: the interface gestures.
1. Record a macro
A macro memorizes a sequence of actions to replay them. Developer ▸ Record Macro: you perform the gestures (format, filter, paste…), you stop recording. Excel generates VBA code. Replaying the macro reproduces everything instantly — ideal for Wassim's weekly report, formatted identically.
You keep macros short and focused: one task = one macro. The monsters that do everything are unreadable and break at the first structural change.
2. Read and adapt the VBA
The code opens in the VBA editor (Alt+F11). Even without being a developer, you can read and adapt it: change a value, a range, a color. It's the best way to learn: record, then understand the produced code — Wassim quickly spots the lines for bold and currency format.
VBA manipulates objects (workbook, sheet, range) with properties and methods: Worksheets, Range, Font.Bold, NumberFormat… Understanding this vocabulary is enough to adapt 80% of recorded macros.
3. Precautions
A workbook with macros is saved as .xlsm — as .xlsx, the macros are lost. Macros can be a virus vector: only enable those from trusted sources, with full awareness. Another limit: a macro doesn't « undo » (Ctrl+Z doesn't reverse its actions) — you test on a copy.
Finally, for simple data transformation, Power Query is often preferable to VBA: no code, refreshable, safer. VBA stays relevant for automating varied interface actions (formatting, dialog boxes, interactions between sheets).
🔎 Concrete example — 🔎 Concrete example — Automate a weekly formatting
Every Monday, Wassim formats the same report; the recorder produces this VBA:
Sub FormatReport()
' Freeze panes + bold headers (Wassim: weekly report)
Range("A2").Select
ActiveWindow.FreezePanes = True
Rows("1:1").Font.Bold = True
' Amounts in dinars + auto-filter
Columns("C:C").NumberFormat = "#,##0 DZD"
Range("A1").AutoFilter
End SubReading: the recorder turns clicks into reusable, readable code.
Lesson: you automate the repetitive AND reproducible; you test on a copy (no undo).
Section vocabulary
- Macro
- A recorded sequence of actions to be replayed in one click.
Simple example: Wassim's FormatReport replays his Monday formatting.
What it is not: a formula that recalculates by itself.
Reflex: an identical weekly gesture: record it as a macro.
- VBA
- Visual Basic for Applications: the language of Excel macros (objects, properties, methods).
Simple example: Wassim reads Range and Font.Bold in his recorded code.
What it is not: Power Query's M language (data preparation).
Reflex: adapting a macro: spot objects and properties.
- .xlsm
- The workbook format that supports macros; .xlsx loses them.
Simple example: Wassim saves his automated report as .xlsm.
What it is not: an .xlsx workbook that would keep the macros.
Reflex: macro created: save as .xlsm immediately.
- Macro security
- Macros can be malicious: enable with full awareness, trusted sources.
Simple example: Wassim only enables his own macros and those validated by the firm.
What it is not: inherently harmless code, enableable without thinking.
Reflex: macro received: check the source before enabling.
In practice — In practice — Record and re-read a macro
- Enable the Developer tab, then Record Macro.
- Perform a simple formatting, stop recording.
- Open the code (Alt+F11) and spot the matching lines.
- Change a value (e.g. color), save as .xlsm, replay on a copy.
Ready-to-say script — announce the macro to the team (30 s)
« This is Wassim: the Monday report formatting is recorded as a macro — one click replays everything: panes, bold, dinars format, filter. The workbook is .xlsm, I tested it on a copy because a macro doesn't undo. Only enable our validated macros. »
Say it before distributing the file: remind them of the .xlsm and the trusted source.
Copy-paste template — safe VBA loop over a range (with a test copy)
Sub NormalizeAmounts()
Dim c As Range
Application.ScreenUpdating = False
For Each c In Worksheets("Sales").Range("C2:C500")
If IsNumeric(c.Value) Then c.Value = Round(c.Value, 2)
Next c
Application.ScreenUpdating = True
MsgBox "Amounts normalized (tested on a copy)."
End SubWeekly formatting
Like Wassim: record panes, bold, DZD format and filter — one click every Monday.
Adapt the code
Change the range C2:C500 or the format in the editor: the macro follows the new structure.
Macro received by email
Unknown source: don't enable it — request validation, analyze with full awareness.
Data to transform
Prefer Power Query (no code, refreshable) over a fragile macro.
✓ Best practices
- Record first, read the code next to learn.
- Keep macros short and focused.
- Test on a copy (a macro doesn't undo).
✗ Common pitfalls
- Enabling macros from unknown sources (viruses).
- Forgetting to save as .xlsm (macros lost).
- Using VBA where Power Query would do better (data transformation).
Key takeaways
- A macro replays a sequence of actions; the code is VBA.
- You record, then read/adapt in the editor (Alt+F11).
- Workbook with macros = .xlsm; macros = a security risk.
- A macro doesn't undo: test on a copy.
48h challenge — apply tomorrow
On a copy of a real report: record your formatting, re-read the code in Alt+F11, change a value, save as .xlsm and replay.
- I know how to record and replay a focused macro.
- I know how to read and adapt the generated VBA (range, value).
- I know the precautions (.xlsm, test copy, trusted sources).
Frequently asked questions
Macro (VBA) or Power Query?
To transform/consolidate data: Power Query (no code, refreshable, safer). To automate varied interface actions (formatting, dialog boxes, interactions), VBA stays relevant.
Do you need to know how to program to use macros?
No to record and replay; a little to adapt the code. It's actually a good entry point to programming: record an action and read the matching VBA.
Which precaution is specific to macros?