Faragiri

Learn Macros and VBA Excel

An insurance back office in Szolnok automates a weekly report: what the macro recorder gets wrong every time, the four lines that fix it, and when a macro is the wrong answer.

7Sections
4Min read
4Depth

Reka produces a claims summary every Thursday in Szolnok: open the export, delete two columns, sort, apply a filter, copy the visible rows into a template, save with the date in the filename. Nineteen minutes, every Thursday of the year.

The fastest route into automating it is not to learn the language. It is to record the nineteen minutes, read what the recorder produced, and fix the three things it always gets wrong.

Record first

The Developer tab, then Record Macro, then do the task once, then Stop. Excel writes the code for the actions you took, and reading it is how the language is learned: every menu you clicked appears as a line, so the vocabulary arrives attached to something you already understand.

61 lines recorded, 20 useful

Record the nineteen minutes once, then read the code Excel writes

Every menu click lands as a line of VBA tied to an action you already know, so vocabulary arrives attached to real steps.

  1. 01Open the Developer tabStart from the workbook you already use every Thursday
  2. 02Click Record MacroExcel begins translating each click and keystroke into lines
  3. 03Perform the task onceDelete two columns, sort, filter, copy visible rows and save
  4. 04Stop and read the moduleReka's 61 recorded lines held about 20 lines that actually mattered

Reading a recording teaches faster than reading syntax manuals because every line maps back to a menu choice you just made.

Reka's recording was 61 lines. About 20 of them did anything.

The three things the recorder gets wrong

It records selecting. Every click becomes Range("D2").Select followed by Selection.Delete, which is how a person works and not how code needs to. Range("D2").Delete does the same thing without moving the cursor, and removing the selects makes a macro both faster and readable.

2,140 rows vs 2,380 rows

Three habits the macro recorder writes in, and the exact fix for each

The recorder copies how a human clicks instead of how code should run, leaving three traps that break next week's export.

  • Recorded selectionsMoving the cursor slows the run and clutters the code
    • Recorder writes Range("D2").Select then Selection.Delete
    • Replace with Range("D2").Delete directly
    • Macro runs faster and reads cleanly
  • Active sheet assumptionCode fires on whichever tab happens to be in front
    • Running on the wrong tab edits the wrong data
    • Name the target worksheet on every range call
Three recorder traps
  • Hardcoded row boundsA fixed A1:H2141 range drops new rows silently
    • Export grew from 2,140 to 2,380 rows next week
    • Bottom 240 rows were left out with no error
    • Fix with Cells(Rows.Count, 1).End(xlUp).Row

Finding the last row dynamically is the single highest-payoff line in VBA because export lengths shift every week.

It records the exact cells. If the export had 2,140 rows on the day you recorded, the macro sorts A1:H2141 forever, and next week's 2,380 rows lose the bottom 240 silently. The repair is to find the last row rather than name it: Cells(Rows.Count, 1).End(xlUp).Row is the line that does it, and it is the single most useful line in the whole subject.

It records the active sheet, whatever it was. Run the macro with a different sheet in front and it does the work in the wrong place. Naming the sheet in each line fixes it.

The four lines worth adding

Option Explicit at the top of the module, which forces every variable to be declared and turns a typo into an error instead of an empty value. It is the single highest-value line in VBA.

40 seconds down to 4

Four additions that turn a cleaned recording into safe, fast code

None of these four lines come from the recorder, yet together they catch typos, cut redraw time and protect future maintainers.

Added before shipping the macro

  1. YesOption Explicit at the topForces variable declarations so a typo throws an error instead of returning emptyYes
  2. YesApplication.ScreenUpdating toggleFalse at start and True at end cut Reka's runtime from 40 seconds to 4Yes
  3. YesColumn heading sanity checkStops with a message if the expected header moved before deleting anythingYes
  4. YesPurpose and owner commentTells whoever inherits the workbook in two years what it does and who to askYes

Option Explicit is the single highest-value line because silent variable typos are harder to spot than syntax crashes.

Application.ScreenUpdating = False at the start and True at the end, which stops Excel redrawing after every step. Reka's macro went from 40 seconds to 4.

A check before anything destructive: if the expected column heading is not where it should be, stop with a message rather than deleting the wrong column.

And a comment saying what the macro is for and who to ask, because in two years the person running it will not be the person who wrote it.

What a macro cannot fix

It automates the steps, so it also automates a bad process at speed. Reka's original included copying into a template that already had last week's data below the paste area, which was harmless when a person could see it and produced a 4,000-row file on the first automated run.

Nineteen minutes, 4,000 rows

Automation accelerates a bad template faster than a person can notice

