How to record a macro in Excel?
Macro is one of the most useful and essential features of MS Excel. Macro is basically a developer feature that is used to perform repetitive tasks by recording in it. Thereby, it saves the time of users to complete a task that requires the repetitive operation to be performed multiple times. You can record that task in macro and execute it automatically.
Macro is an especial feature of Excel and a piece of programming. In this chapter, we will brief about macro and how to use it. We will describe each step to record and execute a macro in Excel.
Before this, we will brief something important about macro.
- What is macro?
- Basics of macro
- Need of a macro
- Get the developer tab in Excel ribbon
- How to create a macro?
- Test a recorded macro
- Create a macro with mathematic task
- See the macro info
- Import the data from CSV file using macro
What is macro?
A macro is a developer feature of Excel that is used to automate repetitive tasks to save time. In Microsoft Excel, you can use macro to record and execute a task that needs to be performed repetitively.
Often you find that a macro refers to as VBA code. It is because the code of the macro is written in VBA (Visual Basic for Application) programming. Macro is nothing but a step of code written in VBA.
In Excel, when you record a macro, save the macro, and then run it, Excel goes to the VBA code generated for the respective task and starts executing the same steps. It allows executing the created code for the recorded macro in the background and performing operations whenever needed.
Basics of macro
Here, we have some basic points about macro that need to be known.
- As we told you that the macro is a developer feature, but the developer tab is not displayed in the Excel menu bar. The users need to add via customize report explicitly.
- Macros are disabled in Excel. So, you have to enable the macros to run them.
- To keep enabled the created macro in your Excel workbook, select macro enabled format (*.xlsm) from file type while saving your Excel file.
- Remember that – Macro name should not contain any space.
- You do not need to familiar with VBA to automate the tasks using macro.
Get the developer tab in Excel ribbon
First of all, we will get the developer tab in Excel ribbon that will contain all the required options related to macro and some other tools as well.
Initially, when you open Excel, you see no Developer tab/option available in the Excel menu bar.
Following are the steps to get the developer tab in Excel ribbon:
Step 1: On the Excel home page, right-click on any of the tabs in the Excel ribbon that will show a list containing different options.
Click on the Customize the Ribbon here.
Note: If your Excel already has this developer option in the menu bar, skip the following steps and directly move to the next step for recording the macro.
Step 2: When you click the Customize the Ribbon from the list, it will directly navigate you to an Excel dialogue box that will contain various settings to customize your Excel ribbon.
Step 3: In this Excel dialogue box, mark the Developer option on the right side of the panel and then click on the OK button below.
Step 4: Now, you will see that the Developer option has been added to the Excel menu bar.
Record a macro in Excel ribbon
After getting the developer tab in the Excel menu bar, the next step is to record a macro. You can also directly move to this step if your Excel already has the developer tab/option in the menu bar.
Everything is now at its place that we require to record a macro. So, let’s start with how to record a macro in Excel.
Here, we have some simple steps; follow them carefully:
Step 1: We have a blank Excel sheet. Here, navigate to the Developer tab in the Excel menu bar.
Step 2: In the Code group section, you will find a Record Macro button; click on this Record Macro button. It will open a dialogue box containing all needed set up to record a macro.
Step 3: In this dialogue box, provide all the required details.
1. Enter the name of macro. Remember that the macro name does not contain whitespace in its name. For example, myMacro is correct and my macro is incorrect.
2. You can also provide a shortcut key here to create the macro. Remember that the new shortcut key will override the existing shortcut key available in your workbook. For example, Ctrl + S. So, the saving shortcut key will be overridden.
3. Now, in the next Store Macro field, always select This Workbook (currently opened workbook). It ensures that macro is a part of the workbook. So, the created macro will always be associated with this workbook.
4. Provide a brief description of macro in this description field. This field is optional.
Step 4: Click the OK button after providing all needed information and start recording your actions.
Step 5: In the Developer tab, you will now see one more option, i.e., Stop Recording. It indicates that the macro recording has been started and currently in progress.
Step 6: Select any cell here in Excel, e.g., A1 cell and enter any text here, like your name or anything else.
Note: Developer tab disabled while recording (when you write/perform something with Excel sheet) you can see in the above screenshot.
Step 7: Press the Enter key to enable the developer tab again. The cursor will also automatically move to the next cell A2.
Step 8: Now, click the Stop Recording button inside the developer tab and halt the macro recording task.
Your first macro (named myMacro) has been recorded and saved successfully. This Stop recording button will hide from the developer tab after completing the recording.
Step 9: Save your macro recorded Excel file. But you will see when you save this file; a popup will show you. Here, click on the No button and go back to choose the macro-enabled workbook.
Step 10: Choose the File type – Macro-Enabled Workbook from the formats list and try to save your Excel file once again.
This time will be saved successfully with macro-enabled workbook format. The extension of the macro-enabled workbook is .xlsx.
Run the recorded macro in Excel
Now, we will test that our recorded macro is created successfully and working correctly as well. Using a few simple steps, we can test the macro and run it to complete our task. We will see that how recorded text macro will work in Excel.
See the following steps below:
Step 1: To test the macro, delete the text that you have written in A1 cell while recording your macro and select another cell except the A1 cell to test that the macro will select the A1 cell or not.
Step 2: Go to the Developer tab.
Step 3: In the Code section, click on the Macro button here and initiate the macro to run.
Step 4: A dialogue box will open where select your saved macro which you want to run. E.g., myMacro and click on the Run button in this dialogue box.
Step 5: You will see that when you run the macro, the recorded text in the myMacro is pasted to the selected A4 cell.
Similarly, when you again run this macro, the text recorded in the macro will gain paste to the selected cell.
Step 6: For example, we run the macro 5 times for five different cells. Now, see the result below.
Record a macro with mathematic task
Sometimes, we require to perform the same mathematical task on a column (several cells). In this scenario, you can record a macro to make the work fast.
In the below example, you how to perform a mathematical task on several cells in the Excel using the macro. Follow the instructions below:
Step 1: Open a blank new file and go to the Developer tab in Excel menu bar.
Step 2: In the Code section, click on the Record Macro button.
Step 3: Specify the required details in this Record Macro dialogue box, such as macro name and click on the OK button.
Your created macro has been initiated for recording.
Step 4: Right-click on the selected cell, e.g., A1. Choose Format Cells from the list here.
Step 5: Select the Percentage (a mathematical operation) here and click the OK button.
Step 6: At the end, click on the Stop Recording button residing in the Developer tab.
Your macro has been recorded successfully having with percentage mathematical operation.
Run the recorded macro
Now, its time to run the recorded macro to see that it can change the number format to percentage.
Step 7: Enter some numbers in column and select them as well.
Step 8: Go to the Developer tab and click on the Macro button to go-ahead to run the recorded macro.
Step 9: A dialogue box will appear, where you select your macro from the list and click on the Run button at the left of the panel.
Step 10: See the result after executing your recorded task in macro for the selected cells.
See the macro info
Macro info is basically the coding step behind the macro created by the user. When a user creates a macro, Microsoft Excel automatically writes VBA code for that macro. The users do not need to know how to code; they just need an interface. However, you can see the VBA code written behind the macro you have created.
For this, go to Developer tab > Macro > Choose macro > click Step info. By following this step, you will get the code created for your created macro.
E.g., see the VBA code for the percentage macro (Macro2) for the above example.
