12.2 Recording a Macro: Absolute vs Relative
The Macro Recorder writes VBA code while you click. Great for learning and for simple formatting tasks.
Steps in Excel – record a header-format macro
- Developer › Code › Record Macro (also View › Macros › Record Macro, or the small record button on the status bar).
- Macro name
FormatHeader(no spaces) › Shortcut key: press Shift + H so it becomes Ctrl + Shift + H › Store macro in: This Workbook › Description › OK. - Do the actions: select A1:I1 › Bold › fill dark green › font white.
- Developer › Code › Stop Recording.
- Alt + F11 to see the code in Modules › Module1.
What the recorder writes (absolute references – always A1:I1):
Sub FormatHeader()
'
' FormatHeader Macro
' Keyboard Shortcut: Ctrl+Shift+H
'
Range("A1:I1").Select
Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.Color = 4616993
End With
Selection.Font.Color = RGB(255, 255, 255)
End Sub
Relative references: click Developer › Code › Use Relative References before recording. The macro then works relative to the active cell – for example, "format the current cell and the 8 cells to its right":
Sub FormatRowFromActiveCell()
ActiveCell.Resize(1, 9).Select
Selection.Font.Bold = True
ActiveCell.Offset(1, 0).Select
End Sub
| Recording mode | Code uses | Replays on |
|---|---|---|
| Absolute (default) | Range("A1:I1") |
Always the same cells |
| Relative | ActiveCell.Offset(…), Resize |
Cells relative to where you start |
Ravindra Bagale's Tip
The recorder records every mistake too – a wrong click, a scroll, an undo – and then many students' macros behave strangely. Before recording, write the steps on paper, practise once, then record. Decide absolute or relative before you start recording; if you toggle in the middle, the code gets mixed.
Ravindra Bagale's Tip – मराठी
Recorder प्रत्येक चूक पण record करतो – चुकीचा click, scroll, undo – आणि मग बऱ्याच students चा macro विचित्र वागतो. Record करण्याआधी steps कागदावर लिहा, एकदा practice करा, मग record करा. Absolute की relative हे recording सुरू करण्याआधी ठरवा; मधे toggle केलं तर code mix होतो.
Ravindra Bagale's Tip – हिंदी
Recorder हर गलती भी record करता है – गलत click, scroll, undo – और फिर बहुत से students का macro अजीब बर्ताव करता है. Record करने से पहले steps काग़ज़ पर लिखो, एक बार practice करो, फिर record करो. Absolute या relative यह recording शुरू करने से पहले तय करो; बीच में toggle किया तो code mix हो जाता है.
Practice task
Record FormatHeader (absolute) and HighlightRow (relative – yellow fill for the active cell and 8 cells to the right). Run both from different starting cells and compare.