HTTP://WWW.LEARNEXCELMACRO.

COM

Click to Send a Feedback to Me

Excel Macro Tutorial for beginners

First Step to make you excellent in Excel VBA

http://www.learnexcelmacro.acom
Vishwamitra Mishra

9/11/2012

This is free E-Book for Excel Macro Beginners. Here you will learn very basic things about Excel Macro.
 Contents

ABOUT THIS E-BOOK IS IT USEFUL TO YOU OR NOT? .................................................... 3

WHAT IS EXCEL MACRO?........................................................................................ 3
WHERE DO YOU FIND MACRO? ................................................................................ 3
HOW TO MAKE DEVELOPER TAB VISIBLE IN EXCEL RIBBON: ............................................. 4
WHAT IS DEVELOPER TAB? .................................................................................. 4
STEPS TO ADD DEVELOPER TAB IN YOUR EXCEL RIBBON:.............................................. 5
WHERE TO WRITE THE VBA CODE IN EXCEL: ................................................................ 6
AN OVERVIEW OF VBA CODE WINDOW.................................................................. 7
I) SECTION 1 (EXCEL OBJECT WINDOW): ........................................................... 7
II) SECTION 2 (VBA CODE WINDOW): ............................................................... 7
III) SECTION 3 (PROPERTY WINDOW): ................................................................ 8
IV) SECTION 4 (WATCHES WINDOW FOR DEBUGGING): .......................................... 9
HOW TO WRITE YOUR FIRST MACRO: ....................................................................... 10
WHAT IS SELECTIONCHANGE EVENT FOR?.............................................................. 10
HOW TO RUN A PROGRAM IN A DEBUG MODE: .......................................................... 12
HOW TO USE ACTIVEX CONTROLS LIKE BUTTON, TEXT BOX ETC. IN EXCEL: ......................... 15
COMBOBOX .................................................................................................... 18
LIST BOX:....................................................................................................... 18
SPIN BUTTON.................................................................................................. 18
TOGGLE BUTTON: ............................................................................................ 18
HOW TO RECORD MACRO IN EXCEL ......................................................................... 19
HOW TO RUN MACRO IN EXCEL.............................................................................. 21

http://www.learnexcelmacro.com Page 2
About this E-Book Is it useful to you or Not?

Dear Friend,

First of all I would like to thank you for downloading this E-Book. This E-Book is basically for
the people who want to start coding in Excel VBA and make their day-to-day work easier by
writing small codes. If the answer of any of the question below is NO, then this is the right
book for you.

1. Do you know, what is Excel VBA?

2. Do you know, how and where to write Macro in Excel?
3. Do you know the basic syntax which requires while writing Excel Macro?
4. Do you know how to enable developer Tab in Excel 2007 - onwards?
5. Do you know how to record a Macro in Excel?
6. Do you know how to run an Excel Macro in Excel?
7. Do you know how to debug an Excel Macro Code?
8. Do you know how to add Controls like Text Boxes, Command Button etc. in Excel
Sheet?
9. Do you know how to associate a recorded macro to simple command button?
10. Do you know how to display a message box in Excel Macro?

So, did you find any of the above questions which have your answer as NO?

If not, sorry this book may not be useful to you, still you can go through it and give me some
feedback and areas to be improved etc.

If yes, go ahead you have chosen the right E-Book. This book will not make you awesome in
Excel VBA but it will definitely enable to become awesome in excel VBA. Its like the first
step towards making you excellent in Excel VBA. As you know that the foundation of any
construction is more important than the whole construction. If the foundation is strong, your
construction is going to be very much strong.

I will not waste any more time of yours in this question answer session and lets begin

What is Excel Macro?

Macro is really great tool. 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.

Where do you find Macro?

http://www.learnexcelmacro.com Page 3
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.
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. Later in
this Book, you will see both the techniques How to write macro in Excel and How to record
macro in Excel.

Before you jump in to writing VBA code lets see few important points which will be required
while writing or recording VBA code.

How to make Developer Tab Visible in Excel Ribbon:

What is Developer Tab?

This is the Tab where all the Excel VBA related options, Controls, settings etc. will be visible. In Excel
2007 onwards all the VBA related stuffs are grouped under Developer Tab. By default Developer Tab
is not visible in the ribbon as shown in below Image

http://www.learnexcelmacro.com

So before you start Excel VBA, you need to know how to make Developer Tab visible in the Excel
Ribbon.

http://www.learnexcelmacro.com Page 4
Steps to Add Developer Tab in your Excel Ribbon:

1. Click on Office Button in the Left-Top Corner of the Excel Workbook.

2. Click on Excel Options

3. Go to Popular Tab of this Excel Option

4. Now Check the Check Box to Show the

Developer Tab in the Ribbon.

5. Click Ok

6. Now Developer Tab in Visible in your Excel

Ribbon:

http://www.learnexcelmacro.com Page 5
Now you can see the Developer Tab visible in your Excel Workbook. Now it
will be always visible until you go back and uncheck the check box which
you have checked it before to display.

You can also watch video tutorial for the same thing on YouTube as well.

You can also refer the tutorial for the beginners on my site:

http://www.learnexcelmacro.com/2011/10/add-developer-tab-in-excel-2010-and-2007/

Where to write the VBA Code in Excel:

Its one of the most frequently asked question by any beginner who just want to start writing excel
macro. So lets not wait and start coding

Easy Steps to Open VBA Coding window from a Workbook:

1. Open your workbook for which you want to write VBA code
2. Press Alt + F11 from your keyboard
3. VBA Code window will appear as shown below

http://www.learnexcelmacro.com Page 6
 1 2

3 4
An Overview of VBA Code Window

Note: Lets discuss what all these main 4 sections are

i) Section 1 (Excel Object Window): This is the section where you will see all the
Worksheets of your workbook. At the end ThisWorkbook is also there. Suppose you
want to write some code in Sheet1 then double Click on Sheet1. Section 2 which is
greyed out currently will enable you for writing the VBA code in it.
ii) Section 2 (VBA Code Window): This is the area where VBA code is written. This
area looks like a simple notepad but when you type anything in there, automatically it
will be compiled by the Excel VBA compiler. For example: In the section 2 (not when it
is greyed out. To enable this double click on any of the sheet name in the Section 1 as
shown in the above image) Type Hello World. Automatically this will be compiled
and one error message is thrown and the Text will be highlighted in the Red Colour. It
understands the English like language but unfortunately it does not understand our
simple English grammar to perform certain operations. Like other languages VBA too
has its own Syntax and formats.

http://www.learnexcelmacro.com Page 7
 iii) Section 3 (Property Window): This is a property window. While doing VBA
programming at any point of time whenever you
select any Object or Sheet, automatically this
window will start showing you the properties of
that selected object. In this case since we have
selected the Sheet1, so it is displaying the
properties of the Sheet1. In this property window,
the first field (Name) is the name of the Object
which used during the coding. Each Object can be
referred by this name of that Object as this is the
unique name. This is not used for displaying
purpose. For example in this Case, if we change
the first field (Name) to VISH then also the
display name of that Sheet will remain same as
Sheet1. Shown in below image:

http://www.learnexcelmacro.com Page 8
 If you have to change the Display Name of the Sheet then change the Name field value of the
property window. Refer the below Image

Note: Display Name can accept Spaces in between but the Object can not accept any space in
between. If you provide any Name in between then it will throw an Error.

iv) Section 4 (Watches Window for Debugging): This section is used only while
debugging a code. When you are running your code in debugging mode that time you
can add as many watches as you can for different variables, Objects etc. to see their
values live. We will discuss more about this section later.

http://www.learnexcelmacro.com Page 9
So this was an overview how to open the VBA code in Excel. We have also discussed about all the 4
main section of this VBA code window.

Now we will learn how to write the First Macro in Excel VBA.

How to write your First Macro:

Before we go and write our first Excel Macro, lets discuss few points about the Section2 where we
will be writing the code.

After double clicking on the Sheet1 in section1,

Section 2 got enabled. Here you can see there are
two drop-downs at the top. As shown in the
Image. First DropDown Holds the name of all the
Objects available in that Sheet and the Second
drop down displays all the Events available for the
selected Objects from the Dropdown 1. Since on
Sheet1 there are no Objects added so far only one
option will be available as Worksheet

Dropdown 1 has one Option as Worksheet.

Select Worksheet from the drop down1. All the
events which can be triggered on this Worksheet
will be automatically populated in DropDown2 as
shown in this picture.

As soon as you selected Worksheet in the first

dropdown, by default in drop down 2,
SelectionChange event will be selected. As a
result in your code are you will find the below
code (refer the image)

In VBA all the code must always be written between Sub and End Sub.

What is SelectionChange Event For?

As the name suggests, this event triggers as and when the selection in the Sheet1 (Changed Name
VISH) Changes. It means that whatever VBA statements will be written between this Event, it will be
automatically executed as soon as the selection on the Sheet changes.

Maintaining the tradition of first programme of any Programming Language, we will also display a
message Hello World on every selection change in Sheet1.

http://www.learnexcelmacro.com Page 10
The Syntax to display a simple message box is as follows: MsgBox Your Message Here

You can be lazy enough here to type letter M and B in capital letter and type it as msgbox. See
what excel does Since msgbox is a known keyword to the Excel compiler, it will automatically make
the msgbox in a proper case. It means msgbox will turn to MsgBox

So your first VBA code will like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

MsgBox "Hello World"

End Sub

Save your Workbook. Did you realize that you have written your first program? Congratulations!!

Now its time to see your macro runningvery happy now? Ok lets see it running

Simple Now in your workbook as soon as you select any Cell one message box will appear with
message as Hello World as shown in the above image.

Congratulations!! Isnt very easy to learn writing macro in Excel??

http://www.learnexcelmacro.com Page 11
Now we will see how to debug a Program or how to run a program in debug mode.

How to Run a Program in a debug Mode:

I will modify our first program a little bit and try to run that program in debug mode. The modified
code is as below:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim message As String

message = "Hello World"

MsgBox message

End Sub

Note: I have declared a variable named message. In this variable I am storing a message Hello
World. And then finally I am displaying the value stored in that variable in a message box. Now lets
start to run the program in debug mode.

1. First Open the VBA Code window

2. Select the line of the code where you want to debug the code
3. Now press the function Key F9 or Double click on the Grey Strip to put the break point.
4. Once the Break Point is put, that statement will be highlighted in Dark Red Colour as shown
below:

http://www.learnexcelmacro.com Page 12
 5. Once the break point is put, now do the operation because of which that event will trigger.
Like in this case, this event will trigger once we change the selection in the Excel Sheet.
6. After changing the selection the Event will trigger and Code will automatically stop at the
point where you have put your break point as shown in the below image. The break point line
would be highlighted now in Yellow Colour as the execution is stoped at that point. Now you
can go line by line and execute the statements thereafter.

The above YELLOW highlighted statement is not yet executed but before that all the statement are
executed.

7. Press F8 each time to execute a single statement.

8. In the above code message variable is declared but there is no value assigned yet, because the
statement to assign Hello World in message variable, is not yet executed. But we are not
sure what is value message variable has as of now. To monitor the value of message variable
we can add a watch of message variable.
a. Select the variable message in that statement and Right Click Add Watch

http://www.learnexcelmacro.com Page 13
 9. Now we have added a watch for the variable message. To execute the statement Press F8.
The statement to assign Hello World to the variable message will be executed and the value
in variable message should be Hello World now. Refer the below picture for more clarity:

http://www.learnexcelmacro.com Page 14
 10. Similarly keep pressing F8 and each statement will keep executing and you can easily monitor
each of the flow of the program.

Note: When your debugging is over and you want program to run in one go then press F5. It will
run the remaining statements in one go.

Now you know how and where to write Excel Macro in Excel. Also you learnt how to run a program in
debug mode. While running the program in debug mode, you got to know how to add Watch in Excel
VBA.

You might have seen in excel, some command buttons, text boxes, combo boxes etc. which looks very
interesting. It gives a better User friendly interface to the user and also it gives you a nice feeling. So
are you ready to know how to user command button in Excel.

How to use Activex Controls like Button, Text Box etc. in Excel:
First we will see how to add a command button in your excel worksheet. Follow the below steps to
add command button in excel sheet.

http://www.learnexcelmacro.com Page 15
 1. Select the Developer Tab in your Excel sheet
2. Go to Insert Activex Controls Command button

3. Click on the Command Button and Click on the sheet where you want to place your Command
Button.

