When learning VBA, we often encounter the requirement to declare variables. So what is a variable? Why do we have to declare variables? How to declare variables in VBA correctly?… Let’s find the answers to these questions.
Nội dung
1. What is a variable and why do we need to declare it?
Variables are places to store data set by users according to available conventions, used in the VBA environment to help the program identify, understand, and manipulate this data correctly.
Why do we need to declare variables?
When we want to put data into a macro (programming command in VBA), we have to go through variables. There are many types of variables corresponding to different data types. Declaring variables helps VBA work more accurately with the data type of the variable, saving resources, computer memory, and program capacity.
In the VBA environment, there are many objects. Objects have their own methods and properties. All are represented in the form of characters. Therefore, when we need to declare to distinguish variables from other objects. This helps avoid conflicts that may occur when running commands, and at the same time helps programmers better manage their commands.
2. How to declare variables in VBA
Variable declarations typically include the following requirements:
- Variable name : for identification, use
- Variable data type : to determine the structure of the data, the memory range to use for the variable
- Scope of operation : used in all modules or only in 1 module, or only in 1 sub. This often affects when wanting to use variables with the same name but different values, or same name but same value. This is also the keyword to start declaring variables.
Structure of a variable declaration statement:
[ Scope of operation ] + [ space ] + [ Variable name ] + [ space ] + [ as ] + [ space ] + [ Variable data type ]
For example:
Dim i As Integer
- Dim: The scope of a variable is local to the place where the declaration is placed.
- i: variable name is letter “ i”
- As Integer: the variable type is Integer, in which the keyword “As” means “ assign to ”, “ is ”
3. Global variables and local variables
These concepts are related to defining the scope of a variable. When declaring a variable, we use the keywords that declare the scope and combine them with the location of the declaration command. Specifically as follows:
- Global variables : Can be shared in many different Sub/Functions and only need to be declared once. Global variables can be for the entire Workbook or only for a certain Project. This variable is not released when a sub/function ends .
- Local variable: Only used in 1 Sub / Function. When you want to use this variable again , you must declare it again. This variable is released when the Sub/Function ends.
To declare variables as global or local, we use the following keywords:
Keyword Public
- When declaring a variable with the Public keyword, that variable can be used anywhere in the Workbook.
- It is a global variable when considered in the entire VBA Project environment.
- When declaring variables in the Public scope, they are usually declared at the beginning of a module (or worksheet, userform…), outside the scope of the Sub.
For example: Public DongCuoi As Long = Declare variable DongCuoi as Long type, global scope
Private Keyword
- It is a global variable when considered at the declaration location, but considered in the entire VBA Project environment, it is a local variable.
- When declaring a variable as Private inside a Sub or Function, it is a local variable. When declaring a variable outside a Sub/Function and at the top of a Module (or in a Userform/Class module/Worksheet/workbook), it is a global variable in the Project containing that declaration.
Example: Private today As Date = Declare variable today as Date type
Dim Keyword
- Is the most common and popular boundary declaration keyword.
- When declaring a variable using Dim inside a Sub or Function, it is a local variable. When declaring a variable outside a Sub/Function and at the top of a Module (or in a Userform/Class module/Worksheet/workbook), it is a global variable in the Project containing that declaration.
Example: Dim i As Integer
Static Keywords
- Used to declare variables inside a sub / function
- Used to declare local variables. Not applicable to global variables.
Example: Static i as Long.
4. Variable types in VBA programming
In the variable declaration syntax that we have guided you, there is a component called Object variable type and Value variable type. We will attach object variables to existing objects such as sheets, workbooks, shapes, etc. on the Excel file. As for value variables, we will attach them to values such as Long, Integer, Double, etc.
For data value types in VBA, please refer to the table below:
| Value type | Data type | Value Range (Simple Understanding) |
| Bytes | Number | Integer from 0 to 255 |
| Integer | Number | Integers from -32,768 to 32,767 |
| Long | Number | Integers from -2.1 billion to 2.1 billion |
| Single | Number | Bigger than Long but takes up the same amount of memory as Long |
| Double | Number | 100 times larger than Single but takes up 2 times more memory |
| Currency | Number | Same as Double but smaller value, has decimal part |
| Decimal | Number | Like Double, has large decimal part, takes up memory |
| Boolean | Number | True or False |
| Date | Number | All days |
| Object | Number | Any object |
| String | Number | Any character, up to 65k characters long |
| Variant | Any | VBA automatically recognizes data types but takes more memory |
To set this mode for all projects in VBA, click Tools in the VBA window and select Options. Next, click the Editor tab in the Options dialog box and check the Require Variable Declaration box. Finally, click OK.
When this mode is active, if you do not declare a variable, the macro will not work.
In the above article, we have learned about variables and how to declare variables in VBA. Hopefully, this article has provided useful knowledge to help you get acquainted with VBA programming more easily. If you want to learn both Excel and VBA knowledge, please register for the Excel and VBA learning program combo. You can refer to more at: Declaring variables (VBA). Good luck!