Skip to content
Tutorials Center
My learning

Topics

Lesson 4 of 37 ยท Getting Started

Recording Macros and Reading the Code

Use the macro recorder to capture actions as VBA code, understand what it generates, clean it up, and learn absolute versus relative recording.

The macro recorder watches what you do in Excel and writes VBA for you. It is the fastest way to discover which objects and properties a task uses. The code it produces is rarely good, but it is an excellent teacher, and once you can read it you can fix it.

Record a macro

  1. Developer > Record Macro.
  2. Name it (FormatHeader), optionally give a shortcut key and choose where to store it. "This Workbook" is usually best; "Personal Macro Workbook" makes it available in every file.
  3. Click OK. Everything you do is recorded.
  4. Select A1:D1, make it bold, fill it blue, centre it.
  5. Developer > Stop Recording.

Open the editor and look in the new Module1. You will see something like this:

VB
Sub FormatHeader()
'
' FormatHeader Macro
'
    Range("A1:D1").Select
    Selection.Font.Bold = True
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Selection.HorizontalAlignment = xlCenter
End Sub

Reading recorded code

  • Range("A1:D1").Select selects the cells, like clicking them. Selection then refers to what is selected.
  • Selection.Font.Bold = True sets the Bold property of the Font object belonging to the selection.
  • The With Selection.Interior ... End With block sets several fill properties. The recorder writes every property of the dialog, including ones you did not change (TintAndShade = 0).
  • xlSolid, xlCenter and the like are named constants built into Excel's VBA.

Clean it up

Selecting is slow and fragile. Edit the procedure to work on the range directly and drop the defaults:

VB
Sub FormatHeader()
    With Range("A1:D1")
        .Font.Bold = True
        .Interior.Color = RGB(31, 78, 121)
        .Font.Color = vbWhite
        .HorizontalAlignment = xlCenter
    End With
End Sub

Fewer lines, no change of selection, and it works even if the user has a different sheet active (as long as you qualify the sheet, see Objects).

Stand-in: from "select then act" to "act directly"

The pattern is the same in any language. This JavaScript stand-in (not VBA) models both styles on a fake sheet.

JS Stand-in: Select/Selection versus direct access

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Absolute and relative recording

By default the recorder is absolute: clicking B5 records Range("B5").Select, so the macro always acts on B5. Turn on Use Relative References (Developer tab) before recording and clicks are recorded as offsets from the active cell, such as ActiveCell.Offset(1, 0).Select. Use relative mode for macros that should work on "whatever row I am on".

What the recorder cannot do

  • No loops, no conditions, no variables. It records one run only.
  • It cannot ask the user anything or react to values.
  • It records mouse-driven noise (scrolling is skipped, but selections and dialog defaults are not).
  • It does not record what happens inside many dialogs and other programs.

A good workflow: record to learn the right object and property names, paste the interesting lines into your own procedure, then add logic (see Control Flow).

Recording tips

  • Plan the steps first and keep them minimal. Every extra click is extra code.
  • Use keyboard navigation (Ctrl+Arrow, Home) to record "go to the last row" style moves.
  • Give macros meaningful names, not Macro1.
  • After recording, press F1 on any unknown word in the editor to open the help, or search it in the Object Browser (F2).
  • To delete a macro: Alt+F8, select, Delete. Or delete the code in the editor.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the main limitation of recorded macros?

    The recorder only captures a fixed sequence of your actions.
  2. 2. What does Selection refer to?

    Selection is the object the user (or code) selected last.
  3. 3. Which is better practice after recording?

    Selecting is slow and brittle; working with objects directly is faster and safer.
  4. 4. When is Use Relative References useful?

    Relative recording stores offsets, so the macro works from wherever you start.

Summary

  • The recorder turns your actions into VBA; use it to discover object and property names.
  • Recorded code selects and sets every property; clean it up.
  • Relative recording stores offsets from the active cell.
  • Add loops, conditions and variables yourself.

Next: Variables and Data Types