Record Macro in Excel (Table of Contents)
Record Macro in Excel
Macro, by definition, is a set of instructions, which are used to perform a particular task.
Macro is an automatic repetitive task sequence that is used to replace a repetitive task of keystrokes and mouse actions. Basically, what we repeatedly do in an excel project, we can use a macro to do that for us. For example, if we have few formulas which are going to be repeated, whether there is any data change or not, we can use a macro to do that for us instead of us doing the same task again and again over and over.
In technical terms, Macro enables a user to trim downtime normally used in repetitive tasks. Macros can contain functions, formulas, logics etc.
Macro is created by recording a sequence of keyboard and mouse actions using the built-in macro recorder function in Excel. When a macro is recorded, it can be used again by assigning a keyboard shortcut or from the View macro function.
There are two ways to use Excel Macros:
- Macro Recorder: We can record our steps or certain tasks by excel record macro function, call it whenever required by any assigned hotkey, or call it from View Macros.
As in Example 1, we created a macro to fetch the department list of employees.
- VBA Editor: In the VBA editor, we write a set of codes in VB language and perform certain tasks in the worksheet.
As in Example 2, we prompted the user about the new code and called in the other macro to fetch the department list.
How to Record Macro in Excel?
Record Macro in Excel is very simple and easy to create. Let’s understand the working of Record Macro in Excel by some examples.
Record Macro in Excel – Example #1
In a workbook, we have two sets of data in two different worksheets. From sheet 2, we want some data in sheet 1. Normally we use V-lookups to find the data. But we can use the macro recorder to do the same for us.
This is data in sheet 1 and data in sheet 2 shown as below,
Suppose we want the Department Name in Sheet 1 for the respective employees. Now we can use the simple V-lookup function for this.
The Output is:
But if the sheet is in the same format and the data gets changed, then we have a repeated task repeatedly. Macro in Excel comes in handy here. We can record the same steps using the excel macro tool and make excel do the same tasks for us. Let us learn to use the Excel Macro Recorder tool.
- Firstly, Go to Views Tab, and in the rightmost corner, there is an option of macros.
- Click on Record Macro.
- A dialog box pops up.
- In the dialog, Box there is a box to name the macro and assign a shortcut key. We can write a small description of the macro too.
(Tip: Do not use space in Excel macro Name)
- Click ok, and we can see that Excel has started recording our sequence.
- Now perform the tasks required to fetch the department name from sheet 2, i.e. V-lookup from sheet 1.
VLOOKUP Formula is applied in Sheet 1.
- It gives the desired output in Sheet 1 as in Sheet 2.
- Select the remaining cells and Press Ctrl +D to get rest results.
- Now our task is complete; we click on stop Recording.
- Under the View tab in the macros section, we can check that our macro has been recorded from the view macros option.
- If we click on the view macros option, it shows the macro name which we added. Here it is, Dept_List.
- Delete the data under Dept Column to check whether our macro is working or not.
- Press CTRL + D and see the result.
We have successfully recorded and tested our first Macro.
Record Macro in Excel – Example #2
Prompt a message to run the above V-lookUp Macro.
Example 1 was a tool by excel which is used to record a macro. Now we will use VBA to create a macro. For this, we need to activate the Developer tab.
Go to Files and then to options.
There is a checkbox in the customize ribbon toolbar on the right-hand side with the name “Developer”. Mark the checkbox as a check and click ok.
Before getting started with VBA, we need to get some basics.
A VBA Code contains variables, declarations, logic and functions, which sums up as a whole code.
First, we need to follow some steps to create a macro-code.
- Select the workbook in which we want to use the macro. In the current example, our target workbook is sheet 1.
- In the developer, Tab clicks on Visual Basic, Which opens a VB Editor for us.
- Click on the Object where we want to write our code, which is sheet 1, and insert than to Insert Module.
- Now we can start writing code in the Module. We open our function by SUB() function.
Tip: We use sub () procedure as its main purpose to carry out a particular task or action.
- Now write the following code in the Sheet.
Message box function gives a prompt to a user as VBA Code, and then macro, which was recorded as Dept_List, will be called.
- When we click on Run in the VBA Editor, it gives us the following prompt.
- We Select VBA Project Module4. New Code as the Macro to Run and excel first gives us a prompt and then the Department List.
Then the Department List will be updated,
Tip: Excel with Macro should be saved as *.xlsm as Excel Macro-Enabled workbook to save the macros in it.
Explanation of Excel Macro:
Macros are used to reduce repetitive tasks, which are done on a daily basis. A set of tasks are recorded and can be accessed over and over. It can be accessed by a menu list or from some hotkeys defined by the user.
If we go by definition, Macro is a set of rules or patterns, which specifies how a certain input sequence should be mapped to a certain output sequence according to a defined procedure.
Things to Remember About Record a Macro in Excel
- Macro Name should not contain space.
- A macro should be saved as “Excel Macro-Enabled Workbook” and extension as *.xlsm.
- Before writing any VBA code, we need to activate the developer Tab by customizing the ribbon button in the options.
- Excel Macro Recorder has its limitations, but we can virtually automate anything from Excel VBA.
Recommended Articles
This has been a guide to a Record Macro in Excel. Here we discuss how to create Record Macro in Excel examples and downloadable excel templates. You may also look at these useful functions in excel –
