VBA I: Recorder to Real Code

Excel Mastery Track · Brown belt (Brown-2) · ~6–8 hours · Microsoft 365 on Windows assumed

A macro is a small program that does in one command what you would otherwise do in fifty clicks: format a report the same way every Monday, stamp a total into the same cell, walk down a column adding as it goes. The language those programs are written in is VBA (Visual Basic for Applications) a full programming language that has lived inside Excel for thirty years, with direct access to every cell, sheet, chart, and workbook you can touch by hand. Learn it and the repetitive tail of your analyst's week (the reformatting, the re-totalling, the copy-this-into-that) stops being work you do and becomes work Excel does while you think about the numbers.

Most people meet VBA through the macro recorder, and most people meet it dishonestly. They record a sequence of clicks, Excel writes some code, the code happens to work, and they never look at it again. That is a trap. The recorder is a magnificent teacher and a terrible programmer: it writes down every mouse twitch literally, so its code is a pile of .Select this and .Activate that, slow, fragile, and unreadable, breaking the moment a colleague clicks a different cell first. This module teaches the honest use of the recorder, the one professionals actually practise: record a task to discover which objects and properties Excel touches, then read that generated code, then refactor it into clean, direct object code you would sign your name to. Record → read → refactor. That loop is the whole art of getting started in VBA, and it is the spine of everything here.

By the end you will have toured the Visual Basic Editor (the VBE, the separate window where VBA lives), talked to Excel live through the Immediate window, understood the object model that arranges everything from the Application down to a single cell into a tidy hierarchy, written Sub procedures that address cells by Range and by Cells(row, column), collapsed repetitive object references with With blocks, declared variables with the right types, adopted the single most important habit in VBA, Option Explicit, and learned why the file must be saved as .xlsm or the macros vanish. One version note, stated once: everything here assumes desktop Microsoft 365 on Windows. VBA also runs on Excel for Mac with small differences, and does not run in Excel for the web at all, automation there is Office Scripts (TypeScript), a different tool for a later node.

Learning objectives

By the end you can:

1. Record a macro honestly, use Developer ▸ Record Macro to capture a task, then open the result in the VBE and read it, and explain why the recorder's output is a starting point, never a finished program.
2. Navigate the Visual Basic Editor fluently: the Project Explorer, the Properties window, a standard code module, and above all the Immediate window, in which you can run one line of code and see its answer at once.
3. State the Excel object model hierarchy (Application ▸ Workbook ▸ Worksheet ▸ Range/Cells) and read a dotted expression like ThisWorkbook.Worksheets("Sales").Range("B6") as a path down that tree.
4. Write a complete Sub procedure, address cells with both Range("A1") and Cells(row, column), and choose the right one for a fixed reference versus a looped or computed one.
5. Refactor recorder output, mechanically removing every .Select/.Activate/Selection/ActiveCell and replacing it with a direct, explicitly-qualified object reference.
6. Collapse repeated object references with a With block, and explain the readability and (minor) speed reasons it is preferred.
7. Declare variables with Dim and the core types (Long, Double, String, Range, Variant) and say what each is for and what it costs.
8. Adopt the Option Explicit law, and describe precisely the class of silent bug (the mistyped variable that becomes an empty Variant) it eliminates.
9. Configure macro security through the Trust Center, save a workbook in the .xlsm (or .xlsb) macro-enabled format, and run a macro five ways: F5, Alt+F8, a worksheet button, the Quick Access Toolbar, and the Immediate window.

Prerequisites & connections

Builds on. The entire formula and dynamic-array stack of Phase EX so far. That means EX0.01's habit of working in Tables (Ctrl+T), EX1.02's lookups, and above all the mental model of a workbook as a system of references and never a pile of typed numbers. VBA takes the same "never hardcode what can be fetched" ethic and pushes it one level deeper. Instead of writing a formula in a cell, you write code that writes the formula, or code that reads the cells and computes directly. You'll also want to be at ease reading a cell address (B6, $A$2:$E$5) and to know what SUM does. Your first macros reproduce in code things you already do with formulas, and that's what makes them a gentle on-ramp.

Feeds forward. This is VBA I, and it opens the automation track. EX6.02 (VBA II, control flow) turns the single straight-line Subs here into programs that make decisions (If/Select Case) and repeat (For/For Each/Do) with intent. EX6.03 (VBA III, event handling and forms) makes code run automatically when a sheet changes or a workbook opens, and adds buttons and user forms. The With-block and explicit-reference habits you build now decide whether your VBA scales or collapses under its own .Selects. On the analyst track, macros are how you industrialise a model. A macro can refresh and re-scenario M3.08's three-statement model. The monthly reporting pack of EX7.02 is a recorded-then-refactored formatting routine. And the "one button rebuilds the deck" reflex that senior analysts prize starts with the same record-read-refactor loop you'll practise here. Everything downstream assumes you can write a clean, explicitly-qualified Sub without a single .Select in it.

Core teaching content

The VBE and the Immediate window

The Visual Basic Editor is a separate application window that holds all your VBA code, and Alt+F11 flips you between it and Excel. Four panes matter now.

• Project Explorer (View ▸ Project Explorer, or Ctrl+R) is a tree of every open workbook and add-in. Under each one you'll see its sheets, its ThisWorkbook object, and a Modules folder. Code for general macros goes in a standard module, which you create with Insert ▸ Module.
• Code window is the large editor where you type. Double-click any object in the Project Explorer to open its code.
• Properties window (View ▸ Properties Window, or F4) shows the design-time properties of whatever is selected in the Project Explorer, such as a sheet's code name. You'll use it more once you reach event handling and forms.
• Immediate window (View ▸ Immediate Window, or Ctrl+G) is the analyst's live scratchpad, and the most useful thing you'll meet here.

That last pane lets you run one line of code and see the result instantly, without writing a whole Sub. Type a statement and press Enter to run it. Put a question mark in front of an expression (? is shorthand for Print) and it prints the answer:

?Application.Version
?ThisWorkbook.Worksheets("Sales").Range("B6").Value
?2 * 21.25
Range("A1").Value = "Hello"

