Using Macro Recorder in Excel (With Easy Steps)

Using Macro Recorder in Excel: Step-by-Step Procedures

STEP 1 – Prepare Macro Recorder

Activate an empty cell.
Choose Developer >> Code >> Record Macro.

As a result, the Record Macro dialog box will pop up.

Enter a new name for the macro to replace Excel’s provided default Macro1. We named it My_Macro.
You can assign a shortcut key CTRL+SHIFT+N for your macro by entering an uppercase N in the Shortcut Key field.
Click OK.

Clicking OK will close the Record Macro dialog box and begin recording your actions.

Note: Select the cell that you want to format before you start recording your macro. This step is important because if you select a cell after you have turned on the macro recorder, the cell will be recorded into the macro. In such a case, when you run the macro, it will always format that particular cell (the cell that is recorded) and it will not be a general-purpose macro.

Check out the video showing the formatting and calculation process.

Apply Border to the dataset >> increase the Font size of the Column Headers >> apply a Fill Color >> make them Bold >> make Middle and Center Alignment >> repeat this formatting for Total Price cell >> use the SUM function to calculate the total price.
From the Developer tab >> press Stop Recording.

STEP 2 – Examining Macro

Macro My_Macro is recorded in a new module named Module1. You have to activate the VB Editor to view the code in this module. We can activate the VB Editor in either of the two ways:

Choose from the Developer tab >> under the Code group >> select Visual Basic.
Or you can press ALT+F11.

In the VB Editor, the Project window will display a list of all open workbooks, worksheets, and add-ins. This list is displayed as a tree diagram, which you can expand or collapse.

The code of the My_Macro that you have recorded is stored in Module1 in the current workbook.

Double-click on Module1 from Modules and the code of the macro appears in the Code window.

The following figure shows the recorded macro in the Code window.

The Recorded Code

Sub My_Macro()
'
' My_Macro Macro
'
' Keyboard Shortcut: Ctrl+Shift+N
'  Range("B4:D14").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Range("B4:D4").Select
    Selection.Font.Size = 12
    Selection.Font.Bold = True
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
    Range("C16:D16").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Range("C16").Select
    Selection.Font.Size = 12
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Font.Bold = True
    Range("D16").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-11]C:R[-2]C)"
    Range("D17").Select
End Sub

Code Breakdown

The macro recorded is a sub procedure and we have named it as My_Macro. The statements in the macro code give the command to Excel to carry out the same procedure when this macro is executed.
Notice that Excel inserted some comments at the top of the procedure. These comments are the information that you have provided in the Record Macro dialog box.
The comment lines (which begin with an Apostrophe) aren’t really necessary and deleting them will not affect the performance of the macro.
If you ignore the comments, you’ll see a long VBA Which is long but simple. In these statements, many basic properties have been used like Selection, Borders, LineStyle, ColorIndex, Alignment and so on.

ActiveCell.FormulaR1C1 = “=SUM(R[-11]C:R[-2]C)”

This single statement calculates the total price.
The FormulaR1C1 part is a property of the Range object.

STEP 3 – Testing Macro

Before we recorded the My_Macro, we have assigned the macro to the CTRL+SHIFT+N shortcut keys combination. We will test My_Macro.

Go to the target worksheet >> activate a cell of that worksheet >> press CTRL+SHIFT+N >> check the output.

If you open a new worksheet, the code will run. Basically, you will see formatted blank cells. And you will get 0 as the total price.

You can run this macro in any workbook that contains the VBA module. So, you can copy this VBA code for a new one and run this macro.

STEP 4 – Editing Macro

After recording a macro, you can also make changes to it. For example, you want to find out the highest price and change the Fill Color of column headers.

You can record the macro again or you can edit the code as it has more advantages. To edit the code,

Press ALT+F11 to activate the VB Editor
Activate Module1 from the Project window and change the ThemeColor index for all headers. Enter the MAX function instead of SUM.

The edited macro will be as shown in the following image:

For better understanding, we have inserted a new Module, copied the code of Module1 then pasted it to Module2 and have made the changes in Module2.

To test this new macro, from the Developer tab >> select Macros >> so you will get the Macro dialog box >> choose Module2.My_Macro >> press Run.

Read More: How to Edit Macros in Excel

Running Recorded Macro with Control Button

There is another method to run a macro with the use of the Control Button. We will insert a Button (Form Control) so that, with a single press of that button, you will get the output.

For inserting the Button, from Developer tab >> go to Insert menu >> under Form Controls >> choose Button (Form Control).
Drag the Mouse Cursor to a suitable location.

After releasing the cursor, you will get a dialog box named Assign Macro.
Choose the target macro >> press OK.

As we have assigned the macro of Module2, after pressing the button you will get the formatted data with the highest price.

Here, we have changed the display name of the button. To do so, right-click on the button >> from the Context Menu Bar >> select Edit Text >> enter your preferred name for the button.

Read More: Types of VBA Macros in Excel

How to Use Relative References While Using Macro Recorder

While using macro, the relative reference denotes that it will work by considering the active cell as the 1st cell of a range. You can use this relative reference in the macro recorder.

From the Developer tab >> press Use Relative References >> click on Record Macro.

As a result, you will see the Record Macro dialog box.

Enter a new name >> assign a shortcut key (CTRL+SHIFT+F) for your macro >> click OK.

Clicking OK will close the Record Macro dialog box and begin recording your actions in Excel.

Macro Recording Brief

We have selected B5:D5 cells >> changed the Fill Color >> then pressed the Down-Arrow.

Now to test the macro, select any cell >> press CTRL+SHIFT+F to get output. Here, you will see that not only that particular cell but also two adjacent cells are colored, and then the macro activates the following cell vertically.

We have colored some more cells by pressing CTRL+SHIFT+F. Basically, we want to show the highest price for each segment. The cell selection is done manually and macro colored the cells.

Limitations of Macro Recorder

Macro recorders can’t record complex operations.
It can’t continue any loop in VBA. It just repeats the same work for every sheet.
Furthermore, the macro recorder can’t cooperate with the If-End If statement.
A macro recorder is very bad for using UserForm.
It can’t do any work for VBA InputBox or MsgBox.
It can’t handle the error.

Macro recorders use long code for simple work as well as it makes the code complex. So, you may use a macro recorder only when you need to do very simple work.

Frequently Asked Questions

1. How do I record a macro in Excel for all workbooks?

From Developer tab >> go to Code group >> select Record Macro >> in the Record Macro dialog box >> enter a new name for the macro in the Macro name field >> assign a shortcut key for your macro in the Shortcut Key field >> choose Personal Macro Workbook in the Store Macro in: field >> click OK.

2. How long can you record a macro in Excel?

Macro recorder can record the operation till it crosses 9999 lines of code in VBA.

3. Can macro record formulas?

A macro recorder can record an Excel formula that you enter manually.

4. What is the extension of a macro-enabled file?

The extension of a macro-enabled file is .xlsm.

Download Practice Workbook

Use of Macro Recorder.xlsm

Get FREE Advanced Excel Exercises with Solutions!