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:
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:
- 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:
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:
- Use the shortcut CTRL+SHIFT+T
- Go to the Developer tab on the Excel ribbon and click Macros:
Next select your Macro and hit Run.
- 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