EXCEL

VBA
A Comprehensive Beginner’s
Guide to Learn Excel VBA Step
by Step
Dustin Adams
© Copyright 2020 by Dustin Adams - All rights reserved.
The content contained within this book may not be reproduced, duplicated or transmitted
without direct written permission from the author or the publisher.
Under no circumstances will any blame or legal responsibility be held against the publisher, or
author, for any damages, reparation, or monetary loss due to the information contained within
this book, either directly or indirectly.
Legal Notice:
This book is copyright protected. It is only for personal use. You cannot amend, distribute, sell,
use, quote or paraphrase any part, or the content within this book, without the consent of the
author or publisher.
Disclaimer Notice:
Please note the information contained within this document is for educational and
entertainment purposes only. All effort has been executed to present accurate, up to date,
reliable, complete information. No warranties of any kind are declared or implied. Readers
acknowledge that the author is not engaged in the rendering of legal, financial, medical or
professional advice. The content within this book has been derived from various sources.
Please consult a licensed professional before attempting any techniques outlined in this book.
By reading this document, the reader agrees that under no circumstances is the author
responsible for any losses, direct or indirect, that are incurred as a result of the use of the
information contained within this document, including, but not limited to, errors, omissions, or
inaccuracies.
 Table of Contents
Introduction
Chapter 1 - Overview
Chapter 2 - Fundamentals of VBA Programming
Chapter 3 - The Excel Programming Environment
Chapter 4 - Hello World!
Chapter 5 - Dialog Boxes
Chapter 6 - Forms
Chapter 7 - Recording and Saving Macros
Chapter 8 - Miscellaneous
Conclusion
References
 Introduction
Before we jump right in, you may be wondering if you need to
know Excel VBA in 2020. A quick search on Indeed.com, just one of
many job search sites shows that there are many jobs where Excel
VBA is a requirement as shown below:

Source: indeed.com (2020)

Other examples of jobs that require knowledge of Excel VBA
include:

Accounting specialists . Heavy Excel usage in recording

transactions, preparing financial statements, budgeting and
forecasting.
Marketers . Study market research information which
invariably involves large databases with customer survey
results
Human resources personnel . Generate reports on various
employee-related aspects, manpower planning, management
reviews
Statistical analysts . They work with quantitative data and
 identify patterns for the end-user.

The list presented here is far from exhaustive. As you can see, the
job types and academic qualifications vary. Whether you are in the
commerce field, computer science, engineering, mathematics, etc., if
your job entails large Excel data, you need Excel VBA skills. If you
are not yet employed or are thinking of going back to the job market,
learning Excel VBA will strengthen your CV and widen the potential
job pool.
If you are already employed in any one of these jobs or are
thinking of a career in any of these fields, you are or will likely work
with a lot of spreadsheet information in your database. Knowledge of
VBA is vital in any of these jobs as VBA helps automate tasks in
Excel, which improves productivity. VBA enables multitasking as
time-consuming Excel tasks can be performed while you attend to
other things and also introduces consistency in how tasks are
performed, thus reducing human errors. Who wouldn’t want to tap
into any of these advantages?
Learning how to program goes beyond just watching a short video
on how to create a specific application and then blindly following the
steps that are detailed in the video. A lot of people make this
mistake, and after countless hours with little success, realize they still
cannot independently think of a concept that can automate tedious
Excel processes and code these in VBA. This is a source of
frustration amongst many people who set out on a VBA journey and
what leads them to stop learning Excel VBA prematurely. Why this
happens is when you are just following steps, but do not understand
what is being done and why you are doing it, this lays an improper
foundation for you to spring from.
This book takes a different approach, and is the perfect companion
for anyone wanting to learn Excel VBA in a number of ways:

It lays a good foundation for all serious beginner Excel VBA

programmers through providing all the theory that one may
need to understand the process.
It also provides detailed instructions on how to navigate the
VBA integrated development environment, and this familiarity
makes the environment more user friendly.
 It lays out a good foundation for you to write your own
programs by laying out code that is commented on, on a line
by line basis, such that you understand exactly what you are
doing and why. This will tie in theory to practice and will enable
you to think of your own variations of problems that you can
solve through Excel VBA.
It has a section dedicated to helping you troubleshoot any
errors that you may encounter during your Excel VBA learning
journey.
Is written in an easy to read, easy to relate to many that will
appeal to any reader with a passion to learn Excel VBA
irrespective of academic and work background. Keep in mind
that just like anything in life, you will have to start somewhere.
If you want to start your Excel VBA journey, this book will be
the best choice you ever made.

One of the assumptions that this book makes is that you are not
reading this beginner Excel VBA book just to stop there, but that you
will want to proceed to the intermediate and expert levels. As such,
attention is given to laying out the foundation for proper VBA
programming. By the end of this book, you should be conversant with
fundamentals of VBA programming that will enable you to appreciate
the ins and out of programming in general. You will also get to apply
these concepts when you write your first VBA programs, which will
give you a deeper understanding of VBA programming and give you
a springboard from which you can start writing your own applications
and to take VBA further.
This book has three distinct sections, the theory, that was laid out
in the first two chapters, an introduction to the VBA Integrated
Development Environment (IDE) which will walk you through the IDE
and familiarize you with the tools that you should have on hand as
you start programming. The third section is the practical aspect,
where you will write your very first VBA code and incrementally be
introduced to other concepts until the final chapter. Before you whip
out your Excel workbook and launch your VBA Editor, please take
the time to go through the first two chapters and make notes. This
will equip you with most of the programming concepts that you will
apply in the later chapters. The recommended approach after you
are done with chapter one and two is to proceed with the book as
you would with a practical course. You have to be hands-on and
cannot read it like a novel. Should you feel there are concepts that
you cannot understand, circle back to Chapter Two and take a look
at the theory about the concept that you are struggling with. Through
it all, have fun! VBA is a world full of possibilities, and you are well on
your way to coming up with innovative solutions that will automate
Excel processes and save you hundreds, if not thousands, of hours
in the process.
 Chapter 1 - Overview
Following its introduction with Excel 5.0 in 1993, it quickly became
obvious that VBA was here to stay. It was well-received by both the
programming community as well as the business leaders, which
helped it achieve tremendous success. It is no surprise that today,
almost three decades later, we’re still talking and writing about it and
no doubt, we shall be doing so for some time to come. But what
made this programming language and spreadsheet duet such a hit?
And why are we still engaged by this duet today, despite the giant
leaps that have been taken in the software industry in the last
decade alone? We have almost certainly seen it all in recent years
and still, VBA has something to offer that few, if any, can rival. As an
aspiring VBA for Excel programmer or applications developer, you
may be wondering what exactly you just signed up for. These are all
fair questions and you certainly mustn’t have to read to the end of the
book to have them answered. This chapter discusses the need for
Excel VBA programming and gives a breakdown of what is involved.

Potential Applications of Excel VBA

Millions of people across the world use Excel VBA. The main
reason why is because of the massive amount of potential
applications. These applications make it possible to, amongst other
uses, automate mundane tasks that take up a lot of time. This
automation process automatically increases productivity, efficiency
and of course cuts down the possibilities of errors that can easily
take many man-hours to fix. The following sections highlight some of
the most popular uses of Excel VBA.

Clean and Format Data

With Excel VBA, you can automate a number of tasks, especially
when you work with many large databases. This automatically
appeals to researchers, who as part of the data collection process
will have to clean the collected data before analysis. Other jobs
where this may be useful are those who are in HR, marketing,
accounting, database administrators, and many more. Functions that
can help to clean and format data which Excel VBA can handle at the
click of a button include:

Removal of duplicates
Deleting unwanted rows cells e.g., blank rows
Formatting raw data. Raw data is information that is collected
from a source. The norm is that this data is usually generated
by someone else and might not be readily accessible in a
format that may be useful to you. You can then use Excel VBA
to manipulate this data and present it in a manner that best
suits your uses.
Creating formulas to easily get more relevant and meaningful
data.
A good example is that of a researcher who wants to compare
people’s Body Mass Index (BMI) in different locations. This
involves calculations that involve taking a person’s mass and
dividing it by height. It is not advisable to directly ask research
participants what their BMIs are, as this may mean explaining
what BMI is, and how to calculate it.
In addition, means that the researcher will have no control
over how this process is actually done including the ability to
identify any errors if any, made during calculation by the
research participants. Instead, research participants can be
asked for information on age, height, and body mass only.
This data will enable the research to identify source data
errors and eliminate these from the calculations. A 28-year-
old respondent who says that he weighs 16 kgs. This could
be wrong if the respondent meant to write 61 kg. But if it’s not
possible to verify this information, then this data which clearly
falls outside the norm may be excluded from the sample.
In this scenario, Excel VBA can be used to highlight such
cases where the collected raw data is questionable and to
create formulas that automatically calculate BMI, which is
what the study seeks to find in the first place.

Protecting and Maintaining Data Integrity

Data integrity is how reliable and trustworthy data is over its
lifetime. A small change in data such as someone accidentally
deleting a cell with a certain formula, can change the information that
is conveyed by that data. If your data can easily be manipulated by
anyone, then this may mean that this data is essentially
untrustworthy. If decisions are made based on this untrustworthy
data that may have been manipulated along the way by whoever
accesses the data, then the chances for error and wrong decisions
are a big possibility. VBA can be used to automate a number of tasks
in a workbook that helps protect and maintain data integrity.
Examples of such tasks include:

Rejecting changes to a workbook . This will mean that the

workbook can be viewed and the data is then sorted, filtered,
etc., but users cannot manipulate the information. This will
essentially make the workbook a read-only document. You can
also prevent individual sheets or cells from being deleted,
which maintains data integrity.
Protecting workbooks and worksheets. An example of
protecting a workbook is to password protect it so
unauthorized users do not have access. Other ways include
protecting individual cells to prevent formulas being changed,
except by those with authorized access. This will result in
users only accessing information that they cannot change,
which helps maintain data integrity.

In addition to data integrity, some of the measures above help

maintain data security. Password protecting data prevents
unauthorized access and helps keep data safe from both intentional
and unintentional corruption. You can also prevent unintentional loss
of data through automating workbook tasks like saving. Other
workbook tasks that can be automated with VBA include opening,
closing, and activating.

Data Analysis
Imagine working with thousands of rows in Excel as part of the
data that you have to analyze. Where would you begin? With the
right tools, data analysis does not have to be stressful. At the click of
a button, Excel VBA allows you to use Pivot tables to query a large
data set, and summarize it in a way that enables you to map out how
best to start your data analysis. You can reorganize, count, average,
group, transpose, etc. No matter how large your data set is, you can
do all this quickly and easily, with no errors. There are also various
other ways that you can Pivot tables to summarize and analyze data
to get more insight into what the data means.

Simplifying the Data Entry Process

Data entry can be simplified with Excel VBA in a number of ways.
You may be wondering what may be so difficult with filling in a simple
Excel spreadsheet, but try coping information for a printed document
into an Excel spreadsheet and see how that goes for you! Now,
imagine trying to collect data from different people… What do you
think the result will be? With Excel VBA, you will have at your
fingertips, a number of tools that you can use to simplify this process.
You can create custom-built UserForms and input boxes to simplify
data entry. If you are collecting data from a large number of users,
instead of letting each individual have access to a spreadsheet and
directly entering data there, you can use these UserForms instead.
This will control what type of data is entered, the format in which it is
entered, and where, in the spreadsheet, the data will be stored.
Why is this necessary? Picture someone who is in the marketing
department, and they need to capture the demographics of people
interested in their product to plan a marketing campaign appealing to
the target market. The information that is needed includes age, sex,
race and other socio-economic data of current customers. Since
more than 80% of sales for this product are online purchases, they
think the best way to capture this information is to offer anyone who
includes this product in their online shopping cart a 10% discount if
they complete a short survey before checkout. After a few minutes,
from the time they try this strategy, they see it is working, but the
challenge is that some of the information captured in some fields is
incomplete, not in a usable format, and there are typos.

Solution? A UserForm can be designed to limit the data types that

are entered in each field. You can use combo boxes, for example, to
give users a drop-down menu with a limited number of options to
choose from. For example, you can set the field under marital status
to only give the following options single, married, divorced, or
widowed. You can also link a calendar to the ‘date of birth’ field
which will capture the date of birth in a format that is useful to you
and will also eliminate issues of errors, incomplete information, and
inconsistent formats.

Simplify Repetitive Work Through Add-ins

An add-in is a small software program that extends what a bigger
application like Excel can do. As an example, an Excel add-in can
include new custom features into Microsoft Excel that are relevant to
the work that you are doing. You can, for example, program add-ins
that allow you to apply formulas to selected cells, sort data, hide
columns, apply filters, generate random numbers or anything else
you may want to add with the click of a button and make you more
productive. With Excel VBA skills, you can create customized add-
ins, and extend Excel functionality. The good thing with this is that
you can also share your add-ins across different applications, across
different Microsoft Office programs and also sell them to other people
should you want to!

Save Time Through Process Automation

There are lots of people out there who work with spreadsheets that
frequently need to be updated, and you could be one of them. If you
have tried to do this manually before, and you appreciate how hard it
is to do so. If you have never, take a moment to picture how much
time you will need to set aside every day to update a spreadsheet
that has more than a hundred rows. Frequent updating means that
after all the hard work you would have put in creating the
spreadsheet, you are not done, you will have to come in periodically
and meticulously and painstakingly update all fields so that the
spreadsheet reflects data that is up to date and relevant, over and
over and over again. This not only ties you down into doing mundane
tasks but also brings it chances of data corruption due to human
error. A lot of the tasks that people can do on Excel can be
automated e.g.:

Importing a file that has information that you need to update

your spreadsheet, copying a particular column or row, and
 then rearranging the data so that it pasted in exactly the
correct cells.
Splitting or parsing data eg cells that contain more than one
set of information can be automatically separated into different
columns.
Automatically updating your spreadsheet from an e-mail or
some other source
Automatically generating regular reports within a certain period
e.g. at the close of business, or every week, or every month,
etc.

There is a lot more you can automate with Excel other than what
has been highlighted in this section. If you want to tap into all these
potential uses of VBA, you have to be prepared to roll up your
sleeves and learn how to write VBA programs/macros. With that
said, the following section lists the basic toolkit that you will need to
get started.

Excel VBA Basic Development Toolkit

Before you set out on the exciting journey of learning application
development with Excel VBA application, you will need the following
essential resources:

Working knowledge of any spreadsheet application

VBA programming language knowledge
An Integrated Development Environment (IDE) and a compiler

Working Knowledge of Any Spreadsheet Application

You may have previously worked with Google Sheets, iWork
Numbers, LibreOffice Calc, Lotus 1-2-3, Lotus Symphony -
Spreadsheets, OpenOffice Calc, VisiCalc, etc. Indeed, with
knowledge of any spreadsheet application, not just Excel, you should
be in a position to follow the content in this book and be a competent
developer of Excel VBA applications in the end. The basic
functionality and concepts of all spreadsheet applications are the
same. So, even if you have never used Excel before, do not let that
deter you as this book can easily be read by anyone who has a
working knowledge of spreadsheet application.

VBA Programming Language Knowledge

Hang on, before you hasten to move on, if you have no prior
knowledge of VBA programming, or programming experience at all
don’t let the content covered in this section scare you. Chapter two is
specifically intended for you. This book assumes no prior knowledge
of programming as it is intended to be a step by step beginner’s
guide for those wanting to learn Excel VBA from scratch. However,
for you to be able to follow the content in the rest of this book, you
need to go over and understand the content of Chapter two, which
covers the fundamentals of VBA programming. This content is
written with the non-programmer in mind and covers all the jargon
that you are likely to stumble upon in your VBA programming
journey. As you read through, keep in mind that this book is not a
novel that you can read through from cover to cover in one sweep.
Instead, you may find yourself repeatedly coming back to refresh on
some of these concepts to get a better understanding of why some
things are done in a certain way when programming, especially when
you start on the practical aspects of VBA programming. The
language itself is not difficult, and it is more common to find very
competent self-taught VBA programmers. With this book, you will
have at your fingertips a one-stop-shop that explains all concepts in
an easy to understand manner and position you to not only be a
competent Excel VBA programmer but be on your path to be an
excellent programmer. With that said, if you do not have any VBA
programming language knowledge, all you need to bring to the table
is the thirst for knowledge in this area, as all other resources are
provided for in this book.

