You are reading the article How To Record A Macro In Excel updated in October 2023 on the website Phuhoabeautyspa.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested November 2023 How To Record A Macro In Excel
Even if you’re a complete newbie to the world of Excel VBA, you can easily record a macro and automate some of your work.
In this detailed guide, I will cover all that you need to know to get started with recording and using macros in Excel.
If you’re interested in learning VBA the easy way, check out my Online Excel VBA Training.
If you’re a newbie to VBA, let me first tell you what a macro is – after all, I will keep using this term in the entire tutorial.
A macro is a code written in VBA (Visual Basic for Applications) that allows you to run a chunk of code whenever it is executed.
Often, you will find people (including myself) refer to a VBA code as a macro – whether it’s generated by using a macro recorder or has been written manually.
When you record a macro, Excel closely watches the steps you’re taking and notes it down in a language that it understands – which is VBA.
And since Excel is a really good note taker, it creates a very detailed code (as we will see later in this tutorial).
Now, when you stop the recording, save the macro, and run it, Excel simply goes back to the VBA code it generated and follows the exact same steps.
This means that even if you know nothing about VBA, you can automate some tasks just by letting Excel record your steps once and then reuse these later.
Now let’s dive in and see how to record a macro in Excel.
The first step to record a macro is to get the Developer tab in the ribbon.
If you can already see the developer tab in the ribbon, go to the next section, else follow the below steps:
In the Excel Options dialogue box, you will have the Customize the Ribbon options. On the right, within the Main Tabs pane, check the Developer option.
The above steps would make the Developer tab available in the ribbon area.
Now that we have everything in place, let’s learn how to record a macro in Excel.
Let’s record a very simple macro – one that selects a cell and enters the text ‘Excel’ in it. I am using the text ‘Excel’ while recording this macro, but feel free to enter your name or any other text that you like.
Here are the steps to record this macro:
In the Record Macro dialog box, enter a name for your macro. I am using the name EnterText. There are some naming conditions that you need to follow when naming a macro. For example, you can not use spaces in between. I usually prefer to keep my macro names as a single word, with different parts with a capitalized first alphabet. You can also use underscore to separate two words – such as Enter_Text.
(Optional Step) You can assign a keyboard shortcut if you want. In this case, we will use the shortcut Control + Shift + N. Remember that the shortcut you assign here would override any existing shorcuts in your workbook. For example, if you assign the shortcut Control + S, you will not be able to use this for saving the workbook (instead, everytime you use it, it will execute the macro).
In the ‘Store macro in’ option, make sure ‘This Workbook’ is selected. This step ensures that the macro is a part of the workbook. It will be there when you save it and reopen again, or even if you share it with someone.
(Optional Step) Enter a description. I usually don’t do this, but if you’re extremely organized, you may want to add what the macro is about.
Select cell A2.
Enter the text Excel (or you can use your name).
Hit the Enter key. This will select cell A3.
Congratulations!
You have just recorded your first macro in Excel. You’re no longer a macro virgin.
While the macro doesn’t do anything useful, it’ll serve its purpose in explaining how a macro recorder works in Excel.
Now let’s go ahead and test this macro.
Follow the steps below to test the macro:
Delete the text in cell A2. This is to test if the macro inserts the text in cell A2 or not.
Select any cell – other than A2. This is to test whether the macro selects cell A2 or not.
Now, this may all happen in a split second, but in reality, the macro – just like an obedient elf – followed the exact steps you showed it while recording the macro.
So the macro first selects the cell A2, then enters the text Excel in it, and then selects the cell A3.
Note: You can also run the macro using the keyboard shortcut Control + Shift + N (hold the Control and the Shift keys and then press the N key). This is the same keyboard shortcut that we assigned to the macro when recording it.
Now let’s go to the Excel backend – the VB Editor – and see what recording a macro really does.
Here are the steps to open the VB Editor in Excel:
Or you can use the keyboard shortcut – ALT + F11 (hold the ALT key and press F11), instead of the above two steps. This shortcut also opens the same VB Editor.
Now if you’re seeing the VB Editor for the first time, don’t be overwhelmed.
Let me quickly make you familiar with the VB Editor anatomy.
Menu Bar: This is where you have all the options of VB Editor. Consider this as the ribbon of VBA. It contains commands that you can use while working with the VB Editor.
Project Explorer Window – This is where Excel lists all the workbooks and all the objects in each workbook. For example, if we have a workbook with 3 worksheets, it would show up in the Project Explorer. There are some additional objects here such as modules, user forms, and class modules.
Code Window – This is where the VBA code is recorded or written. There is a code window for each object listed in the Project explorer – such as worksheets, workbooks, modules, etc. We will see later in this tutorial that the recorded macro goes into the code window of a module.
When we recorded the macro – EnterText, the following things happened in the VB Editor:
A new module was inserted.
A macro was recorded with the name that we specified – EnterText
The code was written in the code window of the module.
Here is the code that the macro recorder and given us:
Sub EnterText() ' ' EnterText Macro ' ' Keyboard Shortcut: Ctrl+Shift+N ' Range("A2").Select ActiveCell.FormulaR1C1 = "Excel" Range("A3").Select End SubNow let me quickly cover what each line of code does:
The code starts with Sub followed by the name of the macro and empty parenthesis. Sub is short for Subroutine. Every subroutine (also called Procedure) in VBA starts with Sub and ends with End Sub.
Range(“A2”).Select – This line selects the cell A2.
ActiveCell.FormulaR1C1 = “Excel” – this line enters the text Excel in the active cell. Since we selected A2 as the first step, it becomes our active cell.
Range(“A3”).Select – This selects cell A3. This happens as we hit the Enter key after entering the text, result of which was to select cell A3.
I hope by now you have some basic understanding of how to record a macro in Excel.
Keep in mind that the code written by a macro recorder is by no means an efficient code.
Macro recorder sometimes adds a lot of fluff to the code which is not necessary at times. But this does not mean that it’s not useful. For someone learning VBA, a macro recorder can be a great way to analyze how things work in VBA.
You already know about absolute and relative references in Excel.. right?
If you don’t – read this tutorial on references first.
Read it? Let’s move on.
We will see later in this section on how to record macros in absolute and relative references. But before that, let me quickly summarise the difference between absolute and relative reference in VBA (in case you got lazy and didn’t read up on the link I gave a few lines ago):
If you use an absolute reference option to record a macro, the VBA code would always refer to the same cells that you used. For example, if you select cell A2, enter the text Excel and press Enter, every time – no matter where you are in the worksheet and no matter which cell is selected, your code would first select cell A2, enter the text Excel, and then move to cell A3.
If you use a relative reference option to record a macro, VBA wouldn’t hardcode the cell references. Rather, it would focus on the movement when compared with the active cell. For example, suppose you already have cell A1 selected, and you start recording the macro in the relative reference mode.
Now you select cell A2, enter the text Excel, and hit the enter key. Now, when you run this macro, it will not go back to cell A2, instead, it will move relative to the active cell. For example, if cell K3 is selected, it will move to K4, enter the text Excel, and then finally select cell K5.
Now let me tell you how to record a macro in relative references mode:
Select cell A1.
In the Record Macro dialog box, enter a name for your macro. I am using the name EnterTextRelRef.
In the Store macro in option, make sure ‘This Workbook’ is selected.
Select cell A2.
Enter the text Excel (or you can enter your name).
Hit the Enter key. This will take the cursor to the cell A3.
This would record the macro in the relative reference mode.
Now do this.
Select any cell (other than A1).
What happens? Did the cursor go back to cell A3.
It wouldn’t – because you have recorded the macro in the relative reference mode. So the cursor would move relative to the active cell. For example, if you do this when cell K3 is selected, it will enter the text Excel is cell K4 and end up selecting cell K5.
Here is the code that gets recorded in the backend (VB Editor module code window):
Sub EnterTextRelRef() ' ' EnterTextRelRef Macro ' ' ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "Excel" ActiveCell.Offset(1, 0).Range("A1").Select End SubNote that this entire code doesn’t refer to the cells K3 or K4 anywhere. Instead, it uses the Activecell to refer to the selected cell and Offset to move relative to the active cell.
Don’t worry about Range(“A1”) part that the code has. It is one of those unnecessary codes that the macro recorder adds that serves no purpose and can be removed. The code would work just fine without it.
Macro recorder is great at following you in Excel and recording your exact steps, but it may fail you when you need it to do more.
You can’t execute a code without selecting the object. If you want the macro recorder to go to the next worksheet and highlight all the filled cells in column A, without leaving the current worksheet, then it won’t be able to do this. It’s because if I ask you to do this, even you won’t be able to do that (without leaving the current sheet). And if you can’t do it yourself, how will the macro recorder capture your actions. In such cases, you need to manually go and create/edit the code.
You can’t create a custom function with a macro recorder. With VBA, you can create custom functions that you can use in the worksheet as regular functions. You can create this by writing the code manually.
You can’t create loops with a macro recorder. When you manually enter the code, you can leverage the power of loops in VBA (such as For Next, For Each Next, Do While, Do until). But you can’t do this when you record a macro.
You can’t analyze conditions: You can check for conditions within the code using macro recorder. If you write a VBA code manually, you can use the IF Then Else statements to analyze a condition and run a code if true (or another code if false).
You can’t pass arguments in a macro procedure: When you record a macro, it will never have any arguments. A subroutine can take input arguments that can be used within the macro to perform a task. While recording a macro, this can not be done as the recorded macros are independent and are not connected to any other existing macros.
When you record a macro, or you manually write VBA code in Excel, you need to save the file with a macro-enabled file extension (.xlsm).
Before Excel 2007, there was one single file format that used to suffice – .xls.
But from 2007 onwards, .xlsx was introduced as the standard file extension. The files that are saved as .xlsx cannot hold a macro in it. So if you have a file with .xlsx extension and you record/write a macro and save it, it will warn you to save it in macro-enabled format, and show you a dialog (as shown below):
So if you have a macro in your workbook, you need to save it in the .xlsm format to keep that macro.
So far, we have seen only one way to run a macro in Excel – which is using the Macro dialog box.
But there are a number of ways you can run macros.
Run a Macro from the Ribbon (Developer Tab)
Using a keyboard shortcut (which you have to assign)
Assign the Macro to a Shape
Assign the Macro to a Button
Run a Macro from the VB Editor
I already mentioned that a macro recorder is a useful tool for anybody working with VBA in Excel.
As you use the macro recorder a few times, you will notice that it spits out a lot of unnecessary code. However, it’s still useful and gives you some ideas on where to start. For example, if I ask you to filter a column of cells using VBA, and you have no idea what the syntax is, you can quickly record a macro and check out the code.
A macro recorder is an indispensable tool and even after years of VBA coding experience under my belt, I often resort to the macro recorder for help.
You May Also like the Following Excel Tutorials:
You're reading How To Record A Macro In Excel
Update the detailed information about How To Record A Macro In Excel on the Phuhoabeautyspa.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!