Open In App

Macros In Excel With Example

Improve
Improve
Like Article
Like
Save
Share
Report

In Microsoft Excel, where spreadsheets are the playground, we often encounter the repetitious demands of certain tasks. These are the tasks that seem to crop up again and again, capturing our attention with relentless persistence. However, in this age of technological progress, a silent hero steps forward to liberate us from this ceaseless cycle – the unassuming yet powerful “Macro.” Think of macros as Excel’s covert operatives for automation, working quietly in the background to spare us the laborious duty of manually carrying out repetitive tasks. They are the unsung champions of efficiency, gifting us the remarkable ability to accomplish in mere moments what would otherwise devour hours of our valuable time. But what exactly is a macro? At its essence, a macro is a sequence of actions carefully recorded by Excel, patiently waiting for our directive to execute a specific task. Picture this: every mouse click and keystroke, discreetly tucked away within the confines of a macro, poised to spring into action at our command, ready to perform its enchantment.

What is Macro in Excel

Macros can be considered as a set of programming instructions that can be recorded, named, saved, and executed in VBA as and when required macros can help us to automate repetitive tasks associated with data manipulation that must be accomplished repeatedly. You can also enable and disable macros in Excel.

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 Turn on the Developer Tab in Excel

The developer tab is hidden by default on the ribbon. To customize the Ribbon, Follow these steps:

Step 1: Right-click on the Ribbon

Right- click on the Ribbon.

Step 2: Navigate to Customize the ribbon and place a check on the Developer Checkbox

Turn on the Developer Tab.

How to Record a Macro in Excel

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

Step 1: Click on the Developer Tab in the Ribbon

Click on the Developer tab.

Step 2: Click on the Record Macro

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

Select Record-Macro

Step 3: Fill the Record Macro Dialog Box

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

Type the Macro name.

In the dialog box, enter the name of the macro in the 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, 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 in the dialog box, click on OK.
  • As soon as OK is clicked, Excel Macro records 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 in Excel

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

Run the Macro by Clicking on a Shape

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

Step 1: Click on the Insert Tab on the ribbon

Click on Insert.

Step 2: Choose any Shape

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 on Illustrations.Select shapes.

Step 3: Add the Shape

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

select the shape of your choice.

Step 4: Edit the Shape

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

Format the shape according to your need.

Step 5: Right Click on the Shape and Click on Assign Macro

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

Assign a macro.

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

Select the Macro from the list.

Step 6: Now Select the Macro

In the dialog box, select the macro from the list you want to assign to your shape and click the OK button. Now the shape would work as a button and whenever you click on it, it will run the assigned macro.

How to 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.

Step 1: Go to the Developer Tab on the Ribbon

Select the Developer tab.

Step 2: In the Controls group, click on the Button Tool

Click on the button tool.

Step 3: Click on the Excel Sheet and Fill the Details in the Dialog box

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

Assign  a macro.

Step 4: Select the Macro you want to Assign and Click OK

Select the macro you want to assign to that button and fill in the other options. Then click on OK.

Step 5: Preview the Shape

After that, a rectangular button can be seen on the sheet, and ready to be used.

Button inserted.

Step 6: Format the Button

The button which appears on the sheet has default settings, and you can’t format it like the color, or 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. 

Edit alt-text.

How to run a Macro from Excel 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.

Step 1: Go to the Developer Tab on the Ribbon

Click on the Developer tab.

Step 2: Then in Code Group, click on the Macros Tool

Click on the Macros tool.

Step 3:Select the Macro that you want to Run

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

Select from the list.

Step 4: Click Run

How to Create Macro in VBA 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. 

Step 1: Go to the Developer Tab on the Ribbon

Select from the Developer tab.

Step 2: Click on the Visual Basic

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

Click on the Virtual basic.Select from the sheet.

Step 3: Click on Run Macro

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

Click on the Run macro.

Step 4: Select the Macro from the list

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. You can also edit a macro as per your need.

Select the macro you want to run.

How to View Macros in Excel

Step 1: Open Microsoft Excel

Step 2: Click on the Developer Tab

Screenshot-(248)

Step 3: Click Macros to see a list of available macros in the active workbook

Step 4: Select a Macro to View

Step 5: You can run or edit the macro from here if needed.

Step 6: Close the Macro dialog when you’re done

Screenshot-(247)

How to Save the Recorded Macros in Excel

Step 1: Record your Actions

Step 2: Click on Stop Recording

Step 3: Save your workbook as “.xlsm” (macro-enabled)

Step 4: Your macro is now saved in the workbook

Also Read

FAQs

What are Macros in Excel?

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. Macros can be considered as a set of programming instructions that can be recorded, named, saved, and executed in VBA as and when required macros can help us to automate repetitive tasks associated with data manipulation that must be accomplished repeatedly.

How to Enable a Macro?

Follow the below steps to Enbale a Macro in Excel:

Step 1: Open Excel, then go to the File tab.

Step 2: Make sure the Developer tab is ON.

Step 3: Select the Developer tab and click on the record macro. It will start recording a Macro.

What are Macros and VBA in Excel?

Macros in Excel are like recorded sequences of actions that automate tasks. They save time and minimize mistakes by letting you repeat actions with a single click.

VBA, or Visual Basic for Applications, is a programming language in Excel. It allows you to write custom code to create advanced and personalized macros. With VBA, you can automate tasks, perform calculations, and manipulate data in Excel, providing more control and flexibility compared to basic recorded macros.

What is Macros in Excel for beginners?

For beginners in Excel, think of macros as a way to make your repetitive Excel tasks easier. You can record a macro to remember those tasks. A macro is like a recording of what you do with your mouse and keyboard in Excel. Later, you can play that recording anytime to repeat those actions as many times as you need.

Can I write macros in Excel manually?

Yes, Macros can be written manually in EXcel using the VBA programming language.

How to Disable a Macro in Excel?

Follow the below steps to disable a macro in Excel:

Step 1: Click on the File tab.

Step 2: Go to options.

Step 3: Select Trust Center from the left-hand pane, and then click Trust Center Settings.

Step 4: Select the Macro setting from the left menu, then disable all macros without notification and click ok.



Last Updated : 21 Sep, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads