Macros in Excel

What is MACRO in Excel?

Macros in Excel are used to automate our tasks which are done manually, there are two ways of creating macros one is by recording a macro where excel records our each step and then repeats it itself while another method is by using VBA code, to use VBA coding one should have knowledge about VBA to design macros.

Before getting started with enabling and recording MACROs, we need to Insert Developer Tab Excel.

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”.

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 worksheet.

And your ribbon of “Developer Tab” will be as below and we can find the options of “Visual Basic”, “Macros”, “Record Macro” 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 and see how it gets automated.

We have to go to “Developer Tab”, where we find the Record Macro option 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 the 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 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 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 Control in excel”

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.

In 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 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 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

• We should make sure that the name of recorded macros should start with letters (alphabets)/underscore and we can use more letters, numeric, underscore characters but not contain space, symbols or punctuation marks. The 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, we can notice a security warning which says “Macros 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 the starting of the cell i.e. cell A1 because this will help to reuse the macro in other worksheets.
• For navigation in macros, 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.

Recommended Articles

This has been a guide to Macros in Excel. Here we discuss how to Enable and Record a Macro in Excel, along with practical examples and a downloadable template. You may learn more about excel from the following articles –

• Excel Open XML
• VBA Macros
• MsgBox in Excel VBA
• VBA Macros Course