An IDE and a Compiler

An IDE is a piece of software that brings together the key tools that
a developer will need when writing code. A compiler is another
software responsible for converting a program written in a human-
readable programming language into machine language, understood
by computers, and ready for execution on a computer. For VBA, both
the IDE and compiler are included in Excel. The VBA IDE and
compiler are included in Microsoft Word, Microsoft Access, Microsoft
Powerpoint as well as Microsoft Projects. In this book, however, the
focus will be on Excel. Therefore, if you have Microsoft Excel, you
automatically have access to all the development tools that you will
need to create Excel VBA applications.

What to Expect When You Decide to Learn

Excel VBA Programing
Well, other than reading on the theory to gain an understanding of
the VBA programming language, you should expect to do a lot of
programming and everything that goes with it! That means both the
good and the bad. The creative streak in you will be unleashed as
you begin designing user interfaces, and coming up with innovative
ways to solve practical problems you may be facing to make your
Excel work easier. There is nothing more rewarding than thinking of
new solutions to simplify a task and executing it successfully.
However, for you to get to this level, you will need to diligently put in
the work that is required to learn coding practices in VBA. The only
way to do this is to practice, practice, practice!
Keep in mind that there are no shortcuts if you want to be good at
VBA programming. Read through the theory and then follow through
by practicing. As you do the practicals, as a learner, you are going to
make mistakes, lots of them. And if that happens, your code will not
compile or run as expected. When this happens, don’t be frustrated,
this happens to everyone and is part of the learning curve. These
errors are what will force you to take a close look at coding best
practices, and combined with the theory will leave you wiser.
 Chapter 2 - Fundamentals of VBA
Programming
Knowledge of programming is arguably an essential skill in the
21st century. The majority of people may be familiar and comfortable
with using computer programs, applications or systems developed by
other people. Most have accepted living with the limitations of those
applications. However, for a growing number of computer users, the
ability to create their own applications or to customize existing
applications, and systems are in-demand.
As a computer application, Excel has not been spared from the
expectation for it to be customizable by this growing number of
users. While powerful Excel spreadsheets can be created without
resorting to the use of VBA, for one to create complete, robust, safe
and deployable applications, the use of VBA quickly becomes
inevitable. Upon resorting to VBA, one soon realizes that it is a fully-
fledged programming language and knowledge of programming
principles is essential in making effective use of VBA.
This book assumes no prior knowledge of programming. This
chapter is therefore intended to introduce the fundamentals of
programming and bring the non-programmer to an appropriate level
of programming skill to get the most out of this book and from Excel
VBA programming.

What Is a Macro in Excel VBA

Assume that you are at work, and you have just been emailed a
workbook filled with worksheets. Within each worksheet, there are
hundreds of rows of data. You have been asked to comb through this
data and create a list of data sets that meets certain criteria. How
long do you think it will take you to comb through all worksheets, row
by row and manually selecting those cases that meet the specified
criteria?
Don’t crack our head open over this task just yet. There is a much
easier way! You can, with a few lines of code, create a small
program that can do the specified task that can be run by the Excel
application. This program, designed and created within Excel, is
called a macro. Armed with a well-written macro, you can perform
the above task in less than a couple of minutes! However, for you to
write a macro, you effectively need to know a programming language
called VBA.

Programming Languages Overview

Computer programming is a structured way of designing a
collection of instructions to be executed by a computer for it to
perform a specific task that involves coding, testing, and debugging.
Most people understand programming as a means by which humans
compose sets of instructions for execution on a computer. Certainly,
there is a lot more involved but at the core, it all comes down to
composing instructions for execution on a computer. These
instructions are commonly referred to as code and as a result,
programming is often called coding.
Theoretically, anyone can write a computer program as long as
they know how to give instructions. The problem is that computers do
not understand English, French, or any other human language.
Instead, they work by deciphering meaning from the state of the
electronic circuitry within them, specifically the microprocessor and
other supporting components. How the instructions we type on a
computer keyboard end up as an electronic circuitry state is the
focus of this section.

The Language of Computers

Any electronic circuit is in one of two states at any given time - ON
or OFF. We can abstract these states by representing them with
familiar symbols namely 1 and 0, with 1 representing an ON state
and 0 representing an OFF state. If we have many electronic circuits,
as we do on a computer, we can represent their collective state by a
string of ones and zeros. For example, to represent the collective
state of a set of 32 circuits, we may use the following string:
This state may be interpreted by the computer as taking the
contents of one memory location and adding them to the contents of
another memory location, storing the result in the first memory
location. Have enough of these microinstructions and you have
yourself a computer program.
 We can conveniently represent these sets of instructions in the
base two or binary number system. In this system, any number can
be represented as a set of ones and zeros. This is similar to how any
number can be represented with the symbols ranging from zero
through to nine in a base 10 or denary system. Binary is the lowest
level of computer instructions. It is the closest we can represent
computer instructions in a human-recognizable form and is what we
have come to identify as a computer or machine language.

From Machine Code to Source Code

Program instructions written in machine language are referred to
as machine code. As much as machine code is sensible and intuitive
for computers, it is not much use for humans. Apart from the obvious
fact that we cannot understand machine language without the use of
special tools, it is also cumbersome. An instruction that can be
conveyed in one word in our natural languages might require
thousands of binary digits to convey. In addition, we cannot possibly
remember the long sequence of binary digits for each instruction that
we may want to write for a computer. Never mind attempting to
combine multiple instructions with data in binary form without making
mistakes.
Enter source code. Source code is the solution to the issues with
the machine code identified above. It represents the exact same
computer instructions as machine code but written in a manner that
humans can read and understand, given a certain level of skill. It is
also the primary means by which computer programs are written. It is
in very rare circumstances that humans need to write computer
programs in machine language. This will typically be when writing
very small, specific programs.

Programming Languages
Source code is written in one of several programming languages.
A programming language is a formal specification of how to write
computer instructions or programs that conform to that specification.
It is a contract that defines the interpretation of textual instructions
when they are written in a certain manner. A programming language
specification will, at the very least, define the syntax and semantics
of the language. Syntax relates to the construction of valid program
elements whereas semantics relates to the meaning attached to
program elements constructed in a certain manner.
There are literally hundreds of programming languages in
existence today and more are being developed every year. These
languages can be categorized according to several characteristics,
the most common of which are described below.

Low Level vs High-Level Languages

This relates to how close a language is to machine language - the
closer it is, the lower its level. Machine code relates to instructions
that a computer can understand, and is written in what is the lowest
level of programming languages. Source code, on the other hand,
can be written in languages ranging from low-level to very high-level
languages.
Assembly language is an example of a low-level language for
writing source code. Here, machine code instructions are replaced
with mnemonics that humans can easily read and write. For example
the assembly language instruction:
This may be interpreted to mean moving the contents of one
location into another specified location. In machine code, the same
instruction will be expressed as a string of ones and zeros. It is
therefore clear that Assembly language, while still not similar to
natural languages, can be readable to humans with the appropriate
level of skill.
High-level languages, on the other hand, are far-removed from
machine code and are closer to natural languages, making them
easy to read and more compact when written. Examples include
VBA, VB.NET, and C#.

Strongly-Typed (Statically Typed) and Weakly-Typed

(Dynamically Typed) Languages
In a strongly-typed programming language, each variable is
assigned a specific data type at the time of declaration and maintains
that data type for the duration of its life. Attempting to assign a value
of a different data type to this variable will cause errors to be flagged
at the time of compiling the program and the program will not
compile. VB.NET and C#fall into this category.
Weakly-typed languages, on the other hand, are less strict when it
comes to data types. They’re more forgiving, allowing the
programmer to assign values of different data types to a variable
from the one that was specified for the variable at the time of
declaration. Some go as far as allowing a variable to be declared
without a data type. While errors may not be flagged at the time of
compilation and the program successfully compiles, runtime errors
may still be experienced. The assumption here is that the
programmer is fully aware of what he or she is doing and will only
make assignments that will not result in runtime errors. VBA is an
example of a weakly-typed language. Another example is Javascript.

Declarative vs Imperative Languages

Declarative programming languages are designed to tell the
computer what is to be achieved, whereas imperative programming
languages are more geared towards specifying how it is to be
achieved. Structured Query Language (SQL) is an example of a
declarative language, while C#, Java, and JavaScript are examples
of imperative languages. VBA is also an imperative language.

Code Compilation
Any program intended for a computer should eventually end up as
machine code. Source code must, therefore, be translatable into
machine code for it to run on a computer. This process is called code
compilation and is handled by a special program called a compiler.
The compiler does a lot more than simply translating source code
into machine code. It also ‘compiles’ together with all programming
resources that will be required for the computer program to run as a
standalone unit in its intended deployment environment.
During the compilation process, the compiler:

Checks that your source code adheres to the syntax of the

programming language used.
Identifies any references to external code in other files and
fetches that code.
Performs some optimization by removing unnecessary or
 redundant program constructs.
Converts the source code into machine code, outputting new
machine code files and other supporting files.

If any syntax errors, or missing or incorrect external code

references, are identified, they are flagged as compile-time errors
and the compilation process is halted. The compiler indicates the
problematic elements of the source code so that the programmer can
fix them. Any errors not caught at this stage will eventually come up
during program execution as either runtime errors or logical errors.
Runtime errors are experienced during the execution of the
program, sometimes after deployment. They often manifest as a
program crash and are due to a program state that was not
anticipated and provided for during the programming stage. This is
closely related to exceptions, which will be discussed later on in the
chapter.
Logical errors are the trickiest. They are due to incorrect logic
being followed at the time of programming, resulting in incorrect
processing results. For example, if the programmer adds one instead
of subtracting one, the computer has no way of knowing that this is
not what was intended. It follows directly from the principle of
‘garbage in, garbage out’ - if the program logic is incorrect, the
computer output will be incorrect. Logical errors are often the most
difficult to identify and require rigorous testing to effectively weed out.

Variables
A variable is a named memory location that is intended to store a
value for retrieval later on. Almost all computer programs manipulate
data of one form or another to produce results. This is true even for
programs that do not appear to be processing any data from the face
e.g., a music player or a game of chess. Computers can only
process one instruction at a time. The outcome of that processing
needs to be stored somewhere, while another instruction is being
processed so that the computer can come back and use that
outcome in another operation. Variables are therefore part of the
basic building blocks of any program.

Relationship to Computer Memory

 As was discussed earlier in this chapter, binary digits are used to
represent the state of electronic circuitry in a computer. The same
applies to computer memory since it is made up of electronic circuitry
as well. The phrase ‘Binary digit’ is used a lot and so it is shortened
to bit , taking the first letter of the first word and the last two letters of
the last word (Binary digIT).
Computer memory is organized into bytes. A byte is a grouping of
eight bits and makes up the fundamental unit of memory. The state
of the eight bits (or electronic circuits) determines what value is
stored in the particular byte. Depending on the data type of the value
to be stored, one byte might not be enough to hold an entire value
and multiple bytes will be required. To put things in context, the
number four in base 10 is represented as follows in binary:
410 = 1002

So in a byte, this will be represented as:

However, the number 256, which is represented as 1000000002 in
binary, cannot be stored in a single byte since it requires nine bits to
fully represent. It will, therefore, need an additional byte of memory
so that it takes up two bytes in total to represent.
This knowledge is important when determining what data type to
use for a given variable. This is because different data types require
different numbers of bytes to represent and, therefore, store.

Data Types
A data type defines the ‘type’ of values that can be legally stored in
a given memory location or variable. In addition to defining storage,
data types also govern the operations that can be performed
between values of different types.
As was demonstrated in the previous section, the value four may
be stored in a memory location that is one byte in size while the
value 256 may not. They are both numbers but, in programming
terms, they are of different data types: four is of type byte while 256
is of type integer . The range of possible data types is governed by
the programming language in use. However, there are basic data
types that are supported and operate in almost the same way across
all programming languages.
 The text we type on a keyboard, such as names, is another
example of data we may want to store in a variable. While we can
perform mathematical operations on numbers such as four and 256,
we clearly cannot do the same to textual data such as “James” or
“California.” There are data types on which mathematical operations
may be performed and some on which they may not. We shall talk
about the various types of operations that can be performed on
variables or data in VBA later in this chapter. However, for now, it
suffices to mention that the operations that are permissible on a
given variable are determined by its data type.
The last point is true even for weakly-typed or dynamically-typed
languages such as VBA; the only difference is who bears the
responsibility of ensuring adherence to the permitted operations on a
given variable as determined by its type. Strongly-typed or statically-
typed languages enforce adherence to type rules, whereas, weakly-
typed or dynamically-typed languages leave this enforcement to the
programmer. If the programmer does not follow data type rules in a
statically-typed language, the code will not compile and the compiler
will flag these data type errors instead. However, if the programmer
does not enforce data type rules in a weakly-typed language, the
code will compile successfully but then they may experience runtime
errors or logical errors.

VBA Data Types

Like all programming languages, VBA defines data types that may
be used when writing VBA code. Some may appear familiar to the
reader who has worked with other programming languages.
Nonetheless, do take time to go over this section as things may not
work in exactly the same way in VBA as they do in the other
languages you are familiar with. Each programming language
specification is free to define how things work when one chooses to
write code in that language. There’s no requirement to make things
work the same way as they do in another language.
All in all, VBA defines 19 data types. The complete list of these
data types, their size and the range of values that may be stored in
each of them is shown in the table below. The first half of the table
shows the most common data types. We shall start by discussing
these, and then end with a brief discussion on the less commonly
used data types. You may never need to use these less common
types in this book, given that it is intended as an introductory
installment.

Source: Microsoft.com

Boolean
The boolean data type is used to represent a value that can either
be True or False . As an example, we may use the boolean data
type to represent a variable that stored validation results. We can set
this variable to indicate that a cell in Excel is valid or otherwise after
a user has captured some input. We can then perform our validation
on the user’s input and set this variable to True if the user input is
valid, or False if the input is invalid. We can later interrogate this
variable to remind ourselves what the outcome of the validation was.
The boolean data type has a default value of False . This means
that when a variable of this type is created and accessed before it
has been assigned a value, it will return the value False .
Integral Number Types - Byte, Integer, Long Integer, and
Longlong Integer
Four data types can be used to represent integral or whole number
values in VBA. These are byte, integer, long integer, and ‘longlong’
integer. They differ only by the range of numbers they can technically
represent. This, in turn, is determined by the number of bytes that
are utilized by each of these data types. From the table above, a byte
occupies one byte (hence the name), an integer occupies 2 bytes, a
long integer occupies 4 bytes while a longlong integer occupies 8
bytes.
Any data is represented in terms of bits in a computer. Numeric
data can be conveniently converted to base two for this purpose.
From our earlier discussion, a byte is composed of eight bits. Given
this, what is the maximum integral number that can be stored in a
byte? This number will be achieved with every bit in the byte set to
“1” so that adding one to this number results in an overflow i.e., an
additional bit will be required to store the new number. In
mathematical terms, this will be the number 11111111 in base two.
Therefore, the maximum number that can be stored in a byte is the
decimal or base ten equivalent of 11111111, which is 255. The
minimum number that can be stored is the decimal equivalent of
00000000 in base two, which is zero. A byte, therefore, has a range
of zero to 255.
Things are, however, not as straightforward with the other three
integral data types as they are with a byte. This stems from the fact
that the byte data type was intended to represent “unsigned” integers
whereas the other three were intended to hold “signed” integers. An
unsigned number is one that is never qualified with a positive or a
negative sign. This is reserved for instances where a negative sign
for a given number will not make any sense. For example, storing
characters as integers using ASCII character encoding. Signed
numbers have either a positive or a negative sign associated with
them.
To represent signed numbers in a byte, we have to reserve the first
bit for the sign of the number. For example, we may choose to
designate “1” in the first bit to tell us that the number represented by
the rest of the bits in the byte is negative. Similarly, “0” in the first bit
will tell us that the number represented by the rest of the bits is
positive. For illustration purposes only, we shall assume that we have
a signed byte data type and demonstrate how the introduction of a
sign affects the range of values that can be technically represented
in a byte. Having reserved one bit for the sign, we now effectively
have seven bits to represent a number in the byte. The maximum
base 10 number that we can possibly represent with seven bits is the
decimal equivalent of 1111111, which is 127. So the effective range
of a signed byte is -128 to +127. The range may appear greater by
one on the negative side but this is because the bit pattern,
“10000000” would have represented “-0” (which is a non-existent
number) and interpreted as -128. In sum, the introduction of a sign
bit splits the range into two, with only half of the positive numbers
that were previously represented now represented, and the other half
converted to negative numbers. In practice, computers use two’s
complement to represent negative numbers. This is slightly different
from the idea of reserving the first bit for the sign of the number but in
essence, the concepts are similar. Two’s complement also inherently
deals with the problem of two zeros in the above example.
The range for the integer type is determined in a similar way. Two
bytes will give us 16 bits and when we reserve the first bit for the
sign, we have a total of 15 bits to represent the number. This gives
us the base two number 111111111111111, which is equal to 32,767
in base ten. The range for the integer type, therefore, works to be
-32,768 to 32,767. Try to determine on your own the ranges for the
long and the longlong data types.
Knowledge of the range of values for a given integral type is crucial
when authoring applications in VBA. The use of an incorrect type for
your variables can result in runtime or logical errors. For example, if
you declare a variable to be of type byte and later on you store user
input into this variable if a user enters a number outside of the range
for byte - say for example 256 - a runtime error will result because
the number 256 cannot be stored using eight bits. Having said that,
however, the temptation to use a larger data type with a greater
range must be countered by the undesirable larger memory that your
application will require to run. If you elect to use the longlong integer
type everywhere you need an integral number type, your application
will demand a larger share of memory to run.
The default value of VBA integral number types is zero.

