WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Macros in Excel

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.

Activate developer Tab step 1

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

Activate developer Tab step 2

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

Activate developer Tab step 3

  • Step 4: Now, we can see the “Developer Tab” on our worksheet.

Activate developer Tab step 4

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…

Activate developer Tab step 5

How to Record a Macro in Excel?

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

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

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

Macro Example 1

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

Macro Example 1-1

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

Macro Example 1-2

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

Macro Example 1-3

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 finding what and replace with _(Underscore) then click on “Replace All.”

Shortcut key to Find

Macro Example 1-4

Macro Example 1-5

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

Macro Example 1-6

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 the 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 the “ReplaceDot” macro.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Macro Example 1-7

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.

Macro Example 1-8

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 we can create a button.

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

Macro Example 1-9

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

Macro Example 1-10

Once we select the button and drag it somewhere in the 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:

Macro Example 1-11

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:

Macro Example 1-12

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

Macro Example 1-13

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

Macro Example 1-14

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

Macro Example 1-15

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.

Macro Example 1-16

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.

Macro Example 1-17

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 the “Head” & “Tail” of a Macros, which are “Sub & End Sub.”

Macro Example 2-1

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 a User Defined Function (UDF) in macro, we should use “Function & End Function” as our “Head & Tail.”

A function will return a value, whereas the 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.

Macro Example 2

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.

Macro Example 2-1

Let’s write code to display text in the form of a msg box.

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

Macro Example 2-2

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

Macro Example 2-3

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 that 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 the file should get save with the “xlsm” file extension.

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

Save Macro

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 to pop out.

Macro Warning

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

Macro Security Warning

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

Macro Security

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

  • Make sure for a 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
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Macro Excel Template

New Year Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More