VBA intermediate class Lesson 1 Summary

We finally started the long-awaited VBA intermediate class! We are honored to be the first student in the intermediate class. Now let's start a new VBA journey!

1. variable naming rules 1.1 Variables

Some data changes during program execution. Therefore, we allocate a memory location to store the uncertain data, which is a variable. Each variable has a variable name and must be unique within the same range. The data type can be specified or not.

1.2 variable naming rules

The variable name must start with a letter (the Chinese version can start with Chinese) and consist of letters, numbers, and underscores (Chinese can be used in the Chinese version). It cannot exceed 255 characters (all versions are the same ). Note that the variable name cannot be a reserved name of the system, such as if, else, and end.

• Variable name in VBACase Insensitive

2 declare Variables

Declaring a variable is to determine the variable name and specify the data type for the variable. The definitions can be divided into explicit and implicit declarations.

2.1 explicit declaration

Declare the variable type before referencing the variable. Syntax:

Public | static | private | dim <Variable name> [Data Type]

Variable name: Used to reference Variables

Data Type: Determine the type and memory occupied by the variable storage Information

• [Data Type] Can be omitted. When omitted, VBA automatically specifies the variable as the variant data type ).

2.1.1 type declaration characters

Some characters can be used in VBA to represent the data type and simplify the declaration of variables. This is the type declaration character. Not all data types have the type declaration character, the following table lists all types of declarative characters:

Data Type	Type Declaration character
String type)	$
Integer)	%
Long (long)	&
Single Precision Floating Point (single)	!
Double)	#
Currency (currency)	@

Example:

Dim irow & 'declares the long integer variable irow

2.1.2 declare multiple variables at the same time

When declaring variables, if there are multiple variables, we can use commas to separate different variables:

Dim <Variable name 1> [Data Type], <Variable name 2> [Data Type],……

• Each variable must specify the data type. Omitting the data type is equivalent to specifying it as a variant.

For example, dim I, j As long 'indicates that I is of the variant type, while J is of the long type.

2.2 implicit Declaration

That is, the variable is not declared beforehand before the process references the variable. In this case, VBA automatically assigns the variable to the variant data type. Because the variant type has the widest range and the largest memory capacity, it can store all data types except the fixed-length string type and user-defined type.

• Because the variant type occupies a large amount of memory, it will drag down the running speed of the program. Therefore, we should try to use explicit declarations to specify a suitable data type for variables, instead of using the variant type too much.
• Note:Only local variables referenced during the process can be implicitly declared. Public module level, private module level, and static variables are not implicitly declared.. The specific content will be discussed in the variable scope.

2.3 force variable Declaration

Two methods can force the explicit declaration of Variables

1) write data in the first line of a moduleOption explicitStatement.

2) The VBE window "tool" option "requires variable Declaration

 

• After "require variable Declaration" is set, VBA willThe option explicit statement is automatically generated in the new module (including the worksheet module, form module, standard module, and class module), but is not automatically generated in the existing module..
• Option explicitStatement onlyIt plays a role in the module where it is located!

3. Scope of the variable and the scope of the lifecycle 3.1 variable

The scope of a variable is also the scope of the variable. The scope of the variable is also defined when the variable is declared.

Level	Keywords	Statement declaration position	Variable Scope
Process-level variables (Local variable)	Dim	Statement in process	This variable can be accessed and changed only during this process.
	Static	Statement in processStatic variables
Private module-level variables (Module-level variables)	Private dim	Declare in the Declaration area at the top of the module	All processes in this module can access and change this variable
Common module-level variables (Global variable)	Public	Declare in the Declaration area at the top of the module	This variable can be accessed and changed in any process in any module of the project.

• At the module level, the dim statement is the same as the private statement. However, using private statements can make it easier to read and interpret code.

3.2 static variables

When a dim statement is replaced by a static statement, the declared variable is called a static variable. Static variables can only be local variables and can only be declared during the process.

3.3 variable lifetime

In addition to a range, a variable also has a life cycle. Its life cycle is consistent with its range, and the variable value will be lost after the range disappears.

The local variable declared by the dim statement. The variable value is released after the process is completed. Before executing the process again, the variable value is re-initialized. After the static variable is completed, as long as the entire program is still running, its value can be retained without being reinitialized. After all the code is run, the static variable will lose its range and lifetime.

The lifetime of a module-level variable and a public-level variable is the same as that of a static variable. the lifetime of a module-level variable is lost only after the end of the entire program.

3.4 variable reference

1) The variables at the public module level in different modules have the same name.

Module name. variable nameMethod reference.

2) In the same module, variables at the public module level have the same name as those at the process level.Module name. variable nameMethod reference, while calling a process-level variable directly calls the variable name.

3) when other modules reference public-level variables in the form module, Worksheet module, and workbook moduleModule name. variable nameMethod reference

• Tip: View variables or processes

When you want to view the variables or other processes referenced in the process, you can select this variable or process, right-click it, and select "Define ", will automatically jump to this variable or process definition, this is convenient and fast. Right-click and select "last position" to return the last cursor position.

4. Summary

In fact, variables are easy to understand and think of as a container. Declaring variables is to determine the container shape and capacity, and the variable name is to post a special name to the container, the data type determines whether the container is filled with liquid, solid, or gas. The variable value is what is put in it. So remember that the variable is the storage location occupied in the memory.

This lesson focuses on the scope of variables and the lifecycle of variables. The scope determines the lifetime. The scope and lifetime of the variable determine the reference of the program to the variable value.

Excel Home Technology Forum

Excel home free online training center