Macros in Excel

What is MACRO in Excel?

A macro in excel is a series of instructions in the form of code that helps automate manual tasks, thereby saving time. Excel executes those instructions in a step-by-step manner on the given data.  For example, it can be used to automate repetitive tasks such as summation, cell formatting, information copying, etc. thereby rapidly replacing repetitious operations with a few clicks.

There are two methods to create the macros – The first is when you can record the macro, where Excel records every step automatically and then repeats it. The second is coding with VBACoding With VBAVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more, which requires good subject knowledge

Before recording a macro, the user has to activate the Developer tab in Excel. The Developer tabThe Developer TabEnabling the developer tab in excel can help the user perform various functions for VBA, Macros and Add-ins like importing and exporting XML, designing forms, etc. This tab is disabled by default on excel; thus, the user needs to enable it first from the options menu.read more is a built-in option in Excel to create macros, generate VBA applications, design forms, import or export XML files, etc. Since it is disabled in Excel by default, it has to be enabled before creating and recording the macros.

Let us learn the method to enable the Developer tab in Excel.

Enable the Developer tab

Below are the steps to activate the Developer tab in the Excel toolbar.

  1. Click on “options” in the File menu (as shown in the succeeding image).


    Activate developer Tab step 1

  2. On clicking the “options,” the “Excel options” window will pop up.


    Activate developer Tab step 2

  3. Select “customize ribbon” in the “Excel options” which provides a list of options in a dialog box.
    Under “customize the ribbon,” select “main tabs”. Among the list of checkboxes, select “developer” and click “ok”.


    Activate developer Tab step 3

  4. The worksheet displays the Developer tab, as highlighted in the succeeding image.


    Activate developer Tab step 4

  5. The user can view options like “visual basic,” “macros,” “record macro,” etc. on the ribbon of Developer tab (as displayed in the image below).

    Activate developer Tab step 5

Examples of Macros in Excel

Let us understand how to add macros in excel with the help of the following example.

Example #1

A list of data with different names is available in the table below. Some names have “.” symbol. We want to replace the “.” symbol with “_” by using macros in Excel.

You can download this Macro Excel Template here – Macro Excel Template
Macro Example 1

The steps to add an excel macro are listed as follows:

  • Click the “record macro” option in the Developer tab.
Macro Example 1-1
  • The “record macro” window will pop out. Name the macro “ReplaceDotin the “macro name” box. To assign a keyboard shortcut, type “Ctrl+q” in the “shortcut key” box.

Select the option “This Workbook” in the “store macro in” box, which will ensure the macro is stored in the particular workbook.

It is optional to fill the “description” box explaining the task. Finally, click the “ok” button.

Macro Example 1-2
  • The “ReplaceDot” macro will start recording the user actions in Excel. The user will observe the “stop recording” button appearing in the Developer tab.
Macro Example 1-3
  • Let us now start replacing the “.(dot)” in the names with “_(underscore)” by using the “find and replace” option. Enter “.” in the “find” and “ _” in the “replace” option, respectively. Then click the “replace all” button.

Note: Use the shortcut key “Ctrl+H” to use “find and replace” option.

Shortcut key to Find
Macro Example 1-4
  • The “replace all” option replaces all the “.” (dots) with the “_” (underscores). The number of replacements and the resulting output is shown in the succeeding image.
Macro Example 1-5
  • The final output is displayed in the below image.
Macro Example 1-6
  • In the end, click the “stop recording” button on the Developer tab to stop the macro recording.

Example #2

We want to run the same task for a new list of names (displayed in the below image). We will run the macro “ReplaceDot,” created in the Developer ribbon.

Macro Example 1-7
  • Select the “enable macro” option from the Developer ribbon to view the list of macros created in the Macro window. The users can choose and run the macros based on their requirement.

The succeeding image shows the result of macros running on the new list of names.

Macro Example 1-8

Adding the Macro Button

Let us assign a button to the macro instead of choosing the “enable macro” option.

There are many groups like Add-ins, Controls, and XML under the Developer ribbon.

Macro Example 1-9
  • The user can choose the type of button to be created. Select the first button from “form controls” in the Controls tab.
Macro Example 1-10
  • Drag the selected button anywhere in the Excel sheet. The “assign macro” dialogue box opens. The macros to be assigned are listed in the “macro name” box.
  • Select the macro “ReplaceDot,” appearing in the list and click “ok”. 
Macro Example 1-11
  • A button appears in the worksheet. Right click the button and use “edit text” option to change the button text as “Button 3.” It is created on the right-hand side of the sheet, as shown in the below image.
Macro Example 1-12
  • Select the new name list to implement the same task by running macros, as indicated in the previous section.
Macro Example 1-13
  • Click the “Button 3” to run the assigned macro “ReplaceDot”.
Macro Example 1-14
  • To change the button name, use the “edit text” option to replace the text “Button 3” with “ReplaceDot”.
Macro Example 1-15

Using the above steps, we can create, record, and assign the macro for various tasks and automate it.

How to View the Code of Macros?

