What Is a Macro in Excel?
Learn what a macro in Excel is, how to record and run one, the role of VBA and .xlsm files, and the security checks that matter.
A macro in plain words
Imagine that every Friday you import a report, format its headings, set column widths, add a total, and export a clean copy. A macro can capture those actions and replay them. The workbook does not “understand” the business task: it follows recorded or programmed instructions in a particular order.
Excel desktop macros use Visual Basic for Applications, usually shortened to VBA. A macro is commonly a public Sub procedure with no arguments. It can change cells, formatting, worksheets, files, and other application state, which makes it useful but also means code must be reviewed and trusted before it runs.
FormulaCamp teaches spreadsheet concepts and can evaluate its supported worksheet formulas in the browser. It does not execute VBA macros. To record, edit, or run the example below, use a desktop Excel environment that supports VBA.
Record a macro carefully
First show the Developer tab if it is hidden. On Windows, use File > Options > Customize Ribbon; on Mac, use Excel > Preferences or Settings > Ribbon & Toolbar. Choose Record Macro, give the macro a descriptive name without spaces, select where to store it, and write a short description. Store it in This Workbook when the automation belongs with that file.
Before pressing Record, put the workbook in a predictable starting state. Then perform only the intended steps and choose Stop Recording. The recorder captures many details, including selections and active-sheet assumptions. Test the result on a disposable copy with the same layout, because a recorded deletion or overwrite can repeat just as faithfully as a useful format change.
Relative References changes how recorded movement behaves. With ordinary recording, code often targets exact cells. With relative recording, it moves from the active cell. Neither is automatically safer: choose the behavior that matches the task, and make the starting position explicit.
Read a small VBA example
This procedure writes a heading, sums B2:B10, and formats the result as a number. Sub begins the procedure and End Sub closes it. Each Range line acts on the active worksheet.
Active-sheet references are convenient for a demonstration but fragile in production. Code can qualify the workbook and worksheet explicitly, validate that expected headers exist, and avoid overwriting nonblank output. Add comments for assumptions, keep a backup, and test on representative copies before using the procedure on important data.
The formula inside the VBA string uses the VBA-facing formula syntax. A localized workbook may display translated function names in the grid, while VBA properties and regional behavior have their own rules. Test code in the actual target environment rather than assuming a recorded procedure is portable everywhere.
Sub AddTotal()
Range("A11").Value = "Total"
Range("B11").Formula = "=SUM(B2:B10)"
Range("B11").NumberFormat = "0.00"
End SubRun it and save the right file type
You can run a macro from Developer > Macros, from the VBA editor, from a button assigned to the macro, or from an approved keyboard shortcut. A button is often clearer for colleagues because its label can describe the action. Avoid overriding familiar shortcuts and document the required starting sheet and selection.
A standard .xlsx file cannot preserve VBA code. Save a workbook containing macros as an Excel Macro-Enabled Workbook with the .xlsm extension. An .xlam file is an add-in, and .xlsb can also contain VBA, but .xlsm is the straightforward format for a normal macro-enabled workbook. Confirm the file type before closing so the code is not discarded.
Saving the correct format does not make a macro trusted. Recipients may see a security warning, and organizational policy may block macros. Distribute code through an approved location and include a plain explanation of what it changes.
Security and when a macro is not needed
Macros can read and alter workbook data and may interact with files or other applications. Never enable content merely because a message tells you to. Verify the sender and expected file, scan it through your organization’s controls, inspect the code when appropriate, and follow local policy. Protected View and macro settings are safeguards, not obstacles to switch off globally.
Use a worksheet formula when the output should recalculate from cells. Use an Excel Table for expanding structured data, conditional formatting for visual rules, data validation for input controls, Power Query for repeatable imports and transformations, or a PivotTable for summaries. These tools are usually easier for another person to inspect than VBA.
A macro earns its maintenance cost when it coordinates a stable multi-step process, needs desktop features that formulas cannot provide, and has a clear owner. If the source layout changes frequently or the procedure is run by many people under different security policies, changing the workflow design may be safer than adding more recorded steps.
Your turn
In C2, total the amounts in B2:B5. This is a case where a formula is enough.
| A | B | C | |
|---|---|---|---|
| 1 | Expense | Amount | Total |
| 2 | Travel | 25 | |
| 3 | Meals | 12 | |
| 4 | Supplies | 8 | |
| 5 | Other | 5 |
Answer: =SUM(B2:B5)
Worked example
The values a simple total macro might find in B2:B5. These worksheet formulas are checked here; the VBA procedure itself must run in desktop Excel.
| A | B | |
|---|---|---|
| 1 | Expense | Amount |
| 2 | Travel | 25 |
| 3 | Meals | 12 |
| 4 | Supplies | 8 |
| 5 | Other | 5 |
| Formula | Result |
|---|---|
=SUM(B2:B5) | 50 |
=IF(SUM(B2:B5)>40,"Review","OK") | "Review" |
=B2+B3+B4+B5 | 50 |
=SUM(B2:B4) | 45 |
Quick mistake checklist
- Recording against the wrong active sheet or starting cell.
- Testing destructive code on the only copy of a workbook.
- Saving VBA in
.xlsxand losing the code. - Enabling macros from an unknown or unexpected file.
- Automating a task that a transparent formula or query already handles.
Frequently asked questions
Is a macro the same as VBA?
A macro is the automation you run. In desktop Excel, recorded macros are stored as VBA procedures, and VBA can create more complex macros than the recorder alone.
Can an .xlsx file contain a VBA macro?
No. Use .xlsm for a normal macro-enabled workbook; saving as .xlsx removes the VBA project.
Are macros safe?
A macro is code and can make broad changes. Run only expected code from a trusted source under your organization’s security policy.
Do I need a macro to calculate totals?
Usually not. A worksheet formula such as SUM, an Excel Table total row, or a PivotTable is clearer and updates with the data.
Can FormulaCamp run my VBA code?
No. The browser practice environment supports its documented worksheet formula engine, not VBA execution.
Practice the skill
Continue with a checked FormulaCamp lesson: VBA: Your First Macros Free
Open the free lesson