Introduction to Excel Macros & VBA

Introduction to Excel Macros & VBA

The Beginners Guide to What Macros and VBA Are and
How You Can Benefit From Them
by J.A. Gomez

Copyright © 2018 by Jorge Alberto Gomez Soto.

All rights reserved.

This publication is licensed for your personal enjoyment only. If you would like to
share this publication with another person, please purchase an additional copy for
each recipient. If you are reading this publication and did not purchase it, or it was
not purchased for your use only, then please return to Amazon.com and purchase
your own copy. Thank you for respecting the hard work of the Publisher and Author.

No part of this publication may be reproduced, stored in a retrieval system,

transmitted, translated, reprinted, reused, recited, broadcasted, sold or similar, in
whole or in part, in any form or by any means, electronic, mechanical, including
without limitation photocopying, microfilms, scanning, electronic adaptation,
recording, computer software, by any information or storage retrieval system, or
otherwise by any similar or dissimilar methodology now known or hereafter
developed without the prior written consent from the Publisher.

Trademarked names, logos and images may appear in this publication. Rather than
use a trademark symbol with every occurrence of a trademarked name, logo or
image, the names, logos and images are used only in an editorial fashion to the
benefit of the trademark owner, with no intention of infringement of the trademark.
The use in this publication of trade names, trademarks, service marks, and similar
terms, even if they are not identified as such, is not to be taken as an expression
of opinion as to whether or not they are subject to proprietary rights. Additionally,
use of a term in this publication should not be regarded as affecting the validity of
any trademark.

Excel is a registered trademark of Microsoft Corporation. All other trademarks and

registered trademarks appearing in this publication are the property of their
respective owners.

2
 Introduction to Excel Macros & VBA

The Publisher and the Author make no representations or warranties with respect
to the accuracy or completeness of the contents of this publication and specifically
disclaim all warranties, including without limitation warranties of fitness for a
particular purpose. No warranty may be created or extended by sales or
promotional materials.

The information provided in this publication is for information purposes only and is
provided on an "as is" basis. The advice and strategies contained herein may not
be suitable for every situation. Users of this publication are advised to carry out
their own due diligence when it comes to making decisions related (directly or
indirectly) to the contents of this publication.

This publication is sold with the understanding that the Publisher and the Author
are not engaged in rendering legal, accounting, technical or other professional
services. If professional assistance is required, the services of a qualified
professional should be sought. All information, products and services that are
mentioned or provided in this publication should be independently verified by your
own qualified professionals.

The fact that an organization or website is referred to in this publication as a citation

or a potential source of further information does not mean that the Author or the
Publisher endorse the information the organization or website may provide or
recommendations it may make. Further, readers should be aware that internet
websites listed in this publication may have changed or disappeared between the
moment when this publication was initially published and the moment when this
publication is read.

Neither the Author, the Editors nor the Publisher shall be liable nor responsible for
any errors or omissions that may be made in this publication, nor for any damages
arising (directly or indirectly) from this publication.

By using this publication, you agree that the Author, the Publisher and any related
(directly or indirectly) persons or parties are not responsible for the success or
failure of any decisions or actions related (directly or indirectly) to any information
presented in this publication. The Author and Publisher shall have neither liability
nor responsibility to any person or entity with respect to any loss or damages
arising (directly or indirectly) from the information contained in this publication.

Author: J.A. Gomez.

Layout: Walter Gray.

3
 Introduction to Excel Macros & VBA

Copy Editor: Martin Phillips.

Proofreader: Kevin Thomas.

Cover Designer: Anthony Baker.

Published by: Power Spreadsheets.

First Edition: September 2018.

4
 Introduction to Excel Macros & VBA

Table of Contents
Introduction ................................................................................................ 9

About this Book ........................................................................................ 9

About J.A. Gomez...................................................................................... 9

How this Book is Organized ...................................................................... 10

More Information .................................................................................... 11

Chapter 1: What Macros and VBA Are .......................................................... 12

The Importance of Terminology ................................................................ 12

What Macros and VBA are ........................................................................ 13

Macros ................................................................................................ 13

VBA .................................................................................................... 13

The Relationship Between Macros and VBA .............................................. 14

Other Important Terms ......................................................................... 15

Summary and Key Concepts ..................................................................... 17

Chapter 2: Why Learn and Use Macros and VBA ............................................ 18

Introduction ........................................................................................... 18

What Macros and VBA Can Do .................................................................. 19

Add Features to Excel with Macros and VBA ............................................. 19

Automate Repetitive or Recurring Tasks with Macros and VBA .................... 23

Customize the User Interface and User Experience with Macros and VBA ..... 25

Control Other Applications From Excel with Macros and VBA ....................... 25

Additional Reasons to Use Macros and VBA to Automate Manual Tasks .......... 25

Macros and VBA are Fast ....................................................................... 26

Macros and VBA are Cost-Efficient .......................................................... 26

Macros and VBA are Precise ................................................................... 27

Summary and Key Concepts ..................................................................... 27

5
 Introduction to Excel Macros & VBA

Chapter 3: Considerations when Working with VBA ........................................ 29

Introduction ........................................................................................... 29

The Possibility of Macro Failure ................................................................. 29

Excel Updates and Macros/VBA ................................................................. 30

Excel 2007 ........................................................................................... 30

Excel 2010 ........................................................................................... 31

Excel 2013 ........................................................................................... 31

Excel 2016 ........................................................................................... 32

Additional Considerations About Excel Updates and Office 365 .................... 33

Excel Updates and Your Work with Macros and VBA .................................. 34

Summary and Key Concepts ..................................................................... 35

Chapter 4: How to Approach Learning Macros and VBA .................................. 36

Introduction ........................................................................................... 36

Learning to Work with Macros and VBA can be Easy .................................... 36

VBA is User-Friendly .............................................................................. 37

As an Excel User, You Are Already Programming ....................................... 38

Several Tools and Features Help You Learn and Work with Macros and VBA . 40

Suggestions to Learn About Macros and VBA .............................................. 41

Summary and Key Concepts ..................................................................... 42

Next Steps................................................................................................ 44

I Want to Hear from You! ......................................................................... 45

6
 Introduction to Excel Macros & VBA

Table of Figures
Figure 1: The relationship between macros and VBA is similar to a chocolate cake
recipe. ..................................................................................................... 14

Figure 2: To hide a worksheet (steps #1 and #2): Right click → select Hide. .... 20

Figure 3: To unhide a worksheet (steps #1 and #2): Right click → select Unhide.
............................................................................................................... 20

Figure 4: To unhide a worksheet (steps #3 and #4): Select worksheet → click OK.
............................................................................................................... 21

Figure 5: A basic message box. ................................................................... 22

Figure 6: A basic input box. ........................................................................ 22

Figure 7: The Ctrl+End keyboard shortcut takes you to the last cell in the used
range. ...................................................................................................... 23

Figure 8: The last cell in the used range may be different from the last cell with
data. ........................................................................................................ 23

Figure 9: The introduction of the Ribbon interface was one of the main changes in
Excel 2007................................................................................................ 31

Figure 10: Sparklines and Slicers were introduced in Excel 2010. .................... 31

Figure 11: The Quick Analysis Tool was introduced in Excel 2013. ................... 32

Figure 12: With the Single Document Interface, each Excel workbook has its own
window. ................................................................................................... 32

Figure 13: VBA support for Power Query (Get & Transform) was introduced in Excel
2016. ....................................................................................................... 32

Figure 14: Recent Excel versions have introduced several chart styles. ............ 34

Figure 15: The statement ActiveCell.Columns.AutoFit autofits the column width.

............................................................................................................... 38

Figure 16: The VLOOKUP function requires you to follow a specific set of
conventions and guidelines. ........................................................................ 39

Figure 17: The Macro Recorder (accessed through the Record Macro button) is one
of the tools and features you can use to start working with macros and VBA. .... 40

7
 Introduction to Excel Macros & VBA

Figure 18: The VBE has several tools and features to help you work with VBA. .. 41

8
 Introduction to Excel Macros & VBA

Introduction
About this Book
As implied by its title, the main purpose of this Book is to introduce you to the
wonderful world of Excel macros and VBA.

One of the main goals of this Book is to help you kickstart the process of learning
about Excel macros and VBA. By purchasing this Book, you have taken your first
step towards becoming a more powerful and efficient Excel user. As I explain in
Chapter 2, becoming a powerful and efficient Excel user has the potential to help
you in several ways.

About J.A. Gomez

This is a training and reference book on macros and VBA.

The learning techniques and strategies I use in the following Chapters were first
applied to my own process of mastering macros and VBA.

I am originally from Colombia, a country in South America. However, I have been

very lucky and have had the opportunity to live, study and work in several
countries, including:

• Spain;

• South Korea;

• Singapore;

• Germany; and

• Canada.

Nowadays, I spend most of my time working on and researching about:

• Automation;

• Excel;

• Macros; and

• VBA.

However, this was not always the case.

9
 Introduction to Excel Macros & VBA

In fact, my undergraduate degree is in Law. Even though I also studied Economics,

I only started my process of becoming an advanced Excel user when I decided to
stop working as a Lawyer and focus on Finance.

At times, I faced common self-limiting beliefs and doubts. These beliefs ranged
from believing I was not very good at Math to thinking I would never be able to
learn how to program.

I was soon proved wrong. From the moment I decided to focus on Finance, data
analysis, automation and Excel, I have achieved a lot. This includes obtaining a
Masters in Quantitative Finance with Merit and offers for several good-paying jobs
(in a variety of countries and industries) where advanced knowledge of Excel was
a requirement.

These achievements, however, pale in comparison to the work I have done at my

website, PowerSpreadsheets.com. Power Spreadsheets has grown to become one
of the largest and most popular websites about Excel macros and VBA. I am grateful
that (every day) I get to help thousands of Excel users, just like you, become more
powerful and efficient.

The main reason I share my story is because I firmly believe that, if I could
overcome my limitations and self-limiting beliefs to master Excel macros and VBA,
you also can. I did not have any unique gifts, traits or experiences to indicate that
I would be able to become an authority in the field of Excel macros and VBA.

I decided to learn about these topics. I have invested a lot of time and money
towards achieving this goal. This experience of learning about Excel macros and
VBA has allowed me to organize and structure the information anybody, like you,
needs to learn about these topics.

How this Book is Organized

This Book is divided in four Chapters.

• In Chapter 1, you learn what macros, VBA and other related terms refer to.

• In Chapter 2, you learn how macros and VBA can help you become a more
efficient and powerful Excel user.

• In Chapter 3, you learn about some important characteristics of macros and

VBA. A good understanding of these characteristics should help you better

10
 Introduction to Excel Macros & VBA

understand some potential pitfalls you may want to avoid while using macros
and VBA.

• In Chapter 4, you learn tips and strategies that help you approach learning
about macros and VBA. These tips and strategies should help you understand
how learning about macros and VBA can be easy and interesting.

More Information
In addition to authoring this Book, J.A. Gomez is the creator of one of the most
successful Excel macro and VBA websites. You can learn more about J and obtain
free supplemental training materials at PowerSpreadsheets.com.

11
 Introduction to Excel Macros & VBA

Chapter 1: What Macros and VBA Are

In this Chapter, you learn:

• The importance of understanding the vocabulary that applies to working with

macros and VBA.

• What a macro is.

• What VBA is.

• The relationship between macros and VBA.

• How some of the terms related to macros and VBA are used in practice.

• What the following terms refer to and how they are different from "macro" and
"VBA":

• Program.

• Application.

• Procedure.

• Sub procedure.

• Routine.

• Sub routine.

The Importance of Terminology

As most other topics you may have studied and learned about, Excel macros and
VBA use a specific terminology. As you learn in future Chapters, this terminology
is not very complicated. You must, however, still learn it.

A good understanding of the terms you use is essential to learning about, and
working with, macros and VBA. The first step to learning how to work with macros
and VBA is understanding what these and other important terms refer to.

Some of the other terms you learn about in this Chapter are commonly used to
refer to the same (or similar) constructs.

12
 Introduction to Excel Macros & VBA

What Macros and VBA are

Macros
At a basic level, a macro is a small computer program.

If you work with computers, you probably use computer programs all the time. For
purposes of this Book, a computer program is:

A set of instructions or commands communicated to a computer in a

programming language.

Therefore, you can define an Excel macro as:

A small set of instructions or commands you communicate to Excel in a

programming language (you learn about this programming language in the
following Section).

You can usually open or execute programs when you need to. The same occurs
with macros. In other words, after you create a macro, you can command Excel to
execute the macro as, and when, required.

Macros have several uses. However, most macro users like working with macros
because macros allow them to automate processes in Excel. You can, for example,
use these automation capabilities for several purposes, including:

• Executing a task or series of tasks; or

• Adding specific features to Excel.

You learn more about the most common uses of macros in Chapter 2.

VBA
In the previous Section, you learned that the commands that compose a computer
program are communicated in a programming language. In the case of Excel
macros, the programming language you work with is known as Visual Basic for
Applications or VBA.

In other words:

VBA is the programming language you use to create Excel macros.

13
 Introduction to Excel Macros & VBA

VBA is a programming language created by Microsoft. You can use this language
to work with the Microsoft Office Applications that support VBA. Excel is one of such
Applications that supports VBA.

VBA is built into the Applications that support it (such as Excel). In other words,
your version of Microsoft Excel includes VBA and you do not have to download or
purchase any additional software to work with macros and VBA.

At the same time, due to this relationship between Excel and VBA, you must have
Excel open to work with VBA.

The Relationship Between Macros and VBA

You can think of the relationship between macros and VBA as a recipe. Imagine,
for example, that you want to bake a chocolate cake.

• The step-by-step instructions you must follow to bake the cake are the
equivalent of a macro.

• The language in which those instructions are written (for example, English) is
the equivalent of VBA.

Figure 1: The relationship between macros and VBA is similar to a chocolate cake recipe.

In other words:

• Macros allow you to give a variety of step-by-step instructions to Excel. You

call these sets of instructions "macros".

• Those instructions (macros) are written in a programming language known as

"Visual Basic for Applications" or "VBA".

Strictly speaking, you do not always have to use VBA to create macros. In other
words, you can create (basic) macros without learning VBA. You learn about the
tool that helps you create macros without using VBA in Chapter 4.

14
 Introduction to Excel Macros & VBA

The macros you create without using VBA can be helpful and powerful. In practice,
you usually need to learn, and work with, VBA to take full advantage of the power
of macros and VBA. This helps you understand, adjust and create the commands
that power the macro.

Other Important Terms

Excel programming terminology can be confusing.

• Sometimes, terms are used interchangeably.

• Other times, a single term is used to refer to more than one element.

In my Books, Courses and other training materials, I try to use terms consistently.
For example, I generally use the terms "macro" and "VBA" as defined in previous
Sections. In my opinion, this terminology is the most consistent with that used by
Microsoft.

• VBA is a programming language.

• Macros are a set of instructions or commands you give to a Microsoft Office

Application (like Excel), using VBA.

In real life, however, you may encounter the following situations:

• Different terms being used to refer to macros or VBA.

• The terms "macro" and "VBA" being used in a different way to that in which I
define them in this Book.

In other words, you sometimes see people:

• Using terms interchangeably;

• Giving them different meanings; or

• Replacing certain terms (such as "macro" or "VBA") with other terms.

The following are some terms that are related to the topic of macros and VBA but
are more commonly used to refer to something else:

• Program.

• Application.

• Procedure.

• Sub procedure.

15
 Introduction to Excel Macros & VBA

• Routine.

• Sub routine.

Program

In a previous Section, you learned that macros are small computer programs. The
key word in this definition is "small".

The scope of a regular computer program is usually much broader than that of a
macro. In other words, macros usually have a specific purpose and scope that is
much more limited than that of a full computer program.

Application

The scope of an Application is usually much broader than that of a macro.

In the context of Excel macros and VBA, the term "Application" is commonly used
to refer to:

Excel files containing macros that are designed for other people to use.

Therefore, if you plan to become an Excel Developer, you are likely to spend a
substantial amount of time developing Applications.

Procedure and Sub Procedure

The term "procedure" usually refers to:

A named sequence of VBA statements that are executed as a unit.

These statements are the instructions you give to Excel using VBA.

The definitions of "macro" (which you learned in a previous Section) and

"procedure" are similar. This is because, from a programming perspective, macros
are procedures. Therefore, the concept of procedure is central to macros and VBA.

When working with VBA, you can create the following two types of procedures:

#1. Sub procedures, which:

• Perform tasks; and

• Allow you to carry out activities.

#2. Function procedures, which:

• Carry out calculations; and

16
 Introduction to Excel Macros & VBA

• Return a value.

Routine and Sub Routine

The terms "routine" and "Sub routine" are sometimes used to refer, respectively,
to:

• Procedures; and

• Sub procedures.

Summary and Key Concepts

• When working with macros and VBA, you use a specific terminology.

• A computer program is a set of instructions or commands communicated to a

computer in a programming language.

• An Excel macro is a small computer program: A small set of instructions or

commands you communicate to Excel in Visual Basic for Applications (VBA).

• Visual Basic for Applications (VBA) is the programming language you use to
create Excel macros.

• You can create basic macros without using VBA.

• In the context of working with Excel macros and VBA, an Application is an

Excel file containing macros that are designed for other people to use.

• A procedure is a named sequence of VBA statements that are executed as a

unit.

• You can create two types of procedures when working with VBA:

#1. Sub procedures.

#2. Function procedures.

• From a programming perspective, macros are procedures.

• In practice, some of the terms you learned about in this Chapter may be used
slightly differently. This includes, for example, terms being used:

• Interchangeably; or

• To refer to more than one element.

17
 Introduction to Excel Macros & VBA

Chapter 2: Why Learn and Use Macros and

VBA
In this Chapter, you learn:

• How macros and VBA can help you.

• What macros and VBA can do.

• How Excel has certain tools and features you cannot access manually. How
VBA helps you work with these tools and features.

• The difference between repetitive and recurring tasks. How macros and VBA
help you deal with both types of tasks.

• How macros and VBA allow you to customize Excel's user interface and
experience.

• How macros and VBA allow you to:

• Control other Applications from Excel; or

• Vice-versa, control Excel from other Applications.

• Some of the main reasons for using macros and VBA to automate manual
tasks.

Introduction
In the previous Chapter, you learned that macros and VBA allow you to control
Excel.

This combination (Excel + Macros/VBA) can be very useful. Therefore, macros and
VBA have the potential to help you become a more powerful and efficient Excel
user.

Becoming a more powerful and efficient Excel user has the potential to help you in
several ways, including the following four:

#1. By helping you save time while carrying out certain activities in Excel, so
that you have more time for other activities you really care about.

#2. By obtaining skills that are in high demand in the job market, and whose
demand continues to grow at a fast pace.

18
 Introduction to Excel Macros & VBA

#3. By enhancing your professional profile and helping you advance in your
career and obtain good-paying jobs.

#4. By receiving recognition from your colleagues, superiors and friends.

What Macros and VBA Can Do

Macros and VBA have thousands of different uses that can help you save huge
amounts of time. This, of course, assumes you have the appropriate knowledge to
work with Excel macros and VBA.

At a basic level, macros and VBA allow you to do almost anything you can do
manually. In other words, Excel does not lose any of its functionality when you
work with macros and VBA.

On the other hand, you stand to gain a lot from working with macros and VBA.
Macros and VBA allow you to get Excel to do things that you either:

• Cannot do manually; or

• Do not want to do manually.

The following are four of the main things Excel macros and VBA can do:

#1. Add features to Excel.

#2. Automate repetitive or recurring tasks.

#3. Customize the user interface and experience.

#4. Work with other Applications from Excel.

Add Features to Excel with Macros and VBA

Regular Excel is very powerful. It has thousands of features and commands you
can access manually.

However, there are several actions you cannot (manually) carry out from Excel.

VBA allows you to access some features that would, otherwise, be inaccessible. The
following are three of these features:

#1. Very hidden sheets.

#2. Message boxes and input boxes.

#3. The ability to reset the used range of a worksheet.

19
 Introduction to Excel Macros & VBA

Very Hidden Sheets

Excel allows you to hide or unhide a sheet.

For example, you can hide a worksheet as follows:

#1. Right click the worksheet tab.

#2. Select Hide from the context menu displayed by Excel.

Figure 2: To hide a worksheet (steps #1 and #2): Right click → select Hide.

Similarly, you can unhide a hidden worksheet as follows:

#1. Right click the worksheet tab.

#2. Select Unhide in the context menu displayed by Excel.

Figure 3: To unhide a worksheet (steps #1 and #2): Right click → select Unhide.

#3. Excel displays the Unhide dialog box containing the hidden worksheets.

#4. In the Unhide dialog box:

#1. Select the worksheet you want to unhide; and

#2. Click the OK button.

20
 Introduction to Excel Macros & VBA

Figure 4: To unhide a worksheet (steps #3 and #4): Select worksheet → click OK.

Excel sheets have a third visibility status: Very hidden. In other words, a sheet can
be:

#1. Visible;

#2. Hidden; or

#3. Very hidden.

From Excel's interface, you cannot:

• Make a sheet very hidden; nor

• Unhide a very hidden sheet.

For these purposes, you must work with VBA.

Knowing how to work with very hidden sheets may be helpful for several purposes.
For example:

• The ability to make sheets very hidden allows you to add an extra layer of
protection or coverage to sheets you want to hide; and

• The knowledge about very hidden sheets may give you an extra advantage
when working with workbooks (created by others) that have very hidden sheets.

Message Boxes and Input Boxes

Message boxes and input boxes allow you to:

#1. Display a dialog box;

#2. Wait for the user to:

• Click a button; or

• Enter input (and click a button); and

21
 Introduction to Excel Macros & VBA

#3. Return data with information about the action taken by the user.

Figure 5: A basic message box.

Figure 6: A basic input box.

You cannot manually create message nor input boxes from Excel. You must work
with VBA in both cases.

Reset the Used Range of a Worksheet

The used range of a worksheet is the range of cells that has been used. This
includes, for example:

• Cells that currently contain values; and

• Cells that used to contain values you deleted.

Excel has a keyboard shortcut (Ctrl+End) that takes you to the last cell in the used
range. This keyboard shortcut works for English (United States) language settings.
Depending on your language settings, you may have to use a different keyboard
shortcut to achieve the same result.

22
 Introduction to Excel Macros & VBA

Figure 7: The Ctrl+End keyboard shortcut takes you to the last cell in the used range.

When using the Ctrl+End keyboard shortcut, you usually want to go to the last cell
with data in the worksheet. However, as I explain above, the used range also
contains cells that used to contain values (were used) even if they no longer contain
values (the value has been deleted). Because of this behavior, the last cell in a
worksheet's used range may not be the last cell with data in the worksheet.

Figure 8: The last cell in the used range may be different from the last cell with data.

You can reset the used range, and avoid problems like the one I describe above,
by working with VBA. You cannot manually reset the used range from Excel.

Automate Repetitive or Recurring Tasks with Macros and VBA

For most Excel users, one of the main reasons to learn about macros and VBA is to
automate repetitive or recurring tasks.

When you create an appropriate macro, Excel can carry out some repetitive or
recurring tasks automatically. The degree of automation you can achieve depends
on a variety of factors. These include, for example:

• The specific characteristics of the task at hand; and

• Your level of knowledge about macros and VBA.

23
 Introduction to Excel Macros & VBA

When the appropriate circumstances exist and your knowledge of macros and VBA
is enough, you can fully or partially automate tasks. In such cases, those tasks are
executed with very little or none input from you.

Recurring Tasks

Recurring tasks are processes you encounter frequently.

A typical example of recurring tasks is the preparation of periodic reports, such as

weekly or monthly reports. Some of these tasks may be:

• Time-consuming;

• Cumbersome; or

• Simply annoying.

Consider your own work responsibilities. You may have to prepare certain periodic
reports that require you to always repeat the same (or very similar) steps. These
frequently-repeated steps may include, among others:

• Organizing data;

• Formatting values; and

• Copying and pasting data.

If you do not want to carry out these tasks manually every single time, you can
use macros and VBA to automate a part of, or the whole, process.

Repetitive Tasks

Repetitive tasks are those where you must carry out the same action several times.
This may be, for example, a task you must repeat for several:

• Cell ranges (including individual cells, rows or columns);

• Charts;

• Worksheets; or

• Workbooks.

Repetitive tasks are very common, among others, when you clean up data.

24
 Introduction to Excel Macros & VBA

Customize the User Interface and User Experience with Macros and
VBA
In the previous Sections, you learned how macros and VBA allow you to:

• Add features to Excel; and

• Automate tasks.

When you do either of these, you are (from a broader perspective) customizing the
user interface and improving the user experience. The ability of macros and VBA
to customize the user interface and experience includes, among others, the
following possibilities:

• Creating custom commands launched by keyboard shortcuts.

• Displaying:

• Message boxes;

• Input boxes; or

• Custom dialog boxes.

• Creating custom worksheet functions

• Creating macros that run automatically when a specific event happens.

Control Other Applications From Excel with Macros and VBA

As you learned in Chapter 1, VBA is not limited to Excel.

Although a relatively advanced topic, you can use VBA to communicate with, and
control, other Applications (for example, Word, PowerPoint, Outlook or Windows
itself). This allows you to both:

• Expand Excel's functionality by obtaining support and assistance from other

Applications; and

• Use Excel to expand the functionality of other Microsoft Office Applications.

Additional Reasons to Use Macros and VBA to Automate

Manual Tasks
In this Chapter, you learned about some of the feats macros and VBA can achieve.
Theoretically, you can do some (not all) of these things manually.

25
 Introduction to Excel Macros & VBA

However, even in the case of tasks you can carry out manually, macros and VBA
may be useful. There are three main reasons for this:

#1. Speed;

#2. Cost; and

#3. Reliability.

Macros and VBA are Fast

You can analyze the speed of macros and VBA from two perspectives:

#1. By comparing macros and VBA with humans.

#2. By analyzing the impact macros and VBA have on the speed and
performance of your Excel files.

VBA is fast regardless of which of the two perspectives you take.

#1. Macros are significantly faster than you or me, and pretty much any other
person. Macros can usually complete tasks in a few seconds.

#2. Macros are stored as text. Therefore, VBA code:

• Is usually compact; and

• Does not have a big influence on the file size or speed of an Excel workbook.

In other words, adding VBA code to your Excel workbooks is unlikely to have a
negative impact on their performance.

Macros and VBA are Cost-Efficient

Considering the capabilities of macros and VBA you learned in previous Sections,
they are cheap.

By appropriately using macros and VBA, you may experience efficiency gains that
are comparable to somebody helping you carry out certain tasks. The savings you
can experience by reducing the number of man-hours required to complete certain
tasks may be enough to justify the upfront time investment in both:

• Learning about macros and VBA; and

• Developing any macros you may need.

26
 Introduction to Excel Macros & VBA

Macros and VBA are Precise

Macros and VBA are usually accurate and precise.

If your macros are appropriately built and coded, and contain no errors, they can
help you reduce the risk of errors and mistakes. This is because VBA always follows
the set of instructions you provide. It does this step-by-step and without any
deviation.

Summary and Key Concepts

• Macros and VBA have the potential to help you become a more powerful and
efficient Excel user. This has the potential to help you in several ways by, for
example:

#1. Helping you save time.

#2. Obtaining skills that are in high demand in the job market.

#3. Enhancing your professional profile.

#4. Receiving recognition.

• Macros and VBA can do:

• Almost anything you can do manually; and

• Several things you cannot, or do not want to, do manually.

• Macros and VBA can do the following:

#1. Add features to Excel.

#2. Automate repetitive or recurring tasks.

#3. Customize the user interface and experience.

#4. Work with other Applications from Excel.

• Carrying out a task with macros and VBA (instead of manually) has the
following three main advantages:

#1. Speed:

#1. Macros and VBA are faster than humans.

#2. Macros are stored as text. Therefore, they (usually) do not have a big
influence on the file size or speed of a workbook.

27
 Introduction to Excel Macros & VBA

#2. Cost: Considering the capabilities of macros and VBA, they are cheap.

#3. Precision: When appropriately used, macros and VBA help you reduce the
risk of errors and mistakes.

28
 Introduction to Excel Macros & VBA

Chapter 3: Considerations when Working

with VBA
In this Chapter, you learn:

• That, despite their power and usefulness, macros and VBA may fail to produce
the results you expect.

• The two most common pitfalls to consider when working with macros and VBA.

• The main reason why macros may fail to work as you expect. How to reduce
the risks associated with your macros failing.

• The impact Excel updates have on both:

• Macros and VBA; and

• How you approach working with macros and VBA.

• Some of the main changes introduced in the most recent versions of Excel.

Introduction
In Chapter 2, you learned that macros and VBA are very useful and powerful.
However, macros and VBA are not perfect. There are a few potential pitfalls to
consider when working with them.

The following are two of the most common pitfalls to consider when working with
Excel macros and VBA:

#1. The possibility of macro failure.

#2. The impact Excel updates have on macros.

The Possibility of Macro Failure

Macros can fail to work as you expect.

As you learned in Chapter 2, VBA follows your instructions, step-by-step and

without critically analyzing them. This is usually a good thing. It tends to reduce
the risk of errors and mistakes.

However, from time-to-time, your instructions and the way VBA follows them
without deviation may result in unexpected errors or results.

29
 Introduction to Excel Macros & VBA

This is a risk you can usually manage. Generally, the more you know about macros
and VBA, the lower the risk of your macros failing. In other words, you can learn
strategies and techniques that can help you manage and reduce the risk of losing
data or suffering other damages when a macro does not work as expected.

Excel Updates and Macros/VBA

Every Excel update has an influence on VBA.

VBA seeks to reflect, and allow you to work with, the tools and features available
in Excel. Therefore, every time Microsoft releases a new Excel version, VBA must
reflect the changes.

Microsoft tries to maintain compatibility between the different versions. In practice,

however, this is easier said than done.

Despite Microsoft's efforts, some Excel features work differently depending on the
Excel version you use. This creates problems both ways:

• Sometimes, macros fail on Excel versions that are older to that in which they
were created.

• Other times, macros fail on Excel versions that are newer to that in which they
were created.

Generally, your macros cannot rely on features introduced by Microsoft on an Excel

version that is newer to the one you are working with.

Further compatibility issues exist between VBA for Windows and Mac.

There are several examples of features that Microsoft has introduced or updated in
the most recent versions of Excel.

Excel 2007
Excel 2007 modified some basic and essential characteristics of Excel. In other
words, if you are using Excel 2003 or earlier, Excel has changed significantly.

Even though the basic concepts and structure of VBA may not have changed much,
the changes introduced by Microsoft after Excel 2003 have an important influence
on the constructs you can use when working with macros and VBA.

The following are some of the main changes introduced with Excel 2007:

• Change of the file structure of Excel workbooks.

30
 Introduction to Excel Macros & VBA

• Increase in the number of rows and columns in a worksheet.

• Appearance of the Ribbon.

• Change in some specific tools, such as SmartArt.

Figure 9: The introduction of the Ribbon interface was one of the main changes in Excel 2007.

If you are using Excel 2007 or newer, there are less changes. However, since
Microsoft is always adding new features and updating existing ones, you must still
consider some modifications.

Excel 2010
The following are two useful features introduced in Excel 2010:

#1. Sparklines, which are small charts you can insert in a single cell.

#2. Slicers (originally created for PivotTables), which make data filtering easier.

Figure 10: Sparklines and Slicers were introduced in Excel 2010.

Slicers have, themselves, gone through further updates after their introduction. For
example, since Excel 2013, Slicers work with both:

• PivotTables; and

• Tables.

Excel 2013
The following are three useful features introduced in Excel 2013:

#1. The Data Model.

#2. The Quick Analysis Tool.

#3. The Single Document Interface.

31
 Introduction to Excel Macros & VBA

Figure 11: The Quick Analysis Tool was introduced in Excel 2013.

The Single Document Interface is the feature (out of the three I mention above)
that is likely to have a bigger influence on your macros and VBA code.

• Under the Multiple Document Interface that Excel used previously, all
workbooks were housed in a single Excel Application window.

• Under the Single Document Interface, each workbook is housed in its own
window. The Excel window of each workbook is separate from the Excel window
of any other workbook.

Figure 12: With the Single Document Interface, each Excel workbook has its own window.

The change from Multiple Document Interface to Single Document Interface has an
important influence on how you work with, and identify, workbooks and windows
when creating macros and working with VBA.

Excel 2016
One of the main changes introduced by Microsoft in Excel 2016 is support for Power
Query (Get & Transform). This allows you to automate the creation and
refreshment of Power Query queries.

Figure 13: VBA support for Power Query (Get & Transform) was introduced in Excel 2016.

32
 Introduction to Excel Macros & VBA

Additional Considerations About Excel Updates and Office 365

The updates and new features you learned about in previous Sections are some of
the most important changes introduced by Microsoft in the most recent versions of
Excel.

The following are three final considerations about Excel updates:

#1. With each update, there are other less visible updates and modifications
that may influence the behavior of your macros and VBA code.

#2. There are some tools and features that are constantly updated. Charts and
PivotTables are two examples of such tools and features. Both have gone
through several updates over the years. For example:

• Excel 2013 introduced a new chart interface. These changes resulted in the
creation of a new VBA command to add charts. This is known as the
AddChart2 method.

• Excel 2016 introduced several new chart styles, such as:

• Treemap;

• Sunburst;

• Histogram;

• Pareto;

• Box and Whisker; and

• Waterfall.

Later Excel versions continue to introduce new chart styles, such as:

• Funnel; and

• Map.

33
 Introduction to Excel Macros & VBA

Figure 14: Recent Excel versions have introduced several chart styles.

#3. With the introduction of Office 365, the Office 365 version of Excel (Excel
365) is constantly updated.

Office 365 is a subscription service. Office 365 subscribers get regular updates.
In other words, users of Excel 365 are constantly getting new features.

The most recent stand-alone Excel version (at the time of writing) is Excel 2019.
Office 2019 (which includes Excel 2019) is sold as a one-time purchase (no
subscription). Users of Excel 2019 do not have access to the regular updates
that Office 365 subscribers receive.

Microsoft is strongly encouraging users to become Office 365 subscribers.

• As more users subscribe to Office 365, the compatibility issues you learned
about in this Chapter are expected to become less of an issue.

• In the meanwhile, however, consider the fact that Microsoft will continue to
(regularly) introduce new features that influence what you can or cannot
manipulate with macros and VBA.

Excel Updates and Your Work with Macros and VBA

The potential compatibility issues you learned about in this Chapter are particularly
relevant in the following two situations:

#1. If you plan to become a professional Excel Developer.

#2. If you plan to create macros to be used across different Excel versions.

From a broader perspective, however, the basic structure and logic of macros and
VBA remains the same across versions.

In other words, by understanding the basic principles behind macros and VBA, you
are likely to be in a better position to use these tools in different Excel versions.

34
 Introduction to Excel Macros & VBA

Because of these reasons, my Books, Courses and several training materials focus
on these broadly applicable principles and frameworks, instead of specific tools and
features that vary from one Excel version to the next.

Summary and Key Concepts

• The following are two of the most common pitfalls to consider when working
with macros and VBA:

#1. The possibility of macro failure.

#2. The impact Excel updates have on macros and VBA.

• Macros can fail to work as you expect. Generally, the more you know about
macros and VBA, the lower this risk.

• Every Excel update has an influence on VBA. Although Microsoft tries to

maintain compatibility between different versions, some Excel features work
differently depending on the Excel version you use.

• Macros may fail on Excel versions that are older or newer to that in which they
were created.

• Microsoft is constantly updating and modifying Excel. This may influence the
behavior of your macros and VBA code.

• Despite the changes in Excel, the basic structure and logic of macros and VBA
remains the same across versions. By understanding the basic principles behind
macros and VBA, you are likely to be in a good position to work with the different
versions of Excel.

35
 Introduction to Excel Macros & VBA

Chapter 4: How to Approach Learning

Macros and VBA
In this Chapter, you learn:

• Why learning about macros and VBA can be easy and enjoyable.

• The main reasons why the VBA language is:

• User-friendly; and

• Easy to understand.

• Why, as an Excel user, you are already (in a way) programming.

• The main Excel and VBA tools and features that help you learn about, and work
with, macros and VBA.

• How to approach the process of learning about macros and VBA.

Introduction
At first glance, it may sound that learning how to work with macros and VBA is
difficult. However, this does not have to be the case.

Learning about macros and VBA does not have to be difficult nor complicated. I am
convinced that, by adopting the appropriate strategies and mindset, you can make
the learning process easier and more enjoyable.

Learning to Work with Macros and VBA can be Easy

In my experience, the main reason people shy away from working with macros and
VBA is because these tools are usually associated with programming.

In my opinion, you should not let the word "programming" stress you. The following
are the four main reasons for this:

#1. VBA has several characteristics that make it:

• Intuitive; and

• User-friendly.

#2. If you use Excel, you are (in a way) already programming.

36
 Introduction to Excel Macros & VBA

#3. The association several people make between macros and programming is
not always correct. You can work with basic macros without programming.

#4. Excel and VBA have a set of tools and features that are designed to help
you work with, and learn about, macros and VBA.

Therefore, if you can use Excel, I am confident you can work with macros and VBA.
In fact, I believe you may find that working with macros and VBA is easier than
you expected.

VBA is User-Friendly
VBA is user-friendly. It allows anyone like you and me to code.

This user-friendliness is perhaps most evident when you consider the statement
structure and vocabulary used by VBA. The following two characteristics stand out:

#1. VBA:

• Borrows a lot of words from the English language; or

• Uses common variations of such words.

#2. The statement structure and punctuation you use when creating a VBA
statement is relatively simple and straightforward.

Consider, for example, the following VBA statement:

ActiveCell.Columns.AutoFit
For purposes of this Book, you do not need to focus on the details of what this
instruction means. Simply notice how VBA:

• Borrows words from the English language (active, cell, columns, auto and fit);
and

• Has a straightforward simple structure.

In fact, you probably do not need to know VBA to get a rough idea of what this
statement does. You can, probably, infer that the constructs in this statement do
the following:

#1. ActiveCell refers to the active cell.

#2. Columns refers to a column (that where the active cell is located).

37
 Introduction to Excel Macros & VBA

#3. AutoFit gives Excel the instruction to autofit a column (that where the
active cell is located) to its contents.

Therefore, by analyzing the entire statement, you can probably conclude that it
autofits the width of the column where the current active cell is located.

Figure 15: The statement ActiveCell.Columns.AutoFit autofits the column width.

This is a very informal way of interpreting a VBA statement. At more advanced

levels, you learn strategies that help you tackle and interpret VBA statements using
a more precise and formal process.

In other words, the point I am trying to make is not that you should always try to
guess what a VBA statement means. My purpose is to show you that the structure
and vocabulary of the VBA language is:

• User-friendly; and

• Easy to learn.

As an Excel User, You Are Already Programming

If you use Excel, you are already (in a way) programming.

In Chapter 1, you learned that a macro is a set of instructions or commands you

communicate to Excel using VBA. From this perspective, VBA is just a set of
conventions and guidelines you follow to communicate your commands to Excel.

When you work with Excel, you are constantly communicating commands. This may
be, for example:

• By clicking a button in the Ribbon or Quick Access Toolbar;

• By pressing a keyboard shortcut; or

• By entering a worksheet formula in a cell.

38
 Introduction to Excel Macros & VBA

Consider the example worksheet displayed in Figure 16 below.

• A two-column table (cells A1 to B6) contains the names of five salespeople and
the number of units they sold.

• Cell E2 contains a worksheet formula that uses the VLOOKUP function. This
formula (=VLOOKUP(E1,A2:B6,2,FALSE)) does the following:

#1. Looks for the name displayed in cell E1 (for example, Suzanne Wilkins)
in the first column of the table.

#2. Returns the corresponding number of units sold from the second column
in the table.

Figure 16: The VLOOKUP function requires you to follow a specific set of conventions and guidelines.

The worksheet formula in cell E2 (=VLOOKUP(E1,A2:B6,2,FALSE)) follows a specific

set of conventions and guidelines. When these conventions and guidelines are
appropriately followed, Excel returns the number of units sold by the selected
salesperson.

In other words, just like VBA, worksheet formulas (as the example above) are a
set of conventions and guidelines you follow to communicate commands to Excel.
These conventions and guidelines differ from those of regular English.

For example, a person that is not familiar with Excel is unlikely to understand the
VLOOKUP formula in the example above.

You, as an Excel user, may have a good idea of this VLOOKUP formula's meaning.
Even if you are not familiar with the VLOOKUP worksheet function, you know there
are certain tools, features and information resources you can use to understand
the instructions given by the formula.

From this perspective, creating worksheet formulas is not that different from
programming in VBA.

39
 Introduction to Excel Macros & VBA

Several Tools and Features Help You Learn and Work with Macros
and VBA
You can rely on a variety of tools and features during your journey towards
becoming a powerful macro and VBA user.

You do not have to be an advanced Excel user to start creating macros. Excel has
several tools that:

• Are easily accessible;

• Help you create macros; and

• Help you learn about VBA.

The Macro Recorder

Perhaps the most important tool to start working with, and learning about, macros
and VBA is the Macro Recorder.

Figure 17: The Macro Recorder (accessed through the Record Macro button) is one of the tools and features you can
use to start working with macros and VBA.

As implied by its name, the Macro Recorder:

• Allows you to record your actions; and

• Takes care of:

#1. Translating your actions; and

#2. Writing the VBA code for you (recording a macro).

For simple and basic macros, the Macro Recorder may be all you need. If a recorded
macro works well, you may not even be interested in the VBA code behind it.
Additionally, if the macro is not working properly or needs to be adjusted, you can
record it again.

The Macro Recorder, therefore, allows you to start creating macros without using
VBA. You can learn how to work with the Macro Recorder and start creating macros
without programming in Create Excel Macros Without Programming.

40
 Introduction to Excel Macros & VBA

The Visual Basic Editor (VBE)

The next step, once you are comfortable working with the Macro Recorder, is to
work with VBA code. This is because, even though the Macro Recorder can be
extremely useful, you must usually work with VBA to fully unleash the power of
macros.

The programming environment you use to work with VBA is known as the Visual
Basic Editor or VBE. The VBE has several tools and features that help you work with
VBA code and create macros. For example, the VBE can:

• Identify certain problems or errors in your VBA code;

• Help you complete VBA statements; and

• Direct you to the appropriate section in the VBA Help System.

Figure 18: The VBE has several tools and features to help you work with VBA.

You can learn the basics of working with the VBE in Visual Basic Editor (VBE) Basics.

Suggestions to Learn About Macros and VBA

Even though learning about macros and VBA does not have to be difficult, the truth
is that (as with any other language) learning about macros and VBA involves a
learning curve.

During your learning process, the tools and features of Excel and the VBE can be
of substantial help.

Consider, for example, the Macro Recorder. Once you know how to use the Macro
Recorder and have a basic grasp of VBA, you can start:

• Advancing your knowledge at your own pace; and

• Creating more advanced macros as required.

41
 Introduction to Excel Macros & VBA

The more you learn, the better you will become at automating tasks with macros
and VBA.

Probably the best way to advance your VBA knowledge is to practice.

• Work with VBA code examples and modify the code.

• See what happens and how the results change depending on the adjustments
you make.

• Try to understand the original code and why the changes you make lead to
different results.

Additionally, try to put your knowledge in practice as soon as possible.

• Develop real life VBA Projects.

• Use the macro examples and knowledge you gain every day to create macros
that help you in your day-to-day work.

In practice, you may find that (most of the time) you do not have to create very
complex macros. In some cases, such as when you are facing a repetitive task, a
one-time quick-and-dirty macro may be all you need.

While practicing, you may encounter errors. My last suggestion is to not let these
errors stop you. Making errors is part of any learning process.

Summary and Key Concepts

• By adopting the appropriate strategies and mindset, you can make the process
of learning about macros and VBA easier and more enjoyable.

• The following are four of the main reasons why learning about macros and VBA
can be easy:

#1. VBA has several characteristics that make it intuitive and user-friendly.

#2. If you use Excel, you are (in a way) already programming.

#3. You can work with basic macros without knowing about programming.

#4. Excel and VBA have a set of tools and features to help you work with,
and learn about, macros and VBA.

• VBA:

42
 Introduction to Excel Macros & VBA

• Borrows a lot of words from the English language or uses common variations
of such words; and

• Has a relatively simple syntax.

• When working with regular Excel, you follow a set of conventions and
guidelines to communicate commands to Excel. VBA is just a different set of
conventions and guidelines you follow to communicate commands to Excel.

• The Macro Recorder allows you to create macros without programming and is,
perhaps, the most important tool to start working with, and learning about,
macros and VBA.

• The next step after becoming comfortable when working with the Macro
Recorder is to work with VBA code.

• The Visual Basic Editor (VBE) is the programming environment you use to work
with VBA. The VBE has several tools and features that help you work with VBA
code and create macros.

• Consider the following suggestions during your journey towards becoming a

powerful Excel macro and VBA user:

• Practice by, for example, doing the following:

• Working with code examples.

• Modifying VBA example code.

• Analyzing what happens depending on the changes you make to code

examples.

• Trying to understand the code examples and why the changes you make
lead to different results.

• Put your knowledge in practice as soon as possible by, for example,

developing real life VBA Projects.

• Do not feel discouraged when you encounter errors.

43
 Introduction to Excel Macros & VBA

Next Steps
If you have made it this far into the Book, I hope you have found it useful and
enjoyable.

After reading this Book, you probably know:

• What macros and VBA are; and

• How you can benefit from them.

This includes, among others:

• The importance of understanding the vocabulary that applies to working with

macros and VBA.

• What the following terms refer to and how they relate to, and differ from, each
other:

• Program.

• Macro.

• Visual Basic for Applications or VBA.

• Application.

• Procedure.

• Sub procedure.

• Routine.

• Sub routine.

• What macros and VBA can do. Why macros and VBA are useful.

• Some of the main reasons for using macros and VBA to automate manual
tasks.

• The two most common pitfalls to consider when working with macros and VBA.

• The impact Excel updates have on both:

• Macros and VBA; and

• How you approach working with macros and VBA.

44
 Introduction to Excel Macros & VBA

• The main reasons why the VBA language is user-friendly and easy to
understand.

• The main Excel and VBA tools and features that help you learn about, and work
with, macros and VBA.

• How to approach the process of learning about macros and VBA.

Although this Book has ended, our conversation about Excel macros and VBA has
only begun. I am constantly creating training materials to help you become an
Excel Power User.

There are numerous other places we can continue our work together.

You can find my other Books about Excel and VBA (including the other Books in the
Excel Macros for Beginners Series) at Amazon.com.

You can join my Free Course for Excel macro beginners, Introduction to Excel
Macros, by subscribing to the Power Spreadsheets Newsletter.

I also provide a Premium Course for Excel users who have little or no
experience/knowledge about macros or VBA: Excel Macros for Beginners. If you
would like to be the first to know when the doors to Excel Macros for Beginners
open again, you can learn more about the Course and join the waitlist here.

PowerSpreadsheets.com contains Tutorials, links and other resources to help you

become an Excel Power User. You can visit the organized Archives here.

I Want to Hear from You!

I loved writing this Book and cannot thank you enough for your support. I
appreciate you for taking time out of your day to read this Book.

If you have a few seconds, I would love to read what you think about it. As the
reader of this Book, you are my most important reviewer and commentator. I value
your opinion and want to know:

• What I am doing right;

• What I could do better;

• What Excel topics you would like me to write about; and

• Any other wise advice you are willing to share with me.

45
 Introduction to Excel Macros & VBA

If you enjoyed this Book, please give it an honest review on Amazon.com. This
helps others learn about Excel macros and VBA, allowing them to become more
efficient and powerful Excel users, positively impacting their own lives and the
broader world.

You can also leave a testimonial at PowerSpreadsheets.com.

I:

• Read all reviews and testimonials; and

• Take them into consideration when creating or updating books (such as this
one) or the rest of the training materials at Power Spreadsheets.

By leaving a review or testimonial, you help me serve you better.

Thanks again for your support. I wish you much success with Excel, macros and
VBA. Until next time,

46