The first line prints Excel's version number and the second prints whatever is in B6. Line three prints 42.5, and line four sets A1 to "Hello" on the fly. Inside your macros, Debug.Print sends a value to this same window. It's the VBA version of a quick print(), and it's invaluable for checking what a variable holds while you debug. Want to test one idea, like what Cells(3, 2) refers to or what some expression evaluates to? Nothing beats asking the Immediate window and reading its answer.

Range and Cells

You can point at cells in two ways, and a fluent analyst uses both on purpose.

Range takes an address string, written just as you'd type it in the Name Box: ws.Range("A1"), ws.Range("B2:B6"), or ws.Range("SalesData") for a named range. It's the natural choice when the address is fixed and known as you write the code. Think of a header row, a specific total cell, a named block. It reads like the spreadsheet, too. Range("A1:B1") obviously means those two header cells.

Cells takes a row number and a column number, so ws.Cells(6, 2) is row 6, column 2, which is cell B6. Both coordinates are numbers, and that makes Cells the tool whenever an address has to be computed or looped. Inside a For r = 2 To 8 loop, ws.Cells(r, 2) walks down column B one row at a time as r changes. Range, with its fixed string, can't do that. Column 2 means B, 3 means C, and so on. Because the column is a number too, a loop can move sideways as well (Cells(r, c)).

The two also work together. ws.Range(ws.Cells(2, 2), ws.Cells(8, 2)) builds the range B2:B8 from two computed corners, which is the idiom for "a block whose extent I calculate at run time." A single-argument Cells(n) on a range counts cells within it. Here's the rule of thumb:

• Range("...") when you know the address and it doesn't move. That keeps the code readable and self-documenting.
• Cells(r, c) when the address is computed, a loop counter, a last-row calculation, a variable column. That's the programmable form.

You'll see Cells doing real work in Worked example 2, where a loop sums a column by visiting Cells(r, 2) for each row.

With blocks

When several statements all address the same object, repeating the reference is noisy, and slightly slow too. A With block names the object once and lets you write only the trailing members:

With ws.Range("A1:B1")
    .Font.Bold = True
    .Font.Color = RGB(255, 255, 255)
    .Interior.Color = RGB(68, 114, 196)
    .HorizontalAlignment = xlCenter
End With

VBA reads every line that begins with a dot as ws.Range("A1:B1").<that member>. Without the block you'd write ws.Range("A1:B1") four times. The gains are real. It reads better, because anyone can see at a glance that these four settings belong to one range. There's less repetition, since you change the range once, at the top. And there's a minor speed benefit. VBA resolves the object reference once instead of four times, which is negligible for one range and meaningful inside a loop over thousands.

With blocks can nest, and the recorder emits them itself. It wraps Interior settings in a With Selection.Interior, and that's one of the few things it writes that survives refactoring. You keep the With and just change Selection to your explicit range.

Whenever you catch yourself typing the same object reference on three or more lines in a row, wrap it in a With.

Common mistakes & how experts think differently

1. .Select and .Activate everywhere, the recorder habit. The recorder writes Range("A1").Select and then Selection.Font.Bold = True. Beginners inherit that two-steps-per-action style and never drop it. It's slow, because every select repaints, and it's fragile. It acts on whatever happens to be selected, so running the macro from the wrong sheet corrupts the wrong data. On top of that, it's unreadable. An expert who sees .Select/Selection deletes the pair and fuses it into one statement attached to an explicit range: ws.Range("A1").Font.Bold = True. You almost never need to select anything in VBA. You act on objects directly. Treat every .Select in inherited code as a defect to remove.
2. No Option Explicit, so typos become silent Variants. Without it, a mistyped variable name isn't an error. VBA simply makes a new, empty variable, and your macro writes a blank or a zero with no warning. That's the totla bug from earlier. The expert puts Option Explicit at the top of every module and sets the VBE to insert it automatically. A whole class of invisible, hours-long bugs becomes a one-second compile error. It's the highest-leverage habit in the language.
3. ActiveSheet / ActiveCell instead of an explicit reference. "Active" means "whatever is in front right now," and that's a moving target the user controls. A macro that reads ActiveSheet.Range("B6") works in testing. Then one day it writes to the wrong sheet, because the user left a different tab open. The expert pins the target once, with Set ws = ThisWorkbook.Worksheets("Sales"), and routes every reference through ws. Name the sheet, and never trust the selection.
4. Hardcoding ranges that should be dynamic. Writing Range("B2:B8") bakes in "eight rows" forever. Add a ninth row of data and the macro ignores it without complaint. Real data grows. The expert finds the extent at run time. ws.Cells(ws.Rows.Count, 2).End(xlUp).Row returns the last used row of column B, and it's the code equivalent of pressing Ctrl+↑ from the bottom. .CurrentRegion returns the whole contiguous block around a cell. Loop to that computed last row and the macro fits the data every time. Hardcoded extents are the second-most-common source of "it worked last month" failures.
5. Saving as .xlsx and losing the macros. The .xlsx format can't hold VBA. Save a macro workbook as .xlsx and Excel deletes every line of your code, after a warning most people click through. The expert saves macro workbooks as .xlsm (or .xlsb) from the very first save. To them, the "cannot be saved in a macro-free workbook" dialog is a red alarm, never an OK-to-dismiss. If your macros have "disappeared," this is almost always why.
6. Reaching for a loop before checking a one-liner exists. Fresh off their first For loop, beginners write a ten-line loop to sum a column that WorksheetFunction.Sum(ws.Range("B2:B8")) sums in one line. Or they loop to format cells a single With block could handle. The expert knows the object model's built-ins (WorksheetFunction.Sum, .NumberFormat, .AutoFit) and loops only when there's real per-row logic. You'll sharpen that distinction when you get to control flow.
7. Confusing Set (objects) with plain assignment (values). Assign an object without Set, as in ws = ThisWorkbook.Worksheets("Sales"), and VBA throws a runtime error. Beginners flail. The expert takes the rule in at once. Object references use Set, and ordinary values don't. It's Set ws = ... for a worksheet and total = ... for a number.

One habit ties all seven together. The recorder shows you the property names, and then you throw its style away. A beginner ships the recording. A professional treats it as raw ore and refines it into explicit, typed, dynamic code, with Option Explicit at the top and not one .Select in the body.

Worked examples

Worked example 1: Refactor a recorded formatting macro (India, ₹)

