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.
