Excel Macro Tutorial : What is Excel Macro ?



Excel Macro Beginner

Dear Beginner,

In this Excel Macros Tutorial, you are going to learn the Basics of Excel Macro. This is the first Article of this Tutorial so Let’s begin with What is Excel Macro

What is Excel Macro ?

Let’s not get too much in detail at this point. For now, in simple language, Macro is very popular software available with the most of the windows applications. Many of the windows applications provide inbuilt Macro programming. For example: Excel, Word etc. Here in this blog you will be learning this how to use it in Microsoft Excel.

It allows you to perform multiple operations just by clicking a simple button or changing a cell value or opening a workbook etc. It enables you to work in a smart and efficient way. In terms of productivity, it is very productive as it reduces lots of manual work and gets the things done very fast especially things which you are doing it repeatedly.

So, is it a programming language?

For now, Yes, it is very similar to Visual Basic. You don’t need to know much about the history of this language at this point of time. If you are interested and want to know history of this Language you can read this article. It is a video tutorial, where I have discussed about the history of this Language.

So now, I will directly jump to my original topic, where, I am going to teach you two Important features of this Tool, which, you as a beginner, will like it the most 🙂
They are

i) Recording a Macro

ii) Playing a Recorded Macro

Where do you find or see excel Macro or VBA code in Excel?

Don’t get confused with these two terms which I am using Macro and VBA. It is not worth spending time on. You can consider them to be the SAME THING for now. You can refer my video tutorial,, if you really want to know the difference between these two terms.
In this tutorial, I will be taking example of MS Excel and will be exploring each and every possible technique to do the things in a very easy manner.

In MS Excel, we have both recording and writing our own macros based on our need. We will start with Macro recording.

How to Record Macro in excel

In this section you will learn, how to Record Macro in Excel. Follow the below Steps one by one to record macros in Excel:

STEP 1.

Open one excel workbook. (Taken example of MS Excel 2010)

STEP 2.

Go to “Developer” Tab of the workbook. (How to add Developer Tab in Excel-2010 and 2007 Workbook)

STEP 3.

Click on “Record Macro” button in the right hand side of the Top Menu Bar

STEP 4.

In Excel 2007 and 2010, Macro recording can be started by clicking on the Red Shape button marked in the bottom bar of the Excel in left hand side.

STEP 5.

Click on Record Macro Button shown above

STEP 6.

In the above dialog box, enter the suitable name for this Macro.
You can also specify the shortcut key for running this particular recorded macro. It means on pressing that particular shortcut-key, this macro will automatically run.
To know more about assigning short keys to your macros read this article: How to assign a Shortcut key to a Macro ?
You can also refer list of important built-in short keys in Excel here

STEP 7.

You can give the location, where you want to store this particular macro and also you can write some description about this particular to be recorded Macro.

STEP 8.

Now click OK and do some operation on the excel sheet. Like formatting of the cell or sorting some of the values etc. and click on the Stop button at the same place when you started running the Macro.

STEP 9.

I have done the above formatting and wrote few column names while recording.

STEP 10.

To view this particular recorded macro, right click on any of the Sheet Name -> View Code

STEP 11.

In left hand side pane, under VBA Project of that workbook, Expand the Module

STEP 12.

One Module would have been created there “Module 1”. Click on this

STEP 13.

You can see the recorded code with the given Macro Name.

How to Run Excel Macro or VBA code in Excel

You can Run this Recorded Macro in two ways:

1. By Pressing Run Button

2. By Pressing a short key – Alt+F8

Before, I run this recorded Macro, I have deleted the formatting done during the recording of this macro. Now once I will run this Macro, then automatically that formatting will be done in that Sheet.. Magic.. Isn’t it? 🙂 First, we will see how to run the recorded Macro by pressing the play button in VB Editor screen.

Here is the clean sheet :

How to Run your macro by pressing the Play button?

STEP 1.

First of all, go to your VBA code screen. You would not find a play button on the workbook screen.

STEP 2.

Place your cursor/mouse within the excel macro which you want to run

STEP 3.

Click on Run or Play button as highlighted in below image:

That is it !! You are done… and here is the result:

Important

If you do not place your cursor somewhere within the VBA code which you want to run, then it will show you below popup with list of all Macros written in your workbook for your select which one you would like to run.

How to run play your macro without going to VBE Screen

If you Do not want to run the Macro, by Pressing the Run Button in the VBE screen, not to worry, you can run it from your Worksheet screen as well. Follow the below 3 simple steps to run your macro without going to the VBE screen.

STEP 1.

Press Alt + F8 as a Shortkey. The following popup will appear (same as mentioned above)

STEP 2.

Select Your Macro from the Drop Down

STEP 3.

Click on Run

That is it!! Congratulations !! Now, you know how to Record and Play Macro in Excel Workbook.



You may follow some tutorials here

Beginner’s Page: Excel Macro Tutorial
HP Quality Center: Excel Macros for HP Quality Center
Excel Tutorial: Advanced Excel Macro Tutorials
FREE Tools: Excel Macro Tools – FREE Download
Send Email: Series of articles about sending emails using excel VBA
UDFs : Useful User Defined Functions
Excel Formula : Some important and useful Excel Formulas

Buy a coffee for the author

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Hurray..Lets Go

Convert PDF Tables to Excel with VBA | Step-by-Step Guide

Dec 4, 2024

Convert PDF Tables to Excel with VBA | Step-by-Step Guide How to Convert PDF Tables to Excel Using Excel VBA (Macros) Extracting data from PDF tables into Excel can be tedious, but with Excel VBA (macros), you can automate this process and save time. This guide will...

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

Feb 9, 2024

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA

Feb 3, 2024

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

Know more

Shagun on February 21, 2012 at 1:39 pm

This is amazing Vishwa. You have given a detailed description step by step.

I am a beginner and didn't even know what a macro is. I have created my first macro. Kudos to you!!!
Reply
- Vishwamitra Mishra on February 21, 2012 at 2:00 pm
  
  You are Welcome Shagun and keep giving feedback 🙂
  Thanks,
  Vishwa
  Reply
  - Johnny Wolfkill on April 18, 2012 at 11:04 am
    
    This is awesome. I just created my first Macro titled Macross 🙂
    
    I feel like I have been taught to fish in Excel!
    
    Your instructions are very thorough. The procedure worked on my first fly through!
    
    Keep this up! I will definitely be back for more!
    Reply
    - Vishwamitra Mishra on April 20, 2012 at 4:22 pm
      
      Thanks Johnny 🙂 🙂
      Reply
      - aswini on April 27, 2013 at 5:28 am
        
        Hai Vishwamitra, This is aswini. Could you please give your mail id??
      - Saroj on August 17, 2016 at 6:19 am
        
        Hi mishra
        I have a macro file developed in excel 2003.But that file does not work properly in excel 2007.Some times it run.If i donot enter any data on that time macro work properly. What will be the reason
  - Baqir on November 20, 2014 at 2:11 pm
    
    Hi Mishra,
    
    I want to create one macro which can pick up only few fields out of 64 colmns and rows from an excel sheet.
    Reply
    - Zen on November 20, 2014 at 2:14 pm
      
      Could u help me out for this ?
      Reply
Mahesh on August 12, 2012 at 10:02 am

Hi Viswa,

This is really amazing .I truely appreciate your work. i have just started learning excel and this is te first macro i ve ever tried. Thanks a lot . i would like to learn a lot.

Thanks,