Floating Point Number Types - Single and Double

Single and double are another form of numeric data types, the first
form being integral number data types. Both single and double are
used to represent numbers with fractional parts, or numbers with
decimal points. In computing, such numbers are known as floating-
point numbers. They are best represented in scientific notation, also
known as the standard form. This form represents a number as the
product of a one number, known as the mantissa, and then raised to
the power of another number, the exponent. This is illustrated in the
equation below, where is the mantissa and is the exponent.
Scientific notation is ideal for representing very large and very
small numbers. However, there is a trade-off; the larger a number (or
the closer to zero a fraction) is, the less precise its representation is.
For example, the speed of light of 299,792,458 m/s is often written as
3×10^8 m/s in scientific notation. Numbers in scientific notation are,
therefore, approximations and the more digits are used to represent
the mantissa, the more precise the approximation.
A memory location intended to store a floating-point number needs
to store both the mantissa and the exponent and this is where the
problem begins. How many bits do you designate for the mantissa
and how many for the exponent? If you assign more bits to the
mantissa, you can represent decimal numbers more precisely.
However, the range of numbers you can represent becomes smaller.
More bits to the exponent will give you a larger range but you lose
the ability to represent numbers more precisely.
The single data type uses four bytes and this means that there is a
total of 32 bits. One bit is used for the sign of the exponent, eight bits
for the exponent and 23 bits for mantissa. The double data type uses
eight bytes, implying a total of 64 bits. As such, one bit is used for the
sign of the exponent, 11 bits for the exponent and 52 bits for
mantissa.
The effect of the above is that the single data type is precise or
accurate to about six decimal places while the double data type is
precise to about 15 decimal places. Thus, if precision is what you’re
after, use the double type. If space conservation is of great
importance to you, consider the single type first and only use the
double type if loss of precision is not an option in a given scenario.
An important point to note is that both these data types should
never be used for processing or storing financial/monetary values.
They are approximations and at a certain point lose accuracy.
Perform enough of these calculations, losing precision at each point,
and you’ll soon begin to experience inconsistent data. For example,
in a banking application, the closing balance for a given period will
not equal the result of manually adding the total inflows to the
opening balance and subtracting the total outflows.
Similar to their integral counterparts, single and double has a
default value of zero.

Fixed Point Number Types - Decimal and Currency

This is the third and last form of numeric data types in VBA. They
were intended mainly to address the limitation of the floating-point
number types when it comes to accuracy. Precision and accuracy
are two often misunderstood concepts, particularly when it comes to
differentiating between them. A decimal number with more digits is
more precise. However, it is not necessarily more accurate. Accuracy
is how close to the actual number to be represented a representation
is. For example, 3.14285714 is more precise than 3.142857 in
representing the value of . The value 3.14159 is less precise
compared to both the previous values but it is more accurate.
If we determine the required number of significant digits to meet
the accuracy requirements of our application, we can fix the decimal
point. In VBA, fixed-point numbers are implemented as scaled-
integer data types and these are decimal and currency. The decimal
type can be used to accurately represent numbers with zero up to 28
decimal places, giving a total of 29 significant figures. It is
indispensable when you wish to avoid rounding errors. It offers the
greatest range when accuracy is also a requirement. This, however,
comes at a cost. The decimal type is the slowest to process and
therefore should only be used when no other data type can meet the
accuracy requirements of the situation. If accuracy is of major
concern, consider the currency type first, otherwise use the double
type.
The decimal type cannot be declared directly as it is a subtype of
the variant data type. We shall discuss the variant data type later in
this chapter. However, for now, it suffices to mention that the decimal
type is created by calling the CDec function and assigning the result
to a variable of the variant type.
Unlike the decimal type, the currency type is not a subtype of
variant. This means that variables can be declared to be of this type.
However, for practical purposes, the currency type is similar to the
decimal type, except that it fixes the decimal point at a
predetermined location. It assigns 15 digits to the left and four digits
to the right of the decimal point. As its name suggests, the currency
type is ideal for storing values or performing calculations related to
money or accounting.
The decimal type has a default value of Empty since it is a subtype
of variant, while the currency type has a default value of zero.

String
A string is a sequence of characters and is used to store textual
data. Alphabet characters, numbers, special characters, other
special symbols or any combination of these can all be stored as
strings. To differentiate between strings and other data types, string
text is enclosed in double-quotes. As such, a number that is
enclosed in double-quotes is treated as a string instead of a numeric
type. Nouns, descriptions, and instructions for the user are all
conveniently stored as strings.

Building Blocks of a Method or Procedure

A method is an action that Excel is supposed to perform on an
object, be it a workbook, a worksheet, a row, or a cell as examples.
In coming up with method, you will have to use different operators as
listed below:
 You can use mathematical operations to come up with a way to
automate actions in Excel. For example, in accounting, Gross Profit
= Sales - Cost of Goods Sold. You can write a macro that enables
Excel to calculate this figure if the variables Sales and Cost of goods
sold are defined.

Expressions
The VBA language relies on program constructs to determine the
order in which program statements and/or lines of code are
executed. Program constructs can be divided into three parts:
sequence, selection, and pattern matching.

Sequence
Any lists of instructions that executes one after the other is a
sequence. The norm is that when you run a macro, this sequence
will be respected and the code will execute the first line of code,
followed by the second and the third, and so on until all the lines
have been executed. There can however be predicted errors in the
code, that may result in the program failing to execute the lines
sequentially until the end. To circumvent this, you can use the Go To
statement. For example, if a line of code fails to run, should the
program crash? If this error was anticipated, a “Go To” statement
could have been added to the code, instructing the code to jump to a
specific line if a particular line could not be executed and then to
proceed sequentially from there.

Conditions
A condition in programming is a statement that allows the code to
choose when to run. A conditional statement thus forces the program
to first check whether a specified boolean condition is true or false
and the outcome of this evaluation will then determine what is
executed. The two conditions that are of importance at this stage are
the ‘If…Then’ statement and the ‘Select Case’ statement.
The If…Then Statement always ends with ‘End if’ is always written
in the following format in Excel VBA:

As can be seen from the above, the lines of code will only run if the
specified condition is true. Should the evaluation of the condition
return a value equal to false, then the lines of code nested between
‘If…Then’ and ‘End if’ will not be executed.
The alternate to the ‘If…Then’ statement is the ‘Select Case’
statement. With this conditional statement, variables are tested
against a list of values for equality. Each value that is tested for is
called a case and only those that match the list of values are
selected. As such each variable will be tested for each case. This
approach saves you from writing multiple if conditions in your code.
 Chapter 3 - The Excel Programming
Environment
So far we have been focusing on the VBA programming language
mostly in isolation. Before we can write our first VBA application, we
need to familiarise ourselves with the rest of the tools needed. This
chapter introduces the environment in which we will be using VBA to
develop Excel applications. Most of the concepts are common across
Microsoft Office applications. However, some of them, such as the
Excel object model, are specific to Excel programming.

Integrated Development Environment (IDE)

The IDE brings together a code editor, a form designer, a compiler,
a debugger as well as the Excel object model. In most cases, this is
all you need and this is where all coding will be done in this book.
As was explained in Chapter One, the IDE comes packaged with
Excel. It can be accessed from the “Developer” tab in the Excel
ribbon. If you launch Excel, and you cannot see this tab in the ribbon,
you can get it to show by following the instructions below.

Activating the Developer Tab

Navigate to File > Options > Customize Ribbon. Make sure that the
developer tab is ticked as shown in the image below:
 When you press ok, the developer tab will now appear on the
ribbon as shown below:

This developer tab is what contains all the built-in commands that
you will be using throughout this book. The VBA IDE can be
accessed by clicking on the button captioned “Visual Basic” in the
Code group. This opens up the IDE in a separate window.
At this point, one thing you most certainly want to do is modify your
IDE settings such that as you write your code, you will be required to
declare variables prior to using them. This has the effect of turning
the VBA feature “OPTION EXPLICIT” on. This feature was discussed
in the previous chapter. To do this, make sure you’re in the VBA IDE.
Navigate to Tools > Options.

Click the checkbox next to “Require Variable Declaration” to check

it. Click OK.

You can customize other functionality to your liking later but for our
purposes, we’re good to go.

Rubberduck
Software development tools have grown tremendously over the
last few years and unfortunately, our favorite IDE has not benefited
much from this growth. Productivity and collaboration have been the
main driving force behind these changes. So while we love VBA and
the fact that the IDE comes prepackaged with Microsoft Office, this
might be part of the reason why we haven’t seen much of the latest
development tools and options being added to the IDE. In fact, it
almost feels like a misnomer to call the Visual Basic Editor (VBE) an
IDE because of the limited features it offers to the programmer.
 To address the limitations of the Visual Basic Editor, I strongly
recommend the installation of the Rubberduck add-in. This will assist
you through your VBA learning process as you go through the
content of this book. You may opt to remove the add-in when you
feel you no longer need it. Rubberduck is an open-source project that
brings powerful development tools that some of the developers
amongst you might be familiar with from other environments. It is
made available under the GNU General Public License version 3,
meaning that it is available, and absolutely free, for commercial or
non-commercial use. While the average user, certainly the beginner,
might not be in a position to take full advantage of the power of
Rubberduck, there is certainly great value in having it as part of your
development environment. Rubberduck brings such features as code
completion, code refactoring, code analysis, code formatting, to-do
items and easier management of external references. Advanced
tools for advanced users include code inspections, regular
expressions management, code metrics, unit tests, and source
control.

Installing Rubberduck
Therefore, before proceeding any further, let’s head out to the
Rubberduck GitHub page and download a genuine, free version of
the add-in and install it. From your preferred browser, navigate to
https://github.com/rubberduck-vba/Rubberduck/releases/tag/v2.5.0 .
Throughout this book, we’re going to be using version 2.5.0.
However, use of a later version is also fine. Scroll down to the Assets
section and download the.exe setup file.
Clicking on the highlighted hyperlink will initiate the download. After
the download has completed, Double-click on the setup file to run it.

Select a language and click OK.

 Select “Anyone who uses this computer” and click on the “next”
button.

Accept the license agreement and click on the “next” button.

 Click on the install button.

Click on the checkbox to allow registration of the Rubberduck add-

in and click on the “next” button.
 You’re all set! Click on the finish button to exit the installer.
Relaunch Excel, navigate to the Developer tab and launch the IDE.
You should see the following splash screen, indicating that the
Rubberduck add-in is loading:

Configuring Rubberduck
You should be having a Rubberduck menu option in the main
menu in the main application toolbar. Click on this menu option and
go to Settings. You will get a modal window, similar to the one shown
below.

In the general tab is where you will be able to turn on or off any
features. You may want to leave the default settings for now until you
have figured out what’s useful and what’s not in your case. Configure
the settings below and as shown and click on Save & Close.
 With that, you’re all set. Having activated the Developer tab and
configured our IDE, We will now explore the main menu toolbar as
well as each of the different windows and tools available in our IDE.

The Menu Toolbars

The main menu toolbar is the top row containing the main
operational buttons. Other supporting toolbars are found on the
second and possibly third row, depending on how you have
configured them. These supporting toolbars have shortcut button
icons that enable you to carry out certain frequently performed
operations such as save, undo, redo, etc., fairly quickly.
File
The file menu option exposes functionality to save, export and
import files to and from the programming environment as well as
print your code or form images.

When saving your Excel VBA files, always make sure that you
save as an Excel Macro-Enabled Workbook as shown below. This is
because VBA code is run as a macro and when macros are disabled,
your program will not be able to run.

Edit
 In addition to the other functions like cut, copy, paste, select, etc.
that you are used to from using other Microsoft products like Word,
the edit button enables you to do the following:

List Properties/Methods
List Constants
Quick Info
Parameter Info
Complete Word

View
This is used to bring into view various windows, most of which will
be explained later in this chapter. It also enables you to customize
your toolbars by navigating to View > Toolbars > Customize. A modal
window will display, giving you various options to customize your IDE
toolbars.

Insert
The insert menu option gives you a way by which to automate the
creation of new procedures, UserForms, modules, and class
modules. Normally you type these in code but using the commands
available with this menu option, you can have the IDE auto-generate
some boilerplate code that you can then edit to suit your
requirements.

Format
This Format menu option provides powerful tools to format the
controls in your UserForms. These tools are indispensable if you
wish to create polished, professional-looking UserForms. For
example, by selecting the controls you wish to format, you can
choose to have them all in the same size by navigating to Format >
Make Same Size, and specifying whether you wish for their width,
height or both to be the same.

Debug
Most people are now familiar with the term “bug” being used to
refer to an error in a program. Debugging is the process of “hunting
down” (identifying) and “squashing” (fixing) bugs (errors) in your
program. The Debug menu option gives access to various tools to
aid you with debugging should you find yourself in a situation where
you need to. These tools will be discussed later in this chapter.

Run
The Run menu option is used in conjunction with the Debug menu
option to run a program within the development environment. It is
mainly used for testing prior to deployment.

Tools
The Tools menu option allows you to, among other functionality,
manage your references to other code libraries that you are using in
your project. For example, you may wish to use Adobe Reader
functionality in your application. You can use the References tool to
add a reference to the Adobe Reader File Preview Type Library to
your application.
 You can also change the name, description and other attributes to
your VBAProject, as well as set a password for one to open your
project by accessing the VBAProject Properties. Some of your IDE
settings can be managed from Tools > Options. You can opt to sign
your VBA project with a digital certificate to increase its
trustworthiness, as will be explained later on in this chapter.

Add-ins
Earlier in this chapter, we added Rubberduck to our IDE as an
add-in. The Add-ins menu option gives us the interface to manage all
our installed add-ins. We can opt to load or unload the add-ins and
choose if we wish them to load automatically on startup of the IDE.

Project Explorer
The project explorer can be opened by navigating to the main
menu and selecting View > Project Explorer, or by simply pressing
Ctrl + R on your keyboard. It is highlighted in the image below.
 The project explorer groups in a hierarchical order all the objects
that are available in your solution. At the highest level of this
hierarchy, we have a VBA project. This is a container for organizing
your solution to a specific problem you’re trying to solve using VBA.
For example, as a teacher, you may want to provide advanced
functionality for use in the capture of marks for your students and do
some statistical analysis on those marks. This is your problem, and
you have resorted to Excel VBA for a solution. All the work you will
do in VBA to solve this problem, i.e., the VBA solution to this
problem, will be contained in a project. In most cases, you will have a
single VBA project for a given workbook or Excel file. A different
workbook intended to solve a different problem altogether will have a
separate VBA project. The Excel VBA object model, which we will be
discussing later in this chapter, is also included in the project
explorer. The hierarchy in our project explorer will grow as we add
more objects and functionality to our project.

Code Editor
The code editor is the space where you write code. It’s just like any
other text editor and displays the text that you type. The text,
however, is automatically formatted in a simple monospace font. This
font is ideal for coding as lines of code are easier to read,
punctuation marks are more visible, and vertical alignment and
indentation are more easily achieved. There is thus no advanced
formatting tools in the VBA code editor as you would have in
Microsoft Word. You can, however, consider customizing your
formating through playing around with the font colors.
As you write your code, the norm nowadays with most code editors
is that the editor automatically assigns a line number to your code as
you program. These line numbers are useful for debugging purposes
as you can directly go to the line of code that is not running as
expected. The VBA Editor, however, does not have this as an in-built
function and should you want to have this functionality, you will have
to install a third-party add-in.

Code Containers
Code is written in containers that are integrated into the VBA IDE.
Before you start coding you need to learn how to choose the best
place to write and store your code, else your code will not be
executed if it cannot be found. There are four different types of code
modules available to you and these are a workbook module, a
worksheet module, a UserForm, a standard module, and a class
module.

Workbook
An Excel workbook is a collection of related worksheets. This
workbook, depending on the task that needs to be accomplished, is
treated as a single object in the Excel VBA hierarchy. Macros for
performing workbook level tasks such as creating a table of contents,
saving and closing workbooks, activating a workbook, saving specific
worksheets within the workbook, etc., are written and stored in the
workbook module, which makes the workbook a code container.

Worksheet
A worksheet is the most common object in Excel VBA. Macros for
automating operations at the worksheet level like activating a
worksheet, selecting and moving a worksheet, copying and pasting a
worksheet, calculating values in a worksheet, etc are stored within
the worksheet module. Such macros, if run, will only apply to the
particular sheet where the macro is stored. As such, each worksheet
within a workbook is also a code container.

UserForm
A UserForm is a dialogue box that you can design and customize
to simplify and control data collection. An example of a UserForm
that you will design in Chapter Six of this book is shown below:
 This form contains a number of buttons that by design are
supposed to do something when clicked. For example, there are
what are called command buttons, e.g., the save button and the
reset button. If clicked, some background processes are supposed to
happen in order for the information that has been entered on this
form to be either saved or cleared. These click events will have to be
coded and the code for what happens when each of the command
buttons is clicked will be written and stored in the UserForm module.
As such, the UserForm itself is a code container. You will learn more
about this later on in this chapter and Chapter Six.

Standard Module
As a learner, being introduced to Excel VBA, this is where you will
be writing your very first Excel VBA programs. All your basic macros
can go into this basic module, which can easily be accessed if you
click on “insert>module”. This will launch a blank text editor which is
where you will start writing your code.

Class Module
This module is also found under the “insert” button. This module
allows the user to create objects through defining methods,
properties and event handlers. We won’t be discussing this type of
module in this book but as you proceed to the intermediate and
expert stage, you will be working with these.

Full Module View vs Procedure View

All code for a particular object within VBA is written in the same
module. You can, however, toggle between viewing all the code that
you have written for that object so far and seeing only the code that
you are currently working on as shown below:

Colors
The editor also enables you to format code using colors. Keywords
i.e. functions and variables can be formatted to show in a certain
color. You can change your editor settings to set the color that
variables should be written in as follows:
Tools > Options > Editor Format > Keyword Text as shown below:
 IntelliSense
When you are typing code, you may find that when you type a
function name, for example, there is a drop-down menu that shows
possible ways of completing the code. This is due to intelligent live
analysis of the text in your code editor, which then outputs an
intelligent guess of what you probably want to type. This
autocomplete option is called IntelliSense. Having this function and
knowing how to use it can reduce keyboard input and errors. To
autocomplete your code line, all you need to do is to use your arrow
keys to highlight the appropriate suggestion and then press tab. A
reduction in errors means that you get to finish coding faster. To
activate this, you can press the Control key plus the space bar key.
To test this out, launch the code editor in and type “ms.” Then press
the control key plus the space bar. If your application has
IntelliSense enabled, you should see the following suggestions as
seen in the picture below:
 If you are not getting the IntelliSense drop-down menu, check if it
is enabled. Go to Tools > Options From the options menu, check if
“Auto List Member” is ticked as shown below.

Windows
When you launch your IDE for the first time, you will notice that
there are three windows shown by default at the bottom of the
screen. These are the Immediate window, the Locals window, and
the Watches window. You can also access these from the View
menu. These three windows are used extensively in the debugging
process. The Locals window, for example, displays the state of
variables that are inside the subroutine that is currently running.
From all the variables that are in the subroutine that is running, you
can select a few that you want to monitor and show them in the
Watches window. The Watches window thus will display the state or
contents of variables that you select to watch as the macro runs. The
Immediate window enables you to inspect and manipulate the values
of variables that are in scope and to call other methods without
necessarily affecting the values of variables. That sounds like a lot
right! Since you are beginning on your VBA journey, this book is not
going into detail about debugging other than tips on how to fix
common errors you may encounter as you write your first few
programs.

Properties Window
All objects in VBA have properties, be it a workbook, worksheet,
UserForm, or controls within a UserForm, etc. A control button, for
example, has properties like a name, border, border color, height,
width, etc. These properties can be viewed and changed in the
Properties Window” In addition to the three windows described
above, under the view tab in the VBA IDE, there is another window
titled “Properties Window”. Clicking on this will open a window to the
left of the VBA Editor, which will display the properties of any object
that you highlight in your VBA project.

Form Designer
The forms that you will be using to collect information from users
are designed using a form designer and the toolbox. The form
designer is just a blank slate that you launch by clicking on
“Insert>Userform”. It is on this form where you can add different
controls, which are objects that display data for the user or accept
input from the user. From the toolbox, you have available to you an
array of different controls that you can use to design your form. A few
examples of such controls are shown below:

These form controls can be formatted easily using the format

button which will give you a lot of options that will standardize the
size of the controls in terms of width, length, alignment, and the
arrangement of the controls within the UserForm. This way, you do
not waste a lot of time trying to manipulate these controls individually
to get a professional-looking UserForm. Each form-control can have
code associated with it, as was explained under code containers,
and you will learn how to do this in detail in Chapter Six.

Object Model
What Is an Object
In the context of programming, an object is an encapsulation of
data and functionality to manipulate that data. It is intended to store
together data relating to a specific entity and the permissible
operations that can be performed on that data. Both data and
operations are collectively referred to as members.
For example, consider the car entity as an example. Data you may
want to store or keep track of for this entity may be maximum speed,
fuel level, mileage, the current gear number (assume this is an
automatic gearbox) and the number of occupants. Permissible
operations that may be performed on this data are top-up fuel as well
as drive the car. One may not alter the maximum speed or directly
alter the mileage - the maximum speed is a function of other factors
while the mileage may only be increased by the drive operation,
under no circumstances may it be reduced. Updating the mileage is
not the responsibility of the driver and he does not need to know how
exactly this happens. The same goes for the gear number in an
automatic vehicle - the driver does not need to know what gear
number the car is in. This is illustrated below.
 The driver can read the maximum speed, fuel level and mileage
from the dashboard. However, he may not directly alter these. The
driver can tell the number of occupants in the car and also directly
alter this number by letting people in or out. However, he cannot tell
the current gear number (consider only vehicles that do not display
this) and neither can he directly change it. Coming to operations, the
driver can top up fuel and drive the vehicle. However, he cannot
directly perform an update of the mileage.
In the context of programming, the car is the object. It
encapsulates properties (data) and methods (operations). Think of
properties as variables, which means they each have a data type.
Object members may be public or private. A public member may be
accessed outside the object while private members may only be
accessed from within the object. In our example,
CurrentGearNumber is only useful to the Car object and hence it
does not need to let anyone (the driver) know what the current gear
number is. However, the other properties are of interest to the
outside world and as such are marked as public. The same goes for
the operations - TopUpFuel and Drive may all be performed by the
outside world (the driver) while UpdateMileage may only be
performed by the Car object itself and therefore does not need to be
exposed to the outside world.

The Dot(.) Operator

The dot operator is a powerful notation in programming. It enables
us to avoid ambiguity where a property or name exists on two or
more different objects and when we use object hierarchies. Most
objects have a Name property. Suppose you have two objects:
objectA and objectB. Simply writing “Name” in your program when
you wish to access the name of objectA is not enough. You need to
be specific as to which object whose Name property you wish to
access. The dot operator gives us a convenient solution here. In
much the same way as it is used in URLs in browsers, it allows us to
specify the higher-level object and a lower-level object or a method
or property, separating them with a dot. In our example, we can write
objectA.Name. For object hierarchies we can write
objectA.childObject.grandchildObject.Name.

The With… End With Construct

There are instances when we are working with the same parent
object in several lines of code. In such cases, it becomes
cumbersome and verbose to continually specify the parent object all
the time. The With… End With construct provides a convenient way
of declaring that in the following block of code, bounded by “With”
and “End With,” you’re referring to a specific parent object and
therefore you will not be prefixing calls to its child objects, properties
or methods with the name of this object. This is illustrated in the
example below.

The code above replaces the code below, which does not make
use of the With… End With construct.

Access Modifiers
To control access to the properties and methods encapsulated in
an object, we use access modifiers. We have already used these in
our Car object example above. These are Public and Private. The
Public access modifier marks a method or property as visible to other
code outside of the object itself. Most methods and properties we
shall be working with are Public and that’s why we can access them
using the dot operator. The private access modifier means that a
method or property is only visible or accessible within the object only,
code outside of the object has no access to it. The implication of this
is that you will only be able to use private methods and properties in
the objects that you define yourself. However, this is outside of the
scope of this book.
In our Car object example, MaximumSpeed, FuelLevel, Mileage,
and NumberOf Occupants are all public properties. Public methods
are TopUpFuel and Drive. CurrentGearNumber is a private property
while UpdateMileage is a private method.

Functions vs Subroutines
Object methods can be of one of two types namely functions and
subroutines. The name function is borrowed from mathematics and
implies that the method returns a value after performing its defined
action. A subroutine, on the other hand, does not return a value after
performing its defined action.
A function is declared as follows in VBA:

A subroutine is declared as follows:

Method Signature
A method signature is the first line in a method definition and
defines the applicable access modifier, what type of method it is, the
method name, the data type of the value that the method returns as
well as any additional information or parameters that the method
needs to perform its function. In the two examples above we can see
that the function declaration has a signature that specifies the Public
access modifier, states that the method is a function, gives it the
name of “MyNewFunction” as a return type of Boolean. The method
signature below shows a typical method signature we are likely to
encounter the most.

In brackets, after the method name, is the parameter list.

Method Parameters and Arguments
Methods can define one or more parameters that they need when
they are called. Parameters on methods provide a way of passing in
additional information to a method to enable it to perform the
required action. They are similar to the concept of operands in
mathematics. In this case, an operator, such as multiplication, takes
in two operands, performs the multiplication operation on them and
returns the result. Likewise, a function or subroutine in VBA will
specify in its method signature that whoever is calling it has to
provide some parameters.
Parameters are defined the same way as variables in the method
signature. When the method is called, however, the actual values
that are assigned to these parameter variables are known as
arguments. For example, in the below call to the Func method
defined above, 123 is an argument.

Collections
Objects of the same type may be grouped into a collection. Think
of your collection of cars (real or imaginary). Collections allow us to
refer to all the objects within them as one entity in certain
circumstances. For example, an Excel workbook is composed of
multiple worksheets, stored as a collection. Within an object, you can
find other objects and collections such that complex hierarchies of
objects and collections may be constructed. This leads us directly to
our next discussion.

The VBA Object Model

VBA is built upon a large hierarchy of objects. This hierarchy has
objects relating to VBA directly as well as each of the Microsoft
Office applications with which VBA may be used. These objects
together determine the state of your project. When you’re
programming in VBA, you will be manipulating these objects. A
Microsoft application such as Excel or Word is known as a host
application in VBA. Each host application extends the VBA object
model with an object hierarchy specific to that particular host
application.
When you launch VBA from a host application, the object model for
that host application is automatically loaded. In our case, since we
are going to be working in Excel, when we launch the IDE, the Excel
object model will be loaded. Suppose however that the solution to
our problem requires that we send commands to another Microsoft
Office application. This may, for example, be when we need to
automatically produce a report in Word. In this case, we have to
manually load the Word object model. To do this, navigate to the
main menu Tools > References. The following modal window will be
displayed.

Click on the checkbox next to Microsoft Office 16.0 Object Library.

This loads the Word object model and gives you access to Word
objects within your Excel VBA project should you need them.

The Excel VBA Object Model

The Excel application represents the application object in the Excel
object model. It is the root object. When your host application is
Excel, it is also the default object and any method called without an
object name prefix will be invoked on the Excel application object. If
working within Excel, you need to call a method on the Word
application object, you must prefix it with the Word application object
identifier. The application object has, among other properties, a
Workbooks collection property, which is the collection of all currently
open Excel files or workbooks. An Excel file represents an Excel
workbook object in the Excel object model. Within each workbook,
there are other objects and collections. In turn, these objects and
collections may also have other objects and collections within them.
All these objects have predefined properties, most of which have
default properties. We use the Excel object model to manipulate
Excel objects.
In hierarchical form, the Excel object model can be represented as
follows:

Among various other properties, an Excel workbook has a

worksheets collection property. This refers to all worksheets that are
on a given Excel file. Creating a new worksheet object and adding it
to this collection is the equivalent of adding a new worksheet to the
Excel spreadsheet. Within a worksheet, you can define several
ranges, which are collections of cells. A range may refer to one cell,
multiple contiguous cells or multiple non-contiguous cells.
Actions may be performed on objects using their methods. For
example, with the worksheet object, you can copy the worksheet
(copy method) and you can delete the worksheet (delete method).

Object Browser
 The object browser provides a means for viewing the object model.
It shows the objects, properties, methods, events, and enums in an
object model. It can be opened by navigating to the main menu >
View > Object Browser and can be seen highlighted in the image
below.

The image below zeroes in on the object browser, highlighting the

various sections available.
 At the top of the object browser window is a dropdown that allows
you to select the object model to be displayed. In the above image,
the current selection is Excel, meaning that the object model shown
is the Excel object model. You can choose to view the entire object
model, the Office object model, the VBA object model, the
VBAProject object model or the OLE (Object Linking and
Embedding) standard model.
In simple terms, a class is another name for an object. To be more
specific, a class is a template that defines the structure of objects of
its type. In this sense, a class is similar to the data types we
discussed in chapter two. We can declare objects of a given class or
type. The leftmost section with “Classes” highlighted lists all the
members of the selected object model. These can take the form of a
class, a module or an enum. Modules and enums are discussed in
chapter four.
The rightmost section lists the members of the class, module or
enum selected in the leftmost section. Members may be properties or
methods. Lastly, the section at the bottom provides a description of
the currently selected item in any of the other sections described
above. Selecting VBAProject from the topmost section gives you all
the objects that are available in your project. You can select any of
these objects to view their members. Whenever you’re stuck as to
what methods and properties are available with a certain VBA or
Excel object, the object browser is ready to serve.

Modules
A module is a unit of functionality. It is a concept synonymous with
procedural programming where functionality is achieved through
defining a series of procedures that work together to achieve the
desired functionality. A module is a container for related procedures.
How you split your procedures amongst your modules is entirely up
to you. However, it is best practice to group procedures that are
related into one module. A procedure can either be a subroutine or a
module.
If you’re coming from an object-oriented programming background,
it may help to think of a module as a static class. The functionality
and state, which is the value of variables at any given time of a
module, is shared across all instances where it is used. You can
create as many modules as you need to organize your code. Take
note that you can only have one procedure with a given name in a
module. However, across different modules, you can repeat a name.
If you’re making use of modules that contain procedures with similar
names in another module, it is always best to fully qualify the
procedure name using the dot nation.

Class Modules
A class module is a template from which objects can be created. It
defines the properties, procedures, and events that objects created
from that class module will have. It is similar to the basic module
described above, except that you may not call any of its members,
i.e., properties, methods or events - directly. You have to create an
object first and then call the properties defined in the class module
on this object. With objects, state is unique to each object. This
means that even though you may create multiple objects from the
same class module, they will each have their own state.
 Enumerations
An enumeration is a convenient way of representing constants that
are related in some way. Instead of defining these constants
separately, you declare them inside a named enumeration. To
access them, you state the name of the enumeration and then the
constant, separated by a dot. This is the dot notation that was
introduced in Chapter Two. Enumerations are also known as enums
in short.
VBA has numerous built-in enumerations. For example, when you
are coding a dialogue box that offers you the option to click the OK
button, you will make use of two enumerations - one that defines the
button to be displayed in the dialog box and another that lets the
code that displayed the message box know which button the user
clicked. There is a predefined set of buttons that may be displayed in
a message box, the OK button is just one of them. These buttons are
identified by an enumeration, shown below. This tells the code that
handles the display of the message box that you wish the message
box to be displayed with a particular set of buttons on it. If you do not
specify which button is to be displayed, the default one, OK, is
displayed.

The construct Enum … End Enum is used to create an enum.

Enumerations may only have integral values such as byte, long, long
integer.
In addition to the system defined enumerations, you’re free to
define your own enumerations.
To access an enumeration value, for example, vbOKOnly , you’d
write the following:
vbMsgBoxStyle.vbOKOnly
This returns the value zero. However, you’re free to use it as 0
should you wish to.

Events
 Events
An event denotes an occurrence, such as the click of a button in
the program. Other examples are opening a new workbook, inserting
a new worksheet and double-clicking a cell. You may want to
perform a specific action when a given event occurs. For example,
you may want to validate a user’s input when they hit enter after
editing
Events gained popularity with the advent of graphical user
interfaces, where a user can possibly perform one of several actions
on the screen. It is important to know what action the user has
performed so that the program can respond appropriately. This
process of responding to the occurrence of an event is called event
handling. To handle an event, you need to subscribe to it so that
when the event occurs, you get a notification and you can then take
any action that you desire in response. In VBA, subscribing to an
event is implied once you write a handler for that event.

Event Handlers
An events handler is a special procedure that executes when an
event occurs. The occurrence of the event automatically calls the
event handler, which in turn may call other procedures required for it
to complete the desired action. In code, the event handler is a
subroutine that has a special structure to differentiate it from other
procedures. This is because the event handling mechanism in VBA
is based on a naming convention. An event handler has the following
structure:

The structure generally resembles that of a subroutine. However,

there are two key peculiarities. The first one is the name of the
subroutine. It starts with the name of the object to which the event
belongs followed by an underscore (_) and ends with the name of the
event that triggers the event handler. The second peculiarity is in the
parameter list of the subroutine. This may not be immediately
obvious but as you make use of events more and more, you will
realize that each event has a predefined set of parameters
associated with it while some do not have parameters at all. The IDE
takes care of this for you; when you create a handler for a given
event, the proper name and parameter list is inserted in the code
editor. Do not tamper with the subroutine name or the parameter list
as it may result in errors or cause your event not to execute. Take
particular care when using breakpoints in event handlers.
Breakpoints can potentially stop other events from firing.

Creating an Event Handler

Event handlers can be created for the following objects:

Excel (application-level events)

Workbooks (workbook-level events)
Worksheets (worksheet-level events)
Userforms (UserForm-level events)
Charts (chart events)
Class modules (module-level events)

There are also two events that do not belong to any object. These
are the OnTime event and the OnKey event. They are generally
referred to as non-object events.
An important point to note is that as a general rule, event handler
procedures are not created in standard modules. Instead, they must
be created in the modules that correspond to the objects to which the
event is expected to happen. Disregarding this rule will result in the
event handler not executing when the event occurs. To compound
the problem, you will not get an error message, meaning that you
may end up spending hours trying to figure out why your event
handler is not getting executed.
However, there are exceptions to this rule and these are when
you’re dealing with non-object events as well as application and
embedded chart events. For non-object events, the event handler
code is created in a standard module. For application and embedded
chart events, the event handler code is created in a class module.

General Rule
To create event handlers that conform to the general rule stated
above, navigate to the project explorer and right-click on the object
for which you want to create an event handler. Select “View Code”
from the context menu. This will open/activate the code editor
window.

Select the object (if not already selected) from the dropdown list on
the top left of the code editor window. For example, if the object you
selected is a workbook select Workbook and if the object is a
worksheet select Worksheet. If there are any existing event handlers
for the object, they will be displayed in the main window within the
code editor. In the image above, we can see that there is already a
handler for the workbook opened event, even though it is presently
empty. To create a new one, click on the dropdown list in the top
right of the code editor window. This will show you all events for the
selected object for which event handlers may be created. Select the
event you wish to create an event handler for. We are going to create
an event handler for the NewSheet event.
 The IDE will automatically create the event handler for you, taking
care of the naming and the parameter list for you. Do not change
these; all you need to do is start adding behavior within the space
provided.
 In the handler for a new worksheet event, we have access to the
new worksheet object through the event handler’s Sh parameter. We
can then perform any action we wish to whenever a new worksheet
is created on the workbook object we selected. For example, we can
set the worksheet name.
Non-Object Events
With non-object events, you can create handlers for these in a
standard module. The framework will be able to pick all event
handlers and execute them regardless of what module they have
been created in.

Application and Embedded Chart Events

Since we do not have access to the module for the application
object, we create application-level event handlers in class modules.

Worksheet Level Events

Think about any actions that a user can take on a worksheet,
changing a cell value, double-click on a cell, right-click on a cell,
selecting a bunch of cells and dragging the contents to different cells,
etc. All these are worksheet level events and you can write code
which specifies what happens should any such event take place.
This code will be written in the worksheet code window. Within the
VBE editor, launch the project explorer (View>Project explorer), then
navigate to the particular worksheet where you want to write
worksheet level macros, right-click and click on view code as shown
below:

This will open a worksheet module that will contain your macros for
that particular worksheet. Alternatively, from the project explorer,
navigate to the particular worksheet you want e.g., worksheet 2, and
double click on it. This will open the code window. Any code that is
written in this window will only be executed if a specified event
happens in worksheet 2 only. When it comes to writing code, you
have two options:

Writing all the code from scratch. After double-clicking on

Sheet 2 as was explained above, when the code window
opens, it will be blank as shown in the picture above, and you
can start specifying what exactly you want to happen.
Alternatively, you can use the drop-down menu

Select Worksheet, move over to the dropdown menu where there

are declarations and click on the arrow and you will see the following:

This drop-down menu lists all the events related to a worksheet

that you may want to code e.g., Activate, double-click, right-click,
calculate, change, deactivate and so on. You can select the event
that is of interest to you which will automatically insert the first line
and last line of code, and your own code goes in between these
automatically inserted two lines of code.

Workbook Level Events

At a workbook level, events that a user can perform that can
trigger a set macro to run include opening and closing a workbook,
adding or removing worksheets, right-click on a worksheet, etc.
Macros for workbook level events are written in the workbook module
that can be accessed from the project window. Follow the same
steps that are depicted above under the worksheet event, except that
for workbook level events, you have to navigate to “This Workbook”
and double click to open the workbook code window or right-click on
“This Workbook” and view code. If you change from general to “This
Workbook” in the workbook code window, you can also see a
complete list of workbook events that you can code in Excel VBA.

UserForm Level Events

If you create a UserForm, the expectation is that users have to
interact with the UserForms in one or the other e.g., double-clicking
on a button, pointing at a label, clicking on an arrow in a combo box,
selecting options, etc. For each control, you will have to think of all
possible events that can happen and the desired effect that each
such event should trigger. You will then have to write an executable
macro and set it to run when appropriate events have occurred for
each button. Have a look at this form that you will create, from
scratch in Chapter Six:
 What events do you think can be associated with this form for
users to get to fill it in?

Activate - Users can only get to fill in this form if it is visible. So

there has to be an event that displays or reactivates the form.
Click- Using a mouse, a user can click on any of the controls
depicted above. To highlight the importance of the click event,
think about what you would want to happen if the user clicks
the command button “Reset”. Should a macro run that just
erases all the information that has been entered? But, what if
the user clicked this command button in error, and instead
wanted to click the “Save” button? So, in this case, it would be
better to have a macro that first asks for confirmation from the
user before resetting the form.
Double click: What if a user, double clicks instead of just
clicking once on any of the command buttons. Should anything
happen?
MouseMove event- If a user moves the mouse and hovers it in
any of the controls buttons, do you want the color of the button
to change to show which button is now active.

The above list of UserForm events is not exhaustive. The code for
each of the controls will only be added after you have designed your
form and added all the controls that you think you will need on the
form. If you double click on each of the controls, a code window will
open where you can add all your event code for each button. Just
like with a Workbook and worksheet event, look for the drop-down
menu that contains all the possible events that can be associated
with the control that you are writing an event for.
Non Object Events
The two non-object events that were mentioned above are OnTime
Events and OnKey Events. With OnTime events, macros are set to
run at a specific time or after a specified time has elapsed. With
OnKey events, macros only run when a specific combination of
keystrokes is hit on a keyboard. The code for these can be included
in any code container.
 Chapt er 4 - Hello World!
Finally! After laying the theoretical foundation, we are ready to
write our first VBA program. While programming is heavily
underpinned by mathematical and computer science theory, it is still
a practical discipline. This book takes cognizance of this fact and as
such, the remainder of the book follows a practical approach. In this
chapter, we will be applying our knowledge of VBA fundamentals and
taking advantage of the tools available in the Excel programming
environment to author a simple, fully functional program.
To instill good programming practice, even for the most trivial of
solutions, we will follow the established five stages of the
programming process. These are defining the problem, planning the
solution, coding, testing and finally documenting the solution. This
will help you easily tackle complex problems, increase your
productivity and improve the quality of your solutions. In subsequent
exercises, you’re encouraged to practice following these steps to
strengthen your grasp.

Problem Definition
What exactly am I trying to achieve by writing this VBA program?
Is it feasible? Is a VBA program the best solution to the problem or
there is a better way of solving the problem without resorting to
writing a program? These are questions that you should attempt to
answer first before you even write a single line of code. Answering
these questions can save you a lot of time later and can quickly set
you on the right path to an optimum solution right from the onset.
Excel is a powerful application straight out of the box. It offers a lot of
powerful functions that are excellent for solving many problems.
Resort to VBA programming only if these tools are not enough to
meet your requirements.
It helps to write down your responses to the above questions.
Continue to refine your answers as new information becomes
available and as you consider more items. In the end, you should
have a clear, concise and unambiguous problem that you are trying
to solve. Develop your problem statement. This should define the
problem in no more than three sentences. One sentence is enough
for most problems. Avoid paragraph-length problem definitions as
these only introduce ambiguity and obscure the main objective. Don’t
be tempted to include details of how you’re going to solve the
problem at this stage; focus on answering what you’re trying to solve.
For our “Hello World!” program, we want to write a program that will
display a message box that contains the message “Hello World!”
when it is run.
After developing the problem statement, supporting information
may be captured to provide more detail and guidance for use in the
planning process. Feel free to use diagrams of how things are
supposed to look like. For example, sketches of how the user
interface is supposed to look like may be useful.

Planning
Only move on to the planning stage if you have determined that a
VBA program is the optimum solution to the problem. Here you
attempt to answer the how part. Get into the specifics of how you’re
going to solve the problem and how the different parts of your
solution are going to work together. To be able to do this, you might
need additional information that may not have been provided in the
first stage. Problem definition focuses on what problem needs to be
solved. In most scenarios, a different person or department
altogether comes up with this problem definition. They may,
therefore, assume knowledge of certain domain-specific processes
and norms. As the solution developer, you may need to go back and
gain a deeper understanding of the processes surrounding the
problem at hand. This will enable you to design a solution that is
relevant and pragmatic. Seeking and obtaining detailed knowledge of
the application domain is known as systems analysis and forms the
first half of the planning process.
The second half of the planning process is systems design. Here,
you take into account all the information at hand and come up with a
design for the solution. The output is a specification that will be used
during the coding stage. You need to be thorough and specific in
your design specifications. This will provide a benchmark against
which to evaluate the correctness of the solution at the end. If the
specification is wrong, a solution that provides incorrect results will
likely be developed, or the solution will fail to solve the intended
problem altogether.
There are various tools available to aid in the planning stage.
Examples include flowcharts, pseudocode, use case diagrams, and
data flow diagrams. We shall not go into detail on these tools.
However, choose the tools you’re most comfortable with as you’re
likely to make more effective use of these. Do not be tempted to use
a tool you’re not familiar with as it will waste your time and possibly
not serve the intended purpose.
In this case, we are going to use a flowchart to show the program
logic. Program logic is best captured in an algorithm, which is a step-
by-step guide to performing a specific task. For complex problems, it
is possible to define your algorithm at a high level and then
recursively fill in the details as you get more specific with each sub-
component of your solution. The following is the logic for our
program.

When the user runs the program, it will immediately display a

message box with a predefined text. This message box will have an
OK button and when the user clicks on this button, the message box
will disappear and the program will stop.

Coding
Armed with our specification, we can now begin to write our
program. Coding translates the design into an actual solution that
can be used to solve the problem at hand. The logic expressed in the
flowcharts and/or pseudocode developed as part of the planning
process is expressed in a suitable programming language.
 If the previous stages were done properly, when you get to this
point you will be having all the information needed to write a
complete program. However, should you find yourself in a situation
where you still have questions regarding the problem you’re trying to
solve or the design specification, the sooner you seek clarification
from the users the better. You certainly do not want to go all the way
with your solution development process to only discover problems
right at the end.
It is important to freeze the user requirements at the planning
stage before you get to the coding. Changes at this stage are very
disruptive and potentially invalidate the solution as not all aspects are
taken into account. For example, a user may decide to add a new
feature to their original request. Sometimes this may seem innocent
and simple enough to include in the solution right in the middle of
coding. However, it may affect another fundamental part of the
program, resulting in the solution taking longer to develop. Adding a
new field to a data capture form may mean more validation code
must be written and the field must be catered for in the code that
saves and reads the form information to the database.

Coding “Hello World!”

Let’s get started with coding our program. Before proceeding, it is
highly recommended that you go through the material in Chapter
Three to help you set up your environment.
Create a new Excel spreadsheet and save it with the name “Hello
World!” Be sure to save it as a Macro-Enabled Workbook file type,
with the extension “.xlsm”.
From the Developer tab, click on the “Visual Basic” button to launch
your IDE. If you cannot see the Developer tab, refer to Chapter
Three on how to activate it.
From the project explorer, right-click on sheet 1.
Click on insert, then module. This will open a code window.
 Type Public Sub HelloWorld and click enter. When you do this, the
program will automatically enter a set of parentheses and also
automatically type End Sub for you. You can press enter twice to give
yourself more space to write your message as shown below:

In the space created between Sub and End Sub type the following:

You may have noticed that the text in quotes above is a string
literal. As such, you are not restricted to typing “Hello World!” you
may type anything you want. But for now, let’s stick to the design
specification!
Navigate to Rubberduck on the menu toolbar and click on Indent >
Current Module. This will automatically format your code for you by
indenting the line you added above. Indentation makes your code
easier to read.
 Navigate to Run in the menu toolbar and click on Run
Sub/UserForm. Alternatively, you can look for the icon button with a
green “play” icon, and click here. You can also use the F5 shortcut
key. With any of these options, the first time you run the program, the
following form will pop up.

Click run. If it’s not your first time using this shortcut key, you won’t
get this dialog box.
Your message will display in a box as shown below.
 Clicking OK will terminate the program and take you back to the
code editor in the IDE. This will not terminate the Excel application or
the VBA IDE.
We shall explain the new concepts introduced here later in this
chapter.

Testing
As programmers, we like to believe that our work is perfect and
free from defects. Few things can be further from the truth. Despite
your superb skills and your best intentions, things don’t always go
according to plan. Most times users don’t really know what they want
or what they’re asking for or perhaps they fail to articulate it clearly to
you, or you fail to understand their articulate explanation. Sometimes
you can also misinterpret your own specification, or even make
genuine errors while writing your code. This is not the end of the
world. Accept that your program may have errors, which is likely in
the first round, then set out to find those errors and fix them. Very
rarely will you produce the perfect program the first time.
Testing entails checking that the program has been developed as
per the design specification and that it meets the initial user
requirements. It validates the program as a solution to the initial
problem we sought to solve in the first place. This highlights the
importance of a clear problem definition as well as a design
specification. Apart from this, there are some basic expectations that
any program should meet. These include validating user input to
make sure that invalid data does not find its way into the system,
keeping the user informed with accurate and meaningful messages
as well as ensuring a consistent user experience throughout their
interaction with the program.
It is your responsibility to test your program. You may ask others to
test different scenarios for you but ultimately the responsibility rests
with you. When estimating the time required to develop a solution,
take into account the time you will need to thoroughly test your
program. The duration of testing required will depend on the
complexity of the program. So what exactly are you expected to do
during the testing stage?

Steps in Program Testing

Having a standardized approach to testing helps ensure that no
loopholes exist in your testing procedure. You can develop practices
when testing your program to cater to your individual or unique
circumstances. However, as a guideline, attempt to cover the steps
below in one way or another during your testing. Our “Hello World!”
example is a simple program, no worthwhile testing is necessary
apart from making sure that the program compiles and displays the
expected message box as per the design specification.

Planning
Determine what you want to test. While it is important to test your
program, it is not possible to test every aspect or scenario.
Resources such as time may not permit this. In addition, some of the
program conditions or states may occur under very rare
circumstances, if at all. It would be better to devote the time towards
testing a wider set of scenarios for those program conditions that are
more likely to be encountered by users. During planning, you will
also make sure that all the external dependencies that the scenario
you wish to test depends on are available, otherwise it will not be
possible to test the given scenario. For example, if the test scenario
involves downloading data from the internet when a user clicks a
button, you may want to ensure that you have a working internet
connection.

Writing the Test Case

 Having come up with a test scenario, you need to write a test case
for this scenario. This is a step-by-step guide towards creating that
test scenario together with a full description of the expected results.
The program inputs are clearly defined, the actions the user will
perform on the program explained and the program outputs detailed.
Try to write your test cases as though the test will be executed by
someone else other than you each time. This avoids making
assumptions that will not exist after your program is deployed.

Setting up the Test Environment

Having determined the scenario to test, the prerequisites to
execute that test as well as the procedure for conducting that test,
you need to set up the environment. Wire up everything in
preparation for executing the test. You should try to replicate the
actual environment in which the program will be executed. Pay
attention to volumes of data to process, the processing power of
available equipment as well as devices available for interacting with
your program.

Executing the Test

This is where you conduct the actual test. There are two types of
testing that you can conduct at this stage, given a test case and
testing environment. You can conduct either black-box testing or
white-box testing. It is recommended to conduct both, starting with
black-box.
With this type of testing, you’re not concerned with the internal
workings of the program. You’re only concerned with the relationship
between the inputs and the outputs. The program must output the
correct data for a given set of inputs. This allows you to pay closer
attention to the correctness and accuracy of the entire program as a
unit. This kind of testing can be delegated to another person as the
tester does not need to know the internal workings of the program.
In white-box testing, you’re interested in both the output of the
program as well as how that output is generated. Not only do you
want to know what is output by the program but you also want to
know how that output is determined. Typically you will execute your
tests in stages, as though you were peeling an onion layer by layer.
You will start from the level of black-box testing and go a level
deeper to inspect the method that returns results. When you’re
satisfied with this method, you proceed to go further looking at how
each method generates its output, and so on. The debugger is your
best friend with this kind of testing. It allows you to pause execution,
inspect the contents of different variables, dive deeper into methods
to inspect the logic they are executing internally and redirect the
program to test scenarios that may otherwise be difficult to replicate
from the outside. Make good use of the debugger.
Needless to say, you will need to fix any errors as you encounter
them. This is the reason why we’re testing in the first place. If you
make far-reaching changes, you may need to redo some tests that
may have been affected by changes done after you had passed
them.

End of Test
After executing each test you need to document the results.
Review your previous results as new tests are concluded to ensure
that the output is consistent. Documenting will also help you validate
users’ experiences and provide you with a starting point in trying to
resolve “bugs” reported by users. If users present problematic
scenarios, you can check against your test cases and results. From
there, you can try to deduce why they are getting different output
before you even go to look at the code.
There may be instances where some tests fail but you still feel
your program can be deployed nonetheless. Provide explanations for
this in your documentation. Remember to document as if someone
else will maintain your program.
As you are testing, you may come across elements of your
program that can potentially be improved, or new functionality that
you think will add value. Take note of these and include them in your
documentation. You may also identify new test scenarios that were
not so obvious when you started testing. Note these down and be
sure to cater for them.
Testing is an iterative process. When you’re done with one test
scenario, go back to the planning stage and prepare for the next test
scenario. Repeat the above five steps until all identified scenarios
have been tested.
What to Look for When Testing
By now you may be familiar with the three broad categories of
program errors. As a recap, these are:

Syntax or compilation errors

Runtime errors
Logical errors

In addition to the above, you also want to test the user experience.
Cater for each of these in your testing. There are different strategies
required to effectively test each of these.

Syntax Errors
These are the easiest to catch as your program will not compile if it
contains syntax errors. As part of the process of converting your
code to machine language, the compiler checks that the program is
written in adherence to the strict rules of the programming language.
Use of keywords, spellings for these keywords, declaration of
variables, permitted operations, etc., are all checked. If violations of
the program specification are identified, they are flagged as errors
and the compilation process is halted. You are directed to where the
problem lies and are expected to fix it.

Runtime Errors
These arise out of unexpected program conditions when the
program is run. The compiler will not pick such errors because when
the program is compiled, there is no state. State is only introduced
when a user runs the program and user data is introduced. This may
come from user input or obtained from an external storage location
such as a database. A seemingly harmless operation such as
mathematical division can potentially throw a runtime error,
depending on what the user is attempting to divide. Dividing 1000 by
50 will not cause problems. However, the same logic, when an
attempt is made to divide 1000 by zero, will cause a runtime error
and your program will crash. This is illustrated in the image below.
 Assuming that userInput is being obtained from the user, the above
program will run fine with a value of 50 for userInput . However, a
value of zero for userInput will result in the error displayed. Be sure
to cater for edge cases when writing your programs to avoid runtime
errors. In the above example, the program can check that the user
input value is not zero before attempting the division operation. An
appropriate error message may then be presented to the user if this
is the case, thus avoiding the nasty runtime error.

Logical Errors
This type of error poses major problems because, despite being
subtle, it can completely invalidate your program. If a program is
invalid, it means it does not at all solve the original problem; it is not
a viable solution. The subtlety comes in the sense that, without
inspecting the output from the program, you may never know that
there is a logical error. The program compiles successfully, no
runtime errors are experienced and output is being generated.
However, this output is wrong. As an example, consider a program
that is intended to determine the largest number in a sequence of
unsorted numbers. The program starts by comparing the first two
numbers, takes the largest of the two and compares with the next
number in the sequence and so on. If the program finds that the
current largest number is larger than the next number, it terminates
and returns the result. This is illustrated in the code snippet below.
 Assuming that userInput is being obtained from the user, the above
program will run fine with a value of 50 for userInput . However, a
value of zero for userInput will result in the error displayed. Be sure
to cater for edge cases when writing your programs to avoid runtime
errors. In the above example, the program can check that the user
input value is not zero before attempting the division operation. An
appropriate error message may then be presented to the user if this
is the case, thus avoiding the nasty runtime error.

Logical Errors
This type of error poses major problems because, despite being
subtle, it can completely invalidate your program. If a program is
invalid, it means it does not at all solve the original problem; it is not
a viable solution. The subtlety comes in the sense that, without
inspecting the output from the program, you may never know that
there is a logical error. The program compiles successfully, no
runtime errors are experienced and output is being generated.
However, this output is wrong. As an example, consider a program
that is intended to determine the largest number in a sequence of
unsorted numbers. The program starts by comparing the first two
numbers, takes the largest of the two and compares with the next
number in the sequence and so on. If the program finds that the
current largest number is larger than the next number, it terminates
and returns the result. This is illustrated in the code snippet below.
 At first glance, it might appear that this program will return the
correct value. Look closer and you will see that given the sequence:
13, 12, 11, 10, 9, 14, 15, 44, the function will return 14. This is
certainly not the largest number in the sequence. The logic of the
function is incorrect and hence the output is incorrect. Challenge
yourself to come back and rewrite this function so that it outputs the
correct number.

User Experience
As you hunt for errors in your program, pay attention to the user
experience. User experience issues are not necessarily errors, they
are concerned with ensuring that the program is user friendly. The
user interface must be intuitive; it must be easy to find controls and
figure out how to accomplish the desired task without having to pour
through mounds of documentation. Error messages must be
complete, providing the user with enough information to be able to
address the issue causing the error. Avoid messages that leave the
user with more questions, or direct the user’s attention to the wrong
place as the source of the error.

Documentation
Documentation is an ongoing activity throughout the development
process. From defining the problem, designing the solution, and
coding as well as testing, it is good practice to update your
documentation as you go. It is easy for someone to pick up from
where you left off if your progress is well-documented. Program
maintenance is an ongoing process and is often done by someone
else other than the original developer of the program. Documentation
becomes indispensable in this scenario.
Documentation for the coding stage is accomplished through
program comments. These are special lines of text inserted in the
code. They are not recognized by the compiler and will therefore not
be converted to machine code or cause syntax errors. A comment is
intended for the programmer or other people who may want to read
the code and understand what is happening. It may, therefore,
contain any text and be as descriptive as you wish it to be, in plain
English or any other natural language. In VBA, program comments
are created by starting the line with a single quote character, as
shown in the two comment lines below.

In addition to technical documentation, you may also have to

produce documentation for your users to help them make effective
use of your program. It is not always possible for you to be around to
explain to users how to accomplish desired tasks within your
program.
 Chapter 5 - Dialog Boxes
Odds are you’ll be writing programs that interact with humans
rather than script-driven programs. With such programs, you
probably want to convey messages to the user as well as obtain
input from the user in a structured way. It is also a reasonable
expectation that VBA and Excel have built-in mechanisms for
achieving these two requirements. And they don’t disappoint. VBA
comes with built-in dialog boxes that you invoke by simply calling a
procedure. You may pass arguments to the procedure call for
options to specify how the dialog box is supposed to behave, in line
with your specific requirements. Almost every single dialog box used
by Excel is available to use freely as programmer.
This chapter will equip you with the ability to use message boxes,
file dialogs, as well as Excel built-in dialogs. This will enable you to
take advantage of the power at your disposal with confidence. At the
end of this chapter, you should be able to write programs that can
engage in constructive “dialog” with the user for the exchange of vital
information.

Message Boxes
Message boxes are used to convey information to the user as well
as to obtain a user’s decision in situations where there’s indirection in
the program logic. We’re going to demonstrate usage of message
boxes by building on the “Hello World!” example from Chapter Four.
Indeed, that example made use of a message box to display the text
“Hello World!” Message boxes are displayed using the MsgBox
function found in the Interaction module within the VBA library. The
fully qualified name for this function is VBA.Interaction.MsgBox . A
fully qualified name shows the complete hierarchy of a procedure or
property, separated by dots. It is useful for avoiding ambiguity when
calling functions or properties with similar names but defined in
different modules and/or projects or libraries.

MsgBox Function Signature

The signature for the MsgBox function is as follows:
 It defines five parameters, of which one is required, one has a
default value and three are optional. “Prompt” is a required string
parameter that specifies the text to be displayed in the message. The
“Buttons” parameter defines buttons to be displayed as part of the
message. This parameter is of an enumeration type named
VbMsgBoxStyle and has a default value of vbOKOnly , meaning if it is
not specified, the vbOKOnly value is used for this parameter resulting
in only the “OK” button being displayed. The “Title” parameter
captures the title of the message and is of type string. The “HelpFile”
parameter is also of type string and specifies the location of the file in
which help regarding the displayed message may be found. The last
parameter is of type integer and is used to indicate the context ID for
the applicable topic within the help file. The last three parameters
may be omitted. However, there is one caveat, if the “HelpFile”
parameter is specified, the “Context” parameter must also be
specified. The method returns a value from the VbMsgBoxResult
enumeration.

Message Title and Text

To begin, open the “Hello World!” Excel worksheet and navigate to
File > Save As, select a location and rename it to “Dialog
Boxes.xlsm”. Open the VBA IDE, double-click on Module1 in the
project explorer, rename the HelloWorld subroutine to
“MessageBoxes” and change the code in it to resemble the following:

We have introduced a variable named enMessageBoxResult , added

more arguments to the call to MsgBox and are receiving a value from
that call. When we run the program we now get the following output:
 In the initial example, the message box displayed with the default
title of “Microsoft Excel”. We’ve changed this title to “My Message
Box Title” by adding the appropriate string for the “Title” parameter of
MsgBox .
In the above example, we can see that the message box is
displayed with an additional button captioned “Cancel.” This is
because we passed in the value vbOKCancel for the “Buttons”
parameter, more on this later. In order for us to pass in a value for
the “Title” parameter, we need to also pass in a value for the
“Buttons” parameter, else our code will not compile. This is because
we have to provide arguments or values for the parameters in the
order in which the parameters are listed in the function signature,
otherwise, the compiler will not know what value to assign to which
parameter. The “Title” parameter is defined after the “Buttons”
parameter thus we have to provide a value for the “Buttons”
parameter before we can provide one for the “Title” parameter.
If we specify a value for the “Buttons” parameter, we have to
assign the value returned by the MsgBox function somewhere,
otherwise, we’ll get a compilation error. For this reason, we have
introduced the enMessageBoxResult variable, even though we are not
using it at the moment.
It is possible to print the message text on multiple lines within the
message box. To do this, we modify the value for the “Prompt”
parameter by including a line break character in the string. This is
shown below.

We use string concatenation to introduce the vbNewLine character

at the point at which we want to insert the line break. This will display
the following result:

Buttons
In the “Hello World” example, we saw one button captioned “OK”
displayed in our message box. In the last example, we saw an
additional button captioned “Cancel” displayed and this was after we
added a value for the “Buttons” parameter. If we do not specify a
value for the “Buttons” parameter, we get the “OK” button only; if we
specify the value vbOKCancel , we get the “OK” and the “Cancel”
buttons. This seems to fit most situations perfectly. However, it does
not cater for all the possible responses we may want to solicit from
our users.
So what other buttons can we possibly display in a message box?
In total, there are six values we can possibly specify for this
parameter to display a corresponding number of preset sets of
buttons. These values are part of the vbMsgBoxStyle enumeration
found in the VBA library. A preset set of buttons may contain one,
two or three buttons. These sets are shown in the table below,
together with their corresponding vbMsgBoxStyle values.
 The choice of which set of buttons to use is dictated by what
makes sense in the usage context. As a programmer, you determine
what action is taken in your code when the user clicks a given button.
This corresponding action must be reasonably intuitive.
The first button is highlighted in all message box images above.
This indicates that this is the default button and hence it is given
focus such that if the user presses the “Enter” key on the keyboard,
this is the button that gets “pressed” and whose value is returned by
the MsgBox function. But what if we wanted the default button to be
the second or the third button, how would we achieve that? It turns
out the vbMsgBoxStyle enumeration provides three additional values
specifically for this purpose. These are vbDefaultButton1 ,
vbDefaultButton2 and vbDefaultButton3 . We indicate the default
button by literally adding the corresponding value for the default
button position to the value being assigned to the “Buttons”
parameter. If we want the first button to be the default button, we add
vbDefaultButton1 , if we want the default button to be the second we
add vbDefaultButton2 and if we want it to be the third we add
vbDefaultButton3 . This is shown in the code below.

Running our code, we get the following result:

 The default button has now been set to the second or middle
button, the one captioned “Retry”. Take note that setting the third
button as the default when you actually have two buttons in your
message box will cause the default value to revert to the default of
the first button. On the same note, attempting to combine
enumeration values for preset sets of buttons will cause the message
box to revert to the default “OK” button.

Icons
We have the option of including preset icons in our message
boxes. Suppose we need to indicate to the user that the message
box is displaying an informative message or a warning or an error.
We can play around with the heading and text but this is more easily
achieved by using an appropriate icon from the presets available to
us. These are defined as part of the VbMsgBoxStyle enumeration.
Similar to the default button, to specify an icon you literally add the
corresponding enumeration value to the “Buttons” parameter of the
MsgBox function. The following line adds an exclamation mark to our
message box:

The possible icons and their corresponding enumeration values

are shown below.
Processing Returned Values
So far we’ve been ignoring the value returned by the MsgBox
function but that’s about to change. If you call the MsgBox function
and you specify only the message to display, you can get away with
not assigning this value somewhere. This is what we did in the “Hello
World” example. However, once you get fancy and start specifying
the message box title, you have to assign the returned value to some
variable. In the example above, we assigned this value to
enMessageBoxResult . We’re not forced to use it. However, in most
cases, you’d want to know what button the user has clicked on and
therefore you have to inspect the variable holding this result. You can
then execute certain logic only if a user has clicked on a specific
button.
In our example, we’re going to display another message box with a
message indicating the button that the user clicked on in the first
message box. We will use an If statement to achieve this. Update the
“MessageBoxes” procedure in your project to resemble the following:

enMessageBoxResult is of type vbMsgBoxResult , which is an

enumeration in the VBA library and is used to denote the possible
buttons that a user can click on in a message box. As such, it has the
following seven possible values, each of which corresponds to a
button that may be displayed in a message box:

vbAbort
vbCancel
vbIgnore
vbNo
vbOK
vbRetry
vbYes

In our code, we’re checking the button that the user clicked on by
checking the value contained in enMessageBoxResult , constructing
the appropriate message to display to the user, storing this message
in another string variable named strUserChoiceMessage and then using
another message box to display the message. The results are as
follows:

It is entirely up to you how you process the result of MsgBox .

You may have noticed the usage of two sets of double quotes in
building the message to display to the user, for example around
“Retry” in the following line:

This is necessary in this case because double quotes are a special

character in VBA and are used to denote string literals. If we need to
include them as literals in a string, we have to “escape” i.e., tell the
compiler that they are to be interpreted literally and not as a special
character. Interestingly in VBA, we use a double quote to escape a
character. The result of this is two double quotes on either side of the
text we wish to display surrounded with double quotation marks: the
first one is an escape character and the second one is the double
quote we want to be included in the string and ultimately the
message.

Input Boxes
Input boxes allow us to collect textual input from the user. They are
invoked using the InputBox function in the VBA library. This function
has the following signature:

“Prompt” is the only required parameter; all other parameters are

optional. It is used for the message to be displayed to the user.
Typically it provides a description of the input that is expected and
any other relevant information for the user. The “Title” parameter
specifies the title of the input box. The “Default” parameter specifies
the default value that will be used if the user opts to not provide
input. When the input box is displayed, this default value will be
shown in the input space. “XPos” and “YPos” indicate the
coordinates of the screen position where the input box will be
displayed. “Help File” and “Context” parameters serve the same
purpose as they did with MsgBox . The InputBox function returns a
string representing the user’s input.
To demonstrate usage, we will continue with our previous project.
Create a new subroutine named “InputBoxes” in the same module as
before, switch to procedure view in your editor and enter the
following code:

It is good programming practice to declare all variables you will

need in your procedure at the beginning of your procedure. In
addition to the declarations, we’re creating and displaying an input
box, setting the prompt, title, and the default value. After receiving
input from the user, we’re concatenating this input with a message
prefix to create our feedback message for the user, which we then
display in a message box.
Running the above code will display an input box prompting the
user to provide input. The default value is displayed in the input box
text box.
 Clicking on “OK” will display a message box with the user’s input,
assuming the user opted to provide input. If the user opted to not
provide input, the default value is displayed.

If the user clicks on “Cancel” in the input box, an empty string is

returned to the calling code. This means that in our case, no text will
be displayed in the message box, except our message prefix (“You
entered:”). This is the case even where a default value has been
specified. For this reason, always test if there is actually a value in
the string returned from the user. What action you take if this is the
case is entirely up to you and is driven by your business logic.

Application.FileDialog
The Application.FileDialog function is used to create an object of
type Office.FileDialog that exposes useful functionality for interacting
with the file system in a Microsoft Office application. We can use the
object to open a dialog box to select files or folders, open a file as
well as to save a file. We can configure the Office.FileDialog object to
suit our requirements prior to displaying it to the user.
The following example creates an Office.FileDialog object,
configures it and displays it to the user, who is prompted to select a
file. A message box then displays the details of the file selected by
the user.
Create a new subroutine named “FileOpenDialogBoxes” and add
the following code.
 In the above code, after declaring our variables we are creating an
instance of the Office.FileDialog object using the Set keyword. This is
known as “instantiating” an object. In this case, we do this by calling
the Application.FileDialog function, passing in the value
msoFileDialogFilePicker . This comes from the MsoFileDialogType
enumeration. Other values defined in this enumeration are
msoFileDialogFolderPicker , msoFileDialogOpen and
msoFileDialogSaveAs . This parameter tells the Application.FileDialog
function what type of Office.Dialog object we’re requesting.
We then set the title and apply filters for our file dialog object.
Filters are important as they prevent the user from selecting invalid
file types. We can limit the file types that are displayed within the file
dialog so that the user does not inadvertently select the wrong file
and cause errors down the line. There are two ways in which filters
may be specified, both of these have been used in the example
above. The first one is to specify the file extension of the file type
we’re expecting, as is the case with .doc. The second one is to
specify part of the file type and then use a wildcard (using the
question mark - ?) to cater for possible variations. This is the case
with Excel files in our example. You will note that .xls, .xlsx, and
.xlsm files are all visible in our file dialog. You can also elect to show
all file types by using the *.* expression. The user will be able to use
a dropdown to select which filter to apply and so you should only use
filters that apply to your expected file types only.
The Show method displays the file dialog to the user and returns
true if the user selects a file, else it returns false.
Running the above code will display the following file dialog box:

Canceling the file dialog will display the following message box:

When the user selects a file, details of the file are displayed as
follows:
 XlBuiltInDialog
It is possible to access any built-in Excel dialog box using the
Dialogs property of the Application object. The Dialogs property is
read-only a collection of all possible dialog boxes within a Microsoft
Office application and since in our case our application is the Excel
application, we get access to all Excel dialog boxes. To indicate
which dialog box you need from the collection, you pass in a value
from the XlBuiltInDialog enumeration. Application.Dialogs will return a
Dialog object whose appearance is determined by the value of
XlBuiltInDialog passed in. All dialog boxes you can possibly obtain
from the Application.Dialogs property are predefined; you may not
create your own and add them to this property. There are over 200
values in the XlBuiltInDialog enumeration, each representing an
Excel dialog box. Each one of these exposes one method, the Show
method. This is used to display the dialog to the user and returns
true if the dialog opened successfully, otherwise it returns false.
You should only resort to using this option if you cannot get the
required functionality in other ways. The reason for this is a relatively
complicated way of interacting with your program and each dialog
box has its own peculiarities.
 C hapter 6 - Forms
User interaction is integral to most applications. Dialog boxes may
be used to capture user input. However, they are more suited to
providing output and the simplest of user input. For complex user
input, we have to use UserForms. Just like paper forms, UserForms
provide various complex fields to capture different kinds of related
data. They may also be used to display the same data without
providing the user with the option to edit. When working with
UserForms, you will typically perform two distinct processes and
these are designing the form and adding behavior to the form.

Designing the Form

Designing appealing, yet functional UserForms will put your artistic
skills to the test. Try to stick to established Microsoft conventions in
your designs so that your forms are intuitive. There are literally
thousands of UserForms in the Windows operating system. Learn
good UserForm design standards and practices from these. Avoid
excessive use of color, use of extravagant fonts, large images on
your controls, or cluttering controls on a form. We will explain the
process of creating and designing a UserForm using a job
application form example.

UserForm
Create a new workbook named “UserForm” and save it as a
Macro-enabled workbook as before. Launch the IDE and navigate to
Insert > UserForm. Alternatively, you can right-click on the
VBAProject in the project explorer and select Insert > UserForm.
This will display a blank UserForm canvas that looks like this:
 If you run your program at this point the following blank form will be
displayed:

You have successfully created your first UserForm. Although this

form is blank and has no custom behavior defined for it, it does have
a default title and a close button. You can move it around and when
you click on the close icon in the top right corner of the form, it will
close and terminate the program. All this is the default behavior. We
shall be customizing the form’s behavior later but for now, let’s
customize how the form looks.

Form Properties
A UserForm is a built-in object and as such has behavior and
properties associated with it. We can customize how the form looks
by setting the values of some of these properties. In the design
stage, we can set default values for our form and these will apply
when the form is loaded for the first time. We can change these
values in our code if we wish to. For example, we may want to show
more user controls on the form under certain circumstances so we
can increase the size of the form dynamically. The UserForm
properties can be accessed from the properties window. If this is not
visible already, you can bring it into view by navigating to View >
Properties Window, or right-clicking on the form and selecting
Properties. This window can display the properties in either
alphabetical order or category order. You can switch between these
two views by clicking on the appropriate tab below the object name.

We are going to change a few properties as shown in the table

below.

The Name property is very important because it identifies this

specific form in our project and it must be unique. To dynamically
access our UserForm in code we shall make use of this property.
The Caption property is what the user sees as the title of the form.
The other two properties we changed determine the size of the form.
Play around with the other properties to gain a better understanding
of the effect of each of them on the form.
User Controls
User controls are what the user will interact with on your form.
Examples include buttons, text boxes and dropdown lists or combo
boxes. There are various controls available to you, care must be
exercised when choosing a control for a given purpose. Some
controls are preferred over others for certain tasks. As a general rule,
choose the control that makes it less likely for the user to enter
incorrect or invalid data. The second important point is to choose the
most user-friendly control.
To add controls, navigate to View > Toolbox. The following window
will be displayed:

You can add controls to your form by dragging them from the
toolbox and onto your form. Similar to the UserForm, controls are
objects and when you drag and drop them onto your form, you’re
effectively creating instances of these controls and adding them onto
the Controls property of your instance of UserForm. You may not see
or have access to this Controls property of the UserForm. It,
however, acts as a container for all controls associated with the form
so that the form is aware of these. The table below explains the
usage of some of the common controls in the toolbox.
 These controls, as they appear here are in their default setting.
Each control has a set of properties that you can change, which will
affect how the control appears and behaves in your form. To change
these, select the control you want to modify and open the properties
window.
When working with the OptionButton control usage of the Frame
control is important. By default, only one option button may be
checked in any container. If you have multiple option buttons that
control different entities on your form, you need to contain these in
different Frames so that they do not interfere with each other. Once
you do that, you can have two different option buttons checked on
the same form so long as they are in different Frames.
Add controls for the following form fields to your form: surname,
first name, highest level of qualification, gender, current employer,
city, country, and willingness to relocate. Remember to put the option
button pairs in separate frames. Add two buttons to save and reset
the contents of the form.

Add a list box in another frame at the bottom of the form. We will
use this to show the contents of our “database”. Name it
“lstDatabase”.

Pay attention to tab indexes. These determine the order in which

focus is obtained in your form when the user “tabs” through the form
by pressing the tab key on the keyboard.
Formatting Tools
You may have realized how difficult it is to set control sizes and
alignment to be in harmony. You can use your mouse to push, pull,
and stretch the controls to adjust their sizes, positions, and
alignment. However, this does not give correct or desired results all
the time. The other option is to manually capture values for the
Height, Width, Top, and Left properties. However, this is time-
consuming and inconvenient.
Fortunately, there are tools to aid with the formatting of our
controls. These allow us to control the size, alignment, horizontal
spacing, vertical spacing, position within the form, automatically
arrange buttons, group together related controls, and much more. To
access these tools, navigate to Format in the menu toolbar. You
should get the following options:

Make good use of these tools to get professional-looking forms.

The following image shows our form after formatting.

Adding Behavior to the Form

 Adding Behavior to the Form
So far we have a nice, well-designed form that does nothing
except showing up when we run the program. We need to write code
for events we expect to happen with our form so that it responds
when these events occur. To keep our event handlers simple, we will
create the logic for saving the form contents in a separate procedure,
and call this procedure from our “Save” button click event handler.
We reset the form at multiple points - when we load the form, when
the user clicks on the reset button and when we save the contents of
our form. This warrants creating the reset logic in a separate
procedure so that we avoid repeating code that does exactly the
same thing.
Before proceeding, we need to rename the first worksheet in our
workbook to “Home” and the second one to “Database”. We are
going to be referring to these in our code. In the “Database”
worksheet, create the following table:

The Reset Procedure

This clears our form of any user input and restores the controls to
their defaults, ready for new input from the user. Create a new
module by navigating to Insert > Module, or right-clicking on the
VBAProject and selecting Insert > Module. From the properties
window, rename the module to “JobApplicationLogic”. Right-click on
the module in the project explorer and select View Code. Enter the
following code in the code editor.

The Save Procedure

We will create the Save procedure in the same module as the
Reset procedure. Create a new procedure named “Submit” and enter
the following code.

Since we want to be launching the form from a module, we are

going to create a procedure whose sole purpose is to display our
form.

This procedure simply calls the Show method on the UserForm we

created above. Our core logic is done, we now need to create event
handlers on our form.

Event Handlers
From the form designer, double-clicking on a control automatically
creates a click event handler. We will do so for the Save and the
Reset event handlers. You can also open the code editor window for
the UserForm, select the appropriate event from the dropdown list on
the right and this will automatically create an event handler for you.
At the end you should have code that resembles the following:

We have made use of the procedures we created in the

JobApplicationLogic module. When a user clicks on the ‘Save”
button, the JobApplicationLogc.Submit procedure will run, saving the
contents of the controls to the “Database” or the Database
worksheet.

Bringing It All Together

 Now we’re ready to run our program. But first, we must provide a
convenient way to access our new functionality. Let’s create a button
in the “Home” worksheet. When a user clicks on this button, the form
we created above will be displayed, allowing the user to capture the
required information and save it to the “Database” worksheet.
From the workbook, navigate to Insert > Shapes and select a
rectangle. Right-click on the shape and select “Edit Text”. Enter
“Capture Applicant Data”. Format the “button” as you desire to make
it more appealing. Right-click on the shape again but this time select
“Assign Macro…”. A list of all available VBA macros is displayed.
Select the “Show_Form” one and click OK. You should now be able
to click on this “button” and launch your UserForm.
Enter values for each of the fields on the form and click on save.
Check if your data is getting saved in the Database worksheet.
 Chapter 7 - Recording and Saving Macros

Macro Recorder
It has been an involved journey so far, huh? Granted, for you to be
able to churn out code like an expert, you will need to practice and
revisit the theory section a few times until you are ready to move on
to the intermediate Excel VBA level. But until then, as you are still
working on upgrading your skill level, there is a very useful tool in
VBA that enables you to record a specific as you are doing it. This
recording will then be converted into lines of code, and this macro
can be used in a particular workbook or available in any workbook
depending on how you save it. With this functionality, this means that
you record yourself doing a task only once and you will be able to
execute the same task in exactly the same way at the click of a
button!
Before we go into detail about this function, you may be
wondering, why go through learning Excel VBA programming when
there is this in-built feature? Code written using a macro recorder is
not as efficient as well planned, good quality code. In most cases,
the code from a recorded macro has unnecessarily long lines of code
as every action that you do, including scrolling is recorded. The result
is that this code may be difficult to maintain, improve on, and in case
of a problem, debug. If the resultant code from the recorded macro is
very long, the macro may take longer to run. In cases where you are
using a device with memory constraint, the length of time it takes to
run the code may outweigh any perceived benefits of using the
recorded macro. You also cannot easily perform loops and other
operations on the data without first editing the code. With that said,
despite these limitations, it is worth it to explore this macro recorder
functionality.
Example:
Assume you are a tutor at a college. At the end of each semester,
all lecturers in your department hand over raw student marks in the
following format:
 For each student in each class, you will have to calculate the
following:

1. The average coursework mark for the term in percentage.

For student A, the average would be ⅓ ( 20/45 +65/70 +55/60)
* 100
2. The average mark for the semester. According to the
university yearbook, the coursework mark contributes ⅓ to the
final mark and the exam mark contributes ⅔ to the overall
course mark. As such for Student A, the overall mark for the
semester will be as follows:

Each class takes on average 10 courses per semester, and all the
ten lecturers hand over their marks to you. Can you automate this
task using a recorded macro?
Before you start using the macro recorder, you will need to
recreate this table in an Excel spreadsheet. Take note of which cells
have merged cells and those without. When done, copy this data and
paste it in Sheet 2. Sheet 1 is where you will record the macro, and
Sheet 2 will be the test environment, where you will check if the
recording you made carries out exactly what you want done. When
your two worksheets are ready, map out exactly what you are going
to do on a piece of paper. This is necessary so as to eliminate
unnecessary steps as you are trying to figure out what to do while
the macro is recording.
Consider the following steps:
Step 1: Name cell F2 “Average CW”.
 Step 2: Name cell G3 “Final Course Mark.
