Record a Macro in Excel – Excel Macro Recorder

How to record a macro in Excel? The best way to learn Excel VBA is exactly by recording macros. This is especially useful if you want to automate mundane Excel tasks e.g. formatting selected cells etc. In this case you can record you series of steps/clicks/typed text etc. and Excel will translate that into VBA code creating a new module into your VBA project.

VBA Macro Recorder

Go to your Developer tab and click Record Macro

To start recording go to the Developer tab:

Excel VBA Tutorial: Click the Record Macro button

Now hit the Record Macro button to start recording:

Excel VBA Tutorial: Record Macro Icon

Complete the Record Macro Form and click OK

Now you will see the following Form appear. Provide a Name for your recorded procedure (no spaces). You can also associate a Excel Shortcut with your Recorded Macro. To do that input a letter or number in the Shortcut Key textbox e.g. type “T” which should generate the following shortcut:
CTRL+SHIFT+T
Next hit OK.

Excel VBA Tutorial: Record Macro Form

Do something in Excel that you would like to record

Now almost every mouseclick and keyboard press will be recorded and translated to an Excel VBA macro. For this tutorial I would recommend testing a typical useful scenario:

  • Click on a single cell
  • Change the background color
  • Change the font to Bold / Italic etc.

Stop recording the Macro

Hit the Stop Recording button to stop recording the macro:

Excel VBA Tutorial: Stop Recording VBA Macro

Execute the recorded macro

Assuming the recorded macro can now be reused, let’s try executing it. You can execute the macro in 3 ways:

  1. Use the shortcut CTRL+SHIFT+T
  2. Go to the Developer tab on the Excel ribbon and click Macros: Excel VBA Tutorial: Click on Macros

    Next select your Macro and hit Run.

    Excel VBA Tutorial: Execute Excel Macro

  3. Go to your VBA project, click on the Macro procedure and hit F5

View the generated code recorded by the Record Macro button

Let’s now open up our VBA Project and take a look into the recorded macro. Open your VBA Project by leveraging the Visual Basic button on the Developer tab.

Excel VBA Tutorial: Click on Macros

Now look for a new module in your VBA Project and click on that item:

Excel VBA Tutorial: Click on the newly created module

The code generated by the Record Macro button should look similarly as below:

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+T
'
    Selection.Font.Bold = True
    Selection.Font.Italic = True
    Selection.Font.Underline = xlUnderlineStyleSingle
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub