Macros in Excel

Table of Contents

What is MACRO in Excel?

MACRO is the programming language of excel which can automate the tasks by writing code or by recording the task and this can be done with Excel VBA (Visual Basic Applications). The extension of the file name in Macros happens to be “xlsm”. We should ensure that we are saving macro file with “xlsm” because we can’t reactivate or run the macro if we save it in “xlsx”.

Before getting started with enabling and recording MACROs in excel, we need to activate the developer tab.

Let’s see how to get “Developer Tab” in your excel where you can find “Visual Basic”, “Macros”, “Record Macro” and so on…

If you had never used Macros earlier, then you may not have “Developer Tab” in your excel.

Steps to Activate “Developer Tab” to Enable Macro Recording

Step 1: Go to File Menu then you can see “Options” on your left side of the list as below.

Step 2: Click on the Options, then the new window of excel options will pop out as shown below.

Step 3: Here we have to select “Customize Ribbon” as we are adding the new tab to our tab menu and customizing the ribbon. When we click on “Customize Ribbon”, a window to select “Developer Tab” will open and this window will give many other options to customize.

From the below window we have to select the “Developer” in the main tab and click “Ok”.

Step 4: Now we can see the “Developer Tab” on our excel sheet.

And your ribbon of “Developer Tab” will be as below and we can find the options of “Visual Basic”, “Macros”, “Record Macro” in excel and so on…

How to “Record a Macro” in Excel?

Assume that we have data with different names but it has “.” Symbol to be removed.

Let’s do this operation by enabling and recording a macro in excel and see how it gets automated.

We have to go to “Developer Tab”, where we find Record Macro option in excel and click on that.

Window to “Record Macro” will pop out in excel where we can give a name to a macro that we are creating. We also get an additional option of creating a shortcut key.

In our example, we are going to name macro as “ReplaceDot” and we are creating a shortcut as “Ctrl + q”.

As we click “Ok” after creating a macro name, a macro in excel will get starts recording as below –

Now we have to perform the task that we want to do. Here we are replacing “.(dot)” in names with _(Underscore).

Go to Find & Replace or use shortcut key “Ctrl + H” and mention “.(dot)” in find what and replace with _(Underscore) then click on “Replace All”.

And this operation will replace with _(Underscore) where ever it finds .(dot).

Now if we want to perform the same operation for a few other lists we can use the macro which we created as “ReplaceDot” from macros icon in “Developer Ribbon”.

Let’s take a new list of names with the same criteria as mentioned for the above example and run “ReplaceDot” macro.

As we select Enable Macro from the “Developer Ribbon”, we can see the Macro window pops out. In this, we can see the list of macros that we created and will be selected and run.

In our example, we had created a macro in excel on “ReplaceDot” so we select it and run the macro.

Now we can observe that the dots in the new will be replaced with underscores.

We can create a “Button” and can assign a macro that is created instead of going to enable macro in excel and selecting the created macro and then run. Let’s see how can we create a button.

If we can observe “Developer ribbon”, there are few more options like Add-ins, Controls & XML. In controls we got insert option where we can create a button as follows:

We can select the type of button we want to create. In our example, we are going to select the 1^st button from “Form Controls”

Once we select the button and drag it somewhere in excel sheet then a window to assign macro will open which shows the list of created macros or to create/record a new macro as below:

We have to select the macro “ReplaceDot” which is already created and click ok.

Then we can see the button created on our sheet as below:

Now select the new list with the same criteria and conditions to be implemented.

Then click on the button for which the “ReplaceDot” macro is assigned and we can the change in the list.

We can change the name of the button as per our wish by editing it. Here let’s change the name to ReplaceDot.

By this way, we can record a macro and assign it for a simple way to use and automate the working.

We can see the code for the above macro that got recorded. Excel will write the code as per the steps that we take while recording the macro.

For the above “ReplaceDot” macro the code would be as follows:

To access the code we can use the shortcut “Alt + F11” or by editing the macro that is created. Let us see how it can be opened.

As we click on the edit option from the above macro/Alt + F11, “Microsoft Visual Basic for Applications” will get pop out where we can write the code or edit the code that is already created.

The above code is created for the “ReplaceDot” macro that was recorded by us and this can be edited or can make changes as per our requirement.

Creating Macro by Writing Code in Excel VBA

Before going to start writing the code let us know about “Head” & “Tail” of a Macros which are “Sub & End Sub”.

Now there are two types of macros in general – One which performs a certain action which mean that it deletes all worksheets or it creates a link of all the worksheet names which are system defined function. And the second could be a user-defined function.

In order to create User Defined Function(UDF) in macro, we should use “Function & End Function” as our “Head & Tail”.

A function will return a value whereas sub cannot.

Let’s write a simple macro in excel VBA:

To write a macro we should open the “Microsoft Visual Basic for Applications” which is a visual basic editor.

As we open the visual basic editor, we can observe the module properties on our left side and click on “module 1” and start writing a macro.

We have to start writing a macro with “Sub and the name of the macro” and automatically End Sub will be displayed at the end. We can write the code between the Sub & End Sub.

Let’s write code to display text in the form of msgbox.

“MsgBox” displays a message box that was given to it. Remember that all text in VBA should be in double inverted commas.

This code will give the message of “Good Morning” when we run it.

Similarly, we can give other messages too to display and this macro can be used to assign it to any button as well and there are many other functions which can be used to automate the work and makes it easy to run with macro.

How to Save the Recorded Macro in Excel?

As discussed earlier, we should make sure that file should get save with “xlsm” file extension.

We have to select “Excel Macro-Enabled Workbook” while saving the file.

Things to Remember About Macros in Excel

We should make sure that name of recorded macros in excel should start with letters (alphabets)/underscore and we can use more letters, numeric, underscore characters but not contain space, symbols or punctuation marks. Maximum length should be 80 characters. When we try to save the name of the macro with space then the below warning gets pop out.

Macros security settings should be enabled. When we open a macro excel, we can notice a security warning which says “Macros in excel have been disabled” and we should enable content.

We can eliminate the security warning for other macros by enabling the condition as below:

Go to the macro security in “Developer Ribbon” and enable the security.

Make sure for while creating absolute macros to start from starting of the cell i.e. cell A1 because this will help to reuse the macro in other worksheets.
For navigation in macros in excel, using directional keys will be reliable to add/delete/changing the data in the spreadsheet because using a mouse to navigate in the macro will be complicated and may not be reliable.

You can Download this Record a Macro Excel template here – Macro Excel Template

What is MACRO in Excel?