The task. A regional manager wants the Q1 sales sheet formatted the same way every quarter: bold white-on-blue header, rupee number format, and a bold, ruled Total row that sums the four regions. Record the formatting once and read what the recorder wrote. Then refactor it into clean object code, and have the macro compute and write the total. The data is on a sheet named Sales:

     A          B
1    Region     Q1 Sales (₹)
2    North      1250000
3    South      980000
4    East       1435000
5    West       1120000
6    Total      [the macro writes this]

Step 1, record it, and behold the mess. Turn on Developer ▸ Record Macro, format the header, set the number format, type "Total", and enter a =SUM(...). You'll get something close to this. It's the recorder's unvarnished output, .Select-riddled and unqualified:

Sub Macro1()
'
' Macro1 Macro (recorded 20-07-2026)
'
    Range("A1:B1").Select
    Selection.Font.Bold = True
    With Selection.Interior
        .Pattern = xlSolid
        .Color = 12611584
        .TintAndShade = 0
    End With
    Selection.Font.Color = vbWhite
    Range("B2:B6").Select
    Selection.NumberFormat = "₹#,##0"
    Range("A6").Select
    ActiveCell.FormulaR1C1 = "Total"
    Range("B6").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"
    Range("A6:B6").Select
    Selection.Font.Bold = True
    Range("A1").Select
End Sub

Read it critically. Every action is a .Select followed by an operation on Selection or ActiveCell. Nothing says which sheet. The total is written by an obscure R1C1 formula, and the final Range("A1").Select is pure navigation noise. It only works if the Sales sheet is already in front. But look at what the recorder gave you, the property names themselves: Font.Bold, Interior.Color, Font.Color, NumberFormat. That's the discovery you were after.

Step 2, refactor into code you would sign. Delete every .Select/Selection/ActiveCell and qualify everything through an explicit ws. Keep the With block, but point it at the real range instead of Selection. Then have the code compute the total itself, so there's no R1C1 string at all:

Option Explicit

Sub FormatRegionReport()
    ' Formats the Q1 regional sales report on "Sales" and writes the total.
    ' No .Select, no ActiveSheet — every reference is explicit.
    Dim ws As Worksheet
    Dim total As Double

    Set ws = ThisWorkbook.Worksheets("Sales")

    ' 1. Header row: bold, white text on a blue fill, centred.
    With ws.Range("A1:B1")
        .Font.Bold = True
        .Font.Color = RGB(255, 255, 255)
        .Interior.Color = RGB(68, 114, 196)
        .HorizontalAlignment = xlCenter
    End With

    ' 2. Rupee number format for the value column (including the total).
    ws.Range("B2:B6").NumberFormat = "₹#,##0"

    ' 3. Compute the total in code and write it, with its label.
    total = WorksheetFunction.Sum(ws.Range("B2:B5"))
    ws.Range("A6").Value = "Total"
    ws.Range("B6").Value = total

    ' 4. Emphasise the total row: bold, with a top border.
    With ws.Range("A6:B6")
        .Font.Bold = True
        .Borders(xlEdgeTop).LineStyle = xlContinuous
    End With
End Sub

Twenty-odd lines of navigation become a readable routine. It names its sheet, formats through With blocks and computes its own total. It runs correctly whichever sheet is active, and a colleague can read it.

Step 3, verify the total the macro writes. WorksheetFunction.Sum(ws.Range("B2:B5")) adds the four regions: 1,250,000 + 980,000 + 1,435,000 + 1,120,000. Do it longhand with me. 1,250,000 + 980,000 = 2,230,000. Add 1,435,000 to get 3,665,000, then 1,120,000 to reach 4,785,000. The macro writes ₹47,85,000 (Indian grouping) into B6. Python-verified as 4785000.

Step 4, read it. The number format "₹#,##0" uses Western thousands grouping (₹4,785,000). For true Indian lakh grouping (₹47,85,000), use the locale-aware code "[$₹-en-IN]#,##,##0" on that same .NumberFormat line. Either way, the value stored is 4785000. Formatting only changes how it's displayed. The bigger lesson is the loop that got you here. The recorder handed you the property names, and refactoring turned its brittle transcript into a routine the manager can bind to a button and run every quarter. That's the record-read-refactor cycle in miniature.

Worked example 2: A loop that sums a column (US, $)

The task. A US store keeps one week of daily revenue down column B and wants a macro to total it into B9. It's your first real loop, walking the column cell by cell with Cells(r, 2). The sheet is named Week:

     A          B
1    Day        Revenue ($)
2    Mon        1250.50
3    Tue        980.25
4    Wed        1435.00
5    Thu        1120.75
6    Fri        1675.50
7    Sat        890.00
8    Sun        1540.00
9    Week total [the macro writes this]

Step 1, the loop macro. Declare a Long counter and a Double accumulator. Start the total at zero, then add each row's value as the loop visits it:

Option Explicit

Sub SumRevenueColumn()
    ' Sums daily revenue in B2:B8 by looping Cells(r, 2), writes total to B9.
    Dim ws As Worksheet
    Dim r As Long
    Dim total As Double

    Set ws = ThisWorkbook.Worksheets("Week")
    total = 0                              ' start the running sum at zero

    For r = 2 To 8                         ' rows 2 through 8 hold the data
        total = total + ws.Cells(r, 2).Value   ' column 2 = column B
    Next r

    ws.Cells(9, 1).Value = "Week total"
    ws.Cells(9, 2).Value = total           ' B9 gets the total
    ws.Cells(9, 2).NumberFormat = "$#,##0.00"
End Sub

Trace the loop once by hand, together. total starts at 0. With r = 2, total = 0 + 1250.50 = 1250.50. Then r = 3 gives + 980.25 = 2230.75, and r = 4 gives + 1435.00 = 3665.75. Next, r = 5 gives + 1120.75 = 4786.50 and r = 6 gives + 1675.50 = 6462.00. Two rows to go. r = 7 gives + 890.00 = 7352.00, and r = 8 gives + 1540.00 = 8892.00. The loop ends with total holding 8892.00, which goes into B9.

