LEARNING UNIT 4UNIT 4
LEARNING
CODING SOLUTIONS
CODING SOLUTIONS BY USING VBA
BY USING VBA
4.1 INTRODUCTION, OUTCOME AND ASSESSMENT CRITERIA
4.2 VARIABLES
4.3 DETERMINING THE LAST ROW BY USING VBA
4.4 SELECTION OF PROGRAMMING STRUCTURES
4.5 LOOP PROGRAMMING STRUCTURES
4.6 FUNCTION PROCEDURES
4.7 BRINGING IT ALL TOGETHER
4.8 CLOSING REMARKS
4.9 REFERENCES
1|P a g e
,4.1 INTRODUCTION, OUTCOME AND ASSESSMENT CRITERIA
In AIN2601, you were introduced to the macro-recorder, the VBE and coding with VBA. In learning
unit 3, we revised important aspects of the Excel object (assigning values, manipulating objects,
copy/paste ranges) but also learned how to add comments to code and link your VBA code to
buttons and icons.
You will agree that the above would offer you some automation in the workplace but would not be
sufficient to cover more advanced automation tasks. The purpose of this learning unit is to expand
your VBA knowledge to assist you in automating most repetitive tasks. This will include some
programming concepts, but you will see that these concepts are not that hard to master.
At the end of this learning unit, you will be able to write your own code or evaluate someone else’s
code to solve or automate a business problem by using procedural, selective, and looping
programming structures or by combining them.
We will kick off our discussion by exploring a programming concept called variables.
4.2 VARIABLES
A variable is a named storage location in your computer’s memory that’s used by a program
(Alexander & Walkenbach, 2019:95). Let’s make it applicable. Remember, at school you did a basic
algebra formula like y = x + 2. The famous words, solve for y. Now let’s say x = 8, what is the value of
y? You will agree we can rewrite the above formula to y = 8 + 2. Therefore, y = 10.
In the above equation, x and y are variables. They are placeholders for values.
When you look at this formula (y = x + 2) your brain will immediately recognise that there are values
to be reserved for x and y. Similarly, a computer will reserve space in its memory for the variables (x
and y). The only difference between your brain and the computer’s memory is that you need to tell
the computer to reserve its memory for these variables.
Remember that a computer is not as smart as a human, and we need to specify what data we want
to store in these variables. We refer to these as data types. There are 19 data types, but it is not
necessary to cover all 19 in this module. Please read the following article by Microsoft (2022) by
clicking here. Focus on the section “Set intrinsic data types”. For additional guidance on variables,
you can also work through the article by CFI Team (2022) by clicking here.
You will see a table with data type, storage size and range. Data type is self-explanatory, and storage
size is the size of memory that this variable will take up. Range refers to the values that can be
stored in this variable. The most used variables are the following:
2|P a g e
, • Boolean (used to store binary results, for example true/false or 1/0)
• Date (stores date values)
• Single (used to store number values that may take on a decimal form. Can also contain
integers.)
• Double (a longer form of the single variable. Takes up more space, but needed for larger
numbers.)
• Integer (used to store number values that won’t take decimal form)
• Object (used when working with an object)
• String (used to store text. Can contain numbers but will store them as text. Note that
calculations cannot be performed on numbers stored as a string.)
The other 12 variables are not within the scope of this module.
It is important to understand the characteristics of your variable before you assign a data class to it.
If you have an invoice price and want to store it to the variable Inv_Price, does it make sense to
assign the Integer data class to it?
If you are sure that the invoice price will never exceed 32 767 then it is fine, but if the invoice value
is higher than 32 767 then you will get an error when executing your VBA.
Now you may think, why do we not always assign Double to all variables that need to store
numbers?
Look again at the table. You will see a storage size column. This indicates how many bytes are used
for each variable. A Double use 8 bytes versus the 2 bytes of the integer. The general rule of thumb
is to use the variable data type that will use the least number of bytes. The more bytes your program
use, the slower it will execute.
Another aspect to consider is that Integer and Long do not contain decimals. If you want to use
percentages or decimals, it is better to declare your variable as a Double data type. Variables that
store text will be declared within the String data type. The Object data type refers to objects, for
example, workbooks. But we will cover this later in this learning unit.
The last data type I want to mention is the Variant data type. If you do not declare a data type, VBA
will assume that Variant is the data type. This data type can be string or numbers. As it uses a lot of
storage (refer to the storage size column) and is not advisable to use.
Let’s test if you understand the variable data types by completing activity 4.1:
3|P a g e
, Activity 4.1
Access activity 4.1 by clicking here.
End of activity
Now that you know what a variable is, and what data type to select for a variable, let’s look at how
to declare a variable in VBA. The syntax is:
Dim <variable_name> as <data type>
The variable name should be a descriptive name, describing the variable. Note that no spaces are
allowed for a variable name. If I want to declare a customer name variable, then the variable will be
declared as follows:
Dim customerName as String
You can also assign variables to objects, but this is a two-step process. First, you need to declare the
variable, for example:
Dim wsCustomer_Info as Worksheet
Secondly, once you have declared the object, you need to assign a value to the object. This is done
with the Set statement. For example:
Set wsCustomer_Info = Worksheets(“Sheet1”)
The below example from Automate Excel (www.automateexcel.com) provides a nice example of
how to use the object variable to rename sheets in a workbook. In this workbook, Sheet1 is renamed
to Customers and Sheet2 to Products. If you want to execute this code, just make sure you add a
Sheet2 to your Excel workbook, otherwise you will get an error.
Figure 4.1 Worksheet object example (https://www.automateexcel.com/vba/set-object-variables/)
4|P a g e
LEARNING
CODING SOLUTIONS
CODING SOLUTIONS BY USING VBA
BY USING VBA
4.1 INTRODUCTION, OUTCOME AND ASSESSMENT CRITERIA
4.2 VARIABLES
4.3 DETERMINING THE LAST ROW BY USING VBA
4.4 SELECTION OF PROGRAMMING STRUCTURES
4.5 LOOP PROGRAMMING STRUCTURES
4.6 FUNCTION PROCEDURES
4.7 BRINGING IT ALL TOGETHER
4.8 CLOSING REMARKS
4.9 REFERENCES
1|P a g e
,4.1 INTRODUCTION, OUTCOME AND ASSESSMENT CRITERIA
In AIN2601, you were introduced to the macro-recorder, the VBE and coding with VBA. In learning
unit 3, we revised important aspects of the Excel object (assigning values, manipulating objects,
copy/paste ranges) but also learned how to add comments to code and link your VBA code to
buttons and icons.
You will agree that the above would offer you some automation in the workplace but would not be
sufficient to cover more advanced automation tasks. The purpose of this learning unit is to expand
your VBA knowledge to assist you in automating most repetitive tasks. This will include some
programming concepts, but you will see that these concepts are not that hard to master.
At the end of this learning unit, you will be able to write your own code or evaluate someone else’s
code to solve or automate a business problem by using procedural, selective, and looping
programming structures or by combining them.
We will kick off our discussion by exploring a programming concept called variables.
4.2 VARIABLES
A variable is a named storage location in your computer’s memory that’s used by a program
(Alexander & Walkenbach, 2019:95). Let’s make it applicable. Remember, at school you did a basic
algebra formula like y = x + 2. The famous words, solve for y. Now let’s say x = 8, what is the value of
y? You will agree we can rewrite the above formula to y = 8 + 2. Therefore, y = 10.
In the above equation, x and y are variables. They are placeholders for values.
When you look at this formula (y = x + 2) your brain will immediately recognise that there are values
to be reserved for x and y. Similarly, a computer will reserve space in its memory for the variables (x
and y). The only difference between your brain and the computer’s memory is that you need to tell
the computer to reserve its memory for these variables.
Remember that a computer is not as smart as a human, and we need to specify what data we want
to store in these variables. We refer to these as data types. There are 19 data types, but it is not
necessary to cover all 19 in this module. Please read the following article by Microsoft (2022) by
clicking here. Focus on the section “Set intrinsic data types”. For additional guidance on variables,
you can also work through the article by CFI Team (2022) by clicking here.
You will see a table with data type, storage size and range. Data type is self-explanatory, and storage
size is the size of memory that this variable will take up. Range refers to the values that can be
stored in this variable. The most used variables are the following:
2|P a g e
, • Boolean (used to store binary results, for example true/false or 1/0)
• Date (stores date values)
• Single (used to store number values that may take on a decimal form. Can also contain
integers.)
• Double (a longer form of the single variable. Takes up more space, but needed for larger
numbers.)
• Integer (used to store number values that won’t take decimal form)
• Object (used when working with an object)
• String (used to store text. Can contain numbers but will store them as text. Note that
calculations cannot be performed on numbers stored as a string.)
The other 12 variables are not within the scope of this module.
It is important to understand the characteristics of your variable before you assign a data class to it.
If you have an invoice price and want to store it to the variable Inv_Price, does it make sense to
assign the Integer data class to it?
If you are sure that the invoice price will never exceed 32 767 then it is fine, but if the invoice value
is higher than 32 767 then you will get an error when executing your VBA.
Now you may think, why do we not always assign Double to all variables that need to store
numbers?
Look again at the table. You will see a storage size column. This indicates how many bytes are used
for each variable. A Double use 8 bytes versus the 2 bytes of the integer. The general rule of thumb
is to use the variable data type that will use the least number of bytes. The more bytes your program
use, the slower it will execute.
Another aspect to consider is that Integer and Long do not contain decimals. If you want to use
percentages or decimals, it is better to declare your variable as a Double data type. Variables that
store text will be declared within the String data type. The Object data type refers to objects, for
example, workbooks. But we will cover this later in this learning unit.
The last data type I want to mention is the Variant data type. If you do not declare a data type, VBA
will assume that Variant is the data type. This data type can be string or numbers. As it uses a lot of
storage (refer to the storage size column) and is not advisable to use.
Let’s test if you understand the variable data types by completing activity 4.1:
3|P a g e
, Activity 4.1
Access activity 4.1 by clicking here.
End of activity
Now that you know what a variable is, and what data type to select for a variable, let’s look at how
to declare a variable in VBA. The syntax is:
Dim <variable_name> as <data type>
The variable name should be a descriptive name, describing the variable. Note that no spaces are
allowed for a variable name. If I want to declare a customer name variable, then the variable will be
declared as follows:
Dim customerName as String
You can also assign variables to objects, but this is a two-step process. First, you need to declare the
variable, for example:
Dim wsCustomer_Info as Worksheet
Secondly, once you have declared the object, you need to assign a value to the object. This is done
with the Set statement. For example:
Set wsCustomer_Info = Worksheets(“Sheet1”)
The below example from Automate Excel (www.automateexcel.com) provides a nice example of
how to use the object variable to rename sheets in a workbook. In this workbook, Sheet1 is renamed
to Customers and Sheet2 to Products. If you want to execute this code, just make sure you add a
Sheet2 to your Excel workbook, otherwise you will get an error.
Figure 4.1 Worksheet object example (https://www.automateexcel.com/vba/set-object-variables/)
4|P a g e
