Excel VBA Tutorial – How to Write Code in a
Spreadsheet Using Visual Basic
Chloe Tucker
Introduction
This is a tutorial about writing code in Excel spreadsheets using
Visual Basic for Applications (VBA).
Excel is one of Microsoft’s most popular products. In 2016, the
CEO of Microsoft said "Think about a world without Excel.
That's just impossible for me.” Well, maybe the world can’t think
without Excel.
In 1996, there were over 30 million users of Microsoft Excel
(source).
Today, there are an estimated 750 million users of Microsoft
Excel. That’s a little more than the population of Europe and 25x more
users than there were in 1996.
We’re one big happy family!
In this tutorial, you’ll learn about VBA and how to write code in
an Excel spreadsheet using Visual Basic.
Prerequisites
You don’t need any prior programming experience to
understand this tutorial. However, you will need:
Basic to intermediate familiarity with Microsoft Excel
If you want to follow along with the VBA examples in this
article, you will need access to Microsoft Excel, preferably the latest
version (2019) but Excel 2016 and Excel 2013 will work just fine.
A willingness to try new things
Learning Objectives
Over the course of this article, you will learn:
1. What VBA is
2. Why you would use VBA
3. How to get set up in Excel to write VBA
4. How to solve some real-world problems with VBA
Important Concepts
Here are some important concepts that you should be familiar
with to fully understand this tutorial.
Objects: Excel is object-oriented, which means everything is an
object - the Excel window, the workbook, a sheet, a chart, a
cell. VBA allows users to manipulate and perform actions with
objects in Excel.
If you don’t have any experience with object-oriented
programming and this is a brand new concept, take a second to
let that sink in!
Procedures: a procedure is a chunk of VBA code, written in the
Visual Basic Editor, that accomplishes a task. Sometimes, this
is also referred to as a macro (more on macros below). There
are two types of procedures:
Subroutines: a group of VBA statements that performs one or
more actions
Functions: a group of VBA statements that performs one or more
actions and returns one or more values
Note: you can have functions operating inside of subroutines.
You’ll see later.
Macros: If you’ve spent any time learning more advanced Excel
functionality, you’ve probably encountered the concept of a
“macro.” Excel users can record macros, consisting of user
commands/keystrokes/clicks, and play them back at lightning
speed to accomplish repetitive tasks. Recorded macros
generate VBA code, which you can then examine. It’s actually
quite fun to record a simple macro and then look at the VBA
code.
Please keep in mind that sometimes it may be easier and faster
to record a macro rather than hand-code a VBA procedure.
For example, maybe you work in project management. Once a
week, you have to turn a raw exported report from your project
management system into a beautifully formatted, clean report
for leadership. You need to format the names of the over-
budget projects in bold red text. You could record the formatting
changes as a macro and run that whenever you need to make
the change.
What is VBA?
Visual Basic for Applications is a programming language
developed by Microsoft. Each software program in the Microsoft
Office suite is bundled with the VBA language at no extra cost.
VBA allows Microsoft Office users to create small programs that
operate within Microsoft Office software programs.
Think of VBA like a pizza oven within a restaurant. Excel is the
restaurant. The kitchen comes with standard commercial
appliances, like large refrigerators, stoves, and regular ole’
ovens - those are all of Excel’s standard features.
But what if you want to make wood-fired pizza? Can’t do that in
a standard commercial baking oven. VBA is the pizza oven.
Yum.
Why use VBA in Excel?
Because wood-fired pizza is the best!
But seriously.
A lot of people spend a lot of time in Excel as a part of their
jobs. Time in Excel moves differently, too. Depending on the
circumstances, 10 minutes in Excel can feel like eternity if
you’re not able to do what you need, or 10 hours can go by very
quickly if everything is going great. Which is when you should
ask yourself, why on earth am I spending 10 hours in Excel?
Sometimes, those days are inevitable. But if you’re spending 8-
10 hours everyday in Excel doing repetitive tasks, repeating a
lot of the same processes, trying to clean up after other users of
the file, or even updating other files after changes are made to
the Excel file, a VBA procedure just might be the solution for
you.
You should consider using VBA if you need to:
Automate repetitive tasks
Create easy ways for users to interact with your spreadsheets
Manipulate large amounts of data
Getting Set Up to Write VBA in Excel
Developer Tab
To write VBA, you’ll need to add the Developer tab to the
ribbon, so you’ll see the ribbon like this.
To add the Developer tab to the ribbon:
1. On the File tab, go to Options > Customize Ribbon.
2. Under Customize the Ribbon and under Main Tabs, select the
Developer check box.
After you show the tab, the Developer tab stays visible, unless
you clear the check box or have to reinstall Excel. For more
information, see Microsoft help documentation.
VBA Editor
Navigate to the Developer Tab, and click the Visual Basic
button. A new window will pop up - this is the Visual Basic
Editor. For the purposes of this tutorial, you just need to be
familiar with the Project Explorer pane and the Property
Properties pane.