4. Nice your command button is here. But now you are thinking the Caption of this button is
not helpful. User will not understand what this buttons will after clicking on it. So dont worry
you can give your own name and caption to this button. As we have already discussed about
the Properties window of the VBA Code window. Now since you are on your excel sheet not
on the VBA code window, so you need to click on Properties button available in the Ribbon
or you can right click on the Command Button object and Choose the option Properties as
shown below:

http://www.learnexcelmacro.com Page 16
Note: From this Properties window you can change the Font Colour, Back Colour etc of your any
Object. Like in the above case, it is Command Buttons.

You have placed your Command Button in your sheet. Now lets focus on how to write a code for this
button. It means once you click on this button, some operations must be performed.

To write code for this button, double click on the button

Once you double click on the command button, you will be automatically to a Click Event of the
button. Here you between Sub and End Sub you can write your code same as you have seen in the
previous example of SelectionChange event of the worksheet. Type this statement MsgBox "Hello
World" between Sub and End Sub.

Private Sub CommandButton1_Click()

MsgBox "Hello World"

End Sub
http://www.learnexcelmacro.com Page 17
Now we need to run the program and see if it is functioning properly.

To run this command button, we need to come out of the Design Mode. You would have noticed that
as soon as you added the Command Button, Excel macro will go in Design Mode.

Once you come out of the design mode, Click on the Command button. As soon as you click on that,
once message box will display with a message Hello World as shown in the below image.

Similarly you can add Text Boxes, List Boxes, Combo Boxes etc. Also you can write the code for them.

To know more about the Commandbutton and other controls read the Articles which is available on
clicking on the below link:

Combobox: http://www.learnexcelmacro.com/2012/03/excel-macro-tutorial-combobox-in-excel-
macro/

List Box: http://www.learnexcelmacro.com/2012/03/excel-macro-tutorial-how-to-use-listbox-in-

excel/

Spin Button :http://www.learnexcelmacro.com/2011/12/use-of-spin-button-in-excel/

Toggle Button: http://www.learnexcelmacro.com/2012/02/toggle-button-in-excel/

http://www.learnexcelmacro.com Page 18
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:

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

2. Go to Developer Tab of the workbook. (How to add Developer Tab in Excel-2010
and 2007 Workbook)
3. Click on Record Macro button in the right hand side of the Top Menu Bar

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.

5. Click on Record Macro Button shown above

http://www.learnexcelmacro.com Page 19
 Note:

i) In the below 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.
ii) You can give the location, where
you want to store this particular
macro. By default it will be stored
in This workbook.
iii) Under description field you can
give a brief description about the
Macro.

6. Now click OK and do some operation on the excel sheet. Like formatting of the cell or
sorting some of the values etc.
7. Once the macro starts recording then the sign of the recording button gets changed as
shown in the image ahead.
8. Now whatever operation you do on Excel, it will get recorded.
For example: Do some formatting on the Sheet1. Once the formatting done then again
Click on the same button as highlighted to stop the recording. Following is the
formatting which I have done on the sheet 1.

9. I have done the above formatting and wrote few column names while recording.
10. To view this particular recorded macro, right click on any of the Sheet Name -> View
Code
11. In left hand side pane, under VBA Project of that workbook, Expand the Module
12. One Module would have been created there Module 1. Click on this.
13. You can see the recorded code with the given Macro Name.

http://www.learnexcelmacro.com Page 20
Now your macro is recorded. Now let us see how to run the Recorded Excel Macro.

How to Run Macro in Excel

You can run this Recorded Macro in two ways:

1. By Pressing Run Button in the VBA Code Window

2. By Pressing Function Key Alt + F8

1. To run this 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.

http://www.learnexcelmacro.com Page 21
2. Now Click on the Run Button to Run the Written Macro.

3. Click on Run or Press F5

http://www.learnexcelmacro.com Page 22
4. After running the Macro, See the Result.

If you do not want to run the Macro, by Pressing the Run Button in VBE, then follow the
below steps:

1. Press Alt + F8
2. Select Your Macro from the Drop Down
3. Click on Run

http://www.learnexcelmacro.com Page 23
 Thank you
Click to Send a Feedback to Me

Thank you so much for reading this e-Book. Kindly spare few minutes to write
a feedback.

Do give me feedback on this. Based on your feedback soon the next version of
the free e-book will be released.

http://www.learnexcelmacro.com Page 24