Mahesh.N
Reply
- Vishwamitra Mishra on August 12, 2012 at 10:16 am
  
  Thanks Mahesh!!
  Reply
  - Mahesh on August 12, 2012 at 11:52 am
    
    Hi Viswa,
    
    I am using a excel document currently and all i wanted to do is b running a macro ,it should create a new excel document with copying the values from the document that i currently have. please help me with this.
    Reply
    - Vishwamitra Mishra on August 12, 2012 at 12:50 pm
      
      Hi Mahesh,
      If you are just trying to make a copy of the same workbook then use the following code: Let me know if this helps you.
      
      Sub Creat_Copy()
      Dim Wb As Workbook
      Set Wb = ThisWorkbook
      Wb.SaveCopyAs "E:LearnExcelMacro.comExcelFormattedhello.xls"
      End Sub
      Reply
      - Mahesh on August 12, 2012 at 1:53 pm
        
        HI Viswa,
        
        1.i just opened the document
        
        2.ALT+f11
        
        3.insert->module
        
        4.copied the script
        
        5.changed the file location and ran it.
        
        6.IT THOROWING A COMPILATION ERROR.
        
        Please bear with me since i am a bigginer i am not so good at this. please help me.
        
        Thanks.
      - Vishwamitra Mishra on August 12, 2012 at 2:29 pm
        
        What is the error?
      - Mahesh on August 12, 2012 at 2:30 pm
        
        Hi Viswa,
        
        Thanks a lot, i correced the sntax error it worked fine …. hats off to you .. feeling great to reach you in case of any oter help. Please ignore the above two posts.
        
        Thanks
        
        Mahesh.N
      - Vishwamitra Mishra on August 12, 2012 at 2:33 pm
        
        Thanks Mahesh !!
Yogesh on August 12, 2012 at 6:18 pm

good work man!!!!!!!
Reply
- Vishwamitra Mishra on August 13, 2012 at 2:41 am
  
  Thanks!!
  Reply
Mahesh on August 21, 2012 at 1:50 am

Hi Viswa,

just like the above task i have to copy certain columns from a excel document and create a new excel document with help of a macro.Please help me with this

Regards,

Mahesh.N
Reply
clinta on November 10, 2012 at 7:19 am

