Recording a Macro

Excel writes the code for you

The Macro Recorder watches what you do and writes the equivalent VBA into a module. You need to know no syntax at all to use it, which makes it the fastest way to find out how something is expressed in code. What it gives you is a first draft, not a macro — this lesson does the whole round trip: something to practise on, the recording, and the same job written properly.

First, something to record against

Press Alt+F11, choose Insert → Module, paste this in and press F5. It builds a small untidy export on a new sheet, so nothing of yours is at risk and you can start over as often as you like.

Option Explicit

Sub MakeDemoExport()
    Dim ws As Worksheet
    Dim i As Long

    Set ws = ThisWorkbook.Worksheets.Add
    ws.Name = "Export " & Format(Now, "hh-mm-ss")

    ws.Range("A1:D1").Value = Array("date", "customer", "units", "amount")

    For i = 1 To 20
        ws.Cells(i + 1, 1).Value = DateSerial(2026, 2, i)
        ws.Cells(i + 1, 2).Value = "Customer " & Chr(64 + i)
        ws.Cells(i + 1, 3).Value = 5 + (i Mod 7)
        ws.Cells(i + 1, 4).Value = 149.5 * i
    Next i
End Sub

Now record the clean-up

• Start — Developer → Record Macro, or the small record button on the status bar. If the Developer tab is missing: File → Options → Customize Ribbon.
• Name it Macro1 for now and leave Store macro in on This Workbook.
• Do the job — bold row 1 and colour it, format column A as a date and column D with two decimals, sort by amount descending, autofit A:D.
• Stop — Developer → Stop Recording, then Alt+F11 and read what Excel wrote. Recording without reading teaches you nothing.

Sub Macro1()
'
' Macro1 Macro
'

'
    Rows("1:1").Select
    Selection.Font.Bold = True
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
    Range("A2:A21").Select
    Selection.NumberFormat = "dd.mm.yyyy"
    Range("D2:D21").Select
    Selection.NumberFormat = "#,##0.00"
    Range("A1:D21").Select
    ActiveWorkbook.Worksheets("Export 09-41-07").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Export 09-41-07").Sort.SortFields.Add2 Key:= _
        Range("D2:D21"), SortOn:=xlSortOnValues, Order:=xlDescending
    With ActiveWorkbook.Worksheets("Export 09-41-07").Sort
        .SetRange Range("A1:D21")
        .Header = xlYes
        .Apply
    End With
    Columns("A:D").Select
    Selection.Columns.AutoFit
    Range("A1").Select
End Sub

What you are looking at

• Every action arrives as a pair: Select something, then do it to Selection. Half the lines exist only to move the cursor.
• The recorder writes every property of a dialog it touched, not the one you changed — hence PatternColorIndex and TintAndShade with fourteen decimal places.
• A2:A21 is burnt in. Next week's export has 300 rows and you will format the first twenty of them.
• The sheet name is burnt in too, so the macro is tied to the tab you happened to be on.

The same job, written

Nothing has been added below beyond a check that you are on the right sheet. The same operations, addressed instead of selected, and sized from the data instead of from the day you recorded:

Option Explicit

Sub FormatExport()
    Dim ws As Worksheet
    Dim lastRow As Long

    Set ws = ActiveSheet

    If ws.Range("A1").Value <> "date" Then
        MsgBox "Run MakeDemoExport first, then run this with that sheet in front.", _
            vbExclamation, "Wrong sheet"
        Exit Sub
    End If

    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    With ws.Range("A1:D1")
        .Font.Bold = True
        .Interior.Color = RGB(226, 232, 240)
    End With

    ws.Range("A2:A" & lastRow).NumberFormat = "dd.mm.yyyy"
    ws.Range("D2:D" & lastRow).NumberFormat = "#,##0.00"

    ws.Range("A1:D" & lastRow).Sort _
        Key1:=ws.Range("D1"), Order1:=xlDescending, Header:=xlYes

    ws.Columns("A:D").AutoFit

    MsgBox "Formatted " & (lastRow - 1) & " rows.", vbInformation, "Done"
End Sub

The trap: your macro is not in the file you sent

The Store macro in box offers Personal Macro Workbook, and it sounds like the sensible choice. It puts the macro in a hidden workbook called PERSONAL.XLSB that only exists on your machine. The macro then works in every file you open, which is exactly why nobody notices: you test it, you email the workbook to a colleague, and for them the button does nothing at all. The reverse also bites — a colleague's file that runs perfectly for them, because their Personal workbook holds the half of the code you never received.

How it goes on

The next lesson deals with absolute and relative recording, which is why one recording writes into A2 forever and another follows the cursor. After that comes reading generated code properly, then the case against Select, With blocks, and a full step-by-step rewrite of a recording like the one above. The course closes with the recorder's real long-term use: not making macros, but looking up the name of a property you cannot remember.