Introduction to Macros
Transform repeated Excel work into reliable one-click actions. Learn how to plan, record, save, run, inspect and safely distribute macros before moving into full VBA programming.
After This Chapter, You Will Be Able To
Recognize suitable automation opportunities, record dependable macros, store them correctly, run them safely and understand the VBA code created by Excel.
Select Tasks
Identify stable, repetitive and rule-based Excel activities that are suitable for macro automation.
Record Actions
Use the Macro Recorder with correct names, storage locations and reference behaviour.
Deploy Macros
Run automation through the Macro dialog, shortcuts, shapes, buttons and Quick Access Toolbar.
Work Safely
Save macro-enabled files correctly and avoid enabling untrusted or unexplained code.
What Is an Excel Macro?
A macro is a stored set of instructions that Excel can run again to perform a sequence of actions automatically.
1 Suitable and Unsuitable Macro Tasks
Macros are most valuable when a task is repeated frequently, follows consistent rules and consumes unnecessary manual time. Examples include formatting a monthly report, importing a fixed data layout, preparing a print area, refreshing reports, creating headers or cleaning standardized fields.
| Good Macro Candidate | Why It Works | Use Caution When |
|---|---|---|
| Apply the same report formatting every week | Steps and output are stable | The workbook layout changes frequently |
| Insert a standard title, date and footer | Actions are repeatable | Each report needs different judgement |
| Clean imported columns with fixed rules | Rules can be recorded or coded | Source fields are inconsistent or unknown |
| Create a one-click print setup | The target sheet and print rules are known | Users may accidentally print confidential information |
Practical Experiment 1: Find Three Automation Opportunities
Review your recent Excel work and identify tasks that could become repeatable macros.
List five tasks you performed more than once during the last week or month.
Rate each task for repetition, rule stability, time consumed and risk of manual error.
Choose three tasks with stable steps and measurable time-saving potential.
2 Prepare Excel for Macro Work
The Developer tab contains the main tools for recording, running, editing and securing VBA macros. It may be hidden in a new Excel installation, so learners should enable it before beginning.
Open Options
Choose File → Options.
Customize Ribbon
Open Customize Ribbon.
Enable Developer
Select Developer under Main Tabs.
Confirm
Click OK and verify the tab.
Essential Developer Tab Commands
| Command | Purpose | Beginner Use |
|---|---|---|
| Visual Basic | Opens the Visual Basic Editor | Inspect or edit recorded code |
| Macros | Lists available macros | Run, edit, delete or manage macros |
| Record Macro | Starts recording user actions | Create automation without writing code initially |
| Use Relative References | Changes how cell movement is recorded | Build location-flexible macros |
| Macro Security | Opens Trust Center macro settings | Review how Excel handles VBA content |
Practical Experiment 2: Build a Safe Practice Environment
Create a separate workbook and sample dataset so macro experiments cannot damage important business files.
Make a workbook named Macro-Practice with Raw Data, Report and Notes sheets.
Add a small sales table containing dates, regions, products, quantities and values.
Save an untouched backup before recording any macro.
3 Record Your First Macro
The Macro Recorder captures many actions exactly as you perform them and writes the corresponding VBA instructions. It is a powerful learning tool, but it also records inefficient selections, unnecessary scrolling and accidental clicks. Plan the steps before pressing Record.
Record Macro Dialog
| Field | Professional Guidance | Example |
|---|---|---|
| Macro name | Begin with a letter; avoid spaces; use a descriptive action name | FormatMonthlyReport |
| Shortcut key | Use cautiously so you do not replace an important Excel shortcut | Ctrl+Shift+R |
| Store macro in | Select the workbook scope according to how and where the macro will be used | This Workbook |
| Description | Explain purpose, target sheet, assumptions and owner | Formats the active monthly sales report |
Practical Experiment 3: Record a Report Formatting Macro
Record a macro that turns a plain data range into a readable office report.
List the required title, header formatting, borders, number format, column widths and freeze-pane action.
Name it FormatSalesReport and perform only the planned steps.
Apply it to a fresh copy of the same report layout and record any unwanted behaviour.
4 Absolute and Relative Macro Recording
The most important beginner decision is whether recorded cell actions should always affect the same address or should move relative to the active cell. Excel records absolute references by default unless Use Relative References is enabled.
Absolute Recording
Targets fixed addresses such as A1, B2 or a named worksheet. Use it when the macro must always update the same title, report area or control cell.
Relative Recording
Records movement from the starting cell. Use it when the macro should perform the same pattern wherever the user begins.
| Requirement | Recommended Mode | Reason |
|---|---|---|
| Format the fixed report heading in A1:H2 | Absolute | The target location never changes |
| Insert a subtotal two rows below the active record | Relative | The target depends on the starting cell |
| Clear a fixed input form range | Absolute | The input form has known addresses |
| Move one column right and apply a formula | Relative | The same movement should work in different locations |
Practical Experiment 4: Compare Reference Modes
Record the same simple formatting action once with absolute references and once with relative references.
Record a macro that formats cell B3, then run it while another cell is active.
Enable Use Relative References and record movement one cell right before applying formatting.
Run both macros from three starting cells and document the difference.
5 Save and Store Macros Correctly
A macro stored inside a normal .xlsx workbook will not be preserved. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) when the file must retain VBA code.
| Storage Choice | Availability | Best Use |
|---|---|---|
| This Workbook | Available when that macro-enabled workbook is open | Automation belongs to a specific report or solution |
| New Workbook | Stored in a newly created workbook | Separating a macro during development |
| Personal Macro Workbook | Available whenever desktop Excel starts for that user | Personal productivity tools used across many workbooks |
Macro-Related File Types
.xlsx
Standard workbook format. It does not retain VBA macro code.
.xlsm
Macro-enabled workbook format for files that contain VBA projects.
Personal.xlsb
Hidden personal macro workbook used for macros that should be available across desktop Excel sessions.
Practical Experiment 5: Test Macro Storage
Save the same practice solution in different formats and verify what happens to the macro.
Save the workbook as a macro-enabled file, close it, reopen it and confirm the macro remains.
Save a separate .xlsx copy and carefully read Excel's warning about VBA content.
Record which format should be used for the master automation file and recipient copies.
6 Run a Macro in Different Ways
A professional automation should have a run method appropriate for its audience. Developers may use the Macro dialog or Visual Basic Editor, while routine users may need a clearly labelled shape or button.
Macro Dialog
Press Alt+F8, select the macro and click Run. Best for testing and occasional use.
Shortcut Key
Useful for trained users, but it must not conflict with important Excel shortcuts.
Shape or Button
Provides an intuitive one-click action for forms, reports and operational workbooks.
VBA Editor
Place the cursor inside a procedure and run it during development or debugging.
Quick Access Toolbar
Add a frequently used personal macro for convenient access in desktop Excel.
Workbook Event
Advanced VBA can run from events, but this should be introduced only after understanding VBA fundamentals.
Practical Experiment 6: Deploy Three Run Methods
Run the same formatting macro through three different interfaces.
Run it from Alt+F8 and observe the macro name and location.
Insert a shape, label it Format Report and assign the macro.
Assign a safe shortcut and document it in the workbook Notes sheet.
7 Inspect Recorded VBA Code
Recording a macro creates VBA code in a standard module. Beginners do not need to understand every line immediately. Start by recognizing the procedure name, objects, properties, methods and the sequence of actions.
Sub FormatReport()
'Format the active report heading
Range("A1:H1").Select
Selection.Font.Bold = True
Selection.Interior.Color = RGB(6, 40, 95)
Selection.Font.Color = RGB(255, 255, 255)
Columns("A:H").AutoFit
End SubRead the Code in Layers
| Code Element | Meaning | Example |
|---|---|---|
| Procedure | The named block that Excel can run | Sub FormatReport() |
| Object | The Excel item being controlled | Range, Selection, Columns |
| Property | A characteristic being read or changed | Font.Bold, Interior.Color |
| Method | An action performed by an object | Select, AutoFit, ClearContents |
| Comment | A note for humans, ignored by VBA | Line beginning with an apostrophe |
Practical Experiment 7: Make a Controlled Code Edit
Open the recorded macro and modify one visible formatting property.
Press Alt+F11, locate Modules and open the procedure.
Change the bold setting, colour value or target range on a safe copy.
Run the macro and compare the result with the original version.
9 Macro Security and Platform Limitations
Macros contain executable code. They can automate valuable work, but malicious macros can also harm data or systems. Never enable a macro merely because the file looks familiar or arrived from a known contact.
Safer Practice
Use trusted internal sources, digitally signed code where required, controlled storage, antivirus protection and documented ownership.
Default Caution
Keep notification-based macro security so VBA content remains disabled until the user makes an informed decision.
Avoid
Do not enable all macros globally. Do not run unknown code, unexplained attachments or files from unverified download sources.
Important Current Platform Notes
Desktop Excel
VBA macros can be created, recorded, edited and run in supported desktop Excel versions.
Excel for the Web
A workbook containing VBA macros can be opened and edited, but VBA macros cannot be created, run or edited in the browser; use the desktop app.
Managed Organization
Macro settings may be controlled by IT policy, trusted locations, signed publishers or organizational security rules.
Choose an Appropriate Macro Approach
Select the task characteristics to receive a practical recording and deployment recommendation.
Build a One-Click Monthly Report Formatter
Create a macro-enabled workbook that transforms a plain monthly sales report into a standardized management-ready output.
Project: Monthly Sales Report Automation
Your workbook should contain a Raw Report sheet, a formatted Report sheet, an Instructions sheet and a working macro interface.
Create a plain report with Date, Region, Executive, Product, Quantity, Sales and Collection columns.
Write the exact formatting sequence: title, headers, number formats, borders, widths, freeze panes, filters and print setup.
Record FormatMonthlySalesReport using the appropriate reference mode and store it in This Workbook.
Create a clearly labelled button and add instructions describing the required starting sheet and expected output.
Run the macro on at least three fresh report copies, including one with more rows and one with fewer rows.
Save as .xlsm, preserve a backup, document the macro source and review macro security before sharing.
Macro Planning and Testing Tasks
Complete these tasks to create evidence of practical understanding and responsible automation behaviour.
List five repeated Excel tasks and score them for frequency, rule stability, time saved and error risk.
Macro opportunity matrix
Record a five-step formatting macro using a descriptive name and description.
Working recorded macro
Record one absolute and one relative macro, then run both from three starting positions.
Behaviour comparison table
Compare This Workbook, Personal Macro Workbook and macro-free file behaviour.
Storage decision note
Run one macro through Alt+F8, a shape and a safe keyboard shortcut.
Three verified interfaces
Highlight the procedure, objects, properties, methods and comments in recorded code.
Annotated VBA screenshot
Change one recorded formatting property and compare the result.
Before-and-after test
Write a checklist for deciding whether an incoming macro-enabled file should be trusted.
Macro security checklist
Ask another learner to run your macro using only the workbook instructions.
User feedback and corrections
Describe the manual process, automated steps, time saved, risks controlled and future VBA improvements.
One-page project summary
Mistakes Macro Beginners Should Avoid
Most early macro failures come from poor planning, uncontrolled recording, incorrect file formats or unsafe trust decisions.
Wrong Habits
- Recording before writing the required step sequence
- Including unnecessary selections, scrolling and corrections
- Using a vague macro name such as Macro1
- Choosing absolute recording for a location-flexible task
- Saving the master file as .xlsx
- Assigning shortcuts that replace useful Excel commands
- Running a macro on important data without backup
- Enabling content from an unknown or unexplained source
Correct Habits
- Standardize the manual process before automation
- Record only planned and required actions
- Use descriptive action-based names and descriptions
- Test absolute and relative behaviour deliberately
- Preserve VBA in .xlsm or an appropriate macro-enabled format
- Provide clear buttons, instructions and owner details
- Test on safe copies and preserve original data
- Trust code only after verifying source and purpose
Check Your Macro Fundamentals
Select the best answer for each question, then submit to view your score and explanations.
1. What is the primary purpose of an Excel macro?
2. Which tab contains Record Macro, Visual Basic and Macro Security?
3. Which file format should normally be used to retain VBA macros in a workbook?
4. When is relative recording most useful?
5. Where should a personal macro be stored if it must be available whenever desktop Excel opens?
6. Which shortcut opens the Macro dialog?
7. What does the Macro Recorder create?
8. What is the safest routine macro-security approach?
9. Which label is best for a button that applies report formatting?
10. Why should a macro be tested on a copy first?
11. What can Excel for the web currently do with a workbook containing VBA macros?
12. Which workflow is most professional?
Remember These Macro Principles
Revise these points before moving to VBA Fundamentals.
Automate Stable Work
Choose repeatable, rule-based tasks with clear inputs and outputs.
Plan Before Recording
Write the exact sequence and avoid unnecessary clicks or selections.
Choose References Carefully
Use absolute references for fixed locations and relative references for movable patterns.
Save in the Correct Format
Use .xlsm when the workbook must retain VBA code.
Test and Document
Use safe copies, varied test cases, instructions and a clear macro owner.
Trust Code Responsibly
Never enable unknown macros or use Enable All Macros as a routine setting.