Recording and Running Macros

VBA COURSE • BEGINNER

Recording and Running Macros in Excel

One of the easiest ways to start learning Excel VBA is by using the Macro Recorder.

The Macro Recorder allows Excel to record the actions you perform in a workbook and automatically convert many of those actions into VBA code. You can then view, run, and modify the generated VBA code in the Visual Basic Editor.

In this lesson, you will learn:
  • What an Excel macro is
  • What the Macro Recorder does
  • How to enable the Developer tab
  • How to record your first macro
  • How to stop recording a macro
  • How to run a recorded macro
  • How to view the VBA code generated by Excel
  • How to edit recorded VBA code
  • How to use relative references
  • How to use absolute references
  • How to assign a macro to a button
  • How to use keyboard shortcuts for macros
  • How to save macro-enabled workbooks
  • Common Macro Recorder mistakes
  • Practical exercises

Table of Contents

  1. What is a Macro?
  2. What is the Macro Recorder?
  3. Why Use the Macro Recorder?
  4. Enable the Developer Tab
  5. Prepare Your Workbook
  6. Record Your First Macro
  7. Understanding the Macro Name
  8. Macro Description
  9. Stop Macro Recording
  10. Run Your First Macro
  11. View Recorded VBA Code
  12. Understanding Recorded VBA Code
  13. Edit Recorded VBA Code
  14. Absolute Cell References
  15. Relative Cell References
  16. Relative Reference Example
  17. Assign Macro to a Button
  18. Assign a Keyboard Shortcut
  19. Personal Macro Workbook
  20. Saving Macro-Enabled Workbooks
  21. Macro Security
  22. Limitations of Macro Recorder
  23. Macro Recording Best Practices
  24. Practice Exercises
  25. Summary

1. What is a Macro?

A macro is a series of instructions that can automate tasks in Excel.

Instead of manually performing the same steps again and again, you can create a macro that performs those steps automatically.

Example

Suppose every day you perform the following tasks:

  1. Open a report.
  2. Format the headings.
  3. Make the headings bold.
  4. Change the background color.
  5. Adjust column widths.
  6. Apply number formatting.

Instead of repeating these steps manually, you can record them once as a macro and run the macro whenever required.

Simple definition: A macro is an automated sequence of Excel actions.

2. What is the Macro Recorder?

The Macro Recorder is an Excel tool that records many actions you perform in the workbook and converts those actions into VBA statements.

For example, if you record yourself:

  • Selecting a cell
  • Typing text
  • Making the text bold
  • Changing the font color
  • Changing the background color

Excel creates VBA code representing those actions.

This makes the Macro Recorder an excellent tool for beginners who are learning how Excel VBA works.

3. Why Use the Macro Recorder?

The Macro Recorder is useful for several reasons.

1. Learn VBA Syntax

You can perform an Excel action and then look at the VBA code generated by Excel.

2. Automate Repetitive Tasks

Tasks that you perform repeatedly can often be automated.

3. Discover VBA Properties and Methods

The recorded code can show you how Excel represents particular actions using VBA.

4. Create a Starting Point

You can record a macro and then simplify or improve the generated code manually.

Important: The Macro Recorder is a learning and automation tool, but the generated code is not always the most efficient VBA code. As you become more experienced, you will learn to write cleaner and more flexible VBA code yourself.

4. Enable the Developer Tab

The Macro Recorder is available from Excel's Developer tab.

If you cannot see the Developer tab, you may need to enable it through Excel's Ribbon customization settings.

Developer Tab Contains Tools Such As:

  • Visual Basic
  • Macros
  • Record Macro
  • Use Relative References
  • Insert Controls
  • Macro Security
Shortcut: You can also open the VBA Editor directly using Alt + F11.

5. Prepare Your Workbook

Before recording your first macro, create a small practice table.

Enter the following data:

Cell Value
A1 Employee Name
B1 Department
C1 Salary
A2 John
B2 Sales
C2 50000

We will record a macro that formats the headings in row 1.

6. Record Your First Macro

Let's create your first recorded macro step-by-step.

1 Open your Excel workbook.
2 Go to the Developer tab.
3 Click Record Macro.
4 Enter a macro name.
5 Optionally enter a shortcut key.
6 Choose where to store the macro.
7 Enter a description if required.
8 Click OK.

Excel is now recording your actions.

Important: After clicking Record Macro, Excel records many of your subsequent actions. Perform only the actions that you want your macro to reproduce.

7. Understanding the Macro Name

When you record a macro, Excel asks you to provide a macro name.

For example: 

FormatHeaders

A good macro name should describe the purpose of the macro.

Good Macro Names

  • FormatHeaders
  • CreateReport
  • CleanData
  • FormatSalesData
  • PrepareMonthlyReport

Avoid Names Like

  • Macro1
  • Test123
  • ABC
  • MyMacro
Best practice: Use descriptive names so that you can understand the purpose of a macro later.

8. Macro Description

Excel also provides a field for a macro description.

You can write something like: 

Formats the header row of the sales report.

Descriptions become especially useful when a workbook contains many macros.

9. Stop Macro Recording

After completing the actions you want to record, you must stop the recorder.

1 Go to the Developer tab.
2 Click Stop Recording.

Your actions have now been converted into VBA code.

Remember: If you forget to stop recording, Excel may continue recording actions that you perform afterward.

10. Run Your First Macro

After recording the macro, let's run it.

Method 1: Developer Tab

1 Click Developer.
2 Click Macros.
3 Select your macro.
4 Click Run.

Method 2: VBA Editor

Open the VBA Editor using: 

Alt + F11

Place your cursor inside the macro and press: 

F5

Method 3: Keyboard Shortcut

If you assigned a shortcut key while recording the macro, you can use that shortcut to run it.

11. View Recorded VBA Code

One of the most useful learning techniques is to record a macro and immediately examine the VBA code generated by Excel.

1 Press Alt + F11.
2 Open the VBA Project.
3 Find the Modules folder.
4 Open the module containing your macro.

You will see VBA code similar to: 

Sub FormatHeaders()

    Range("A1:C1").Select
    Selection.Font.Bold = True
    Selection.Interior.Color = RGB(0, 176, 80)
    Selection.Font.Color = RGB(255, 255, 255)

End Sub

The exact code generated can vary depending on the actions you recorded and your Excel version.

12. Understanding Recorded VBA Code

Let's examine the example: 

Range("A1:C1").Select

This selects cells A1 through C1.

Next: 

Selection.Font.Bold = True

This makes the selected cells bold.

Then: 

Selection.Interior.Color = RGB(0, 176, 80)

This changes the background color of the selection.

Finally: 

Selection.Font.Color = RGB(255, 255, 255)

This changes the font color to white.

13. Edit Recorded VBA Code

Recorded code is not necessarily the final code you should use. You can modify it manually.

Recorded Version

Sub FormatHeaders()

    Range("A1:C1").Select
    Selection.Font.Bold = True
    Selection.Interior.Color = RGB(0, 176, 80)

End Sub

You can often simplify the code by removing unnecessary Select and Selection statements.

Improved Version

Sub FormatHeaders()

    With Range("A1:C1")

        .Font.Bold = True
        .Interior.Color = RGB(0, 176, 80)

    End With

End Sub
Important VBA concept: As you progress through this course, you will learn how to replace recorded code with cleaner, faster and more flexible VBA.

14. Absolute Cell References

By default, the Macro Recorder often records specific cell locations. These are known as absolute references.

For example: 

Range("A1").Select

This code specifically refers to cell A1.

If the macro always needs to work with A1, this may be perfectly fine.

Example

Sub FormatTitle()

    Range("A1").Font.Bold = True
    Range("A1").Font.Size = 18

End Sub

15. Relative Cell References

Relative references allow the recorded macro to work relative to the current active cell.

This can be extremely useful when you want the same macro to perform an operation relative to wherever you start.

On the Developer tab, Excel provides the:

Use Relative References

option while recording macros.

16. Relative Reference Example

Suppose you start from cell A1 and record an action that moves one cell to the right.

Depending on the recording settings, Excel can record movement relative to the starting cell rather than always referring to one fixed address.

This makes the macro more flexible.

Important: Absolute and relative references are important concepts when using the Macro Recorder. Practice both methods to understand how they behave.

17. Assign a Macro to a Button

You can make a macro easier to run by assigning it to a worksheet button.

Using a Form Control Button

1 Go to the Developer tab.
2 Click Insert.
3 Choose a Button from Form Controls.
4 Draw the button on the worksheet.
5 Select the macro you want to assign.
6 Click OK.

Now users can click the button to run the macro.

Example Button

Format Report

When the user clicks the button, the macro can automatically format the report.

18. Assign a Keyboard Shortcut

You can assign a keyboard shortcut while recording a macro.

For example, you might assign: 

Ctrl + Shift + F

to a formatting macro.

Be careful with shortcuts: Avoid assigning shortcuts that you frequently use for normal Excel operations, because the macro shortcut may override the normal Excel shortcut while the workbook is active.

19. Personal Macro Workbook

Excel also provides a special workbook called the Personal Macro Workbook.

Macros stored there can be available whenever you use Excel, rather than being limited to one particular workbook.

This can be useful for general-purpose macros that you use regularly.

Example

Suppose you create a macro that formats selected cells in a standard way. You may want to use that macro in many different workbooks.

Storing it in the Personal Macro Workbook can make it available across your Excel sessions.

For beginners: You do not need to use the Personal Macro Workbook immediately. First become comfortable with macros stored inside your own workbook.

20. Saving Macro-Enabled Workbooks

If your workbook contains VBA macros, use a macro-enabled workbook format when saving it.

Excel Macro-Enabled Workbook (*.xlsm)

If you save a macro-containing workbook as a normal .xlsx file, Excel cannot retain the VBA project in that file format.

Always check the file type: Before closing your workbook, make sure your VBA code has been saved in a macro-enabled format.

21. Understanding Macro Security

Excel includes security features designed to protect users from potentially harmful macros.

When opening a workbook containing macros from an untrusted source, Excel may display a security warning.

Security Rule: Never enable macros in an unknown or untrusted workbook simply because Excel asks you to enable them.

Only enable macros when you understand and trust the source of the workbook and its contents.

22. Limitations of the Macro Recorder

The Macro Recorder is powerful for beginners, but it has limitations.

1. It Records Actions

The recorder primarily records what you do in Excel rather than understanding the business goal behind those actions.

2. Recorded Code Can Be Long

The recorder may generate more code than is actually necessary.

3. Select and Selection

Recorded macros often contain unnecessary Select and Selection statements.

4. Limited Logic

The recorder does not automatically create sophisticated programming structures such as:

  • Complex If statements
  • Loops
  • Error handling
  • Custom functions
  • Advanced object-oriented programming

These are topics you will learn by writing VBA manually.

23. Macro Recording Best Practices

Best Practice 1: Plan Before Recording

Decide what you want the macro to accomplish before pressing Record Macro.

Best Practice 2: Avoid Unnecessary Actions

Every unnecessary action can become part of the recorded procedure.

Best Practice 3: Use Descriptive Names

Prefer: 

FormatSalesReport

instead of: 

Macro1

Best Practice 4: Review the Generated Code

Always open the VBA Editor and study the code created by the recorder.

Best Practice 5: Remove Unnecessary Code

As you learn VBA, simplify recorded procedures and remove unnecessary selections.

Best Practice 6: Test Your Macro

Always test the macro on sample data before using it on important workbooks.

24. Practice Exercises

Exercise 1: Record a Formatting Macro

Create a worksheet with the following headings: 

Name | Department | Salary | Joining Date

Record a macro that:

  • Makes the headings bold.
  • Changes the background color.
  • Changes the font color.
  • Centers the headings.

Exercise 2: Study the Generated Code

Open the VBA Editor and find the macro you recorded.

Identify:

  • Sub
  • End Sub
  • Range
  • Select
  • Selection
  • Font
  • Interior

Exercise 3: Improve the Code

If your recorded code contains: 

Range("A1:C1").Select
Selection.Font.Bold = True

try changing it to: 

Range("A1:C1").Font.Bold = True

Run both versions and verify that they produce the same result.

Exercise 4: Create a Report Formatting Macro

Create a worksheet containing a small sales report.

Record a macro that:

  • Formats the heading row.
  • Adjusts column widths.
  • Formats the salary or sales column.
  • Adds borders to the table.
  • Centers appropriate headings.

Then open the VBA Editor and study the generated code.

25. Summary

In this lesson, you learned how to record and run Excel macros and how the Macro Recorder can help you learn VBA.

  • A macro is a sequence of automated Excel instructions.
  • The Macro Recorder converts many Excel actions into VBA code.
  • The Developer tab contains the Macro Recorder.
  • You can give your macro a descriptive name and description.
  • After recording, you must stop the recorder.
  • Recorded macros can be run from the Macros dialog.
  • You can run VBA procedures using F5 from the VBA Editor.
  • The generated VBA code can be viewed and edited.
  • Recorded code may contain Select and Selection statements.
  • Absolute references refer to specific cell addresses.
  • Relative references work relative to the starting position.
  • Macros can be assigned to worksheet buttons.
  • Macros can also be assigned keyboard shortcuts.
  • The Personal Macro Workbook can store reusable macros.
  • Macro-enabled workbooks normally use the .xlsm format.
  • You should only enable macros from trusted sources.
  • The Macro Recorder is a great learning tool, but manual VBA coding provides much greater flexibility.

Next Lesson: Understanding VBA Syntax and Structure

Now that you know how to record and run macros, the next step is to understand the basic structure and syntax of VBA code.

In the next lesson, you will learn:

  • VBA statements
  • Keywords
  • Objects
  • Properties
  • Methods
  • Arguments
  • Procedures
  • Variables
  • Comments
  • Basic VBA coding conventions

Continue to Understanding VBA Syntax and Structure →

Post a Comment

0 Comments