Step 2, verify the total. Sum the values independently: 1,250.50 + 980.25 + 1,435.00 + 1,120.75 + 1,675.50 + 890.00 + 1,540.00 = $8,892.00. Python-verified as 8892.0. The hand trace and the direct sum agree.

Step 3, make it dynamic (the professional upgrade). The hardcoded To 8 is a latent bug. Add an eighth day and the macro ignores it. Find the last row at run time instead, with End(xlUp) from the bottom of the sheet:

Option Explicit

Sub SumRevenueDynamic()
    Dim ws As Worksheet
    Dim r As Long, lastRow As Long
    Dim total As Double

    Set ws = ThisWorkbook.Worksheets("Week")
    lastRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row   ' last used row in col B

    total = 0
    For r = 2 To lastRow                    ' loops exactly as far as the data goes
        total = total + ws.Cells(r, 2).Value
    Next r

    ws.Cells(lastRow + 1, 1).Value = "Week total"
    ws.Cells(lastRow + 1, 2).Value = total
    ws.Cells(lastRow + 1, 2).NumberFormat = "$#,##0.00"
End Sub

On this seven-row dataset lastRow comes out as 8, the loop runs 2 To 8, and the total is the same $8,892.00. The difference is that an eighth or an eightieth day now gets picked up automatically, and the label and total land on the row just below the data.

Step 4, read it. With the loop, VBA stops being "formulas in disguise" and becomes programming. You told Excel how to compute, step by step, and not just what to display. Cells(r, 2) made the column walkable, because the counter drives its numeric row. Range("B2"), with its fixed string, couldn't have moved. And the End(xlUp) upgrade turns a macro that fits this week into one that fits every week. That's the promise of your first real macro. You write it once and it's correct forever, however the data grows.

Practice set

Work each problem before you read its solution. Every solution is a complete, runnable listing, with module-level Option Explicit plus the Sub. Paste it into a standard module (Insert ▸ Module) and run it with F5. Use the sheet names as stated, and make small matching datasets to test with.

P1 (guided). Refactor a recorded snippet. The recorder produced the macro below. Rewrite it as clean, explicit code with no .Select, targeting a sheet named Report.

Sub Macro3()
    Range("A1").Select
    Selection.Font.Bold = True
    Selection.Font.Size = 14
    Range("A2").Select
    ActiveCell.Value = "Prepared by Analytics"
    Range("A1").Select
End Sub

Solution. Delete every .Select/Selection/ActiveCell, qualify through an explicit worksheet, and fuse each action into one statement. A With block collects the two font settings on A1.

Option Explicit

Sub FormatTitle()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Report")

    With ws.Range("A1").Font
        .Bold = True
        .Size = 14
    End With
    ws.Range("A2").Value = "Prepared by Analytics"
End Sub

The recorder's five navigation-heavy lines become three lines of real work. Nothing is selected, and the code runs whatever sheet is active. The trailing Range("A1").Select was pure noise, and now it's gone.

P2 (guided). Add Option Explicit and Dim. The macro below has no type declarations and computes 18% GST. Add Option Explicit, declare every variable with the right type, and route the output through an explicit sheet named GST. State the total it writes.

Sub AddTax()
    price = 1000
    rate = 0.18
    gst = price * rate
    Range("B1").Value = gst
End Sub

Solution. price, rate and gst all hold decimals, so they're Double. With Option Explicit on, every one of them has to be Dim-ed or the macro won't compile.

Option Explicit

Sub AddTax()
    Dim price As Double
    Dim rate As Double
    Dim gst As Double

    price = 1000
    rate = 0.18
    gst = price * rate                 ' 1000 * 0.18
    ThisWorkbook.Worksheets("GST").Range("B1").Value = gst
End Sub

It writes gst = 1000 × 0.18 = ₹180 into B1, Python-verified as 180. Here the value of Option Explicit is concrete. Say you'd mistyped gst as gts on the last line. The undeclared version would have written a blank without a murmur, while the declared version refuses to compile until the name matches.

P3 (guided). Range and Cells both ways. Write a macro on a sheet named Scratch that puts the text Hello into cell C3 twice, once addressing it with Range and once with Cells, to prove the two forms are equivalent.

Solution. C3 is row 3, column 3, so Cells(3, 3) is the numeric twin of Range("C3").

Option Explicit

Sub WriteTwoWays()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Scratch")

    ws.Range("C3").Value = "Hello"      ' by address string
    ws.Cells(3, 3).Value = "Hello"      ' by (row, column) — same cell
End Sub

Both lines hit the same cell. Use Range("C3") when the address is fixed and you want the code to read like the sheet. Use Cells(3, 3) when either coordinate is a variable or a loop counter.

P4. Format a header with a With block. On a sheet named Dash, format the range A1:D1 as a header: bold, white font, fill colour RGB(68, 114, 196), centred horizontally, with a bottom border. Use a single With block.

Solution.

Option Explicit

Sub FormatHeader()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Dash")

    With ws.Range("A1:D1")
        .Font.Bold = True
        .Font.Color = RGB(255, 255, 255)
        .Interior.Color = RGB(68, 114, 196)
        .HorizontalAlignment = xlCenter
        .Borders(xlEdgeBottom).LineStyle = xlContinuous
    End With
End Sub

You name the object ws.Range("A1:D1") once, at the With, and every dotted line hangs off it. Change the header range in one place and all five settings follow.

P5. Loop-sum a column (US, $). A sheet named Sales2 holds six daily sales in B2:B7: 325.50, 410.75, 288.25, 502.00, 367.50, 450.00. Loop Cells(r, 2) to total them into B8, formatted as US dollars. State the total.

Solution.

Option Explicit

Sub SumSales2()
    Dim ws As Worksheet
    Dim r As Long
    Dim total As Double

    Set ws = ThisWorkbook.Worksheets("Sales2")
    total = 0
    For r = 2 To 7
        total = total + ws.Cells(r, 2).Value
    Next r

    ws.Cells(8, 1).Value = "Total"
    ws.Cells(8, 2).Value = total
    ws.Cells(8, 2).NumberFormat = "$#,##0.00"
End Sub

The loop accumulates 325.50 + 410.75 + 288.25 + 502.00 + 367.50 + 450.00 = $2,344.00 into B8, Python-verified as 2344.0. Starting total at zero before the loop is essential. Leave it out and VBA's default zero saves you here, but relying on defaults is the very habit Option Explicit and explicit initialisation exist to break.

