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
Recorder pratyek chuk pan record karto – chukicha click, scroll, undo – aani khup students cha macro mag vichitra vagto. Record karaychya aadhi steps kagdavar liha, ek-da practice kara, mag record kara. Absolute ki relative he recording suru karaychya aadhi tharva; madhe toggle kela tar code mix hoto.
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.