What is Macro in Excel? A macro is a way to automate repetitive or routine tasks. We can use an Excel Macro to record and playback our Excel steps as many times as we want. We can perform a Macro with just one keystroke or command. It is a series of instructions that make up a Macro. Why we use Macro? It automates repetitive tasks to save time and reduce the risk of human error.
In this article, we will learn how to use Macro in Excel.
Table of Content
How many ways we can create a Macro in Excel?
There are two ways to create Macros: VBA Programming Language and Macro Recorder
VBA Programming Language
A programmer who wants to create a Macro using VBA programming needs to have programming knowledge.
Macro Recorder
It is not necessary to know the programming language to use the Macro Recorder tool. It is a built-in method in Excel.
Where can we find Macro?
There are two places, where we can find Macro: View Tab and Developer Tab
View Tab
Go to View Tab -> Macros Group -> Macros -> then we can select our option.
There are a few options or frequently used options in the View Tab.
In Developer Tab
The Macro tool can be found on the Developer Tab, which is hidden by default, so the first step is to enable it.
Go to File Menu -> Options -> Customized Ribbon -> Main Tabs -> Tick mark or check Developer -> Ok
View Record Macro Dialog Box
Go to the Developer Tab -> Code Group -> Click Record Macro
We will see the Record Macro Dialog box.
OR
Go to View Tab -> Macros Group -> Click Record Macro
In any of these ways, we can go to the Record Macro Dialog box.
Description of Record Macro Dialog Box
Go to the Developer Tab -> Code Group -> Click on Record Macro
A Record Macro dialog box will appear.
Macro Name
- We will need to give our Macro a name.
- There must be a letter at the beginning of the name.
- There must be no spaces in it.
- If you want to separate words, use an underscore ().
Shortcut Key
- We can create a shortcut key for this. Whenever we want to use shortcut keys, remember the syntax. This is an optional step.
Stored Macro In
- There are several ways to save Macros when creating them:
This Workbook
The Macro will only be available in the spreadsheet in which it is created.
Personal Macro Workbook
All spreadsheets will have access to the Macro.
New Workbook
A Macro will only work in spreadsheets that are newer than the previous one.
Description
- If we would like to provide some descriptions, please do so.
Click Ok. Macro will now record all of your actions.
Macro Example
Let’s say we have to prepare the same spreadsheet each month with the same formatting and formulas. In that case, we use Macro, because here we have repetitive work. Here we will create a Macro using Macro Recorder Tool.
Step 1: Go to the Developer Tab -> Click on Record Macro
It will pop up a dialog box to record Macros.
We enter a name for the Macro, assign it a shortcut key (optional), store it, and give it a description (optional).
As soon as we click OK, the recording of our Macro will begin. To check whether that recording is the start or not. On the lower left corner of the screen, we can see that the stop button is enabled.
Step 2: In order to create our Macro, we need to follow the layout.
When you have created the above layout, click on the Stop Recording option under Developer Tab -> Stop Recording.
Step 3: Go to the sheet where we want that layout to appear. Make sure you select the exact range when you recorded the Macro. We use Macros only on the cells we use for recording, and they do not work on the rest.
Step 4: Go to Developer Tab -> Macros -> Select the macro that we created for that layout -> Click on the Run option.
OR
Go to View Tab -> Macros -> View Macros -> Choose the macro that we created for that layout -> Click on Run option.
Here is the layout.
The following field contains random items.
View Recorded Macro
To view recorded Macro, then go to View Tab -> Macros -> View Macros. A Macro dialog box will appear. We can view all the Macros We have recorded.
Here is a list of all recorded Macros. We can select the Macro which we want to run.
How to Run a Macro?
To run a Macro, go to the Developer Tab -> Code Group -> Macros -> Select your Macro -> Click on Run option.
We can run a Macro by pressing a key combination, or we can select the Macro from the Macro List and then select Run.
Add a Button to Run Your Macro
In Excel, we can create a button that will run our Macro. Follow these steps to do so:
Step 1: Go to Insert Tab -> Illustration Group -> Shapes -> Select any Shape. We will use a Rectangle: Rounded Coroners.
OR
We can also insert a button by going to the Developer Tab -> Insert -> Form Control -> Button (Control).
Step 2: To edit the text, right click the Shape and choose Edit Text. We can also give some formatting to the shape.
Step 3: Right click on the Shape, then select Assign Macro. On the Assign Macro dialog box, click OK to assign the Macro.
Step 4: Once you’ve clicked on Run, leave your cursor on the first recorded cell. That is the Date cell.
We can now work with our macro layout.
Make sure we selected the right cell range that we used while creating Macro. In our example, we use the same range of cells as we use while creating it. The Macro will run whenever we select (by clicking) this Shape.
Key Point to Remember while Recording Macro
- The Macros will only work on the cells we use during recording. They do not work on the remaining cells.
- Once we have recorded the Macro, make sure we check it.
- Don’t use the undo button when recording a Macro.
Conclusion
Our Excel work are simplified by Macros because they automate most of our routine tasks. Throughout this article, we learned how to use Macros in Excel. With one click or keystroke, we can perform several parts of a task at once.