P6. Dynamic last row with End(xlUp) (India, ₹). A sheet named Ledger has an unknown number of amounts down column A, starting at A2. This time the dataset is 15000, 22000, 18500, 27000, 19500. Without hardcoding the row count, find the last used row, sum column A into cell C1, and report the count of entries in C2.

Solution. End(xlUp) from the bottom of the sheet finds the last populated row of column A at run time.

Option Explicit

Sub SumLedger()
    Dim ws As Worksheet
    Dim r As Long, lastRow As Long
    Dim total As Double

    Set ws = ThisWorkbook.Worksheets("Ledger")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row   ' last row in column A

    total = 0
    For r = 2 To lastRow
        total = total + ws.Cells(r, 1).Value
    Next r

    ws.Range("C1").Value = total
    ws.Range("C2").Value = lastRow - 1                    ' entries = rows minus header
End Sub

For the five amounts, lastRow comes out as 6 and the loop runs 2 To 6. The total is 15,000 + 22,000 + 18,500 + 27,000 + 19,500 = ₹1,02,000, Python-verified as 102000, and the count is 6 − 1 = 5. Add a sixth amount and both outputs adjust with no change to the code. That's the payoff for computing the extent instead of hardcoding it.

P7. CurrentRegion to size a block (India, ₹). A sheet named Expenses has a contiguous table starting at A1 with a header row and six data rows of amounts in column B (45000, 32000, 28500, 51000, 39750, 42300). Use CurrentRegion to discover the block, then sum column B with WorksheetFunction.Sum, without a loop, and report the sum in D1 and the data-row count in D2.

Solution. .CurrentRegion returns the whole contiguous block around A1, and its .Rows.Count includes the header.

Option Explicit

Sub SummariseExpenses()
    Dim ws As Worksheet
    Dim block As Range
    Dim dataRows As Long
    Dim total As Double

    Set ws = ThisWorkbook.Worksheets("Expenses")
    Set block = ws.Range("A1").CurrentRegion      ' the whole contiguous table
    dataRows = block.Rows.Count - 1               ' minus the header row

    total = WorksheetFunction.Sum(ws.Range("B2").Resize(dataRows, 1))
    ws.Range("D1").Value = total
    ws.Range("D2").Value = dataRows
End Sub

CurrentRegion finds a 7-row block (1 header + 6 data), so dataRows = 6. The sum of column B is 45,000 + 32,000 + 28,500 + 51,000 + 39,750 + 42,300 = ₹2,38,550, Python-verified as 238550, and it goes to D1. The count 6 goes to D2. WorksheetFunction.Sum does in one line what a loop would do in five, so reach for the built-in when there's no per-row logic.

P8. Debug.Print to the Immediate window. On a sheet named Week, holding the seven-day revenue data from Worked example 2 in B2:B8, write a macro that prints the average daily revenue to the Immediate window without changing any cell. State what prints.

Solution. WorksheetFunction.Average computes it and Debug.Print sends the result to the Immediate window. Open that with Ctrl+G in the VBE to read the output.

Option Explicit

Sub ShowAverage()
    Dim ws As Worksheet
    Dim avg As Double

    Set ws = ThisWorkbook.Worksheets("Week")
    avg = WorksheetFunction.Average(ws.Range("B2:B8"))
    Debug.Print "Average daily revenue: " & Format(avg, "$#,##0.00")
End Sub

The week totals $8,892.00 over 7 days, so the average is 8892.00 / 7 = $1,270.29, Python-verified as 1270.29. The Immediate window shows Average daily revenue: $1,270.29. Debug.Print is the analyst's print(), and it's the fastest way to look at a value mid-macro without disturbing the sheet.

P9. Total as a value vs. as a live formula. On a sheet named Calc with numbers in B2:B5 (100, 200, 300, 400), write a macro that puts the total in B6 as a live =SUM(...) formula and the same total in C6 as a static computed value. Explain the difference in behaviour.

Solution. Assign a formula string to .Formula for the live version, and a computed number to .Value for the static one.

Option Explicit

Sub TotalTwoWays()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Calc")

    ws.Range("B6").Formula = "=SUM(B2:B5)"                  ' live: recalcs on edit
    ws.Range("C6").Value = WorksheetFunction.Sum(ws.Range("B2:B5"))  ' static snapshot
End Sub

Both cells show 1000 for now (100 + 200 + 300 + 400 = 1,000, Python-verified as 1000). But B6 holds a formula, so when you change B2, B6 updates itself. C6 holds a number. Change B2 and C6 stays at 1000 until the macro runs again. Pick the live formula when the sheet should stay reactive, and the static value when you want a frozen snapshot, such as an archived month-end figure.

P10. Save as macro-enabled. Explain, and show in code, how to save the active macro workbook so its code survives, and name what happens if it is saved as .xlsx instead. Provide a macro that saves ThisWorkbook in the correct format.

Solution. Macros need a macro-enabled format. .xlsx can't store VBA, and saving to it deletes the code. The macro-enabled format is .xlsm, and its FileFormat constant is xlOpenXMLWorkbookMacroEnabled.

Option Explicit

Sub SaveAsMacroEnabled()
    Dim savePath As String
    savePath = ThisWorkbook.Path & "\RegionReport.xlsm"

    Application.DisplayAlerts = False            ' suppress the overwrite prompt
    ThisWorkbook.SaveAs Filename:=savePath, _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled   ' .xlsm — keeps the VB project
    Application.DisplayAlerts = True
End Sub

By hand, the same thing is File ▸ Save As ▸ Excel Macro-Enabled Workbook (*.xlsm). Save the workbook as .xlsx and Excel warns that "VB project" cannot be saved in a macro-free workbook. Carry on anyway and it strips every macro, with no further warning. Treat that dialog as an alarm, never an OK.

P11 (timed: 6 min). End-to-end line pricer (US, $). A sheet named Order has three order lines: quantities in A2:A4 (100, 250, 40) and unit prices in B2:B4 (12.50, 8.50, 21.25). Write a macro that computes each line's extended cost (Qty × price) into column C, then writes the grand total into C5 as US dollars. State the grand total.