chanceless vishwa helps me a lot as a new to excel i need to learn a lot need ur help:(
Reply
Kumar on December 31, 2012 at 5:29 pm

I would like to set up a macro in a workbook which automatically saves/closes the workbook after a specific period of time.

For example….if its idle for 5 mins without any activity it should give alert msg and close the excel file.

The trick is, the macro should only run if there has been no activity/changes to the workbook in that period of time.

If a workbook is accidently left open on a computer, this will close it automatically.

I have been searching for info on how to do this, and need help. Any advice is appreciated.

I am novice so step by step with programming will work for me…
Reply
Prabhu on January 30, 2013 at 8:43 am

Hi Vishwa,

I need to learn Excel macro cording,can you please help on this regards just give me small cordings with examples.I iknow recording I want to go to next step.
Reply
Yogisha on March 5, 2013 at 11:01 am

Hi Vishwa,

This is Yogisha.M you are doing wonder full job thank you for your suggestion, and I need one Branch Dash Board template (including all Parameters). Please help me on this.
Reply
nagaraja on April 4, 2013 at 10:57 am

hi I want to learn macro in excel kindly let me know how to work on macro
Reply
sheta on April 15, 2013 at 9:54 am

hai…i am a chemical engineer student..my lecturer give me assignment..where..user have to key in the x value..and the macro would provide the y value…its like an equation with x variable in it ..to give the y value to the user..can i know how to do it?? thanks..
Reply
Jeena on April 18, 2013 at 5:09 am

I have a master sheet which have information of 10 of my employee and their task status in detail.

i need to run a macro which will identify these 10 employee and create new sheet as per there name.

which i have done with my below code:

————————————————————

Sub MAIN()

Dim MyCell As Range, MyRange As Range

Set MyRange = Sheets("Sheet1").Range("B2:B16")

Set MyRange = Range(MyRange, MyRange.End(xlDown))

For Each MyCell In MyRange

Sheets.Add after:=Sheets(Sheets.Count) 'creates a new worksheet

Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet

Next MyCell

End Sub

——————————————————————–

now what i need to do is extra the rows with the employee name and put the row into the new sheet as per their name.
Reply
sandy on April 25, 2013 at 5:15 pm

my macro is not running it's shows the error…
Reply
manthan thakkar on May 10, 2013 at 12:41 pm

kindly learn me more macro demo
Reply
Amazingreviewz on May 20, 2013 at 6:55 am

Hey its really cool website and really helpful for New People to learn Excel macro. I want to add your site on my amzingreviewz com . Please allow me to add u r site in my list

thanks

amazingreviewz team
Reply
shalini on May 21, 2013 at 10:48 am

It was reallly very helpful.

Thanks for sharing.
Reply
Feni on May 29, 2013 at 4:25 am

Hi.. U r doing a great job..I just want u to help me in inserting a photo/picture in my userform for each user..
Reply
Abhijit on June 7, 2013 at 6:36 am

Very easy and useful. It helps me alot. Thanks.
Reply
Marl Jins on March 7, 2014 at 5:26 am

Hey, I have noticed that occasionally this site shows a 403 server error. I figured you would be keen to know. All the best
Reply
christilda nalatham on September 9, 2014 at 6:12 am

Hi Vishwa,

you have any book or CD or all these things. because i am a beginner and i need to work on Macros for 1 year.. But i dont no anything about it.. Can you please help me
Reply
Zahid on September 28, 2014 at 6:16 pm

Thanks
Reply
- Ajith on October 28, 2014 at 3:54 am
  
  Thanks,for your consideration,,that is very helpfull for me,becouse i haven’t eny idea about macro ,but after follow up this E-book,,it helped me,,,thanks lot ,,again…so,,if you have any new tutorials regarding macro please mail link…
  Reply
@khan on November 3, 2014 at 11:41 am

once i save the file and close it the module is not there,what shud be done if one wants to run the micro not in currnet time but after saving it and stuff.I hope u got my question.
Reply
- Vishwamitra Mishra on November 3, 2014 at 12:59 pm
  
  Hi Mr. Khan,
  
  It looks like while saving you must be getting a pop message like “do you want to save your file as a Macro-free Workbook” and if you say “Yes” then you will loose all VBA code written in a Module. On such message you say “No” and save your file as “.xlsm”. Hope this helps.
  Reply
  - Srikanth on November 28, 2015 at 11:10 am
    
    Hi,
    i have got 5yrs of Mis Executive experience
    and am looking for an institute to learn VBA Macros
    pls suggest me
    Institute name
    what all courses need to be added
    
    Thanks/ Srikanth
    Reply
silu on January 10, 2015 at 12:29 pm

Hi Mishra,

i’m a beginner and wanna learn macro upto depth. I tried 2 subscriber but its not happening, can u please send me the e book.
Reply
sadham on February 18, 2015 at 7:29 pm

HAISIVA, BASIC MACRO IN EXCEL SHEET SEND MU EMAIL ID :sadhamhusain15@gmail.com
Reply
Nisar Aboobacker on March 22, 2015 at 11:25 am

hi Dear Vishwamitra Mishra.
really nice work, and I get a promotion due to your work
thanks a lot.
Reply
- charanya on February 5, 2016 at 12:25 pm
  
  congratulations!! Kindly help me too and share some of the easier macros like exporting from sql database to excel and the steps to follow.
  Reply
Aisha on July 13, 2015 at 7:19 pm

Hi Viswa,

i am a beginner i do not know anything much about macros.
but i have a requirement. it would be of great help if you could guide me with this.

the requirement is as follows:
1. i want to create a macro which would generate a work book which would have data in particular tabular format.

2. the column name should be fixed but few fields should be populated according to the values i provide i provide in the parent workbook

appreciate your time and help in advance.!!

Thanks,
Aisha
Reply
- charanya on February 5, 2016 at 12:24 pm
  
  Hi Aisha/Vishwa,
  If you got the help from vishwa, kindly share me the excel macro details or workbook and the steps which you followed. I too have the same requirement.It would be a great help for me to generate the workbook in tabular format.
  
  Thanks in Advance!!
  Reply
simhachalam on July 24, 2015 at 6:43 am

hi,

i have one q can any body help me… in my company we are using macros… by pressing 1 , 2 , 3 ,4 on excell the respective mail goes to manager.. so instead pressing 1 2, 3 i would like to put y or n. so how to runt can you help me
Reply
Subbu on September 21, 2015 at 2:50 pm

hai , i have seen the macro, it is very good and can understand it, but i have question on it , if we save a recorded macro in one excel file it can be used in another excel file ?
Reply
Rams on September 26, 2015 at 1:45 pm

Hello

I am a beginner and i do not know anything about macros. But I want to learn macro in depth. what is the use and where I use…. Can u sent any tutorial material with examples to my mail id

Thanks..

Rams
Reply
Lakshmi on October 2, 2015 at 1:44 pm

Hi guys,

KIndly help me on macro.

you have any book or CD or any other source of Macro. because i am a beginner and i need to work on Macros.. But i dont know much about it.. I know how to record macro but I do not how to write macro Can you please help me
Reply
Areshia on October 7, 2015 at 4:45 am

i make macro on change selection, it work when ever i change selection it run. but i want want to run macro when only particular cell selection change.

Please help me in this regard

my code is this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set Target = Range(“H9”)
If Target.Value = “ACC” Or Target.Value = “ACC” Then
Call Hide_1
Else
Call UnHide_2
End If

End Sub
Reply
Prashali on December 18, 2015 at 8:30 am

Hi

how can i give error message if the cell is selected No by a drop down list
i want error saying “Approval Mandatory- Please Attach”

Please help
Reply
Rajesh Kumar on January 7, 2016 at 6:52 am

Hi, I am a new learner in excel and want to learn macro from begining so could you please hel me on this …

Thanks in Advance

Rajesh k
Reply
- rose on January 8, 2016 at 5:52 am
  
  Hello
  
  I am a beginner and i do not know anything about macros. But I want to learn macro in depth. what is the use and where I use…. Can u sent any tutorial material with examples to my mail id
  
  Thanks..
  
  Rams
  Reply
Uche Uche on March 3, 2016 at 12:32 pm

I am interested in learning about macros but get confused at the beginning about the syntax and how I can develop it for my own. could you please help me on how to develop the syntax.
Thanks Uche in Nigeria
Reply
parthi ban on March 28, 2016 at 6:57 am

Hello

I am a beginner and i do not know anything about macros. But I want to learn macro in depth. what is the use and where I use…. Can u sent any tutorial material with examples to my mail id

Thanks..
Reply
- Rajeeb lochan on September 14, 2016 at 9:51 am
  
  I am a beginner and i do not know anything about macros. But I want to learn macro in depth. what is the use and where I use…. Can u sent any tutorial material with examples to my mail id
  
  Thanks..
  Reply
anbu on July 28, 2016 at 10:06 am

How can we send emails from excel to gmail ,the name is selected from a dropdown and the email has to be sent
Reply
Saideswararao kilaru on September 15, 2016 at 8:33 am

nice article
Reply
Madhurjya Gogoi on November 30, 2016 at 4:23 pm

Hi Vishwamitra,
Hope you are doing good. Am little good in Excel but need to learn macros too as it is required in my current company. So please let me know some shortcut ways to learn macros completely.

Thanks and Best Regards,
Madhurjya Gogoi
E-Mail: madhurjya@nsiamerica.com
Reply
- Vishwamitra Mishra on December 1, 2016 at 4:48 am
  
  Hi Madhurjya,
  
  Did you look at the material i have got for excel VBA tutorial page. Do provide your feedback on this? http://learnexcelmacro.com/wp/excel-macro-tutorial/
  Reply