VBA (Visual Basic for Applications) is the programming language built into Microsoft Office that lets you automate repetitive tasks, and macros are the recorded or written sequences of actions that run in that language. In Excel, you can create a macro either by recording your mouse clicks and keystrokes (no coding needed) or by writing VBA code directly in the Visual Basic Editor.
What Exactly Is a Macro?
A macro is a set of instructions that Excel executes automatically. When you record a macro, Excel translates your actions into VBA code in the background. For example, if you regularly format a sales report with bold headers, column widths, and number formats, you can record those steps once and then replay them with a single click or keyboard shortcut.
Macros are stored in the workbook that contains them, or in your Personal Macro Workbook so they are available across all your Excel files. The file extension matters: a standard Excel file is .xlsx, but a file with macros must be saved as .xlsm (macro-enabled) or .xlsb (binary, also macro-enabled). If you save a macro-containing workbook as .xlsx, Excel will either strip the macros or warn you.
How VBA Differs from Simple Recorded Macros
Recording a macro is like using a tape recorder: it captures exactly what you do, including mistakes and unnecessary clicks. VBA code, on the other hand, is the actual program that runs. You can open the Visual Basic Editor (press Alt+F11 on Windows, Option+F11 on Mac) and read or edit the generated code.
For instance, a recorded macro that selects cell A1 and types “Hello” might generate code like:
Sub SayHello()
Range("A1").Select
ActiveCell.FormulaR1C1 = "Hello"
End Sub
VBA lets you go far beyond recording: you can loop through thousands of rows, make decisions with If statements, interact with other Office applications (like sending an email from Outlook), and build custom functions. A common example is a macro that finds all blank cells in a column and fills them with the value from the cell above:
Sub FillBlanks()
Dim cell As Range
For Each cell In Range("A1:A100")
If cell.Value = "" Then
cell.Value = cell.Offset(-1, 0).Value
End If
Next cell
End Sub
Always save a copy of your workbook before running any macro, because macros cannot be undone with Ctrl+Z.
How to Create Your First Macro
The easiest way to start is by recording. Here are the steps for Excel 365 on Windows:
- Go to the View tab and click Macros > Record Macro.
- Give the macro a name (no spaces, must start with a letter).
- Optionally assign a shortcut key (like Ctrl+Shift+R) and choose where to store it (This Workbook or Personal Macro Workbook).
- Click OK, then perform the actions you want to record.
- Click Stop Recording on the same menu.
On a Mac, the path is Tools > Macro > Record Macro. The recorded macro is now available under View > Macros > View Macros.
To write VBA manually, press Alt+F11 to open the editor, insert a new module, and type your code. Then run it by pressing F5 while the cursor is inside the subroutine.
Common Problems and How to Avoid Them
Macros fail for a few predictable reasons. The most common is that the workbook is not saved as .xlsm. Another frequent issue is security settings: Excel disables macros by default. When you open a macro-enabled file, you may see a yellow bar with an Enable Content button. If macros are completely blocked, you can change the setting in File > Options > Trust Center > Trust Center Settings > Macro Settings.
Other typical problems include:
- Relative vs. absolute references: When recording, use the Use Relative References toggle if you want the macro to work on different cells, not just the exact ones you clicked.
- Hard-coded ranges: VBA code that says
Range("A1:A100")will only work for that exact range. UseCurrentRegionorUsedRangeto make it dynamic. - Forgetting to save a backup: A macro that deletes data or overwrites formulas cannot be undone. Always test on a copy.
- Macro won’t run on another computer: If you use a Personal Macro Workbook, it only exists on your machine. To share, copy the macro into the workbook you send.
When to Use VBA vs. Built-in Excel Features
VBA is powerful, but it is not always the best tool. Modern Excel has many built-in features that can replace macros for common tasks:
- Power Query (Data > Get & Transform) can clean and combine data without any code.
- Conditional Formatting and Data Validation handle visual rules and input restrictions.
- Formulas like
IF,VLOOKUP, andSUMIFScan automate calculations without VBA. - Tables and PivotTables update automatically when data changes.
Use VBA when you need to do something that these features cannot, such as automating a multi-step process across multiple sheets, interacting with external files, or creating a custom user interface. For example, a macro that imports data from a text file, cleans it, and generates a chart is a good VBA candidate. But if you just need to filter a list, use the built-in filter buttons.
Security and Safety with Macros
Macros can contain malicious code. Because VBA can access the file system, send emails, and modify other files, an untrusted macro can harm your computer. Microsoft Office uses a system called Digital Signatures to verify that a macro comes from a trusted publisher. You should only enable macros from sources you trust.
If you receive a spreadsheet with macros, check the source before enabling. Even from a known contact, be cautious if the file asks for unusual permissions. To protect yourself:
- Keep macro security set to Disable all macros with notification (the default).
- Never enable macros in files from unknown or unexpected emails.
- Consider using Protected View for files downloaded from the internet.
- Save a clean copy of any workbook before running an unfamiliar macro.
Alternatives to VBA
If you find VBA too complex or if your organization restricts macros, there are alternatives:
- Office Scripts in Excel for the web and Microsoft 365 – a modern, cloud-based automation language that uses JavaScript-like syntax.
- Power Automate – a no-code/low-code tool that can trigger workflows across Office apps.
- Python in Excel (currently in preview for Microsoft 365) – allows you to use Python code directly in formulas.
- Add-ins like Power Pivot or third-party tools for specific tasks.
Each has its own learning curve, but for simple automation, recording a macro is still the quickest entry point.
Frequently Asked Questions
Can I undo a macro?
No. Macros cannot be undone with Ctrl+Z. That is why you should always save a backup copy of your workbook before running a macro, especially one that modifies data.
Why is my macro not working on another computer?
The most common reason is that the macro is stored in your Personal Macro Workbook, which is only on your machine. Move the code into the workbook you are sharing, or copy the Personal.xlsb file. Also, the other user may have macro security set to disable all macros.
What is the difference between a macro and a VBA function?
A macro is a subroutine (Sub) that performs actions. A VBA function (Function) returns a value and can be used in worksheet formulas, like a custom =MyFunction(). Macros are for automating tasks; functions are for calculations.
How do I learn VBA?
Start by recording simple macros and examining the generated code. Use the Visual Basic Editor’s built-in help (press F1) and the Object Browser (F2) to explore. Many free tutorials and forums exist, but practice is key.
Are macros dangerous?
They can be. Malicious macros have been used to deliver ransomware. Always disable macros unless you are certain the file is safe. Even legitimate macros can cause data loss if they contain bugs, so test on a copy.
Related
- Export EndNote to Excel: 4 Easy Ways
- Excel Yearly Budget Template: How to Build One
- Excel Zip Extract: How to Open ZIP Files
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Filter an Excel PivotTable With VBA: Fields, Items, and CodeTo filter an Excel PivotTable with VBA, reference the PivotTable and field, clear any existing filter, then set…
- Excel VBA Exit Sub: How It Works and When to Use ItIn Excel VBA, Exit Sub immediately ends the currently running Sub procedure and returns control to the code…
- VBA MsgBox With a Variable: Examples and TipsTo show a VBA variable in a message box, join it to the message text with the ampersand…
- LibreOffice Macro Recorder: Setup and LimitsYes—LibreOffice includes a built-in macro recorder, but it is limited: it captures some actions in Calc and Writer…
- Excel VBA LAMBDA: Calling Worksheet Functions from VBAExcel’s LAMBDA function is a worksheet feature, not a VBA language feature: VBA has no built-in anonymous-lambda syntax.…
- Excel VBA Import CSV: Methods, Code, and Common IssuesTo import a CSV into an Excel worksheet with VBA, open the file with Workbooks.OpenText, then copy its…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.