Excel Macros and VBA Basics

What a Macro Is

A macro is a saved sequence of actions that Excel can replay on demand. Under the hood, every macro is written in VBA (Visual Basic for Applications) as a block of code starting with Sub and ending with End Sub. If the Developer tab isn't visible on your ribbon, turn it on once via File > Options > Customize Ribbon and checking the Developer box.

Recording Your First Macro

1. Developer tab > Record Macro.
2. Give it a name with no spaces, and optionally a Ctrl+ shortcut key.
3. Choose where to store it - This Workbook to keep it with the current file, or Personal Macro Workbook to make it available in every workbook you open.
4. Perform the actions you want recorded, then click Developer > Stop Recording.

Sub Procedures and the VBA Editor

Press Alt+F11 to open the VBA Editor and see the code behind a recorded macro, sitting inside a Module. Every macro is wrapped between a Sub line naming it and an End Sub line closing it; everything in between runs top to bottom when the macro is triggered. The smallest possible macro just shows a message box.

Sub SayHello()
    MsgBox "Hello"
End Sub

Writing Your Own Macro: Looping Through Cells

Recording is great for clicks and formatting, but writing VBA by hand is what lets a macro touch many cells based on logic. The Cells(row, column) and Range("A1") objects both point at a cell so you can read or set its Value, and a For loop repeats a block of code a fixed number of times. The example below fills the first ten cells of column A with each row number squared.

Sub FillSquares()
    Dim i As Integer
    For i = 1 To 10
        Cells(i, 1).Value = i * i
    Next i
End Sub

To run a macro, use Developer > Macros (or the shortcut key you assigned, or a button you've drawn and linked to it). Because a workbook's default .xlsx format cannot store VBA code, you must save any file containing macros as a Macro-Enabled Workbook (.xlsm), or Excel will strip the code out when you save.