LEARNING UNIT 3
INTRODUCTION TO VISUAL BASIC FOR APPLICATIONS (VBA)
3.1 INTRODUCTION, OUTCOME AND ASSESSMENT CRITERIA
3.2 WHY PROGRAMMING LANGUAGES?
3.3 TYPES OF PROGRAMMING STRUCTURES
3.4 THE MACRO RECORDER VS MANUALLY GENERATING VBA CODE
3.5 VALUABLE CODING ADVICE
3.6 ASSIGNING VALUES BY USING VBA
3.7 CHANGING CELL/RANGE FORMATTING PROPERTIES
3.8 EXPLORING MORE PROPERTIES AND ACTIONS IN THE EXCEL OBJECT
3.9 ADDING COMMENTS TO YOUR VBA CODE
3.10 COPYING AND PASTING ACTIONS IN VBA
3.11 ASSIGNING VBA TO A BUTTON OR ICON
3.12 CLOSING REMARKS
3.13 REFERENCES
1|P a g e
,3.1 INTRODUCTION, OUTCOME AND ASSESSMENT CRITERIA
In learning unit 1, you learnt to apply computational thinking to business problems. Learning unit 2
covered a more theoretical approach to automation by making use of RPA and robotics.
The purpose of learning unit 3 and learning unit 4 is to teach you the skill to build a command that
automates a process using Microsoft Visual Basic for Applications (VBA). This will involve writing but
also evaluating an algorithm by making use of VBA.
In AIN2601, you learnt how to use the macro recorder and you also started to write basic VBA code
in the Visual Basic Editor (VBE) using the Excel object. This learning unit will build on the knowledge
you obtained in AIN2601. Therefore, it is strongly recommended that you revise your AIN2601
learning units on these aspects before commencing with this learning unit.
It is important to reiterate that VBA can assist you in completing a boring routine task much faster
and without any errors. This will enable you to focus more on the exciting stuff in your work
environment.
But let’s dig in by asking: why programming languages?
3.2 WHY PROGRAMMING LANGUAGES?
Figure 3.1: Microsoft Office 365 Stock Images
Fac mihi aliquid capulus, quaeso.
Do you understand what I am saying?
I think most of you do not know what I am instructing you to do here �.
Now let’s run this through a machine translator:
2|P a g e
, “Fac mihi aliquid capulus, quaeso” is Latin and means “Make me some coffee, please”. Now you
understand the instruction given and will perform the task by making me some coffee.
But what did you need to perform this task? You had the instruction in Latin, a Latin translator and
the instruction given in a language you could understand, which was English.
Our communication with computers is the same. Computers communicate in binary code (a
language consisting of 0s and 1s). Let’s illustrate this: a computer wants to communicate to you and
displays the following: 01001000 01100101 01101100 01101100 01101111 00100001. According to
Zych (2015), the computer is telling you “Hello”. If you want to learn more about binary code, you
can read the article by Zych by clicking here. Note that this article is not part of the syllabus and
therefore not examinable.
Just like in the case with the Latin phrase, we need interpreters to interpret instructions and
communications between us and computers. Programming languages act as these interpreters. So, if
I tell a programming language to give a computer an instruction to copy a range of cells in a sheet of
a spreadsheet and to copy it to another workbook, the computer will comply. Luckily, I do not have
to communicate this directly to the computer using 0s and 1s, as you get different interpreters,
which in this case are your programming languages. The problem is that each programming language
has its own rules (or syntax). We need to understand the rules so that we can get the computing
results we want. Essentially, for us to communicate efficiently with a computer we need to know the
programming language. There are different programming languages on the market. Popular
languages are Java, R, Python, C, C++ and Visual Basic for Applications (VBA). As you were introduced
to a Microsoft-based solution called Microsoft Excel (Excel) in AIN1501 and AIN2601, we will teach
you to create a solution using VBA.
Note that VBA is not available on the web-version of Microsoft Excel. You will need to use the
desktop version of Microsoft Excel 365 to work with VBA.
Next, we are going to explore the different types of programming structures.
3.3 DIFFERENT TYPES OF PROGRAMMING STRUCTURES
In learning unit 1, you learnt about algorithms. Most programming languages make use of
sequences, selections, and loops. It is important to understand when and how to make use of these
different coding methods as you are going to use them when writing the code for VBA. Watch the
video by GCFGlobal (LearnFree.org) that illustrates these concepts by clicking here.
3|P a g e
INTRODUCTION TO VISUAL BASIC FOR APPLICATIONS (VBA)
3.1 INTRODUCTION, OUTCOME AND ASSESSMENT CRITERIA
3.2 WHY PROGRAMMING LANGUAGES?
3.3 TYPES OF PROGRAMMING STRUCTURES
3.4 THE MACRO RECORDER VS MANUALLY GENERATING VBA CODE
3.5 VALUABLE CODING ADVICE
3.6 ASSIGNING VALUES BY USING VBA
3.7 CHANGING CELL/RANGE FORMATTING PROPERTIES
3.8 EXPLORING MORE PROPERTIES AND ACTIONS IN THE EXCEL OBJECT
3.9 ADDING COMMENTS TO YOUR VBA CODE
3.10 COPYING AND PASTING ACTIONS IN VBA
3.11 ASSIGNING VBA TO A BUTTON OR ICON
3.12 CLOSING REMARKS
3.13 REFERENCES
1|P a g e
,3.1 INTRODUCTION, OUTCOME AND ASSESSMENT CRITERIA
In learning unit 1, you learnt to apply computational thinking to business problems. Learning unit 2
covered a more theoretical approach to automation by making use of RPA and robotics.
The purpose of learning unit 3 and learning unit 4 is to teach you the skill to build a command that
automates a process using Microsoft Visual Basic for Applications (VBA). This will involve writing but
also evaluating an algorithm by making use of VBA.
In AIN2601, you learnt how to use the macro recorder and you also started to write basic VBA code
in the Visual Basic Editor (VBE) using the Excel object. This learning unit will build on the knowledge
you obtained in AIN2601. Therefore, it is strongly recommended that you revise your AIN2601
learning units on these aspects before commencing with this learning unit.
It is important to reiterate that VBA can assist you in completing a boring routine task much faster
and without any errors. This will enable you to focus more on the exciting stuff in your work
environment.
But let’s dig in by asking: why programming languages?
3.2 WHY PROGRAMMING LANGUAGES?
Figure 3.1: Microsoft Office 365 Stock Images
Fac mihi aliquid capulus, quaeso.
Do you understand what I am saying?
I think most of you do not know what I am instructing you to do here �.
Now let’s run this through a machine translator:
2|P a g e
, “Fac mihi aliquid capulus, quaeso” is Latin and means “Make me some coffee, please”. Now you
understand the instruction given and will perform the task by making me some coffee.
But what did you need to perform this task? You had the instruction in Latin, a Latin translator and
the instruction given in a language you could understand, which was English.
Our communication with computers is the same. Computers communicate in binary code (a
language consisting of 0s and 1s). Let’s illustrate this: a computer wants to communicate to you and
displays the following: 01001000 01100101 01101100 01101100 01101111 00100001. According to
Zych (2015), the computer is telling you “Hello”. If you want to learn more about binary code, you
can read the article by Zych by clicking here. Note that this article is not part of the syllabus and
therefore not examinable.
Just like in the case with the Latin phrase, we need interpreters to interpret instructions and
communications between us and computers. Programming languages act as these interpreters. So, if
I tell a programming language to give a computer an instruction to copy a range of cells in a sheet of
a spreadsheet and to copy it to another workbook, the computer will comply. Luckily, I do not have
to communicate this directly to the computer using 0s and 1s, as you get different interpreters,
which in this case are your programming languages. The problem is that each programming language
has its own rules (or syntax). We need to understand the rules so that we can get the computing
results we want. Essentially, for us to communicate efficiently with a computer we need to know the
programming language. There are different programming languages on the market. Popular
languages are Java, R, Python, C, C++ and Visual Basic for Applications (VBA). As you were introduced
to a Microsoft-based solution called Microsoft Excel (Excel) in AIN1501 and AIN2601, we will teach
you to create a solution using VBA.
Note that VBA is not available on the web-version of Microsoft Excel. You will need to use the
desktop version of Microsoft Excel 365 to work with VBA.
Next, we are going to explore the different types of programming structures.
3.3 DIFFERENT TYPES OF PROGRAMMING STRUCTURES
In learning unit 1, you learnt about algorithms. Most programming languages make use of
sequences, selections, and loops. It is important to understand when and how to make use of these
different coding methods as you are going to use them when writing the code for VBA. Watch the
video by GCFGlobal (LearnFree.org) that illustrates these concepts by clicking here.
3|P a g e
