VBA & automation
How to Write a VBA Macro in Excel (Step-by-Step Guide)
A VBA macro is a small program you write inside Excel to automate work you would otherwise do by hand: formatting reports, looping through rows, cleaning data, or building a file from scratch. VBA (Visual Basic for Applications) is the language built into Excel, and every macro lives inside a Sub procedure.
You do not need to be a developer to start. This guide shows you how to open the Visual Basic editor, write your first Sub, run it, and save your workbook so the macro sticks around. By the end you will be able to read and adapt the short examples below for your own sheets.
Examples
Real, copy-paste-ready formulas.
1. Your first macro: a message box
Sub SayHello()
MsgBox "Hello from VBA!"
End Sub Every macro is a Sub with a name and an End Sub. MsgBox shows a dialog. Place the cursor inside the Sub and press F5 to run it and see the message appear.
2. Loop through a range of cells
Sub MarkDone()
Dim c As Range
For Each c In Range("A2:A100")
If c.Value <> "" Then c.Offset(0, 1).Value = "Done"
Next c
End Sub For Each walks every cell in the range. The If skips blanks, and Offset(0, 1) writes one column to the right. Loops like this are the core of most useful macros.
3. Format a header row in one click
Sub FormatHeader()
With Rows(1)
.Font.Bold = True
.Interior.Color = RGB(230, 230, 230)
End With
End Sub The With block lets you set several properties on the same object without repeating Rows(1). RGB(230, 230, 230) is a light gray. Assign the macro to a button to reuse it instantly.
How to write VBA macros step by step
- 1
Open the Visual Basic editor with Alt + F11 (on Mac, Option + F11, or use Tools > Macro > Visual Basic Editor).
- 2
In the editor, go to Insert > Module to create a blank code module where your macro will live.
- 3
Type your macro starting with Sub MacroName() and ending with End Sub, then write your instructions on the lines between.
- 4
Click inside the Sub and press F5 (or the green Run arrow) to run it; switch back to Excel with Alt + F11 to see the result.
- 5
Save the workbook as a macro-enabled file (.xlsm) via File > Save As so the macro is not stripped out.
- 6
To rerun later, press Alt + F8 in Excel to open the Macro dialog, pick your macro, and click Run, or assign it to a button or shape.
Frequently asked questions
How do I open the VBA editor in Excel?
Press Alt + F11 on Windows (Option + F11 on Mac). You can also enable the Developer tab via File > Options > Customize Ribbon and click Visual Basic, or go to Tools > Macro > Visual Basic Editor.
Why can I not save my macro?
Standard .xlsx files cannot store macros. Use File > Save As and choose Excel Macro-Enabled Workbook (.xlsm). If macros are blocked when you reopen the file, enable them in File > Options > Trust Center > Macro Settings.
What is the difference between a Sub and a Function?
A Sub performs actions and is what you run as a macro. A Function returns a value and can be used inside a worksheet formula. Most automation tasks use a Sub.
Can I generate VBA without writing code?
Yes. With ExcelPerfect you describe what you want in plain English and it writes the VBA for you, ready to paste into a module. It is the fastest way to get a working macro when you are not sure of the exact syntax.