If you want Excel to work for you
while you sit back in chair and have a cup of coffee then this Post is for
you!!! Yes you are right; I am talking about Macro in Excel, guys. The literal
meaning of Macro is - on large scale, but do not confuse this with that
meaning, in Excel, Macro means recording your actions and to play those
recorded action in future by hitting the specified keystroke on your keyboard
or toolbar button or an icon in a spreadsheet.
It means that whenever you have some repetitive work to perform in Excel
e.g. a daily, weekly, bi-weekly, monthly, quarterly report etc. then while
preparing that report for the first time you can just start the recording of the
macro and start doing your work to prepare the report and after finishing the
same you can just stop the recording and you are done….. So whenever in future,
if you want to create that report again just hit the keystroke you assigned to
that macro and Excel will take from few seconds to few minutes to prepare that
report which you have created in hours.
One of the most beautiful
features of Macro recording is that while recording is turned ON and if you leave
your computer for another hour or two or whatever time, Excel will not record
anything, because as I already mentioned earlier that Macro will record only your actions.
Guys trust me it’s really easy to
record a Macro that even a kid can do that (because as I told you that,
ultimately it’s your own work and it’s your own actions that you will record
and nobody knows that better then you) but there are few questions for which we
need to find the answer first before we enter into the automated world of
Macros. Things we need to focus on are –
- Should we assign a shortcut key to a Macro or not?
- Where to store a Macro?
- What is “Use Relative Reference”?
- What are the “Security Settings of a Macro”?
(Note: - I’ll discuss
the last two questions in my coming post)
Before we discuss the above
questions let’s first discuss the ways through which we can record a Macro. We
can record a Macro by hitting through any of the three ways mentioned below.
Way # 1: Show Developer Tab
Excel 2007 users –
- - Click the Microsoft Office Button, and then click Excel Options.
- - In the Popular category, under Top options for
working with Excel, select the Show Developer tab in the Ribbon check box, and
then click OK.
Excel 2010/2013 users –
- Click the File tab
- - Click Options, and then click Customize Ribbon
- - In the Customize Ribbon category, in the Main
Tabs list, select the Developer check box, and then click OK
Way # 2: View Tab
- Click on view tab.
- - Go to Macros group (extreme right)
- - Click on drop down menu and choose Record Macros.
Way # 3: Status Bar
- Click on the Macro Recorder button on the Status Bar.
Note :- If you can’t find
the Macro Recorder button on the status bar than right click on the status bar
and click on Macro Recording.
By
going through from any of the above ways you will see a “Record Macro” dialog
box on your screen. You can see in the dialog box the first thing required to
fill in is “Macro Name”. Let me tell you guys that before giving any name to
your Macro just keep in mind few things that name can not contain any spaces or
any special character. You even can’t give a name which starts with a number
but yes after starting a name with an alphabet you can use numbers. Just try to
write a name which is crisp and meaningful.
So
let’s say we want to record a Macro that will write our name in an active cell.
So, let’s give this Macro a name as “Name”.
Now
we need to provide a shortcut to this Macro. Now again few things to take care
of before you choose any shortcut, you can see that in the dialog box it is
written as “Ctrl + “i.e. if you write any alphabet in the box, that alphabet
with control key will be the shortcut for this Macro. But keep in mind one
thing that almost every alphabet is already assigned as a shortcut with control
key e.g. if I use “C” than “Ctrl + C” will be the shortcut to run this Macro in
future and this in turn means that this will replace your default shortcut i.e.
in future if this workbook is opened in which you will store the macro and if
you will press “Ctrl + C” to copy any content (which is a usual thing) but
instead of copying, Excel will run this Macro. So, now the question comes that
how we can assign a shortcut key?..... the answer to this question is, instead
of just pressing an alphabet on your keyboard , press “Shift + alphabet “ e.g.
in this case we will use “Shift + N”.
(Note: - It is not mandatory to always assign a
shortcut key to a Macro. We can leave this field blank and if we want to run
this Macro then we can use “Alt + F8” to view the list of all the Macros we
have created.)
Where to store the Macro? (As I
told you earlier, that whenever we record a Macro, only your actions will be
recorded and in the backstage of Excel, these actions will be saved as
different statements. So what happens is, whenever we run the Macro, Excel execute
these statements one by one so quickly that it perform all the task in few
minutes or few seconds) so if excel is asking you that where to store the
Macro, it means that where you want excel to store the coding.
In this drop down you will see
three options - This Workbook, New Workbook & Personal Macro Workbook.
Now let’s discuss these options,
the first one is “This Workbook”, if you will choose this option then the Macro
will be saved in the current Workbook (i.e. the coding of the Macro will be saved
in the current Workbook) and before saving this workbook you will need to save
it as “Macro Enabled Workbook” and If you will choose to store the Macro in
“New Workbook” then a new Workbook will open automatically and the coding will
be saved in that Workbook and again before saving it you will need to save it
as “Macro Enabled Workbook”. But if you will choose to store the Macro in
“Personal Macro Workbook” then Excel will generate this Workbook and coding of
this Macro will be saved in “Personal Macro Workbook” but we can’t see this
workbook as it is always hidden and you will need not to save it as “Macro
Enabled Workbook” (You can unhide it by using unhide command under View Tab in
the ribbon).
If you want to run any Macro in
future than do remember one thing that the Workbook in which you have saved the
Macro (or in other words, in which the coding is saved) should be open at that time, otherwise you won’t be able to use the
macro. In this case, if you choose to store the macro in either “This Workbook”
or “New Workbook” than these should be open so that you can use these macro
anywhere in your excel program but this is not the case with “Personal Macro
Workbook” because as I already mention that this is always open but hidden.
For our example, let’s choose
“Personal Macro Workbook”.
The
last part of this dialog box is Description. As we can’t explain everything
about the Macro by its “Macro Name” so we can use “Description”. This will help
us to know which Macro will do what.
Now just press OK and the
recording will start. You can also see the color of the Macro recorder (in the
left corner of the status Bar is changed to Blue).Now just type your name in
the active cell and then press enter. After doing so just stop the recording.
You can stop the recording from either of the ways I discussed in the beginning
of this post.
You can test this Macro by
pressing “Ctrl + Shift + N” and you will see that your name will appear where
your active cell was but after that cursor will rest just where you left it
while recording e.g. in my example it will come back to cell “A2”. It means
that excel is capturing the cell address (A2) that is way if you will run this
Macro anywhere it will write your name in that very cell but ultimately the
cursor will rest at “A2”.
(In my next post I’ll explain in
detail that how we can avoid this situation and make excel use relative
references instead of fixed references while recording a Macro. I am also going
to write a post on how to Edit or Delete a Macro)
After this, when you will try to
close the Excel Program, Excel will ask you that do you want to save the
changes you made? So just click yes and now you are free to close the Excel
Program.
Note: - I would like to advise
you to use all the three options available in “Store Macro in” one by one so
that you will have a much better idea than you got after just reading this post.
And after doing so if you will face any problem just write the same in the comments
below.
If you want to learn Basic &
Advanced Excel or VBA then click here and I’ll be there to help you. Or if you want to buy my Video
tutorials then click here. Hope you enjoyed this post. Happy learning!!!