A macro is a recorded set of Excel actions you can replay with one click. VBA (Visual Basic for Applications) is the language those actions are written in. You do not need to know VBA to start: Excel can write the code for you while you record. This guide takes you from zero to a working macro you understand, then shows you how to edit the code yourself.
What Is a Macro, and What Is VBA?
A macro automates a repetitive task. If you format the same report every Monday, a macro can do the whole sequence in a fraction of a second.
VBA is the programming language behind macros. When you record a macro, Excel translates your clicks and keystrokes into VBA and stores them in a module. You can run that code as-is, or open it and change it. Record when the task is a fixed sequence of clicks you can perform once. Write VBA when you need logic Excel cannot record: loops, conditions (If), or working across many sheets.Show the Developer Tab
Everything macro-related lives on the Developer tab, which is hidden by default.
Windows: File → Options → Customize Ribbon. In the right-hand list under Main Tabs, tick Developer, then OK. Mac: Excel → Preferences → Ribbon & Toolbar. In the right-hand list, tick Developer, then Save.The Developer tab now appears in the ribbon, with a Code group containing Record Macro, Macros, and Visual Basic.
Record Your First Macro (No Code)
- On the Developer tab, click Record Macro.
- Give it a name with no spaces, for example FormatHeader. Optionally assign a shortcut key.
- Choose where to store it: This Workbook for a macro tied to this file.
- Click OK. Recording has started, so every action is now captured.
- Perform the task once, slowly. For example: select row 1, make it bold, apply a grey fill.
- On the Developer tab, click Stop Recording.
Run a Macro
Developer → Macros (or press Alt+F8) opens the macro list. Select your macro and click Run. If you assigned a shortcut key while recording, that runs it too.For a macro you use constantly, add a button: Developer → Insert → Button (Form Control), draw it on the sheet, and assign the macro when prompted.
Read the Recorded Code
Click Developer → Visual Basic (or press Alt+F11) to open the Visual Basic Editor (VBE). In the Project pane on the left, expand Modules and double-click Module1. You will see something like:
Sub FormatHeader()
Rows("1:1").Select
Selection.Font.Bold = True
Selection.Interior.Color = RGB(217, 217, 217)
End Sub
Reading it line by line teaches you VBA faster than any tutorial: each line is one action you just performed. Sub starts the macro, End Sub ends it, and the lines between are the steps.
Write a Macro from Scratch
Recorded code always acts on whatever is selected, which makes it fragile. Written code names its target directly and is more reliable. In the VBE, choose Insert → Module and type:
Sub FormatReport()
Range("A1:D1").Font.Bold = True
Range("A1:D1").Interior.Color = RGB(217, 217, 217)
Columns("A:D").AutoFit
End Sub
This bolds the header, fills it grey, and auto-fits the columns without selecting anything first. Press F5 in the editor to run it, or run it from the macro list on the sheet.
To add logic the recorder cannot capture, use a condition:
Sub FlagLowStock()
Dim r As Long
For r = 2 To 100
If Cells(r, 2).Value < 10 Then
Cells(r, 3).Value = "Reorder"
End If
Next r
End Sub
This walks rows 2 to 100 and writes "Reorder" in column C wherever column B is below 10. A loop and an If are the two things worth learning first, because they turn a recorder into real automation.
Save a Macro-Enabled Workbook
A normal .xlsx file cannot store macros. If you save without changing the format, your code is silently discarded.
Use File → Save As and choose Excel Macro-Enabled Workbook (\*.xlsm). The .xlsm extension is what tells Excel, and anyone you send the file to, that it contains code.
Macro Security
Because macros can run code, Excel blocks them by default in files from outside your computer. When you open a downloaded .xlsm, a yellow Security Warning bar appears with an Enable Content button.
Only enable macros in files you trust. A malicious macro can do real damage. For files you create, Excel will not warn you. To change the default, go to File → Options → Trust Center → Trust Center Settings → Macro Settings, but the safe default of "Disable with notification" is the right choice for most people.
Common Mistakes
- Saving as .xlsx. The macro vanishes. Always use .xlsm.
- Relying on .Select. Recorded code selects things and acts on the selection. Rewrite it to name the range directly (Range("A1")) so it works no matter what is selected.
- No Option Explicit. Add Option Explicit at the top of a module and Excel forces you to declare variables, catching typos before they cause silent bugs.
- Recording a macro that includes opening the file. The recorded path is hard-coded and breaks on another machine.
Frequently Asked Questions
Do I need to know how to code to use macros?
No. The macro recorder writes the VBA for you. You can record, run, and reuse macros without writing a single line. Reading the recorded code is the easiest way to start learning VBA when you are ready.
Does VBA work in Excel for Mac?
Yes. Excel for Mac (Microsoft 365 and 2019 or later) includes the full VBA editor and macro recorder. Open the editor with Fn+Option+F11 if the plain Alt+F11 is intercepted by macOS. A few Windows-only objects are unavailable, but everyday automation works the same.
Why did my macro disappear when I reopened the file?
You saved it as .xlsx, which cannot hold code. Re-create the macro and save as Excel Macro-Enabled Workbook (.xlsm).
What is the difference between a macro and VBA?
A macro is the automated task. VBA is the language it is written in. Every macro is VBA code; recording is just a way to generate that code without typing it.
Are macros safe to enable?
Macros you write are safe. Macros in files from other people can contain harmful code, which is why Excel disables them until you click Enable Content. Only enable macros in files from a source you trust.
Can a macro run automatically when the workbook opens?
Yes. A Sub named Workbook_Open placed in the ThisWorkbook object runs every time the file opens. Use it sparingly, and never in a file others open without knowing it contains code.