The Macro Recorder and the VBA Editor

Why automate?

Every Monday, you import an export, bold the headers, adjust the columns, add a total and send the file. These repetitive gestures take time and cause omissions. A macro records a series of actions and replays it in one click. In Excel for Windows and Mac, macros are written in VBA (Visual Basic for Applications); in Excel for the web, you use Office Scripts (lesson 4).

Before writing a macro, ask yourself whether a no-code tool is enough: Power Query (XL5 course) to import and clean, structured tables and dynamic functions (XL4 course) for calculations that follow the data. A macro is useful for chaining actions: formatting, creating sheets, printing, copying to another workbook.

Showing the Developer tab

File > Options > Customize Ribbon, check Developer in the right-hand list. The tab contains Record Macro, Macros (Alt+F8, the list of macros), Visual Basic (Alt+F11, the editor) and the form controls (buttons).

Recording a macro

Developer > Record Macro:

• Name: no spaces or accents, starting with a letter (MettreEnFormeVentes);
• Shortcut key: optional; prefer Ctrl+Shift+letter so as not to override an Excel shortcut (Ctrl+C, for example);
• Store macro in: This Workbook (the macro travels with the file), New Workbook, or Personal Macro Workbook (PERSONAL.XLSB, available in all your workbooks on this machine);
• Description: what the macro is for.

Then perform the actions, and Stop Recording. Everything is recorded, including your hesitant clicks: prepare the sequence before starting the recorder.

Absolute or relative references

By default, the recorder notes fixed addresses: clicking A1 produces Range("A1").Select. If you turn on Use Relative References before the action, it notes a move from the active cell: ActiveCell.Offset(1, 0).Select. Choose:

• absolute to always act in the same place (formatting the header row);
• relative to repeat an action wherever the cursor is (formatting the current row).

Reading the code produced

Alt+F11 opens the VBA editor. In the Project Explorer on the left, the Modules folder contains Module1 with your macro:

Sub MettreEnFormeVentes()
'
' MettreEnFormeVentes Macro
' Bold headers and adjusted columns
'
    Range("A1:F1").Select
    Selection.Font.Bold = True
    With Selection.Interior
        .Pattern = xlSolid
        .Color = 15773696
    End With
    Columns("A:F").Select
    Selection.Columns.AutoFit
    Range("A1").Select
End Sub

You recognize the structure: Sub Name() … End Sub, comments preceded by an apostrophe, objects (Range, Columns) whose properties (Font.Bold) you change or whose methods (AutoFit) you call. Recorded code is verbose: it selects before acting. You simplify it by acting directly:

Sub MettreEnFormeVentes()
    Range("A1:F1").Font.Bold = True
    Range("A1:F1").Interior.Color = RGB(0, 176, 240)
    Columns("A:F").AutoFit
End Sub

The result is identical, shorter, faster and no longer depends on the selection.

Running and saving

To launch the macro: Developer > Macros > Run, the chosen shortcut, F5 in the editor (cursor inside the macro), or a button (Developer > Insert > Form Controls Button, then assign the macro).

An .xlsx workbook cannot contain macros: when saving, Excel offers to remove them. Choose Excel Macro-Enabled Workbook (*.xlsm). The .xlsb (binary) format accepts them too.

The limits of the recorder

The recorder cannot do a loop (“for each row…”), a condition (“if the amount exceeds…”) or adapt to a variable number of rows: it freezes Range("A1:F11"). It is an excellent tool for discovering the code that corresponds to an action, and the starting point for the following lessons, where you will write yourself what it cannot produce.