A macro is a Sub procedure: from Sub Name() to End Sub. Inside are Excel commands written through objects: workbook → sheet → range → its properties.
Referring to cells
Range("B2"), Range("A1:D1") | by address — as in formulas |
Cells(2, 3) | by numbers: row 2, column 3 (C2). Handy in loops where the number is a variable |
ActiveCell, Selection | the active cell and the selection |
Range("A1").Offset(1, 2) | shift: 1 row down and 2 columns right (C2) |
Properties and methods
.Value | the cell value; Range("B2").Value = 100 |
.Formula | a formula: Range("D2").Formula = "=B2*C2". Written into a range, the references shift as when copying |
.Font.Bold, .Interior.Color | bold font and fill colour: RGB(198, 239, 206) |
.ClearContents, .Select | methods are actions: clear, select |
A With … End With block saves repetition: inside it, lines starting with a dot refer to the same object.
Sub Header()
With Range("A1:D1")
.Font.Bold = True
.Interior.Color = RGB(198, 239, 206)
End With
End Sub
Using the editor below. Write code, pick a macro in the list and click Run (F5). The result appears on the sheet below the editor, messages in the Output box. One module can hold several macros. Reset sheet restores the original data.