A manual copy hides template clutter when a human eye catches it; a macro pastes straight on top and compounds the mess.

  • Visible paste area looks normalA person pasting by hand spots old leftover rows below
  • Clean sequence of recorded stepsDelete columns, sort, filter and copy run without error
What looks like a working nineteen-minute procedure
  • Last week's data sitting below the paste rangeTemplate never cleared old rows before receiving new exports
  • 4,000-row file on the first automated runCode faithfully executes a flawed process at full machine speed

Examine the nineteen minutes of manual steps for hidden template debt before encoding them into a macro.

Nineteen minutes of manual work is worth examining before it is worth encoding.

When a macro is the wrong answer

When the task is importing and reshaping data, which is what a query does better, with steps anybody can read and no code to maintain.

Nineteen minutes, every Thursday

Five answers to the same weekly task, placed by how much they cost to keep working

Placed by maintenance rather than by effort to build. The rightmost is the most powerful and the most expensive to still have working in three years.

placed by what it costs to keep working, not by what it costs to build

nothing to maintaincode somebody has to own
  1. Do it by handRight for one-off tasks, though nineteen minutes every Thursday adds up across a year
  2. A better processThe template with last week's data underneath was the real fault
  3. Power QuerySteps anybody can read, no code, and the right answer for import and reshape
  4. A recorded macro, cleaned upSelects removed, last row found, sheet named. About twenty useful lines
  5. Written VBAAnything involving other applications, branching decisions or judgement, and it needs an owner

The rail says nothing about where the file has to open, which overrides all of it: the two right-hand positions do not run in Excel on the web or where policy blocks macro-enabled files.

When the file has to open where macros do not run: Excel on the web, and most managed environments where macro-enabled files are blocked by policy. A macro-enabled workbook that arrives at a colleague as a blocked attachment is a common and avoidable outcome.

And when the task involves other applications, decisions or judgement, where automating it produces something confidently wrong every week rather than once.

The file format, which catches people

A workbook with macros must be saved as .xlsm. Saving as .xlsx silently discards the code, with a warning most people click through, and the macro is gone with no way back.

Save dialog

One save format keeps the macro, and the default discards it silently

Most people click past the warning dialog without reading it, stripping every line of code out of the workbook.

Which workbook format do you choose when saving?
  • Save as .xlsm
    Macro-enabled workbookPreserves the VBA modules alongside your sheets
    • Code stays intact for next Thursday's run
    • Blocked in web Excel and strict attachment policies
  • Save as .xlsx
    Standard workbookStrips all VBA code on save with one warning prompt
    • Clicking through discards the macro permanently
    • No undo or recovery once the file closes

Check the file extension before closing a freshly recorded workbook, because .xlsx drops the code with no second chance.

What to use, as of 2026

The macro recorder, on the Developer tab, unchanged for two decades and still the fastest way to learn what a line of VBA corresponds to.

What to use, as of 2026

Four tools around Excel automation in 2026, each solving a different job

Desktop VBA, browser TypeScript scripts, Power Query and model-assisted cleanup sit side by side rather than forming a single upgrade path.

2026 Excel automation
  • Macro recorderOn the Developer tab, still the fastest way to learn what VBA lines correspond to
  • Office ScriptsRecords TypeScript in Excel on the web, separate from desktop VBA
  • Power QueryHandles import and reshape without writing or maintaining macro code
  • AI model cleanupStrips selects and adds dynamic last row well, but misses template clutter behind 4,000-row bugs
  • Macro recorderOn the Developer tab, still the fastest way to learn what VBA lines correspond to
  • Office ScriptsRecords TypeScript in Excel on the web, separate from desktop VBA
  • Power QueryHandles import and reshape without writing or maintaining macro code
  • AI model cleanupStrips selects and adds dynamic last row well, but misses template clutter behind 4,000-row bugs

Office Scripts and desktop VBA are parallel alternatives rather than a migration path, so pick based on where the file opens.

Office Scripts, in Excel on the web, which records in TypeScript rather than VBA and runs where VBA does not. In 2026 it is the direction for anything that has to work in a browser, and it does not run in desktop Excel's macro world, so the two are alternatives rather than a migration.

Power Query for anything that is import and reshape, which removed most of the reasons a back office used to write macros at all.

A model will read a recorded macro and rewrite it without the selects and with a dynamic last row, free on every major assistant, and it does that well because it is a mechanical transformation. It cannot know that your template has last week's data underneath the paste area, and that is the fault that turns a working macro into a 4,000-row file.

Deciding whether the weekly task is an import rather than a process is repeatable imports and transformations, and the last-row problem is the same one as cells, references and the interface. Whether the summary the macro builds should have been a pivot is summarising thousands of rows in seconds, under the most used analysis tool in the world.

Alongside this

Above this