Record a Macro in Excel

Record a macro means to record the actions that you perform in Excel.
You need to use the macro recorder to record a macro.
The macro recorder generates the code at the back end while you are performing the activity.
In Excel, you cannot pause while recording a macro.
Macro recorder doesn’t create perfect code, so make sure to clean up your code after recording.

This guide will help you to learn all the aspects of recording a macro. You will also learn to clean the code after recording it.

So let’s get started.

What is the macro recorder?

Excel Macro Recorder is a tool that can record actions (not all) that you perform in Excel and generates code for that. Perform a specific task once, and it generates a macro for it, and for next time instead of doing that task manually, you need to run that code.

Think about a video camera, it just works like it and generates code for the activity that you perform.

You need to understand that there can be some occasions where a macro recorder won’t be able to provide a code. However, still, it’s a great way to know the code if you don’t know.

You can find the macro recorder button on the developer tab.

Using VBA for Programming in Excel is one of the Advanced Excel Skills and to get started with VBA you need to master macro recording.

Planning before you record a macro

If you want to record a video, you need to do some planning before it, like creating a script, having proper lights, and stuff like that.

In the same way, for recording a macro, it’s essential to do some planning.

The thing is, you don’t want to get a code for the activity which you don’t want to execute while using the recorded macro again.

The best way for this is to note down the steps you need to perform and then stick to it while recording.

Here we are going to record a macro that can make apply the following formatting to the selected cell:

Font Color: Red
Font Size: 12
Font Style: Verdena
Font Text: Bold

As I said, planning is essential, and before recording this macro, there’s one thing we need to take care of, and that is selecting the target cell before actually starting recording.

The reason for this is if we select a cell while recording the macro, it will record that selection as well.

So, when you rerun this macro, it will select that particular cell to apply all the formatting on it. But what we want is to apply the formatting on the cell, which is active (which is selected).

Steps to record a macro using the macro recorder

To RECORD a MACRO in Excel, you can use the following steps:

First of all, select cell A4 (it’s the cell we are going to apply the formatting and recording of our macro).
After that, go to the “Developer Tab”, and before you hit the “RECORD” button, Turn ON the relative reference (we are going to learn about it in a few minutes).
Hit the record button.
And the moment you click it you’ll get a dialog box to fill some of the details about the macro you are going to record (yes, we need to fill it).
- Name of the macro: “HighlightCell”.
- Shortcut Key: Press Shift + H to define the keyboard shortcut Control + Shift + H.
- Store Macro in: Select Personal Macro Workbook.
- Description: This macro applies a red color, font size 12, font style Verdana, and makes the font bold.
Finally, click OK.
At this point, Excel’s stars to record everything (you can see the icon on the status bar which says “A macro is currently recording, click to stop recording”).
So now, apply all the four formattings which we have decided.
- Font Color: Red
- Font Size: 12
- Font Style: Verdena
- Font Text: Bold
Once you do this, go back to the developer tab and click “Stop Recording” or you can also stop recording from the status bar where it says “click here to stop recording”.

Congratulations! You have made it and at this point, you have a recorded macro in your workbook but now the next thing is to understand it.

How macro recorder generates a code

The macro recorder generates a code for you at the back end when you are performing the activities.

To understand this, you need to see the code first. So for this, open the Visual Basic Editor, go to the Developer tab ➜ Visual Basic editor.

And then open module1 where you have your code stored.

In the code window, you can see the entire code which is generated by the macro recorder along with all the details which you have entered.

As you can see, the name of the macro, shortcut key, and description is there before the code.

Now let’s understand the code (as I said earlier, the macro recorder doesn’t create a perfect code, so you need to clean it up every time, and we will learn this in the next part).

You have performed the four activities in, and the macro recorder has generated the code into four parts.

In the first part, the code says the font color of the selected cell should be red, and it has used a numeric value for it.
The second part of the code, says the font size of the selected cells should be “12”.
And in the third part, it says the font style should be “Verdana”.
At last, in the fourth part, it says the font in the selected cell should be bold.

Cleaning up recorded macro

At this point, you have recorded your first macro and you know how that code gets generated. But you need to clean up the code that you have in the module.

Sub HighlightCell()
'
'HighlightCell Macro
'This macro applies a red   color, font size 12,
'font style Verdana, and makes the font bold
'
'Keyboard Shortcut:   Ctrl+Shift+H
'
With Selection.Font
   .Color = -16776961
   .TintAndShade = 0
End With
  
With Selection.Font
   .Name = "Calibri"
   .Size = 12
   .Strikethrough = False
   .Superscript = False
   .Subscript = False
   .OutlineFont = False
   .Shadow = False
   .Underline = xlUnderlineStyleNo
   .Color = -16776961
   .TintAndShade = 0
   .ThemeFont = xlThemeFontMinor
End With
   
With Selection.Font
   .Name = "Verdana"
   .Size = 12
   .Strikethrough = False
   .Superscript = False
   .Subscript = False
   .OutlineFont = False
   .Shadow = False
   .Underline = xlUnderlineStyleNone
   .Color = -16776961
   .TintAndShade = 0
   .ThemeFont = xlThemeFontNone
End With  
Selection.Font.Bold = True
   
End Sub

If you look closely, it’s way longer than the activities it can perform. There are a lot of properties included in this code that are not required.

Not just with this code, whenever you record a macro, you need to clean it up every time.

But before you start cleaning up this code you need to remember the four activities which you have performed and for which you need to have code in your module:

Font Color: Red
Font Size: 12
Font Style: Verdena
Font Text: Bold

Let’s identify the lines of code that execute these four activities.

Now you identified the lines of code that do the actual activities you have performed, and also we have this code in four parts.

It’s time to clean the code and delete all the unwanted lines. Follow the below steps.

In the FIRST part, you need the line code which applies the font color RED, so the “TintAndShade” property doesn’t require it.

line-code-which-applies-the-font-color-red

Now in the SECOND part, what we need is the size of the font, not all other properties, so delete them.

After that, in the THIRD part, we need to have the font name property, so delete all the other properties.

In the FOURTH part, you only have one line of code, which is to make the font bold.

At this point, the code looks something like this, which is far less than the code generated by the macro recorder.

Sub HighlightCell()
'
'HighlightCell Macro
'This macro applies a red   color, font size 12,
'font style Verdana, and makes the font bold
'
'Keyboard Shortcut:   Ctrl+Shift+H
'
With Selection.Font
 .Color = -16776961
End With
  
With Selection.Font
 .Size = 12
End With
  
With Selection.Font
 .Name = "Verdana"
End With
   
Selection.Font.Bold = True
   
End Sub

But there’s still more you can do.

If you look closely at the code, it has used the “With Selection.Font” four times but as you are referring to the same object for all the four properties so you can use it once.

So the final code will be:

Sub HighlightCell()
'
'HighlightCell Macro
'This macro applies a red   color, font size 12,
'font style Verdana, and makes the font bold
'
'Keyboard Shortcut:   Ctrl+Shift+H
'
With Selection.Font
 .Color = -16776961
 .Name = "Verdana"
 .Size = 12
 .Bold = True
End With
End Sub

When you run this code, it will perform the same activities which you have recorded.

What you have just done?

In the new code, you only have lines of code that perform the activities you need.

The point to understand here is that with each object, there are always some properties, and when the macro recorder records it adds all those properties with that object even if you don’t use them.

That’s why you need to delete all those properties from the code.

And the other thing is, even if you are using only one object in your code macro recorder refers to it again and again when you perform different activities and that’s why have used “With Selection. Font” only once and add all the properties under it.

In any situation, to clean a code that is generated by the macro recorder, you need to identify the actual lines of code that perform the activities you need and delete the rest.

While cleaning a recorded macro code, the best way is to debug the code step by step.

Read this

Open the VBA editor and Excel window side by side and then press F8 to run the code step by step.
As you have the Excel window opened on the side so you can see which line of code performs the activity.
And once you identify the code which you require and can delete the code, which isn’t needed.

Naming a macro

Whenever you record a macro it’s required to give a name to that macro, but there are a few rules with which a name should comply:

You can have a macro’s name up to 80 characters long.
You can’t use spaces, punctuation, or special characters.
It must start with a letter.

Apart from these rules, there are a few words that are prohibited by VBA to use as a name.

On the other hand, it’s essential to use a meaningful name instead of having default names like Macro1 or Test1.

As you can’t use spaces in the name of a macro but you can use an underscore, or you can make the first letter of each word capital to increase readability.

Storing the recorded macro

Before recording a macro you need to specify where you want to save it and you have three options for this:

New Workbook: You can select this option if you want to record your macro in a new workbook.
This Workbook: By selecting this option, you can record the macro in the current workbook.
Personal Macro Workbook: With this option, you can access macros from all the workbooks.

Relative reference

While recording the above macro, I told you to select “Use Relative References” before you hit the “Start Recording” button.

In Excel, when you record a macro, Excel uses the “Absolute Reference” by default. However, you can change it before recording a macro.

The point is, that using relative references is crucial if you want a macro to perform the recorded activity anywhere in the worksheet.

To make you understand the difference between both modes of reference, I want you to record the activity in two different ways.

First of all, select cell A1 and start recording a macro (without turning ON “Use Relative Reference”), and select cell E5.
You’ll get the below code by the macro recorder:

Sub AbsoluteReferenceMacro()
Range("E5").Select
End Sub

Now again, select cell A1, turn ON the “Use Relative Reference”, and record the same activity (select cell E5).
But at this time the code you got is different:

Sub RelativeReferenceMacro()
ActiveCell.Offset(4, 4).Range(“A1”).Select
End Sub

You have recorded both of these codes doing the same activity but using different reference modes. Right?

The first macro code is pretty simple to understand; it says to select cell E5.

And if you run this macro, it performs the same thing, i.e. selecting the cell E5, no matter which cell you have selected at that point.

On the other hand, the second macro code says something else, and if you select another cell before running this code, then it will select a different cell.

Why?

When you recorded this macro, the active cell was A1, and then you selected E5, which is four cells down and four cells right from cell A1.

Now, as you have recorded this macro with relative reference, this will always select the cell, which is four cells down and four cells right from the active cell.

Let’s say if the active cell is D4, then it will select the H8 cell, which is four cells down and four cells right.

You can change the reference at any time, even in the middle of the recording.

Pausing a macro

Unfortunately, Excel doesn’t allow you to pause a macro while recording it. It is possible in Microsoft Word, but not in Excel.

If you are recording a macro where you need to do many activities, it’s better to record it in parts so that you have less chance of mistakes.

Limitations of the macro recorder

Yes, a few limitations come with the macro recorder. There are a lot of things that you can perform with a VBA code, but the macro recorder cannot record all the activities.

Can’t create a custom function: VBA allows you to create a custom function that you can use in the worksheet, but it is not possible while recording a macro.
Can’t use in-built functions: VBA has its functions (VBA Functions), but you can’t use them with the Macro recorder.
Can’t test conditions: While recording a macro, you cannot use the VBA IF Then Else Statement to check conditions.
Can’t perform a loop: In VBA, you can use loop statements while writing code, but it is not possible with the macro recorder.
Can’t perform an activity with the object: While recording a macro, you cannot perform an action without actually selecting an object. For example, if you want to make a text bold from a particular cell, then you need to select that cell first.

Is it worth using the macro recorder?

I can bet that you have this question in your mind, and that’s valid. Let me share a personal story with you to answer this question.

A few years back, I was working on a VBA code to insert a pivot table, and at that time I was not aware of all the objects that I need to use to write this code. So, what I did, I recorded a macro while creating a pivot table manually.

Then I reviewed that code to understand the entire process involved in inserting a pivot table. Yes, that code was not perfect but gave me an idea to write.

My point is, that there could be situations where you need to write the code, and the macro recorder can help you at that time. The macro recorder also helped me to write codes for this inventory template.

Wrapping up

You need to focus on the following points for successfully recording a macro:

Set the workbook in which you are going to record the macro and close all the other workbooks.
Make sure to select the right object for recording the macro.
Make sure to decide to use absolute or relative references before recording the Macro.
Plan the actions that you want to perform.

What is VBA