How to Write Macros in Excel: Step by Step

Tutorial
As humans, we are creatures of habit. There are certain things that we do on a daily basis
every working day. Wouldn't it be better if there were some magical way of pressing a
single button and all of our routine tasks are done? I can hear you say yes. In a
nutshell, a macro is the magical single click button.

We will cover the following topics in this tutorial.

 What is a macro?
 The importance of macros in Excel
 What is VBA in a layman's language?
 Macro Basics
 Step by step example of recording macros in Excel

What is a macro?
A macro is a piece of programming code that runs in Excel environment and helps
automate routine tasks. In a layman's language, a macro is a recording of your routine
steps in Excel that you can replay using a single button.

The importance of macros in Excel

Let's say you work as a cashier for a water utility company. Some of the customers pay
through the bank and at the end of the day, you are required to download the data from the
bank and format it in a format that meets your business requirements.

You can import the data into Excel and format. The following day you will be required to
perform the same ritual. It will soon become boring and tedious. Macros solve such
problems by automating such routine tasks. You can use a macro to record the steps of

 Importing the data

 Formatting it to meet your business reporting requirements.

What is VBA in a layman's language?

VBA is the acronym for Visual Basic for Applications. It is a programming language that
Excel uses to record your steps as you perform routine tasks. You do not need to be a
programmer or a very technical person to enjoy the benefits of macros in Excel. Excel has
features that automatically generated the source code for you. Read the article on Vba for
more details.

Macro Basics
Macros are one of the developer features. By default, the tab for developers is not
displayed in excel. You will need to display it via customize report

Macros can be used to compromise your system by attackers. By default, they are disabled
in excel. If you need to run macros, you will need to enable running macros and only run
macros that you know come from a trusted source

If you want to save macros, then you must save your workbook in a macro-enabled format
*.xlsm

The macro name should not contain any spaces.

Always fill in the description of the macro when creating one. This will help you and others
to understand what the macro is doing.

Step by step example of recording macros in Excel

We will work with the scenario described in the importance of macros excel. We will work
with the following CSV file.

You can download the above file here

Download the above CSV File & Macros

We will create a macro enabled template that will import the above data and format it to
meet our business reporting requirements.

Enable Developer Option

To execute VBA program, you have to have access to developer option in Excel. Enable the
developer option as shown below and pin it into your main ribbon in Excel.

Step 1) Go to main menu "FILE" and selection option "Options."

Step 2) Select "Options" from the menu list as shown in screen shot below.

Step 3) Now another window will open, in that window do following things

 Click on Customize Ribbon

 Mark the checker box for Developer option
 Click on OK button
Step 4) You will now be able to see the DEVELOPER tab in the ribbon

First, we will see how we can create a command button on the spreadsheet and execute the
program.

 Create a folder in drive C named Bank Receipts

 Paste the receipts.csv file that you downloaded
 1. Click on the DEVELOPER tab
2. Click on Record Macro as shown in the image below

You will get the following dialogue window

 1. Enter ImportBankReceipts as the macro name.
2. Step two will be there by default
3. Enter the description as shown in the above diagram
4. Click on "OK" tab

 Put the cursor in cell A1

 Click on the DATA tab
 Click on From Text button on the Get External data ribbon bar

You will get the following dialogue window

 Go to the local drive where you have stored the CSV file
 Select the CSV file
 Click on Import button

You will get the following wizard

Click on Next button after following the above steps
Follow the above steps and click on next button

 Click on Finish button

 Your workbook should now look as follows

Make the columns bold, add the grand total and use the SUM function to get the total
amount.
Now that we have finished our routine work, we can click on stop recording macro button
as shown in the image below

Before we save our work book, we will need to delete the imported data. We will do this to
create a template that we will be copying every time we have new receipts and want to run
the ImportBankReceipts macro.

 Highlight all the imported data

 Right click on the highlighted data
 Click on Delete
 Click on save as button
 Save the workbook in a macro enabled format as shown below
  Make a copy of the newly saved template
 Open it
 Click on DEVELOPER tab
 Click on Macros button

You will get the following dialogue window

 1. Select ImportBankReceipts
2. Highlights the description of your macro
3. Click on Run button

You will get the following data

Congratulations, you just created your first macro in excel.

Summary
Macros simplify our work lives by automating most of the routine works that we do.
Macros in Excel are powered by Visual Basic for Applications.

 
How to Create Visual Basic for
Applications (VBA) in Excel with Examples
Everybody in this country should learn how to program a computer... because it teaches
you how to think." -Steve Jobs

I wish to extend the wise words of Steve Jobs and say everyone in the world should learn
how to program a computer. You may not necessary end up working as a programming or
writing programs at all but it will teach you how to think.

In this tutorial, we are going to cover the following topics.

 What is VBA?
 Why VBA?
 Personal & business applications of VBA in excel
 Visual Basic For Applications VBA basics
 Step by step example of creating a simple EMI calculator in Excel

What is VBA?
VBA stands for Visual Basic for Applications. Before we go into further details, let's look
at what computer programming is in a layman's language. Assume you have a maid. If you
want the maid to clean the house and do the laundry. You tell her what to do using let's say
English and she does the work for you. As you work with a computer, you will want to
perform certain tasks. Just like you told the maid to do the house chores, you can also tell
the computer to do the tasks for you.

The process of telling the computer what you want it to do for you is what is known as
computer programming. Just as you used English to tell the maid what to do, you can also
use English like statements to tell the computer what to do. The English like statements fall
in the category of high level languages. VBA is a high level language that you can use to
bend excel to your all powerful will.

VBA is actually a sub set of Visual Basic 6.0 BASIC stands for Beginners All-Purpose
Symbolic Instruction Code.

Why VBA?
 It uses English like statements to write instructions
 Creating the user interface is like using a paint program. You just have to drag, drop and
align the graphical user interface controls.
 Short learning curve. From day one that you start learning, you can immediately start
writing simple programs.
 Enhances the functionality of excel by allowing you to make excel behave the way you want
it to

Personal & business applications of VBA in excel

For personal use, you can use it for simple macros that will automate most of your routine
tasks. Read the article on Macros for more information on how you can achieve this.

For business use, you can create complete powerful programs powered by excel and VBA.
The advantage of this approach is you can leverage the powerful features of excel in your
own custom programs.

Visual Basic for Applications VBA basics

Before we can write any code, we need to know the basics first. The following basics will
help you get started.

 Variable – in high school we learnt about algebra. Find (x + 2y) where x = 1 and y = 3. In
this expression, x and y are variables. They can be assigned any numbers i.e. 1 and 3
respective as in this example. They can also be changed to say 4 and 2 respectively.
Variables in short are memory locations. As you work with VBA, you will be required to
declare variables too just like in algebra classes
 Rules for creating variables
o Don't use reserved words – if you work as a student, you cannot use the title
lecturer or principal. These titles are reserved for the lecturers and the school
authority. Reserved words are those words that have special meaning in Vba and as
such, you cannot use them as variable names.
o Variable names cannot contain spaces – you cannot define a variable named first
number. You can use firstNumber or first_number.
o Use descriptive names – it's very tempting to name a variable after yourself but
avoid this. Use descriptive names i.e. quantity, price, subtotal etc. this will make
your VBA code easy to read
 Arithmetic operators - The rules of Brackets of Division Multiplication Addition and
Subtraction (BODMAS) apply so remember to apply them when working with expressions
that use multiple different arithmetic operators. Just like in excel, you can use
o + for addition
o - for subtraction
o * for multiplication
o / for division.
 Logical operators - The concept of logical operators covered in the earlier tutorials also
apply when working with VBA. These include
o If statements
o OR
o NOT
o AND
o TRUE
o FALSE

Enable Developer Option

 Create a new workbook
 Click on the ribbon start button
 Select options
 Click on customize ribbon
 Select the developer checkbox as shown in the image below
 Click OK
You will now be able to see the DEVELOPER tab in the ribbon

VBA Hello world

Now we will demonstrate how to program in VBA. All program in VBA has to start with
"Sub" and end with "End sub". Here the name is the name you want to assign to your
program. While sub stands for a subroutine which we will learn in the later part of the
tutorial.

Sub name()

. End Sub

We will create a basic VBA program that displays an input box to ask for the user's name
then display a greeting message
This tutorial assumes you have completed the tutorial on Macros in excel and have enabled
the DEVELOPER tab in excel.

 Create a new work book

 Save it in an excel macro enabled worksheet format *.xlsm
 Click on the DEVELOPER tab
 Click on INSERT drop down box under controls ribbon bar
 Select a command button as shown in the image below

Draw the command button anywhere on the worksheet

You will get the following dialogue window

 Rename the macro name to btnHelloWorld_Click
 Click on new button
 You will get the following code window
Enter the following instruction codes

Dim name As String

name = InputBox("Enter your name")

MsgBox "Hello " + name

HERE,

 "Dim name as String" creates a variable called name. The variable will accept text, numeric
and other characters because we defined it as a string
 "name = InputBox("Enter your name")" calls the built in function InputBox that displays
a window with the caption Enter your name. The entered name is then stored in the name
variable.
 "MsgBox "Hello " + name" calls the built in function MsgBox that display Hello and the
entered name.

Your complete code window should now look as follows

 Close the code window
 Right click on button 1 and select edit text
 Enter Say hello

 Click on Say Hello

 You will get the following input box
  Enter your name i.e. Jordan
 You will get the following message box

Congratulations, you just created your first VBA program in excel

Step by step example of creating a simple EMI calculator

in Excel
In this tutorial exercise, we are going to create a simple program that calculates the EMI.
EMI is the acronym for Equated Monthly Instalment. It's the monthly amount that you
repay when you get a loan. The following image shows the formula for calculating EMI.

The above formula is complex and can be written in excel. The good news is excel already
took care of the above problem. You can use the PMT function to compute the above.

The PMT function works as follows

=PMT(rate,nper,pv)

HERE,
  "rate" this is the monthly rate. It's the interest rate divided by the number of payments per
year
 "nper" it is the total number of payments. It's the loan term multiplied by number of
payments per year
 "pv" present value. It's the actual loan amount

Create the GUI using excel cells as shown below

Add a command button between rows 7 and 8

Give the button macro name btnCalculateEMI_Click

Click on edit button

Enter the following code

Dim monthly_rate As Single, loan_amount As Double, number_of_periods As Single, emi As Double

monthly_rate = Range("B6").Value / Range("B5").Value
loan_amount = Range("B3").Value
number_of_periods = Range("B4").Value * Range("B5").Value
emi = WorksheetFunction.Pmt(monthly_rate, number_of_periods, -loan_amount)
Range("B9").Value = emi

HERE,

 "Dim monthly_rate As Single,…" Dim is the keyword that is used to define variables in
VBA, monthly_rate is the variable name, Single is the data type that means the variable will
accept number.
 "monthly_rate = Range("B6").Value / Range("B5").Value" Range is the function used to
access excel cells from VBA, Range("B6").Value makes reference to the value in B6
 "WorksheetFunction.Pmt(…)" WorksheetFunction is the function used to access all the
functions in excel
The following image shows the complete source code

 Click on save and close the code window

 Test your program as shown in the animated image below

Example 2
Step 1) Under Developer tab from the main menu, click on "Visual Basic" icon it will open
your VBA editor.

Step 2) It will open a VBA editor, from where you can select the Excel sheet where you
want to run the code. To open VBA editor double click on the worksheet.
It will open a VBA editor on the right-hand side of the folder. It will appear like a white
space.

Step 3) In this step we going to see our fist VBA program. To read and display our program
we need an object. In VBA that object or medium in a MsgBox.

 First, write "Sub" and then your "program name" (Guru99)

 Write anything you want to display in the MsgBox (guru99-learning is fun)
 End the program by End Sub
Step 4) In next step you have to run this code by clicking on the green run button on top of
the editor menu.

Step 5) When you run the code, another window will pops out. Here you have to select the
sheet where you want to display the program and click on "Run" button

Step 6) When you click on Run button, the program will get executed. It will display the
msg in MsgBox.
Download the above Excel Code

Summary
VBA stands for Visual Basic for Application. It's a sub component of visual basic
programming language that you can use to create applications in excel. With VBA, you can
still take advantage of the powerful features of excel and use them in VBA.
Excel VBA Variables, Data Types &
Constant
Variables are used in almost all computer program and VBA is no different. It's a good
practice to declare a variable at the beginning of the procedure. It is not necessary, but it
helps to identify the nature of the content (text, data, numbers, etc.)

In this tutorial, you will learn-

 VBA Variables
 VBA Data-Types
 Constant in VBA

VBA Variables
Variables are specific values that are stored in a computer memory or storage system.
Later, you can use that value in code and execute. The computer will fetch that value from
the system and show in the output. Each variable must be given a name.

To name the variable in VBA, you need to follow the following rules.

 It must be less than 255 characters

 No spacing is allowed
 It must not begin with a number
 Period is not permitted

Here are some example for Valid and Invalid names for variables in VBA.

Valid Names Invalid Names

My_Watch My.Watch
NewCar1 1_NewCar (not begin with number)
EmployeeID Employee ID ( Space not allowed)

In VBA, we need to declare the variables before using them by assigning names and data
type.

In VBA, Variables are either declared Implicitly or Explicitly.

 Implicitly: Below is an example of a variable declared Implicitly.

o label=guru99
o volume=4
 Explicitly: Below is an example of variable declared Explicitly. You can use "Dim"
keyword in syntax
o Dim Num As Integer
o Dim password As String
VBA variable is no different than other programming languages. To declare a variable in
VBA you use the keyword "Dim."

Syntax for VBA Variable,

To declare a variable in VBA, type Dim followed by a name:

Sub Exercise ()
Dim <name>
End Sub

Before we execute the variables we have to record a macro in Excel. To record a macro do
the following -

Step 1): Record the Macro 1

Step 2) : Stop Macro 1

Step 3): Open the Macro editor, enter the code for variable in the Macro1

Step 4): Execute the code for Macro 1

Example, for VBA Variable

Sub Macro1()
Dim Num As Integer
Num = 99
MsgBox " Guru " & Num
End Sub

When you run this code, you will get the following output in your sheet.

Excel VBA Data-Types

Computer cannot differentiate between the numbers (1,2,3..) and strings (a,b,c,..). To make
this differentiation, we use Data Types.

VBA data types can be segregated into two types

 Numeric Data Types

Type StorageRange of Values
Byte 1 byte 0 to 255
Integer 2 bytes-32,768 to 32,767
Long 4 bytes-2,147,483,648 to 2,147,483,648
-3.402823E+38 to -1.401298E-45 for negative values 1.401298E-45 to
Single 4 bytes
3.402823E+38 for positive values.
-1.79769313486232e+308 to -4.94065645841247E-324 for negative
Double 8 bytes values 4.94065645841247E-324 to 1.79769313486232e+308 for
positive values.
Currency 8 bytes -922,337,203,685,477.5808 to 922,337,203,685,477.5807
+/- 79,228,162,514,264,337,593,543,950,335 if no decimal is use +/-
Decimal 12 bytes
7.9228162514264337593543950335 (28 decimal places)

 Non-numeric Data Types

Data Type Bytes Used Range of Values

String (fixed Length) Length of string 1 to 65,400 characters
String (Variable Length) Length + 10 bytes 0 to 2 billion characters
Boolean 2 bytes True or False
Date 8 bytes January 1, 100 to December 31, 9999
Object 4 bytes Any embedded object
Variant(numeric) 16 bytes Any value as large as Double
Variant(text) Length+22 bytes Same as variable-length string

In VBA, if the data type is not specified, it will automatically declare the variable as a
Variant.

Let see an example, on how to declare variables in VBA. In this example, we will declare
three types of variables string, joining date and currency.

Step 1) Like, in the previous tutorial, we will insert the commandButton1 in our Excel
sheet.

Step 2) In next step, right-click on the button and select View code. It will open the code
window as shown below.
Step 3) In this step,

 Save your file by clicking on save button

 Then click on Excel icon in the same window to return the Excel sheet.
 You can see the design mode is "on" highlighted in green

Step 4) Turn off design mode, before clicking on command button

Step 5) After turning off the design mode, you will click on commandButton1. It will show
the following variable as an output for the range we declared in code.

 Name
 Joining Date
 Income in curreny
Constant in VBA
Constant is like a variable, but you cannot modify it. To declare a constant in VBA you use
keyword Const.

There are two types of constant,

 Built-in or intrinsic provided by the application.

 Symbolic or user defined

You can either specify the scope as private by default or public. For example,

Public Const DaysInYear=365

Private Const Workdays=250

Download Excel containing above code

Download the above Excel Code

Summary:

 Variables are specific values that are stored in a computer memory or storage
system.
 You can use "Dim" keyword in syntax to declare variable explicitly
 VBA data types can be segregated into two types
o Numeric Data Types
o Non-numeric Data Types
 In VBA, if the data type is not specified. It will automatically declare the variable as a
Variant
 Constant is like a variable, but you cannot modify it. To declare a constant in VBA
you use keyword Const.
VBA Array: Dynamic, Multidimensional
with Example in Excel
What is an Array?
An array is a memory location capable of storing more than one value. The values must all
be of the same data type. Let's say you want to store a list of your favourite beverages in a
single variable, you can use an array to do that.

By using an array, you can refer to the related values by the same name. You can use an
index or subscript to tell them apart. The individual values are referred as the elements of
the array. They are contiguous from index 0 through the highest index value.

This tutorial assumes you are using Microsoft Excel version 2013. The knowledge still
applies to other versions of Microsoft Excel as well.

In this tutorial, you will learn-

 What are Advantages of arrays?

 Types of arrays
 VBA Array Demonstrated with Example
 Testing our application

What are Advantages of arrays?

The following are some of the benefits offered by arrays

1. Group logically related data together – let's say you want to store a list of students.
You can use a single array variable that has separate locations for student categories
i.e. kinder garden, primary, secondary, high school, etc.
2. Arrays make it easy to write maintainable code. For the same logically related data,
it allows you to define a single variable, instead of defining more than one variable.
3. Better performance – once an array has been defined, it is faster to retrieve, sort,
and modify data.

Types of arrays
VBA supports two types of arrays namely;

 Static – These types of arrays have a fixed pre-determined number of elements that
can be stored. One cannot change the size of the data type of a Static Array. These
are useful when you want to work with known entities such as the number of days
in a week, gender, etc.

For Example: Dim ArrayMonth(12) As String

 Dynamic – These types of arrays do not have a fixed pre-determined number of

elements that can be stored. These are useful when working with entities that you
cannot predetermine the number.

For Example: Dim ArrayMonth(12) As Variant

Syntax for declaring arrays

Static arrays

The syntax for declaring STATIC arrays is as follows:

Dim arrayName (n) as datatype

HERE,

Code Action
1. It declares an array variable called arrayName with a size of n
Dim arrayName and datatype. Size refers to the number of elements that the
(n) datatype array can store.

Dynamic arrays

The syntax for declaring DYNAMIC arrays is as follows:

Dim arrayName() as datatype

ReDim arrayName(4)

HERE,
Code Action
1. It declares an array variable called arrayName without
Dim arrayName ()
specifying the number of elements
datatype
2. It specifies the array size after the array has been defined.
ReDim arrayName(4)

Array Dimensions

An array can be one dimension, two dimensions or multidimensional.

 One dimension: In this dimension, the array uses only one index. For example, a
number of people of each age.
 Two dimensions: In this dimension, the array uses two indexes. For example, a
number of students in each class. It requires number of classes and student number
in each class
 Multi-dimension: In this dimension, the array uses more than two indexes. For
example, temperatures during the daytime. ( 30, 40, 20).

VBA Array Demonstrated with Example

We will create a simple application. This application populates an Excel sheet with data
from an array variable. In this example, we are going to do following things.

 Create a new Microsoft Excel workbook and save it as Excel Macro-Enabled

Workbook (*.xlsm)
 Add a command button to the workbook
 Set the name and caption properties of the command button
 Write the code that populates the Excel sheet

Let do this exercise step by step,

Step 1 – Create a new workbook

1. Open Microsoft Excel

2. Save the new workbook as VBA Arrays.xlsm

Step 2 – Add a command button

Note: This section assumes you are familiar with the process of creating an interface in
excel. If you are not familiar, read the tutorial VBA Subroutines and Functions. It will show
you how to create the interface

1. Add a command button to the sheet

 2. Set the name property to cmdLoadBeverages
3. Set the caption property to Load Beverages

Your GUI should now be as follows

Step 3 – Save the file

1. Click on save as button

2. Choose Excel Macro-Enabled Workbook (*.xlsm) as shown in the image below

Step 4 – Write the code

We will now write the code for our application

1. Right click on Load Beverages button and select view code

 2. Add the following code to the click event of cmdLoadBeverages

Private Sub cmdLoadBeverages_Click()

Dim Drinks(1 To 4) As String

Drinks(1) = "Pepsi"
Drinks(2) = "Coke"
Drinks(3) = "Fanta"
Drinks(4) = "Juice"

Sheet1.Cells(1, 1).Value = "My Favorite Beverages"

Sheet1.Cells(2, 1).Value = Drinks(1)
Sheet1.Cells(3, 1).Value = Drinks(2)
Sheet1.Cells(4, 1).Value = Drinks(3)
Sheet1.Cells(5, 1).Value = Drinks(4)
End Sub

HERE,

Code Action
 It declares an array variable called Drinks. The first array
Dim Drinks(1 To 4) As
index is 1 and the last array index is 4.
String
 Assigns the value Pepsi to the first array element. The
other similar code does the same for the other elements in
Drinks(1) = "Pepsi"
the array.

 Writes the value My Favorite Beverages in cell address A1.

Sheet1.Cells(1, 1).Value = Sheet1 makes reference to the sheet, and Cells(1,1) makes
"My Favorite Beverages." reference to row number 1 and column 1 (B)

 Writes the value of the array element with index 1 to row

Sheet1.Cells(2, 1).Value =
number two of column 1
Drinks(1)

Testing our application

Select the developer tab and ensure that the Design mode button is "off." The indicator is, it
will have a white background and not a coloured (greenish) background. (See image
below)
Click on Load Beverages button

You will get the following results

Download Excel containing above code

Download the above Excel Code

Summary

1. An array is a variable capable of storing more than one value

2. VBA supports static and dynamic arrays
3. Arrays make it easy to write maintainable code compared to declaring a lot of
variables for data that is logically related.
VBA Excel Form Control & ActiveX Control
Creating VBA Form/GUI controls in Excel
GUI is the acronym for Graphical User Interface. The GUI is the part of the program that the
user interacts with. A GUI is made up of controls. These controls can be used in a Form. The
following table shows some of the most commonly used GUI controls in VBA.

S/N Control Description

1 Command Used to execute code

2 Combo Box Used to present a drop down list to the users

Used for true or false values. Multiple check boxes can have true value at the same
3 CheckBox
time.

4 List Box Used to present a simple list to the users

5 Text Box Used to accept user input

6 Scroll Bar Used for to provide a scrollbar for navigation purposes.

Used to increment or decrease numeric values by clicking on up or down arrows

7 Spin Button
that appear in the spin button

Option Used for true or false values. Only one option button can have a true value at a
8
Button time.

9 Label Used to display static text to the user

10 Image Used to display images

 Toggle
11 Button that has pressed and un-pressed states.
Button

In the GUI control,

1. Click on the developer tab

2. Click on Insert Drop down button

You will get the following drop down panel

If you hover the mouse over control, the name of the control will appear as shown below

Adding GUI controls to a spreadsheet

We will now add a command button to our workbook, for that we have to

 Click on Insert drop down

 Click on Command Button (ActiveX Control) as shown in the image above
 Drag and draw the button on the sheet
Setting GUI control properties

We will now set the caption of the button to something more descriptive

1. Right click on the equal button that we have just created

2. Select properties menu
3. You will get the following properties window
  Set the name property to btnButton. The name property is used to identify the control in the
code window. btn is the prefix for the button.
 Set the Caption property to Click Me. The text in the caption property is what the users will
see on the button.

Close the window when you are done.

You will get the following results.

How to use ActiveX control in VBA

In this section, we will see how to incorporate 'commandclick' button in VBA and execute a
program using the button.

Step 1) In this step, click the option "insert button" from the Active X Control. Then select
the command button option from it.
Step 2) To insert "clickcommandbutton1" drag the mouse cursor to Excel sheet.

Step 3) Then right click on the command button and select option "View Code".
Step 4) Check you are on the right sheet. A code editor will open. Enter your code.

 In code, we defined range A1 to A10.

 This range defines the cell number in the Excel sheet from "A1 to A10."
 Next, we define the string "Guru99 VBA Tutorial" in code
 So, when you click on "command button" in excel. It should print "Guru99 VBA Tutorial" in
the cell range "A1 to A10."

Step 5) In next step, save code file and then exit the editor. To return to the Excel file click
the Excel sheet icon on the extreme left.

Step 6) In Excel sheet, you will see Design Mode tab is on. Make sure it is "OFF" or else your
code will not work.
Step 7) When design mode is off, there will be no green highlight on it. Now you can Click
on the command button.

Step 8) Click on "CommandButton1". It will print "Guru99 VBA Tutorial" in the cell range
"A1 to A10".
Download the above Excel Code

Prerequisite
Configure Microsoft Excel

As we see in previous tutorial, make sure your ribbon will have "Developer" icon as shown
here.

Now, rename sheet1 to "VBA Operator" as shown in screen-shot below. Save the workbook
in an Excel Macro-Enabled Workbook (*.xlsm) file extension. (see the image below).
After that, click on Save button.

VBA Arithmetic Operators: Modulus,

Multiplication, Division, Addition
VBA Arithmetic Operators are used to perform arithmetic operations such as adding,
subtracting, dividing or multiplying numbers.

The following table shows the arithmetic operators in VBA

S/N Operator Description Example Output

1 + Addition: This operator is used to add up numbers 2+2 4

2 - Subtraction: This operator is used to subtract numbers 5-3 2

3 * Multiplication: This operator is used to multiply numbers 3*2 6

4 / Division: This operator is used to divide numbers 9/3 3

Exponentiation: This operator is used to raise a number to the

5 ^ 2^3 8
power of another number

6 mod Modulus Operator: Divides a number and returns the reminder 10 mod 3 1

VBA Arithmetic Operators: Modulus, Multiplication, Division, Addition

VBA Arithmetic Operators Example

Add a button to the Excel sheet as we show earlier, and then follow the following points

 Change the name property to btnAdd

 Change the caption property to Add Operator
 Right click on the button
 Select view code
 You will get the following code window
Enter the following code in between Private Sub btnAdd_Click() and End Sub

 Dim x As Integer, z As Integer

o x=2
o z=3
 MsgBox x + z, vbOKOnly, "Addition Operator"
 Click on save button
 Close the code editor window

Let's now execute our code

On the ribbon bar, look for the button Design Mode

If the button is in active state (green background colour), then it's in design mode. You
cannot execute code in this state. If it is not in the active state (white background color),
then it allows you to run the code.

Click on Design Mode button

The button should now appear as follows

Click on Add operator

You will get the following results

Download the above Excel Code

Arithmetic Operators Tutorial exercise

The best way to learn is by practicing. Follow the above steps to create buttons for
subtraction, division, multiplication and exponentiation.

Write the code for the buttons and test them to see if the code executes.

VBA String Operators

VBA String Operators
String data is used to hold data that is made up of numbers, characters, and symbols. "Jul-
2015" is an example of a string data. It is made up of

 Characters (Jul)
 Symbol (-)
 Numbers (2015)

String operators are used to manipulate string data. For example, you can concatenate the
value of July-2015 from the first 3 letters of the month and the year like "Jul-2015".

The following table shows the concatenation string operator.

S/N Operator Description Example Output

Concatenate: This operator is used to concatenate strings "John " &

1 & John Doe
together "Doe"

Example Source Code

MsgBox "John " & "Doe", vbOKOnly, "Concatenate Operator"

Executing the above code produces the following result

Download the above Excel Code

VBA Comparison Operators: Not equal to,

Less than or equal to, Greater than
VBA Comparison operators
These are operators that are used to compare values. Comparison operators include equal to, less
than, greater than and not equal to

The following table shows VBA Comparison Operators.

Comparison operators are used to compare values for validation purposes. Let's say you
are developing a simple point of sale application. In this application, you want to validate
the values entered before you post. In such cases, you can use comparison operators. This
operator will check against the negative numbers or to ensure that the amount paid does
not exceed the billed amount. Comparison operators come in handy in such situations.

S/N Operator Description Example Output

Equal: checks if two values are equal. It is If x = z Returns true if they are equal,
1 =
also used as an assignment operator Then else it returns false

Less than: This operator is used to If x < z Returns true if x is less than z,
2 <
subtract numbers Then else it returns false

Greater than: This operator is used to If x > z Returns true if x is greater than
3 >
multiply numbers Then z, else it returns false

Not equal to: This operator is used to If x <> z Returns true if they are not
4 <>
divide numbers Then equal, else it returns false

If x <= z Returns true if x is less than or

5 <= Less than or equal to:
Then equal to z, else it returns false

If x >= Returns true if x is greater than

6 >= Greater than or equal to:
Then z, else it returns false

Example source code

Equal Comparison Operator

If 2 = 1 Then
MsgBox "True", vbOKOnly, "Equal Operator"
Else
MsgBox "False", vbOKOnly, "Equal Operator"
End If

HERE,

 "If 2 = 1 Then… Else… End If" uses the if statement to evaluate the condition "2 = 1"
 "MsgBox…" Is a built-in function that displays a message box.
o The first parameter "True" or "False" is what will be displayed in the message box.
In our example, 2 is not equal to 1, therefore, it will show "false" in the msg box.
o The second parameter "vbOKOnly" is the button that is displayed in the message
box
 o The third parameter "Equal Operator" is the title of the message box.

Executing the above code gives the following results

Download the above Excel Code

VBA Logical Operators: AND, OR, NOT

VBA Logical Operators: AND, OR, NOT
Logical Operators
Let's say you want to process a customer order. For that, you want to first check to see if
the ordered product exists or not. If it does, you also want to check if the quantity on hand
is enough. Logical operators come in handy in such cases. Logical operators are used to
evaluate more than one condition.

S/N Operator Description Example Output

AND: This is used to combine more than one condition. If

If true = true AND
1 AND all the conditions are true, AND evaluates to true. If any of false
false = true THEN
the condition is false, AND evaluates to false

OR: This is used to combine more than one condition. If

If true = true OR
2 OR any of the conditions evaluate to true, OR returns true. If true
true = false THEN
all of them are false, OR returns false

NOT: This one works like an inverse function. If the

If NOT (true)
3 NOT condition is true, it returns false, and if a condition is false, false
Then
it returns true.

Example Source Code

For the sake of simplicity, we will be comparing hard coded numbers.

Add ActiveX buttons to the sheet from the "Insert option."

Set the properties as shown in the image below

The following table shows the properties that you need to change and the values that you
need to update too.

S/N Control Property Value

1 CommandButton1 Name btnAND

Caption AND Operator (0 = 0)

2 CommandButton2 Name btnOR

Caption OR Operator (1 = 1) Or (5 = 0)

3 CommandButton3 Name btnNOT

 Caption NOT Operator Not (0 = )

Add the following code to btnAND_Click

Private Sub btnAND_Click()

If (1 = 1) And (0 = 0) Then
MsgBox "AND evaluated to TRUE", vbOKOnly, "AND operator"
Else
MsgBox "AND evaluated to FALSE", vbOKOnly, "AND operator"
End If
End Sub

HERE,

 "If (1 = 1) And (0 = 0) Then" the if statement uses the AND logical operator to combine
two conditions (1 = 1) And (0 = 0). If both conditions are true, the code above 'Else'
keyword is executed. If both conditions are not true, the code below 'Else' keyword is
executed.

Add the following code to btnOR_Click

Private Sub btnOR_Click()

If (1 = 1) Or (5 = 0) Then
MsgBox "OR evaluated to TRUE", vbOKOnly, "OR operator"
Else
MsgBox "OR evaluated to FALSE", vbOKOnly, "OR operator"
End If
End Sub

HERE,

 "If (1 = 1) Or (5 = 0) Then" the if statement uses the OR logical operator to combine two
conditions (1 = 1) And (5 = 0). If any of the conditions is true, the code above Else keyword
is executed. If both conditions are false, the code below Else keyword is executed.

Add the following code to btnNOT_Click

Private Sub btnNOT_Click()

If Not (0 = 0) Then
MsgBox "NOT evaluated to TRUE", vbOKOnly, "NOT operator"
Else
MsgBox "NOT evaluated to FALSE", vbOKOnly, "NOT operator"
End If
End Sub

HERE,

 "If Not (0 = 0) Then" the if statement uses the NOT logical operator to negate the result of
the condition. If the conditions is true, the code below 'Else' keyword is executed. If the
condition is true, the code above Else keyword is executed.

Download Excel containing above code

Excel VBA: Call a Subroutine
What is Subroutine?
A subroutine is a piece of code that performs a specific task and does not return a result.
Subroutines are used to break down large pieces code into small manageable parts.

Let's say you have created a user interface with text boxes for accepting user input data.
You can create a subroutine that clears the contents of the text boxes. A subroutine is
appropriate in such a scenario because you do not want to return any results.

In this tutorial, you will learn-

 Why use subroutines

 Rules of naming subroutines and functions
 Subroutine practical example
 Syntax for declaring subroutines

Why use subroutines

 Break code into small manageable code: An average computer program has thousands
and thousands of source code lines. This introduces complexity. Subroutines help solve this
problem by breaking down the program into small manageable chunks of code.
 Code reusability. Let's say you have a program that needs to access the database, almost all
of the windows in the program will need to interact with the database. Instead of writing
separate code for these windows, you can create a function that handles all database
interactions. You can then call it from whichever window you want.
 Subroutines and functions are self-documenting. Let's say you have a function
calculateLoanInterest and another that says connectToDatabase. By just looking at the
name of the subroutine/function, the programmer will be able to tell what the program
does.

Rules of naming subroutines and functions

To use subroutines and functions, there are set of rules that one has to follow.

 A subroutine or function name cannot contain space

 A subroutine or function name should start with a letter or an underscore. It cannot start
with a number or a special character
 A subroutine or function name cannot be a keyword. A keyword is a word that has special
meaning in VBA. Words like Private, Sub, Function, and End, etc. are all examples of
keywords. The compiler uses them for specific tasks.

Subroutine practical example

In this section, we are going to;

1. Look at the syntax of subroutines in VBA

2. Create a simple program that uses a subroutine. It should display a message box when a
user clicks on a command button.

You will need to enable the Developer tab in Excel to follow along with this example. If you
do not know how to enable the Developer tab then read the tutorial on VBA Operators
VBA Syntax for declaring subroutines
Private Sub mySubRoutine(ByVal arg1 As String, ByVal arg2 As String)
'do something
End Sub

HERE in the syntax,

Code Action
 Here the keyword "Sub" is used to declare a subroutine
named "mySubRoutine" and start the body of the
 "Private Sub
subroutine.
mySubRoutine(…)"
 The keyword Private is used to specify the scope of the
subroutine

 "ByVal arg1 As String, ByVal  It declares two parameters of string data type name
arg2 As String" : arg1 and arg2

 "End Sub"  "End Sub" is used to end the body of the subroutine

The following subroutine accepts the first and last name and displays them in a message
box.

Now we are going to program and execute this whole process for the subroutine. Let see
this.

Sub-routines demonstrated with Example:

1. Design the user interface and set the properties for the user controls.
2. Add the subroutine
3. Write the click event code for the command button that calls the subroutine
4. Test the application

Step 1) User Interface

Design the user interface as shown in the image below

Set the following properties. The properties that we are setting

S/N Control Property Value

1 CommandButton1 Name btnDisplayFullName
2 Caption Fullname Subroutine
Your interface should now look as follows

Step 2) Add subroutine

1. Press Alt + F11 to open the code window

2. Add the following subroutine

Private Sub displayFullName(ByVal firstName As String, ByVal lastName As String)

MsgBox firstName & " " & lastName
End Sub

HERE in the code,

Code Actions
 "Private Sub  It declares a private subroutine displayFullName that
displayFullName(…)" accepts two string parameters.

 "ByVal firstName As String,  It declares two parameter variables firstName and

ByVal lastName As String" lastName

 It calls the MsgBox built-in function to display a

message box. It then passes the 'firstName' and
 MsgBox firstName & " " &
'lastName' variables as parameters.
lastName"
 The ampersand "&" is used to concatenate the two
variables and add an empty space between them.

Step 3) Call the subroutine from the command button click event.

 Right click on the command button as shown in the image below. Select View Code.
 The code editor will open
Add the following code in code editor for the click event of btnDisplayFullName command
button.

Private Sub btnDisplayFullName_Click()

displayFullName "John", "Doe"
End Sub

Your code window should now look as follows

Save the changes and close the code window.

Step 4) Testing the code

On the developer toolbar put the design mode 'off'. As shown below.
Step 5) Click on the command button 'FullName Subroutine'.

You will get the following results

Download the above Excel Code

Summary:

 A subroutine is a piece of code that performs a specific task. A subroutine does not return a
value after execution
 Subroutines offer code reusability
 Subroutines help break down large chunks of code into small manageable code.
Excel VBA Function Tutorial: Return, Call,
Examples
What is a Function?
A function is a piece of code that performs a specific task and returns a result. Functions are
mostly used to carry out repetitive tasks such as formatting data for output, performing
calculations, etc.

Suppose you are developing a program that calculates interest on a loan. You can create a
function that accepts the loan amount and the payback period. The function can then use
the loan amount and payback period to calculate the interest and return the value.

Why use functions

The advantages of using functions are the same as the ones in the above section on why use
subroutines.

Rules of naming functions

The rules for naming functions as the same as the ones in the above section on rules for
naming subroutines.

VBA Syntax for declaring Function

Private Function myFunction (ByVal arg1 As Integer, ByVal arg2 As Integer)
myFunction = arg1 + arg2
End Function

HERE in the syntax,

Code Action
 Here the keyword "Function" is used to declare a
function named "myFunction" and start the body of
 "Private Function
the function.
myFunction(…)"
 The keyword 'Private' is used to specify the scope of
the function

 "ByVal arg1 As Integer, ByVal  It declares two parameters of integer data type named
arg2 As Integer" 'arg1' and 'arg2.'

 evaluates the expression arg1 + arg2 and assigns the

 myFunction = arg1 + arg2
result to the name of the function.

 "End Function"  "End Sub" is used to end the body of the function

Function demonstrated with Example:

Functions are very similar to the subroutine. The major difference between a subroutine
and a function is that the function returns a value when it is called. While a subroutine does
not return a value, when it is called. Let's say you want to add two numbers. You can create
a function that accepts two numbers and returns the sum of the numbers.

1. Create the user interface

2. Add the function
3. Write code for the command button
4. Test the code

Step 1) User interface

Add a command button to the worksheet as shown below

Set the following properties of CommanButton1 to the following.

S/N Control Property Value

1 CommandButton1 Name btnAddNumbers

2 Caption Add Numbers Function

Your interface should now appear as follows

Step 2) Function code.

1. Press Alt + F11 to open the code window

2. Add the following code

Private Function addNumbers(ByVal firstNumber As Integer, ByVal secondNumber As Integer)

addNumbers = firstNumber + secondNumber
End Function

HERE in the code,

Code Action

 It declares a private function "addNumbers"

 "Private Function addNumbers(…)"
that accepts two integer parameters.

 "ByVal firstNumber As Integer, ByVal  It declares two parameter variables

secondNumber As Integer" firstNumber and secondNumber

 "addNumbers = firstNumber +  It adds the firstNumber and secondNumber

secondNumber" values and assigns the sum to addNumbers.

Step 3) Write Code that calls the function

1. Right click on btnAddNumbers_Click command button

2. Select View Code
3. Add the following code

Private Sub btnAddNumbersFunction_Click()

MsgBox addNumbers(2, 3)
End Sub

HERE in the code,

Code Action

 It calls the function addNumbers and passes in 2 and 3 as the

"MsgBox
parameters. The function returns the sum of the two numbers five
addNumbers(2,3)" (5)

Step 4) Run the program, you will get the following results
Download Excel containing above code

Download the above Excel Code

Summary:

 A function is a piece of code that performs a specific task. A function returns a value after
execution.
 Both subroutines and functions offer code reusability
 Both subroutines and functions help break down large chunks of code into small
manageable code.
Excel VBA Range Object
What is VBA Range?
The VBA Range Object represents a cell or multiple cells in your Excel worksheet. It is the
most important object of Excel VBA. By using Excel VBA range object, you can refer to,

 A single cell
 A row or a column of cells
 A selection of cells
 A 3-D range

As we discussed in our previous tutorial, that VBA is used to record and run Macro. But
how VBA identify what data from the sheet needs to be executed. This is where VBA Range
Objects is useful.

In this tutorial, you will learn-

 Introduction to Referencing Objects in VBA

 Refer to a Single cell using the Worksheet.Range Property
 Cell Property
 Range Offset property

Introduction to Referencing Objects in VBA

Referencing Excel's VBA Range Object and the Object Qualifier.

 Object Qualifier: This is used for referencing the object. It specifies the workbook
or worksheet you are referring to.

To manipulate these cell values, Properties and Methods are used.

 Property: A property stores information about the object.

 Method: A method is an action of the object it will perform. Range object can
perform actions like selected, copied, cleared, sorted, etc.

VBA follow object hierarchy pattern to refer object in Excel. You have to follow the
following structure. Remember the .dot overhere connects the object at each of the
different levels.

Application.Workbooks.Worksheets.Range

There are two main types of default objects.

How to refer to Excel VBA Range Object using Range

property
Range property can be applied in two different types of objects.
  Worksheet Objects
 Range Objects

Syntax for Range Property

1. The keyword "Range."

2. Parentheses that follow the keyword
3. Relevant Cell Range
4. Quotation (" ")

Application.Workbooks("Book1.xlsm").Worksheets("Sheet1").Range("A1")

When you refer Range object, as shown above, it is referred as fully qualified reference.
You have told Excel exactly which range you want, what sheet and in what worksheet.

Example: MsgBox Worksheet("sheet1").Range("A1").Value

Using Range property, you can perform many tasks like,

 Refer to a Single cell using range property

 Refer to a Single cell using the Worksheet.Range Property
 Refer to an entire row or column
 Refer to merged cells using Worksheet.Range Property and many more

As such it will be too lengthy to cover all scenarios for range property. For scenarios
mentioned above, we will demonstrate an example only for one. Refer to a Single cell using
range property.

Refer to a Single cell using the Worksheet.Range

Property
To refer to a single cell, you have to refer to a single cell.

Syntax is simple "Range("Cell")".

Here, we will use ".Select" command to select the single cell from the sheet.

Step 1) In this step, open your excel.

Step 2) In this step,

 Click on button.
 It will open a window.
 Enter your program name here and click 'OK' button.
 It will take you to main Excel file, from top menu click on 'stop' record button to stop
recording Macro.
Step 3) In next step,

 Click on Macro button from the top menu. It will open the window below.
 In this window, Click on the 'edit' button.

Step 4) The above step will open VBA code editor for file name "Single Cell Range". Enter
the code as shown below for selecting range "A1" from the excel.

Step 5) Now save the file and run the program as shown below.
Step 6) You will see Cell "A1" is selected after execution of the program.

Likewise, you can select a cell with a particular Name. For example, if you want to search
cell with name "Guru99- VBA Tutorial". You have to run the command as shown below. It
will select the cell with that name.

Range("Guru99- VBA Tutorial").Select

To apply other range object here is the code sample.

Range for selecting cell in

Range declared
Excel
For single Row Range("1:1")
For single Column Range("A: A")
For Contiguous Cells Range("A1:C5")
For Non-Contiguous Cells Range("A1:C5, F1:F5")
Range("A1:C5 F1:F5")
For Intersection of two ranges
(For intersection cell, remember there is no comma
operator)
Range("A1:C5")
To merge Cell
( To merge cell use "merge" command)
Cell Property
Similarly to the range, in VBA you can also you "Cell Property". The only difference is that it
has an "item" property that you use to reference the cells on your spreadsheet. Cell
property is useful in a programming loop.

For example,

Cells.item(Row, Column). Both the lines below refer to cell A1.

 Cells.item(1,1) OR
 Cells.item(1,"A")

Range Offset property

Range offset property will select rows/columns away from its original position. On the
basis of the range declared, cells are selected. See example below.

For example,

Range("A1").offset(Rowoffset:=1, Columnoffset:=1).Select

The result for this will cell B2. The offset property will move A1 cell to 1 column and 1 row
away. You can change the value of rowoffset / columnoffset as per requirement. You can
use a negative value (-1) to move cells backward.

Download Excel containing above code

Download the above Excel Code

Summary:

 The VBA Range Object represents a cell or multiple cells in your Excel worksheet
o A single cell
o A row or a column of cells
o A selection of cells
o A 3-D range
 To manipulate cell values, Properties and Methods are used
o A property stores information about the object
o A method is an action of the object it will perform like select, merge, sorted,
etc.
 VBA follow object hierarchy pattern to refer object in Excel using .dot operator
 Range property can be applied in two different types of objects
o Worksheet Objects
o Range Objects