Analyst Cave

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:

Now hit the Record Macro button to start recording:

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.

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:

Stop recording the Macro

Hit the Stop Recording button to stop recording the 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:

    Next select your Macro and hit Run.

  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.

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

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
Exit mobile version