Solution. Loop the three rows with Cells, working out each line's extension and adding it to the grand total.

Option Explicit

Sub PriceOrder()
    Dim ws As Worksheet
    Dim r As Long
    Dim ext As Double, grand As Double

    Set ws = ThisWorkbook.Worksheets("Order")
    grand = 0
    For r = 2 To 4
        ext = ws.Cells(r, 1).Value * ws.Cells(r, 2).Value   ' Qty x price
        ws.Cells(r, 3).Value = ext
        grand = grand + ext
    Next r

    ws.Cells(5, 2).Value = "Grand total"
    ws.Cells(5, 3).Value = grand
    ws.Range("C2:C5").NumberFormat = "$#,##0.00"
End Sub

The line extensions are 100 × 12.50 = 1,250.00, then 250 × 8.50 = 2,125.00, then 40 × 21.25 = 850.00. Grand total = 1,250.00 + 2,125.00 + 850.00 = $4,225.00, Python-verified as 4225.0 and written to C5. You don't type a single figure. Every extension and the total are computed, so if you fix a quantity and re-run, the whole order re-prices.

P12 (timed: 6 min). Spot the bugs and rewrite. The macro below is meant to bold the header and total column B on a sheet called Data, but it has four defects a professional would fix on sight. Identify them and rewrite it correctly.

Sub Summarise()
    Sheets("Data").Activate
    Range("A1:C1").Select
    Selection.Font.Bold = True
    tot = 0
    For r = 2 To 100
        tot = tot + Cells(r, 2)
    Next r
    Range("B101").Value = tot
End Sub

Solution. There are four defects. (1) There's no Option Explicit, and tot/r are undeclared. (2) .Activate + .Select/Selection stand in for a direct reference. (3) The unqualified Range/Cells are ActiveSheet-style, so they act on whatever sheet is active and not explicitly on Data. (4) The hardcoded To 100 stands in for the real last row, so it both misses data past row 100 and sums the empty cells up to it. Here's the clean rewrite:

Option Explicit

Sub Summarise()
    Dim ws As Worksheet
    Dim r As Long, lastRow As Long
    Dim tot As Double

    Set ws = ThisWorkbook.Worksheets("Data")

    ws.Range("A1:C1").Font.Bold = True                      ' no Select

    lastRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row      ' real extent
    tot = 0
    For r = 2 To lastRow
        tot = tot + ws.Cells(r, 2).Value                    ' explicitly on ws
    Next r
    ws.Cells(lastRow + 1, 2).Value = tot
End Sub

Every reference goes through ws and nothing is selected. The loop fits the data, and Option Explicit guards the variable names. It's everything you've learned so far in one exercise: explicit, typed, dynamic, and .Select-free.

Applied mini-project

Build a one-button quarterly report formatter. It's a small workbook that turns a raw regional sales sheet into a finished, formatted report at the press of a single button. You'll use the full record-read-refactor loop, and save in the right format so the code survives.

Set up the data. On a sheet named Sales, lay out a header row (Region, Q1 Sales (₹)) with at least six regions and their sales figures below it. Keep it contiguous from A1 so CurrentRegion and End(xlUp) both work. Leave the total row empty, because the macro will build it.

Build it in three passes:

1. Record. Turn on Developer ▸ Record Macro. By hand, format the header (bold, fill, white font), apply a rupee number format to the values and type a Total label. Then click Stop Recording. Press Alt+F11 and read what the recorder wrote. Note every .Select, Selection and ActiveCell, and note the property names it revealed (Font.Bold, Interior.Color, NumberFormat).
2. Refactor. In a fresh standard module (Insert ▸ Module) with Option Explicit at the top, rewrite the recording as a clean Sub. Start with Set ws = ThisWorkbook.Worksheets("Sales"), format the header inside a With block and apply the number format directly. Then find the last row with End(xlUp) and loop Cells(r, 2) to compute the total, or use WorksheetFunction.Sum over the dynamic range. Write the labelled, bold, top-ruled total row below the data, and don't hardcode its address. Not one .Select may remain.
3. Wire and harden. Add a button (Developer ▸ Insert ▸ Button) to the sheet and assign your macro to it, so a non-programmer can run it. Then check that it holds up. Add a seventh region and re-run it. The total has to move down and stay correct. Finally, Save As ▸ Excel Macro-Enabled Workbook (*.xlsm) and check the code is still there after you close and reopen the file. Try Save As ▸ .xlsx once on purpose, too. Read the warning and cancel, so you've seen the trap with your own eyes.

Rubric (self-score; 0 = missing/wrong, 1 = partial, 2 = correct and defended):

Pass: ≥ 8/10, with no zero on any criterion. Keep this workbook. Its formatter is the seed of the reporting-pack automation you'll extend once you've learned control flow.

Lab: open the VBA I, recorder to real code workbook lab in the Practice ▸ Excel tab here and pass its checks.

Reading & resources

Official reference (reach for these first):

• Microsoft Learn, the Excel VBA object model reference and the Getting started with VBA in Office guide (search "Excel VBA object model Microsoft Learn"). They give authoritative definitions of Range, Cells, Worksheet, and every property and method you'll meet. [Free] [Beginner]
• Microsoft Learn. "Enable or disable macros" / Trust Center support pages, for the exact security dialogs and the .xlsm/.xlsb format descriptions. [Free] [Beginner]

Best tutorials and pattern libraries:

• Excel Macro Mastery (Paul Kelly, excelmacromastery.com), the clearest writing anywhere on Range vs Cells, the object model, and why to avoid .Select. His "VBA for beginners" and "how to avoid Select" articles map straight onto everything here. [Free] [Beginner]
• WiseOwl (Andrew Gould, YouTube + wiseowl.co.uk), patient video walkthroughs of the VBE, recording, and refactoring. It's the best moving-picture treatment of the record-read-refactor loop. [Free] [Beginner]
• Chandoo (chandoo.org), the "VBA classes" and macro tutorials, with downloadable practice workbooks and an analyst's framing much like the one you're reading. [Free] [Beginner]

For the common mistakes, expanded:

• Any reputable "stop using Select in VBA" article. Get the refactor reflex into your hands before you inherit a workbook full of recordings. Search "VBA avoid Select Activate." [Free] [Beginner]

Bridge forward: the loops you met here informally (For r = 2 To lastRow) and the decisions you haven't made yet are the whole subject of EX6.02 (VBA II, control flow). There, If/Select Case, For Each, and Do loops turn these straight-line Subs into programs that branch and repeat with intent.

The Modern Analyst's Addendum

Everything above teaches this skill from first principles, by hand, that is how you learn it, and the mastery check still tests it that way. This addendum shows how a working analyst amplifies the same skill today. It adds; it never replaces. (R1/R10)

VBA is the one Excel skill here where a generator's advantage is real and not just marketing. The object model is small and has barely changed since Office 97. It's documented to exhaustion on Microsoft Learn and in two decades of forum answers. A small, stable API surface with that much written about it is the combination a language model handles well. Ask a generator for the FormatRegionReport-style refactor of a Macro1-style recording and it'll typically get the property names right first try. That same ease makes VBA dangerous to automate carelessly. Generated code runs with full trust, unsandboxed, with the same access to your file system, your network, and every open workbook that code you typed yourself has. Nothing in Excel asks a macro's permission before it deletes a file.

AI-Augment this skill

{
  "skill": "Recording a macro to discover the object model, then reading and refactoring the transcript into explicit, Option-Explicit-guarded code safe to bind to a button.",
  "use": "Paste recorder output or describe a formatting/totalling task and a generator will typically produce a close FormatRegionReport-style refactor in seconds: correct property names, a Set worksheet reference, a With block. That is a genuine saving on the mechanical part of the record-read-refactor loop. It is not a saving on the judgment part — which sheet to hardcode, whether the total should be a live formula or a frozen value, and what could go wrong if this runs against a workbook that is not exactly today's are still yours to decide.",
  "tools": ["Chat assistants (Claude, ChatGPT) for refactoring recorder output and drafting loop skeletons", "The VBA object browser (F2 in the VBE) and Microsoft Learn's VBA reference for confirming a generated property or method actually exists", "A plain read-through of the generated code against the five-item danger list before the first run"],
  "prompt": "Here is a macro the Excel recorder produced: <PASTE Macro1-style code>. Refactor it to remove every .Select/Selection/ActiveCell, qualify all ranges through an explicit Worksheet variable set from ThisWorkbook.Worksheets(\"<name>\"), add Option Explicit and typed Dim statements, and keep the With block structure. Do not change what it formats or computes. Then list every file-system, network, or Shell operation in your answer, even if the honest list is none.",
  "verify": "Read every generated line before running it once, against five named patterns: Kill (deletes a file, no recycle bin), Shell or ShellExecute (launches another program), any FileSystemObject write (.DeleteFile, .DeleteFolder, .CreateTextFile, .CopyFile, .MoveFile), a path built from ThisWorkbook.Path's neighbours (writes or deletes beside the workbook, wherever that happens to be), and Application.DisplayAlerts = False wrapped around a destructive call (it exists to hide a warning you specifically want to see). None of the five are automatically wrong — SaveAsMacroEnabled later in this module uses two legitimately — but every one earns a stop and a read. Separately confirm the refactor did not just relocate the recorder's Select-dependence: asked only to 'clean this up,' a generator will often reproduce reliance on whatever sheet happens to be active unless explicitly told to qualify every reference.",
  "diy": "The mastery check is unaided: you must recognise every .Select/Selection/ActiveCell pattern on sight, state the object-model containment order, and hand-refactor a short recording into explicit, Option-Explicit code with a calculator for the arithmetic. Nothing generated is admissible in that room."
}

Modern Data Analysis

By hand first. You recorded Macro1, read its .Select-riddled transcript, and refactored it by hand into FormatRegionReport. Along the way you deleted every navigation line, named the sheet once, and computed the total in code. The skill that transfers is the editorial judgment underneath, telling real work apart from the recorder narrating its own mouse movements. It's what lets you read any VBA a generator hands you, and your own as well.

Today's workflow. Treat generated VBA the way you treat recorder output, as a first draft nobody has read yet. A short script can turn that read from a manual scan into something systematic. Before the first run, it searches the code text for a small set of named danger patterns. It's the way you'd scan a bank statement for a handful of specific line items instead of giving every row equal attention.

Tools & sources (IN + US). Microsoft Learn's VBA reference (learn.microsoft.com/office/vba/api/overview/excel) is the ground truth for whether a generated property or method actually exists. Now and then a generator invents a plausible-sounding one that compiles into a runtime error. The reports this kind of macro formats still need sourcing the way you've been taught to source everything else. An Indian analyst automating a regional-sales report pulls the regional split from the company's own segment disclosures in its annual report, and never types the value into the macro. A US analyst automating the equivalent pulls the segment footnote from the 10-K/10-Q via EDGAR. The macro formats and totals what a person has already sourced. It's a presentation layer, never a data source.

# Recomputed in-session — R3. Classify every executable line of this module's two named
# Subs, then scan both for the five VBA danger patterns.
import re

recorded_lines = [
    'Range("A1:B1").Select', 'Selection.Font.Bold = True', 'With Selection.Interior',
    '.Pattern = xlSolid', '.Color = 12611584', '.TintAndShade = 0', 'End With',
    'Selection.Font.Color = vbWhite', 'Range("B2:B6").Select', 'Selection.NumberFormat = "#,##0"',
    'Range("A6").Select', 'ActiveCell.FormulaR1C1 = "Total"', 'Range("B6").Select',
    'ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"', 'Range("A6:B6").Select',
    'Selection.Font.Bold = True', 'Range("A1").Select',
]
refactored_lines = [
    'Dim ws As Worksheet', 'Dim total As Double', 'Set ws = ThisWorkbook.Worksheets("Sales")',
    'With ws.Range("A1:B1")', '.Font.Bold = True', '.Font.Color = RGB(255, 255, 255)',
    '.Interior.Color = RGB(68, 114, 196)', '.HorizontalAlignment = xlCenter', 'End With',
    'ws.Range("B2:B6").NumberFormat = "#,##0"', 'total = WorksheetFunction.Sum(ws.Range("B2:B5"))',
    'ws.Range("A6").Value = "Total"', 'ws.Range("B6").Value = total', 'With ws.Range("A6:B6")',
    '.Font.Bold = True', '.Borders(xlEdgeTop).LineStyle = xlContinuous', 'End With',
]

