------------------
VBA syntax BASICS (I)
------------------
Data Type
"Data Type" refers to how to store data in the memory.
(1) Boolean
Logical Data. It can be either true or false. The storage space occupies 2 bytes. The value range is true or false. The default value is false.
(2) byte
It can only represent positive numbers. 1-byte storage space. The value range is 0-255. The default value is 0.
(3) Currency
A special numeric format that stores currency value data. It occupies 8 bytes of storage space. The value range is-922337203685477.5808-922337203685477.5807. The default value is 0.
(4) Date
A special format used to represent a date or time. It occupies 8 bytes of storage space. The value range is from January 1, January 1, 100 to January 1, December 31, 9999. The default value is 00:00:00. (For details about how to use date and time in VBA, see a series of articles later .)
(5) decimal
A subtype of a decimal number that contains a scale of 10 power. It can only be created through the cdec conversion function, not an independent data type. 14 bytes of storage space. The value range is ± 79228162514264337593543950335 (without the decimal point) or ± 7. 9228162514264337593543950335 (with 28 decimal points). The default value is 0.
(Decimal data type is introduced in Excel 2000 and cannot be used in earlier versions. This data type is very special because it cannot be actually declared. It is a child type of variant and must be converted to a decimal data type using the cdec function)
(6) double
Stores double-precision floating-point numbers, which occupy 8 bytes of storage space. The value range is negative:-1.79769313486232e308 -- 4.94065645841247e-324. The positive value is 127-- 4.94065645841247e-324.
(7) Integer
Represents an integer between-32768-32767, one of which represents a symbol and occupies 2 bytes of storage space. The default value is 0.
(8) Long
Represents the number of characters in four bytes, one of which represents the number of characters. The value range is-2147483648-2147483647, and the default value is 0.
(9) object
Contains a reference (Address) to an object. It occupies 4 bytes of storage space and can be referenced to any object. The default value is "nothing.
(10) single
It indicates the number of single-Precision values, such as scores, decimal places, or exponent. It occupies 4 bytes of storage space. The value range is negative:-3.402823e38 -- 1.401298e-45, positive: 1.401298e-45--3.402823e38, the default value is 0.
(11) String
Specifies the length and length of the string data type. The storage space occupied by the fixed-length string data type is the length of the string. The value range is 1-characters. The default value is equal to the number of spaces in the string length. The variable-length string data type can dynamically lengthen or shorten the number of strings required for storage. The occupied storage space is 10 bytes plus the length of the string. The value range is 0-20 million characters, the default value is a zero-length string (""). (For details about the string data type and the VBA built-in functions for processing and operating string data, see a series of articles .)
(12) Variant
The storage space of the variant string type is 22 bytes plus the length of the string. The value range is the same as that of the variable-length string data type. The default value is empty.
The storage space of the variant numeric type is 16 bytes. Its value range is the same as that of the double data type. The default value is empty.
(For more information about variant data types, see a series of articles later .)
(13) user-defined types
Allows users to create a special data type, which is composed of internal data types, arrays, objects, or other user-defined types of VBA, the bucket is the sum of the buckets of each component. The value range is the same as the value range of the Data Type of each component. The default value is the default value of each component. (For more information about user-defined types, see the following articles .)
Data Types can also be converted to each other. (For more information about data type conversion, see a series of articles)
(For details about the numeric data types (byte, integer, long, single, double, currency, decimal, and variant) and corresponding VBA built-in functions, see the following series of articles .)
------------------
Constant (number)
A constant is a value or string that does not change during program execution.
Use the const statement to declare constants. For example:
Const rate = 0.25
Const nummonths as integer = 12
Public const myname as string = "babypig"
The last statement declares a public constant and should be declared before all processes in the module.
VBA itself contains many built-in constants whose values are pre-defined by VBA and do not need to be defined when internal constants are used.
■ Several special Constants
Because there are several different "invalid value" constants, VBA provides several methods to check whether a variable is empty or null, or set a variable to empty or null.
(1) vbnull
Used with the vartype function to determine whether the variable contains null.
(2) vbnullchar
Assign values or detect null characters. The null character value is CHR (0). That is, the constant vbnullchar is equivalent to assigning a variable to CHR (0) and can be used to detect variables, determines whether the value is a null character.
(3) vbnullstring
Assign values or detect zero-length (null) strings.
(4) null keyword
After the null value is assigned to the variant variable, you can call the isnull function to check whether the variable is a null value.
(5) vbempty
Checks whether a variant variable is initialized.
(6) Nothing keywords
It can only be used with object variables to determine whether a variable has a valid object reference. In addition, the nothing keyword can be used to destroy the current object reference.
(The Introduction to constants is also visible.
Data Types of excelvba programming series (1): Constants)
------------------
Variable
Variables are mainly used to access data and provide containers for storing data information. According to different scopes of variables, they can be divided into local variables and global variables. For details, see the variable (constant) Scope and life cycle introduction.
Note the following when naming variables:
1. validity. A variable starts with a letter and contains numbers and some punctuation marks. Except for the underscore (_), the variable name cannot contain spaces, plus signs (+), or minus signs (-), comma (,), period (.).
2. VBA is case insensitive. However, when naming a variable, it is best to reflect the role of the variable.
3. Keywords in VBA cannot be used as variables.
4. Variable names cannot contain special types of declarative characters (#, $, %, &, or !).
5. The variable name can contain a maximum of 254 characters.
(For details about VBA naming rules, see a series of articles later)
------------------
Declare Variables
Its syntax is:
Dim <
Variable name> As <
Data Type>
Or: Private <
Variable name> As <
Data Type>
Or: Public <
Variable name> As <
Data Type>
Multiple variables can be declared in one row. Each variable is separated by a comma.
Another way to declare a variable is to add a character after the variable name to declare the Data Type of the variable. For example
Dim myvar %
Declares the variable myvar as an integer. Some types of specifiers are:
Data Type Declaration characters
Integer %
Long &
Single!
Double #
Currency @
String $
Add the option explicit statement before the module to forcibly declare all variables used.
------------------
Object variable
Before using the attributes, methods, and events of the object model, you must create a reference to the class containing the required attributes, methods, and events. You can declare a local object variable to store the object reference, and then assign the object reference to the local variable.
The method for declaring object variables is basically the same as that for declaring other types of variables. There are three methods to declare object variables:
(1) dim myobject as <
Database Name>. <
Class Name>
This method points to the Type Library of the class, but does not assign the variable to any class instance. In this case, the variable myobject is assigned a value of nothing. To reference a class in this way, you must use the reference dialog box to add a reference to the class module to the project. To assign a class instance reference to this variable, you must assign a value using the set statement before using this variable. For example:
Set myobject = <
Database Name>. <
Class Name>
(2) dim myobject as new <
Database Name>. <
Class Name>
This method assigns a new instance of the class to the object variable. Similarly, to reference a class in this way, you must first Add a reference to the class module to the project using the "Reference" dialog box.
(3) dim myobject as object
This method declares the myobject variable as a general object data type, which is useful when you do not know the Data Type of the object to be created in advance. In this case, the object variable is assigned a value of nothing. To assign an object reference to this variable, you must use the Createobject function or the GetObject function.
You can replace dim statements with private or public statements, and the scope rules of object variables are the same as those of other types of variables.
Declaring object variables can significantly simplify code and speed up code execution.
For basic knowledge about object models, see
Excelvba programming series Object Model (2): preliminary understanding and use of Excel Object ModelArticle.
------------------
Collection object
A collection object is a container of other objects.
Generally, there are four methods:
(1) Add Method
Add an item to the collection. In addition to specifying data, you can also specify key values to access members in the set through key values.
(2) Count Method
Returns the number of items in the set.
(3) item method
You can use the index (the sequence number of the items in the set) or key (assuming this item is specified when added to the Set) in the set to retrieve the members in the set.
(4) Remove Method
Deletes a Member from a set by using the index or key in the set.
You can use... End with constructor and for each... The next structure can easily process objects and sets. For more information, see
Excelvba programming series Object Model (2): preliminary understanding and use of Excel Object ModelArticle.
------------------
Operator
Operators are a series of symbols used to complete operations, including Arithmetic Operators, comparison operators, logical operators, and string operators. It can be used to connect one or more language elements, or to complete some operations to form an expression.
------------------
Expression
An expression is a set of variables, constants, and operators. It can be divided into arithmetic expressions, string expressions, value assignment expressions, and boolean expressions.
For more information, see
VBA programming series: operators and expressions.
------------------
Array
An array is a group of elements with the same name. After the array is defined, an array is created. A single data item in an array is called an array element. The number used to access an array element is called an array index number, and the minimum index number and the maximum index number are called boundaries.
In VBA, array elements are divided into fixed-size arrays and dynamic arrays based on their variation. The dimension can be divided into one-dimensional arrays and multi-dimensional arrays.
1. Create an array
The dim statement is used to define an array of fixed sizes, that is, to declare an array. For example
Dim myarray (9) as integer
The code above creates a one-dimensional array named myarray containing 10 array elements. Note that the lower bound of all VBA arrays starts from 0, so the array elements created by the code above start from myarray (0) to myarray (9 ).
The dim statement does not specify the number of array elements to declare a dynamic array, as shown in
Dim mydynamicarray () as integer
Use the redim keyword to redefine the array size:
Redim mydynamicarray (10)
You can also use the redim keyword to declare a dynamic array and specify the number of elements in the array:
Redim mydynamicarray (5) as integer
VBA does not limit the number of times to redefine the dynamic array size, but when the array size is redefined, the original array data will be lost. To retain the original data, you can use the preserve Keyword:
Redim preserve mydynamicarray (5)
It should be noted that if the array size is reduced when the array is redefined, the data of the reduced element will be lost.
Of course, like declaring variables, you can also declare public arrays using public statements.
2. Determine the boundary of the array
You can use the ubound function and the lbound function to obtain the maximum and minimum boundary of the array.
By default, the lower bound of the VBA array starts from 0. You can use the option base statement in the declaration part of the module to change the starting boundary of the array in the module. For example
Option base 1
This statement starts the index number of the array element from 1.
You can also specify the upper and lower bounds of an array when defining an array, as shown in figure
Dim <
Array name> (<
Lower Bound> To <
Upper Bound>) As <
Data Type>
3. Multi-dimensional array
Multi-dimensional arrays can store a set of data in each array element. Therefore, each array element of a multi-dimensional array contains an array. Similar to a one-dimensional array, you can use the following two methods to create a multi-dimensional array:
(1) dim <
Array name> (<
Array Element count 1>,<
Number of array elements 2> ,......) As <
Data Type>
(2) dim <
Array name> (<
Lower Bound> To <
Upper Bound>,<
Lower Bound> To <
Upper Bound> ,......) As <
Data Type>
Similar to a one-dimensional array, a multi-dimensional array can also be dynamic.
4. Reference elements in the array
You can use an array name and an index number to reference a specific element in the array.
(For more information about arrays and their applications, see a series of articles)
------------------
Built-in functions
VBA contains various built-in functions to simplify computing and operations. Using Functions in VBA expressions is the same as using functions in worksheet formulas.
In the VBA code, you can also use many Excel worksheet functions, that is, using the worksheetfunction object to call the worksheet function. However, you cannot use a worksheet function with the same functions as the built-in functions of VBA.
