In the last chapter “Excel VBA Basics”, we learned some basic concepts and operations. In some tutorials, Some specific VBA code would appears, which you may not understand. But it doesn’t matter. From the beginning of this chapter, we will learn the grammar part of VBA specifically, which will take you into the real VBA world and let you write code that can really be used.
In this article, we will learn one of the most basic concepts of VBA, VBA variables.
What Are VBA Variables?
A VBA variable is a VBA code structure that stores data. The data stored in the variable can be changed during the running of the program. It can also represent the data stored in it and participate in various calculations in the program.
Variables can be simply understood as the cell reference address in Excel, such as A1. In the formula, A1 represents the data in cell A1 and participates in the calculation.
Variables have two important concepts, variable name and variable type.
- Variable name: represents the name of the variable
- Variable type: the type of data stored in the variable, such as numbers, text, logical values, etc.
Dim Statement
If you want to use a variable in the code, you need to declare it first, that is, give the variable name and variable type.
Use the following syntax to declare variables:
Dim [variable name] As [data type]
Dim and As are keywords that need to be used to declare variables in VBA. Dim represents the beginning of the declaration statement, and As represents the data type specified after it.[Variable name] and [data type] are variable parts in the declaration code and require user input.
- [Variable name] is the name of the variable
- [Data Type] is the data type of the variable
For example, Now we declare two variables, one is the employee’s name and the other is the employee’s age.
Dim name As String
Dim age As Integer
As you can see, name and age are the names of variables, String is text type data, representing the employee name; Integer is an integer data type, representing the employee’s age.
Naming Rules
In the above part, we have learned the basic statements for declaring variables, where the variable name needs to be input by the user. VBA stipulates that variable naming must meet the following conditions, otherwise the program will go wrong.
- The first letter must start with a letter.
- Cannot contain spaces,. (Period),! (Exclamation mark), @, &, $, # and other characters.
- The length cannot exceed 255 characters.
- You cannot use keywords saved in VBA as variable names.
The above are the rules that variable names must comply with. Under the premise of complying with the rules, although you can name it arbitrarily, in the actual development process, it is recommended to name it according to the Camel-Case naming method.
The Camel-Case naming method is a common naming rule for many programming languages. It has the advantages of high readability and easy understanding.
Camel-Case is when the variable name or function name is composed of one or more words connected together to form a unique identification word, the first word starts with a lowercase letter; each word after the second word Use capital letters for the first letter.
For example: myFirstName, myLastName, such variable names look like camel peaks.
Variable Type
The second important element of a variable is the variable type, which is the data type in which the variable stores data. Specifying the data type correctly can make the program writing and running more efficient and easy to understand.
In addition, after specifying the data type, if you assign different types of data, VBA will prompt an error, so you can avoid using the wrong data.
The data types in VBA are mainly divided into three categories, one is a number type, one is a non-number type, and the other is a general type.
Number Type
Different types have different data ranges.
Non-numeric Type
Non-numeric variables usually cannot directly participate in arithmetic operations.
General Type
General data type refers to the storage of any type of data. During the running of the program, VBA can automatically identify data types and participate in calculations.
Assign Values to Variables
After learning the basics of variables, the next step is how to assign values to variables.
Assigning values to variables is very simple, using the following syntax: [Variable Name] = [Data]
The core of the assignment syntax is “=” (equal sign), with variables on the left and data on the right. It can be understood that the variable is equal to the assigned data.
One point worth noting is the form of [data].
After the variable assignment is completed, you can use the variable to participate in various calculations in the subsequent program.
Let’s look at a specific example.
In the above code, the name variable is declared first, and the data type is text. Then assign “Marry” text to it. Finally, write the data of the name variable into cell A1 of the worksheet.
To Sum up
This article mainly introduces the most basic concepts in VBA, variables. Because the value of the variable can change during the running of the program, the efficiency of the program is greatly improved. There are three main steps to using variables:
- Dim Statement
- Specify the data type of the variable
- Assign values to variables
Leave a Reply