Step 3: Highlight column G and make the text bold, and font size
20.
Step 4: In Row G1 write “Marks prepared by Tutor XYZ”.
Step 5: Click on cell G1 and format the text to be italicized, bold
with font size 8, with font color red.
Step 6: Select the header rows from A1 to G2, fill these in with a
light grey color, and make sure all cells in this range are showing
borders.
Step 7. Select column F and G and make sure all cells in this
range are showing a border.
Step 8: Calculate Average CW for Student A by inputting the
following formula in cell
F4: =1/3*(B4/$B$3+C4/$C$3+D4/$D$3)*100.
Step 9: Click on cell F4 and adjust decimal numbers until none are
showing.
Step 10: Click on cell G4 and input the following formula: =E4* ⅔
+F4*⅓.
Step 11: Click on cell G4 and adjust decimal numbers until none
are showing.
If you have looked at the eleven steps, and are sure that this
captures exactly what you want to achieve, then you are ready to
start recording your macro.
In your Excel sheet where you recreated this list of student, click
View > expand drop-down menu in the “macro” tab > Record Macro
as shown below:
 Fill out the form as shown below. Note that the macro name should
not have any spaces in it. You have the option to store the recorded
macro in either the current workbook, in which case, the macro will
only run in sheets opened in the particular workbook that you are
working on. The other option will be to select the personal macro
workbook, which is a hidden worksheet on your computer. In this
case, the macro will be available for you to run in any workbook that
you open on your computer.

When done click OK. Once you click ok, the program is already
recorded and waiting for you to start executing the steps laid out
above. Go through all the steps as efficiently as possible, and when
done, go back to the view macros tab and stop the recording.
Navigate to Sheet 2, where there is the raw, untouched data. Click
View > expand the drop-down menu in the “macro” tab > click on
View Macro and run. The program will perform all the steps that you
detailed and the result will be something like this:

All you will need to do to finish computing marks for all students is
to drag and drop formulas to fill in the column F and column G for all
students. This will be to allow for variations in the number of students
per class. With this recorded macro, you can calculate marks for all
10 courses for all students irrespective of class size in less than 5
minutes flat!
 If you navigate to the VBA Editor, in the project explorer window,
you can see this code if you right-click on ‘Module 1’ under
‘Personal.xlsb’ and click ‘View Code’. In this case, the code that the
recorder generated for automating this task was very very long as
seen below: After accessing this code, you may want to edit it if you
want to add more functionality.
 This example showcased most visibly, the ease with which you
can use a macro recorder to automate mundane tasks in Excel that
take up a lot of time to accomplish. But should you want to add more
functionality, can you read this code and edit it? For example, if you
want to search the database and highlight in red all students that got
a final mark below 50, where would you start in trying to add this
functionality?
This brings us back to the need for you to be able to conceptualize
and write your own code so that it best serves you.

How to Share and Deploy Macros

The first thing that you need to do whenever you create a macro is
to make sure that you save these. Always save your Excel
worksheets as macro-enabled documents. As you practice and work
on automating your spreadsheet tasks, your stash of macros will
definitely begin to grow and the functionality that you can add to your
macros as your programming skills improve. This may make you
want to share your work with other users who share your computer
with you or across devices. To do this, you may have to copy macros
to a workbook titled ‘Personal.xlsb’ that is stored in your machine.
If you have never recorded a macro, then this workbook does not
exist. But once you record your first macro, this workbook will be
automatically created for you in Excel. Open a blank workbook,
navigate to view and look at the status of the unhide button. If it is
disabled (greyed out) then this workbook does not exist on your
machine as shown below:

If the unhide button is active as shown in the button below, then

you do have a Personal.xlsb workbook.

Click on unhide and you will see the workbook name as shown
below:

Once you have determined that it is there, navigate to the VBA IDE
for you to easily identify the location of this file. Make sure the Project
Explorer window and the Local window are open. Highlight VBA
Project (PERSONA.XLSB) in the project explorer window, and then
navigate to the immediate window and type:
?thiswookbook.Path
Press enter. This action will return a value which is the file path to
where this workbook is located as shown below:

Highlight the file path, navigate to the project explorer and paste it,
and you will see this workbook. All your macros that can provide you
with a way to automate tasks across all your workbooks can be
copied to this workbook. Whenever Excel starts, this workbook is
automatically opened too, and you will be able to access your
macros even when you launch a new workbook. You can copy and
save this workbook to a flash disk and paste it in a different
computer, which will enable you to move your macros across
different computers. Take note that this workbook is vulnerable and
can be easily corrupted. So, just in case, consider making a copy of
this file as a way of backing up all your macros. Should you want
your macros to be available to other people outside your immediate
work environment, consider creating an Excel Add-in. The
specification of how you can do this is a topic for another day.
 Chapter 8 - Miscellaneous

Add-Ins
There are many third party add-ins that can be of use to you as
you continue with your VBA journey. This book introduced one such
add-in in Rubberduck, and demonstrated throughout how one can
use this add-in to increase the scope of the Visual Basic Editor. This
add-in can notably improve code navigation and unit tests for bug
detection, etc. Other add-ins that might be of interest to you include
VB Document generator, MZ Tools, VBCodeHelper, amongst others.
You can review more than 20 other add-ins what added functionality
that each of these add-ins may offer on this site:
http://programmerworld.net/resources/visual_basic/visual_basic_addin.php

XML Maps
XML stands for Extensible Markup Language.These files make it
easy to import data created in other applications into Excel. When
such a file is opened, Excel automatically creates an XML map,
which is a way of binding data from an XML file to cells or a range of
cells on a worksheet (Microsoft Office development Center). As such,
using XML files, you can import Invoices, Financial results and any
other reports that you need into Excel using the Add method. The
XML map in this case will be an object. You can work with XML
objects and use methods such as ImportXML to collect data that is
generated outside of Excel. When that data has been imported, you
can write a code using Excel VBA that allows Excel to automate
certain properties:

AdjustColumnWidth - which will make Excel automatically

adjust columns to best fit the data being displayed.
AppendOnImport- which will automatically add new rows to
XML lists when importing new data without overwriting
contents of the cells bound to the specified schema map, etc.

Digital Signing
 A macro, as explained earlier, is a set of instructions designed to
automate certain tasks. These sets of instructions can be written or
created using VBA, and a set to automatically run when certain
events happen eg a user double clicks on a cell or opens a file.
However, this can pose a potential security risk as someone with
malicious intent or a hacker can introduce a destructive macro in a
file. If an event happens that triggers such a macro to run, then this
can spread a virus on your computer and possibly even your
network. To protect you from this, the default setting on most
Microsoft applications is set to “disable all macros with notification”.
So, if you have written your macros, and you find that trying to run
your program displays a security alert, you click on the following to
address the issue:
File > Options > Trust Center > Trust Center Settings > Macro
Setting
You will have access to all the different Macro settings that are
available to you as shown below: You can choose to enable all
macros, but this option will make you the most vulnerable.

A better option will be to enable digitally signed macros. A digital

signature lets Excel know that the macro attached to a workbook file
is valid and unaltered. You should however ideally sign macros after
you are done coding and your project is ready for deployment, as
changing even the slightest code renders the signature invalid. If you
are ready to digitally sign your macro project using a certificate,
navigate to the Visual Basic IDE, then click on the following: Tools >
Digital Signature. This will give you the option to sign the macro
using a certificate that is already stored on your computer as shown
below:

If you do not have a certificate, you can sign your macros using a
personal digital signature via the Microsoft Selfcert.exe tool, and you
will be issued with a certificate that you should store in the Personal
Store. For Windows 10, this is how you create a personal certificate:
C: > Program Files (x86) > Microsoft Office > root >Office16 >
SelfCert.exe
The Create Digital Certificate box appears. You then type a name
for the certificate of your choice as shown below and then click OK:

To use this certificate, go to the Visual Basic IDE and click on Tool
> Digital Certificate > Choose Certificate. You will then see your
certificate appearing as following:
 Keep in mind that if you go for this option, the macro will only run
on a computer that has the certificate in the store, and this is ideal for
personal workbooks. Should you want to distribute your applications
outside your organization, you would have to acquire a commercial
digital signature.
Digital signing is a way of making macros trustworthy so that users
do not continuously get security risk warnings and the only way to
achieve this is to get a trustworthy certificate that is ideal for the
intended use of the macros.

Best Practices
Code indentation the norm no matter which programming language
you may use. You should always keep in mind that there are two
audiences for any code that is written: people and computers. This
indentation is not for the compiler, but for other fellow human beings
who may review your code to understand it and the logic therein.
When you indent code, it thus makes it readable. At a glance, if your
code is well indented, a reader can understand the structure of the
code, which comes in handy when one is trying to identify and fix
bugs.
Vertical space, also called white space. Just like you would use
paragraphs when you are writing something in a text editor like
Word, vertical space is also important in coding. This vertical space
helps separate ideas and again makes code readable and
understandable to anyone who looks at it. All this spacing- called
white space, does not affect how a compiler will interpret your code,
but it makes your code clearer. When you are starting, this need to
keep code neat and tidy may seem like a waste of time, but you will
quickly realize the amount of time it will save you should you try to
identify a bug on a particularly long Subroutine.

Naming Conventions
As part of making code more readable, you have to practice right
from the beginning to be consistent when naming constants,
variables, methods, forms, controls, code modules, etc. It is good
practice to ensure that every name you choose makes sense to you
and in addition has two properties - a descriptive reminder of the
function of the item being named and the property of the item of the
being named, for example, its data type.
For example, take a look at the following variable names:

There are also naming conventions for object variables that you
can also adopt as follows:

Avoid Using Reserved Names for Your Variables and

Procedures
Examples of these include As, Else, Enum, Let, Loop, Not, Public,
String, Private, While, Stop, True, Nothing, Dim, Each, Event, etc.
This list is not at all exhaustive but you can read up on the VBA
keywords on this website: https://docs.microsoft.com/en-
us/office/vba/language/reference/keywords-visual-basic-for-
applications

Avoid Spaghetti Code

Good code is well planned, straightforward, and achieves results in
the most obvious ways.
Add Comments to Code
As you write your Excel VBA applications, you should always keep
in mind that you or someone else will revisit your code someday,
either to read it with the aid of understanding how your code works,
or to improve on the code by adding new features. With this in mind,
in addition to making sure that your code is well planned and you use
indentations and vertical white spaces to improve on code
readability.

Write Code in Small Reusable Chunks

If you find yourself in a single method constantly writing the same
lines of code, it is good practice to then write this short piece of code
in a separate module as a method (i.e., function or subroutine) and
then call it whenever necessary. Reusing code in this way makes
you more efficient in coding and results in code that is not only easy
to read but also easy to maintain.

Use the Smallest Possible Data Types

If you know that your VBA applications are likely to be run on
memory-constrained machines then you are memory constrained. In
every case, as you are coding, it is good practice that as you are
declaring your variables, limit yourself to using the smallest possible
space. If you can get away with using byte, then do not use an
integer. If you can get away with using an integer, then do not use a
long integer, etc. This is because the way you write your program
determines how much memory resources will be required to
successfully execute or run the program. As was explained in
Chapter Two, when you declared a variable, it reserves a location in
memory If for example, you declare decimals everywhere, even
when you do not need decimals, and you have a lot of these
decimals used in a loop, how much memory do you think you will
need? That means that for every decimal declared, you will need
64bytes of data compared to just 1 byte of data. Given that there are
‘a lot’ of these decimals, and these are used in a loop, then this will
have a huge impact on the amount of memory that your program
needs to run.
Catch Exceptions
An exception is an error condition that results in a program crash.
If you know as you code that there can potentially be an error
condition, then you should catch such specific exceptions. A good
example is logic that you know may result in your program trying to
divide by zero. This will obviously result in your program crashing
unless you catch it. Take for example someone who is writing a VBA
code that will track a few companies’ performances through enabling
Excel to automatically calculate accounting ratios from published
accounts. If one of the ratios to be calculated is the price/earnings
ratio what will happen if for any one of the companies, reported
earnings in any period is equal to zero? Or if there is no information
that is available for earnings and there is a blank cell? This can
happen if there is simply no data that is available at the time of
calculating the ratios. What is one of the companies reports a loss,
and as a result, Earnings are negative. Will a negative ratio make
sense? How should this be handled by Excel? Anticipating such error
conditions means that you can factor such exceptions in your code
by specifying how Excel should handle this. Catching such
exceptions will result in well thought out code that provides a good
user experience through muffling the issues. But keep in mind that if
you cannot deal with the exception then do not catch it.

Useful Shortcut Keys

This section contains useful keyboard shortcut commands that can
help you navigate Visual Basic Editor faster and with ease. If you get
into the habit of using these commands every time you are using the
Visual Basic Editor, you will eventually get comfortable and save
yourself a lot of time in the process.
 Conclusion
As was set out in the introduction, the mission of this book was to
introduce Excel VBA to the beginner. To make sure that you can
competently write Excel VBA code, the book included a carefully
written theoretical chapter on the fundamentals of VBA programming.
The intention was for this chapter to be your go-to guide even in later
chapters should you feel that you are lost and no longer understand
what you are doing and why. Have you managed to go through all
the chapters and master the concepts we covered? If so, then
congratulations! You have been equipped with the knowledge and
tools to understand the VBA language and the Excel interface that
you will come across when writing code.
You should be in a position to identify problems that can be solved
using Excel VBA, write your own code and Excel VBA applications.
You can also test these applications and deploy them. This assertion
is regarding concepts covered in this book from Chapter Four to the
end where you learn about how to code message boxes, code
control, command buttons, and how to manage event handlers. What
this means is, that you have everything that you need to move from
where you were before reading this book, to having Excel VBA
coding skills that you can list on your curriculum vitae. However, for
you to move from a beginner to someone with advanced Excel VBA
coding skills, you need to practice.
As was highlighted in the introduction, coding is a skill that is
becoming more and more relevant in this era. The more you practice,
the easier it will be to come up with your own customized solutions to
problems that you may be working on. There are no boundaries or
limitations to the extent that you may want to take Excel VBA skills,
but the bottom line is- practice is what will make you more
competent! All the fundamentals that were covered in Chapter Two
such as variables, data types, arrays, scope, etc., lay a good
foundation for taking Excel VBA programming to the expert stage.
In order to build on this foundational knowledge and appreciate the
concepts, you will have to practice. Not once, but lots of times. If you
have gone through all the practical exercises laid out in this book,
challenge yourself to practice the concepts learned by applying them
in different contexts. For example, if you are employed, look at your
work process flow and start critically thinking about the things that
you can automate and customize using Excel VBA to make your
work easier. If you are studying, look at any assignments and or data
collection activities where you think you could have used Excel VBA.
Revisit those things and try to apply these same concepts. As your
knowledge expands, do not stop. This foundation is also enough for
you to branch out into trying out other programming languages such
as C#, Java, etc., as these concepts are common across all
programming languages. Should you want to take your Excel skills
further, check out the other book on Excel VBA targeted at the
intermediate stage order, and make that purchase. Good luck!
 References
Microsoft.com. Data Type Summary - Visual Basic . Microsoft.
https://docs.microsoft.com/en-us/dotnet/visual-basic/language-
reference/data-types/
Indeed.com. Excel Vba Skill Jobs. Excel Vba Skill Jobs - March 2020
. Indeed. www.indeed.co.za/jobs?q=Excel Vba
Skill&l&vjk=2c75fc23da44a255.
Keywords (Visual Basic for Applications). (n.d.). Microsoft.
https://docs.microsoft.com/en-
us/office/vba/language/reference/keywords-visual-basic-for-
applications
Microsoft Office development Center. (n.d.). XmlMaps object (Excel)
. Microsoft. https://docs.microsoft.com/en-
us/office/vba/api/excel.xmlmaps
Rubberduck v2.5.0. Rubberduck Latest Release . (n.d.). GitHub.
github.com/rubberduck-vba/Rubberduck/releases/tag/v2.5.0.