Skip to content
Related Articles

Related Articles

Macros in Excel

Improve Article
Save Article
  • Last Updated : 06 Jun, 2021
Improve Article
Save Article

Macros allow automating repetitive tasks in Microsoft Excel. Some tasks need to be performed multiple times and repetition of all the actions would consume time. In these scenarios, Macros helps in automating these tasks. 

A macro is a set of actions that we can run as many times as required to perform a particular task. When a macro is created, the whole mouse clicks, and the keystrokes get recorded. 

For example: suppose for every month, a report is to be created for all the teachers of a school. As it is a repetitive task, macros can come into action here. 

How to Record a Macro?

The following steps can be performed to record a macro in Excel:

  • Click on the Developer Tab in the ribbon.

  • In the Code Group, the tool Record Macros is located. Click on it.

  • As macros are selected, a dialog box is opened which contains the following options to fill.

In the dialog box, enter the name of the macro in Macro name. However, space cannot be used in the Macro name to separate the words, underscore(_) can be used for this purpose. Example: Macro_name.

There are some other options also in the dialog box, i.e, where to store the macro in, the shortcut key which can be used to activate the macro, and the description for the macro created. They can be filled accordingly. 

  • After filling the dialog box, click on OK.
  • As soon as OK is clicked, Excel Macro is recording the set of operations the user is practicing.
  • Once, the work is done, in the Developer Tab, an option “Stop Recording” can be seen. Clicking on which the recording gets stopped and the created macro is saved.

How to Run the Macro?

There are different methods by which a macro can run. Some of the methods are discussed here in this article. 

1. Run the Macro by Clicking on a Shape

It is one of the easiest ways to run a macro is to create any shape in the worksheet and use it for running the macro.

The steps that need to be followed are:

  • Click on the Insert Tab on the ribbon.

  • Go to the Illustrations group and click on the Shapes icon. In the sheet, choose any shape we like and want it to be assigned as the macro.

  • Click where you want to add the shape in the sheet and the shape will automatically get inserted.

  • The shape can be resized or re-formatted accordingly to the way you want. Text can also be added in the shape.

  • Then Right-Click on the shape and a dialog box would be opened. Then click on Assign Macro.

  • After right-clicking on the shape, another dialog box of Macro would be opened.

  • In the dialog box, select the macro from the list you want to assign to your shape and click the OK button.

The work is done! Now the shape would work as a button and whenever you click on it, it will run the assigned macro.

2. Run Macro By Clicking a Button

While the shape is something you can format and play with it, a button has a standard format. A macro can also be assigned to a button for easy use and then can run the macro by simply clicking that button.

The steps that need to be followed are:

  • Go to the Developer Tab on the ribbon.

  • In the Controls group, click on the Button tool.

  • Now, click anywhere on the Excel sheet. As soon as you do this, the Assign Macro dialog box would be opened.

  • Select the macro you want to assign to that button and fill in the other options. Then click on OK.
  • After that, a rectangular button can be seen on the sheet and ready to be used.

The button which appears on the sheet has default settings, and you can’t format it like the color, shape of the button.

However, the text which appears on the button can be changed. Just a right-click on the button and a dialog box would open.

Click on Edit Alt Text and change the text. 

3. Run a Macro from the Ribbon (Developer Tab)

If you have multiple macros in the workbook defining different tasks, you can see a list of all the macros in the Macros dialogue box and it makes it easy to run multiple macros from a single list.

The steps that need to be followed are:

  • Go to the Developer Tab on the ribbon.

  • Then in Code Group, click on the Macros Tool.

  • A dialog box would be opened containing the list of all the macros created in that particular workbook.

  • Select that Macro that you want to run.
  • Click Run.

4. Run a Macro from the VB Editor 

If the user and creator of the macros are the same, then instead of spending time creating buttons and shapes, we can directly run the macro from the VB Editor. 

Here are the steps:

  • Go to the Developer Tab on the ribbon.

  • In the Code Group, click on Visual Basic and a new window would be opened.

  • Then click on the Run Macro sign, it will open a dialog box listing all the macros of that workbook.

  • Select the macro you want to run from the list and click Run.

As soon as Run is clicked, the macro would be executed on the excel-sheet. 

If you can only see the VB Editor window and not the sheet, then you may not see the changes happening in the worksheet. To see the changes, minimize/close the VB Editor window. 

Several other features of Macros can be explored. 

My Personal Notes arrow_drop_up
Related Articles

Start Your Coding Journey Now!