def classify(line):
    s = line.strip()
    if s == "End With" or s.startswith("With "):
        return "structural"
    if ".Select" in s or s.startswith("Selection") or s.startswith("ActiveCell"):
        return "navigation"
    return "work"

for name, lines in [("Macro1 (recorded)", recorded_lines), ("FormatRegionReport (refactored)", refactored_lines)]:
    c = {"navigation": 0, "structural": 0, "work": 0}
    for ln in lines:
        c[classify(ln)] += 1
    print(f"{name}: total={len(lines)} nav={c['navigation']} struct={c['structural']} "
          f"work={c['work']} nav%={100*c['navigation']/len(lines):.1f}")
# Macro1 (recorded):               total=17 nav=12 struct=2 work=3  nav%=70.6
# FormatRegionReport (refactored): total=17 nav=0  struct=4 work=13 nav%=0.0

DANGER_PATTERNS = {
    "Kill": re.compile(r"\bKill\b"),
    "Shell/ShellExecute": re.compile(r"\bShell(Execute)?\b"),
    "FileSystemObject write": re.compile(r"\.(DeleteFile|DeleteFolder|CreateTextFile|CopyFile|MoveFile)\b"),
    "ThisWorkbook.Path neighbour": re.compile(r"ThisWorkbook\.Path"),
    "DisplayAlerts suppressed": re.compile(r"Application\.DisplayAlerts\s*=\s*False"),
    "On Error Resume Next": re.compile(r"On Error Resume Next"),
}
save_as_macro_enabled = ('Dim savePath As String\n'
    'savePath = ThisWorkbook.Path & "\\RegionReport.xlsm"\n'
    'Application.DisplayAlerts = False\n'
    'ThisWorkbook.SaveAs Filename:=savePath, FileFormat:=xlOpenXMLWorkbookMacroEnabled\n'
    'Application.DisplayAlerts = True')
for name, code in [("FormatRegionReport", "\n".join(refactored_lines)), ("SaveAsMacroEnabled", save_as_macro_enabled)]:
    hits = [(label, len(p.findall(code))) for label, p in DANGER_PATTERNS.items() if p.findall(code)]
    print(f"{name}: {hits if hits else 'clean'}")
# FormatRegionReport: clean
# SaveAsMacroEnabled: [('ThisWorkbook.Path neighbour', 1), ('DisplayAlerts suppressed', 1)]

Verify. The classification matches the two named Subs as printed earlier. Macro1 is 12 navigation / 2 structural / 3 work lines of 17, and FormatRegionReport is 0 / 4 / 13 of 17. The scanner passes FormatRegionReport as clean and flags SaveAsMacroEnabled twice, on ThisWorkbook.Path and on the suppressed DisplayAlerts. Both flagged lines are legitimate here. savePath is built from the workbook's own folder, and DisplayAlerts = False is paired with = True three lines later, around a single SaveAs call. A flag only tells you to read the line, and reading confirms these two are fine. Now picture a generated variant that built savePath from a sibling folder, or left DisplayAlerts off after the wrapped call. It would flag identically and deserve a different verdict. The scanner can't tell the two apart. Only a read can.

Quantitative lens

The tell is in what the recorder's lines are made of, and the line count won't show it. Macro1 and FormatRegionReport both run to 17 executable lines, so the refactor isn't a compression exercise. The difference is the job each line does. 70.6% of the recorded macro is pure screen navigation, meaning a .Select or a reference to Selection/ActiveCell with no formatting or computation of its own. After refactoring, that fraction is 0.0%. The freed lines went to two more Dims, one Set and a second With block. That block makes the total row's emphasis explicit, where the recording left it implicit in whatever was still selected. Refactoring VBA is a substitution exercise, swapping narration for reference, and only secondarily a shortening one.

Limits. 70.6% is the number for this one 17-line macro, and it isn't a general constant. A macro that does one .Select and then ten computed rows would show a far lower navigation fraction even before refactoring. A longer recording with more formatting passes would likely show a higher one. The classification says nothing about correctness either, since a macro can be 0% navigation and still write to the wrong cell. The danger scan is a separate, third check again. No wall-clock performance number is claimed anywhere in this addendum. Composition, safety, and speed are three different questions, and the evidence verified here covers the first two only.

Where this goes next: galaxy cross-links

• G2 · CS2.06 Software Engineering & AI-Assisted Development. The five-pattern review here is one case of the general practice taught there, a checklist for reading any generated code before you run it, in any language.
• X1 · AI0.06 Failure Modes, Verification & the Primary-Source Guardrail. Generated code that runs and produces a plausible wrong number is a specific instance of the guardrail it builds in general terms.
• X1 · AI0.03 Prompt Engineering & Structured Prompting. The refactor prompt above names an exact transformation and an explicit constraint list. There you build the general skill of writing a prompt precise enough that its output is checkable.

Teach it back & journal

Feynman prompt. Explain to a colleague who has only ever used formulas what a macro is, using the metaphor of a player piano. A formula is a note you press. A macro is a roll that plays the whole tune every time you feed it in, with the same reformatting, the same total, the same labels. Then explain the twist that trips everyone up. The macro recorder is like a machine that punches a roll by watching your fingers. It faithfully records every clumsy hand-shift between notes, so you have to go back and punch a clean roll that plays only the music. You may use the words Range, .Select, and Option Explicit only in your last two sentences, and attach each one to the moment in the story it describes.

Journal prompt. Pick one repetitive Excel task you do by hand every week, a reformat, a re-total, a copy-and-label. Record it once with the macro recorder, then open the VBE and read what it wrote. Write down (1) how many .Select/Selection lines it produced and what the real work under the noise was, and (2) the property names the recorder taught you that you didn't know before. Then refactor it into a clean, explicit, Option Explicit-guarded Sub and bind it to a button. Finish with the one sentence you want to remember forever about why the recorder's output is raw ore and not a finished tool, and why the first two words in every module you ever write will be Option Explicit.

This module's flashcards and mastery quiz are wired into the app: see the node's Quiz and Reviews.

This module's flashcards and mastery quiz are wired into the app: see the node's Quiz and Reviews.