The users can view the code for a recorded macro. Excel generates the code based on the steps carried out while recording the macro.

We can access the code using the shortcut “Alt+F11” or by editing the macro that was created earlier.

Let us view the code for the “ReplaceDot” macro using the following steps:

  • Open the worksheet that contains the “ReplaceDot” macro. In the View tab, click the option “macros”. Select the “ReplaceDot” macro from the list and click the “edit” button.
Macro Example 1-16
  • The “Microsoft Visual Basic for Applications” will be launched.  The user can write or edit the code using this application. The below image shows the code of the “ReplaceDot” macro.
Macro Example 1-17

Creating Macro by Writing VBA Code

Before writing the VBA code, let us understand the “head” and “tail” of macros, which are the “sub” and “end sub.”

Macro Example 2-1

The user places the lines of VBA codes in the keyword “sub”. It executes the instructions in the code. The “end sub” keyword stops the execution of the “sub.”

Generally, there are two types of macros.

  1. “System Defined Function” –  It performs actions like creating a link of all the worksheet names, deleting all worksheets and so on.
  2. “User-Defined Function” – To create a User Defined Function User Defined Function User Defined Function in VBA is a group of customized commands created to give out a certain result. It is a flexibility given to a user to design functions similar to those already provided in Excel.read more(UDF) in macro, the user uses the “function and end function” as the “head” and “tail” of the codes.

Note: A function returns a value, whereas the sub does not.

A macro is written on the Visual Basic EditorVisual Basic EditorThe Visual Basic for Applications Editor is a scripting interface. These scripts are primarily responsible for the creation and execution of macros in Microsoft software.read more (VBE) of the “Microsoft Visual Basic for Applications”.

Let us learn the steps to write a simple macro in the VBA.

  • Click the “module 1” in the “module” properties displayed on the left-hand side panel of the VBE window, and start writing the macro.
Macro Example 2
  • Begin with “sub” followed by the macro name and end with “end sub”. The code is written between the “sub” and “end sub.”

The below image shows the “sub” and “end sub” for the macro named “simplemacro ( )”.

Macro Example 2-1

For example,  the code:  MsgBox “Good Morning” (shown in the below image) displays the message “Good Morning” in the text box.

Macro Example 2-2
  • The output is displayed in the succeeding image.
Macro Example 2-3

Hence, the same macro can be assigned buttons to automate the task.

How to Save the Recorded Macro in Excel?

After recording, the user saves the macro to reuse in any other worksheet in the future.

Let us follow the below-mentioned steps to save the macro:

  • In the macro-enabled workbook,  click “save as.”
  • Select the “Excel macro-enabled workbook” option in the “save as type” box while saving the file.
  • Finally, save the macros with the “.xlsm” file extension.
Save Macro

The guidelines for saving the macro names are stated as follows:

  • Make sure that the name of recorded macros should start with letters (alphabets) or underscore.
  • Use letters, numeric, and underscore characters.
  • Avoid space, symbols, or punctuation marks.
  • Maintain a maximum length of about 80 characters.

Note: When the user saves a macro’s name with space, Excel issues a warning (as shown in the succeeding image).

Macro Warning

How to Enable “Macro Security Settings”?

In this section, let us learn to enable the “Macros security settings”.

When a user opens a workbook containing macros, a security warning – “Macros have been disabled” is displayed under the ribbon.  Choose the “enable content” option in the box.

Macro Security Warning

To eliminate the security warning, we need to change the “trust center settings” by using the following steps:

  • Under the “trust center settings” in the File options, click “macro settings”.
  • Choose the button, “Disable all macros with notification.”
  • The security is enabled in the “Macro Security” of the Developer ribbon.
Macro Security

Note 1: The user can create absolute macros (functioning from cell A1) that help to reuse the macro in other worksheets.

Note 2: The usage of directional keys (rather than a mouse) for navigation in macros is reliable to add, delete, and change the data in the worksheet.

Frequently Asked Questions (FAQs)

1. What are macros in Excel?

Macros are a set of simple programs or instructions to automate the common and repetitive tasks performed in the Excel worksheet. It can be recorded, saved, and run multiple times as per the user’s requirement.

It is a time-saving tool involved in preparing data reports and manipulating data carried out frequently in a worksheet.

2. How to enable macros in Excel?

The users can eliminate the security warning in the worksheet and enable macros by using the following steps:
• Click “options” in the File tab.
• Select “trust center” in the “options” window.
• Choose the option “trust center settings.”
• Click “macro settings” on the left side of the navigation pane.
• Select “enable all macros” and click “ok”.

3. What is the difference between macro and VBA?

The difference between macro and VBA is stated as follows.
• Macros are programming codes that function in the Excel worksheet to perform automated and repetitive tasks. It saves the user’s time and extends the efficiency of Excel.
• Visual Basic for Applications (VBA) is a programming language of Excel used for creating macros.

Recommended Articles

This has been a tutorial to Macros in Excel. Here we discuss how to add Macros in Excel along with , along with practical examples and a downloadable template. You may also look at these useful functions in Excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *