VBA syntax Basics

VBA language basics

Id 1
I. Definition
An identifier is a symbol that identifies the unit of a variable, constant, process, function, class, and other languages. It can be used to reference variables, constants, processes, functions, and classes.
Ii. Naming rules
1) It is a combination of letters, numbers, and underscores (_), such as a987b_23abc.
2) The character length is less than 40 (excel2002 and later Chinese versions, can use Chinese characters and can contain up to 254 characters)
3) it cannot be the same as the Reserved Words in VB, such as public, private, dim, Goto, next, with, integer, single, etc.

Section 2 Operators
Definition: An operator is a symbol that represents a certain operation function of VB.
1) Value assignment operator =
2) mathematical operators &, + (character connector), + (plus),-(minus), MOD (remainder), \ (entire division), * (multiplication) ,/(except),-(negative), ^ (INDEX)
3) logical operators not (not), and (and), or (OR), XOR (exclusive or), eqv (equal), IMP (implicit)
4) Relational operators = (Same), <> (unequal),> (greater than), <(less than), >=( not less than), and <= (not greater), like, is
5) bitwise operators not (logical not), and (logical and), or (logical or), XOR (logical exclusive or), eqv (logical, etc.), IMP (implicit)

Section 3 Data Types
There are 12 Data Types in VBA. For details, see the table below. You can also use type to customize the data types according to the following types.
Data Type Identifier byte
String string $ String Length (0-65400)
Byte No 1
Boolean No 2
Integer integer % 2
Long & 4
Single precision type single! 4
Double #8
Date type: No 8 AD 100/1/1-9999/12/31
Currency @ 8
Decimal: No 14
Variant does not have any of the above types.
No object 4

Section 4 variables and constants
1) VBA allows the use of undefined variables. By default, it is a variant variable.
2) in the general description section of the module, the option explicit statement can be added to force the user to define variables.
3) variable definition statement and variable scope
The dim variable as type is defined as a local variable, such as dim XYZ as integer.
Private variable as type 'is defined as private variable, such as private XYZ as byte
The public variable as type is defined as a public variable, such as public XYZ as single
The global variable as type is defined as a global variable, such as Globlal XYZ as date
Static variable as type 'is defined as static variable, such as static XYZ as double
In general, the principle of variable scope is that some definitions work in that part, while the definitions in the module play the role in this module.
4) constants are special cases of variables. They are defined by const and assigned values during definition,ProgramAnd the scope is similar to the variable scope. Const Pi = 3.1415926 as single

Section 5 Array
An array is a set of variables of the same data type. each variable in an array is referenced by an array index subscript. A continuous memory block must be defined using a global or dim statement. The rules are defined as follows:
Dim array name ([lower to] upper [, [lower to] upper,…]) As type; the default value of lower is 0. Two-dimensional arrays are arranged by columns, such as XYZ (rows and columns ).
In addition to the above Fixed Array, VBA also has a powerful dynamic array with no size dimension Statement defined. In the program, redim statements are used to re-change the array size, the original array content can be retained by adding the preserve keyword. For example:
Dim array1 () as double: redim array1 (5): array1 (3) = 250: redim preserve array1 (5, 10)

Section 6 comments and value assignment statements
1) The comment statement is used to describe the functions and functions of some statements in the program. There are two methods in VBA to mark the comment statement.
U single quotation marks '. For example,' defines global variables. It can be placed at the end of another statement or a single line.
Ü REM; for example, Rem defines global variables. Only one row is allowed.
2) The value assignment statement is used to assign values to variables or object attributes. The value assignment number is =, for example, x = 123: form1.caption = "my window"
Assign values to objects using: Set myobject = object or myobject: = Object

Section 7 writing specifications
1) VBA is case-insensitive and is considered to be lowercase letters;
2) multiple statements can be written in one row, separated by colons;
3) a statement can be written in multiple lines and marked with spaces and underscores;
4) identifiers should be concise and clear without ambiguity.

section 8 judgment statement
1) if... Then... Else statement
If condition then [statements] [else elsestatements]
Example 1: if A> B and C such as 2: If x> 250 then x = X-100
or, block Syntax:
If condition then
[statements]
[elseif condition-N then
[elseifstatements]...
[else
[elsestatements]
end if
Example 1:
If number <10 then
digits = 1
elseif number <100 then
digits = 2
else
digits = 3
end if

2) Select case... Case... End case statement
Example 1:
select case PID
case "a101"
price = 200
case "a102"
price = 300
......
case else
price = 900
end case
3) choose function
choose (index, choce-1, choice-2 ,..., Choice-N), which can be used to select a value in the independent variable string column and return it. The required index parameter, value expression, or field, returns a value, it must be between 1 and the number of projects that can be selected. A required parameter of choice, Variant Expression, which contains one of the projects that can be selected. Example:
getchoice = choose (IND, "Speedy", "united", "federal")
4) switch function
switch (expr-1, value-1 [, expr-2, value-2 _ [, expr-N, value-N])
the switch function is similar to the choose function, however, it returns the expected value in two groups. In the string column, the value true is returned first. Required parameter of expr, the variant expression to be calculated. Value parameter. If the related expression is true, return the value or expression of this part. If no expression is true, switch returns a null value.

Section 9 cyclic statements
1) The for next statement repeats a set of statements for a specified number of times.
For counter = start to end [step] 'step is 1 by default
[Statements]
[Exit for]
[Statements]
Next [Counter]
Example 1:
For words = 10 to 1 step-1 'create 10 Loops
For chars = 0 to 9' create 10 cycles
Mystring = mystring & chars add the number to the string
Next chars 'crement counter
Mystring = mystring & "" 'Add a space
Next words

2) For each... The main function of the next statement is to execute an array or set object, so that all elements can execute the statement once again.
For each element in group
Statements
[Exit for]
Statements
Next [element]
Example 1:
For each rang2 in range1
With range2.interior
. Colorindex = 6
. Pattern = xlsolid
End
Next
In the above example, with… is used... The end with statement is used to save multiple object calls and speed up the process. Syntax:
With object
[Statements]
End

3) do... When the condition of the loop statement is true, the block command is repeatedly executed.
Do {While | until} condition 'while is a type-oriented loop, while until is a type-oriented loop.
Statements
Exit do
Statements
Loop
Or use the following syntax:
Do 'do first and then judge, no matter how you do it first
Statements
Exit do
Statements
Loop {While | until} Condition

Section 10 Other statements and error statements
1. Other loop statements
It is sufficient for a structured program to use the above judgment and loop statements. We recommend that you do not use the following statements easily, although VBA also supports.
1) goto line this statement is a line to jump to the line statement line
2) The on expression gosub destinatioinlist or on expression goto destinationlist statement jumps to the desired row number or row mark based on the exprssion expression value.
3) gosub line... Line... Return Statement, return returns to the gosub line, as shown in the following example:
Sub gosubtry ()
Dim num
Num = inputbox ("enter a number and this value will be judged as a loop ")
If num> 0 then gosub routine1: Debug. Print num: Exit sub
Routine1:
Num = num/5
Return
End sub
4) while... The Wend statement is executed cyclically as long as the condition is true. This is reserved by the earlier VB syntax, as shown in the following example:
While condition 'while I <50
[Statements] 'I = I + 1
Wend 'wend

Ii. Handle error statements
Sometimes errors occur in the execution phase. You can use the on error statement to handle errors and start an error processing program. Syntax:
On Error goto line' when an error occurs, it is immediately transferred to the line
On Error resume next 'when an error occurs, it is immediately transferred to the next row where the error occurs.
On erro goto 0' when an error occurs, any error handling process will be immediately stopped.

Section 11th processes and functions
A process is a module that forms a program and is often used to complete a relatively independent function. The process can make the program clearer and more structured. VBA has four processes: sub process, function, property process, and event process.
I. Sub Process
Sub process parameters can be passed by value (byval) or by address (byref ). For example:
Sub password (byval X as integer, byref y as integer)
If y = 100 then Y = x + y else y = x-y
X = x ++ 100
End sub

Sub call_password ()
Dim X1 as integer
Dim Y1 as integer
X1 = 12
Y1 = 100
Call password (x1, Y1) 'call Procedure Method: 1. Call procedure name (parameter 1, parameter 2 ...) ; 2. process name parameter 1, parameter 2...
Debug. Print X1, Y1 'results are 12, 112, Y1 changed by address transmission, while X1 passed by value without changing the original value
End sub
Ii. Function Functions
A function is actually a ing. It completes operations and returns results through certain ing rules. There are also two types of parameter transfer: pass by value (byval) and pass by address (byref ). For example:
Function password (byval X as integer, byref y as integer) as Boolean
If y = 100 then Y = x + y else y = x-y
X = x ++ 100
If y = 150 then Password = true else Password = false
End Function

Sub call_password ()
Dim X1 as integer
Dim Y1 as integer
X1 = 12
Y1 = 100
If password then' call the function: 1. Place it on the right side as an expression; 2. Use it as a parameter.
Debug. Print X1
End if
End sub
3. Property process and event Process
This is the process of adding VB to object functions. It is closely related to Object Features and is also an important component of VBA. The technology is complicated and can be referred to in related books.

Section 12th internal functions
There are many built-in functions in the VBA program language to help the programCodeDesign and reduce code writing.
I. test functions
Whether isnumeric (x) 'is a number. The return value is Boolean, true or false.
Whether isdate (x) 'is a date. The return value is Boolean, true or false.
Whether isempty (x) 'is empty, return Boolean, true or false
Isarray (x) 'indicates whether the variable is an array.
Iserror (expression) 'indicates whether the expression is an error value.
Isnull (expression) 'indicates whether the expression does not contain any valid data (null ).
Isobject (identifier) 'indicates whether the identifier represents an object variable

Ii. mathematical functions
Sin (x), cos (x), Tan (x), atan (x) trigonometric function, unit: radian
Log (x) returns the natural logarithm of X.
Exp (x) returns ex
ABS (x) returns the absolute value.
INT (number) and fix (number) both return the integer part of the parameter. Difference: int converts-8.4 to-9, and fix converts-8.4 to-8.
SGN (number) returns a variant (integer), indicating the positive and negative numbers of parameters.
Sqr (number) returns a double value, which specifies the square root of the parameter.
Vartype (varname) returns an integer indicating the child type of the variable.
RND (x) returns the single-precision data between 0 and 1. X is a random seed.

Iii. String Functions
Trim (string) removes the left and right white spaces of the string.
Ltrim (string) removes the left blank of string
Rtrim (string) removes the right white space of string
Len (string) calculates the string length.
Left (string, x) is a string consisting of X characters in the left segment of string.
Right (string, x) is a string consisting of X characters in the right segment of string.
Mid (string, start, x) is a string consisting of X characters starting from the start bit.
Convert ucase (string) to uppercase
Lcase (string) to lowercase
Space (x) returns x blank strings.
ASC (string) returns an integer representing the character code of the first letter in the string.
CHR (charcode) returns a string containing Characters Related to the specified character code

Iv. conversion functions
Cbool (expression) to boolean type
Cbyte (expression) to byte type
Ccur (expression) to currency type
Cdate (expression) to date type
Cdbl (expression) to double type
Cdec (expression) to decemal type
CINT (expression) to integer type
Clng (expression) to long type
Csng (expression) to single type
CSTR (expression) to string type
CVaR (expression) to Variant Type
Val (string) to Data Type
STR (number) to string

V. Time Functions
Now returns a variant (date), which specifies the date and time based on the date and time set by the computer system.
Date returns the variant (date) that contains the system date ).
Time returns a variant (date) that specifies the current system time ).
Timer returns a single, representing the number of seconds that have elapsed since midnight.
Timeserial (hour, minute, second) returns a variant (date) that contains a specific time, minute, or second.
Datediff (interval, date1, date2 [, firstdayofweek [, firstweekofyear]) returns the value of variant (long), indicating the number of time intervals between two specified days.
Second (time) returns a variant (integer) with an integer between 0 and 59, indicating a second in a minute.
Minute (time) returns a variant (integer) with an integer between 0 and 59, indicating a minute in an hour.
Hour (time) returns a variant (integer) with an integer between 0 and 23, indicating a certain hour of the day.
Day (date) returns a variant (integer) with an integer between 1 and 31, indicating a day in a month.
Month (date) returns a variant (integer) with an integer between 1 and 12, indicating a month in a year.
Year (date) returns variant (integer), which contains an integer representing the year.
Weekday (date, [firstdayofweek]) returns a variant (integer) that contains an integer representing the day of the week

Section 3 File Operations
1) File
Dir [(pathname [, attributes])]; an optional parameter of pathname, used to specify the string expression of the file name, which may contain directories, folders, and drives. If no pathname is found, the zero-length string ("") is returned. The optional parameter attributes. The sum of constants or numeric expressions used to specify file attributes. If omitted, a file that matches the pathname but does not contain the attribute is returned.
2) Delete
Kill pathname: delete a file from the disk. The pathname parameter is used to specify a file name.
Rmdir pathname deletes a directory from the disk. The pathname parameter is used to specify a folder.
3) Open
Open pathname for mode [Access access] [lock] as [#] filenumber [Len = reclength] can input/output files (I/O ).
Pathname is required. String expression that specifies the file name. The file name may also include directories, folders, and drives.
Mode is required. Keyword: Specifies the file method, including append, binary, input, output, or random. If no method is specified, the file is opened in random access mode.
Access is optional. Keyword, indicating that the opened file can be operated, including read, write, or read write.
Optional. Keyword, which indicates operations on files opened by other processes, including shared, lock read, lock write, and lock read write.
Filenumber is required. A valid file number ranging from 1 to 511. Use the freefile function to obtain the next available file number. Reclength is optional. It is less than or equal to 32,767 (bytes. For files opened in random access mode, this value is the record length. For ordered files, this value is the number of buffered characters.
This indicates that you must open the file before performing any I/O operations on the file. The open statement allocates a buffer for file I/O and determines the access method used by the buffer. If the file specified by pathname does not exist, you can create this file when you open the file using append, binary, output, or random. If the file has been opened by another process and the specified access type is not allowed, the open operation fails and an error occurs. If the mode is binary, the Len clause is ignored.
It is important that you can use different file numbers in the binary, input, and random modes to open the same file, rather than closing the file first. In append and output modes, if you want to open the same file with different file numbers, you must close the file before opening it.
4) read
Input # filenumber, varlist read data from open sequence files and specify the data to the variable
Get [#] filenumber, [recnumber], varname reads an opened disk file into a variable.
5) Write
Write # filenumber, [outputlist] write data to an ordered File
Print # filenumber, [outputlist]: Write formatted and displayed data to the ordered file.
Put [#] filenumber, [recnumber], varname writes the data of a variable to the disk file.
6) Close
Close [filenumberlist] Close the input/output (I/O) file opened by the open statement

Note: If you want to use the input # statement to read the file data in the future, you must use the write # statement instead of the print # statement to write the data to the file. When using write #, the data domain demarcation ensures the integrity of each data domain. Therefore, input # can be used to read the data. Use Write # To ensure that data in any region is read correctly. The Write statement is different from the print # statement. To write data to a file, the write # statement inserts a comma between the project and the quotation marks used to mark the string. The write # statement inserts a new line character after writing the last character in the outputlist into the file, that is, the carriage return linefeed (CHR (13) + CHR (10 )).
7) Other file functions
Lof (filenumber) returns a long value, indicating the size of the file opened with the open statement, in bytes.
EOF (filenumber) returns an integer that contains the Boolean value true, indicating that the end of the file opened for random or sequential input has been reached.
LOC (filenumber) returns a long value, specifying the current read/write location in the opened file
Seek (filenumber) returns a long value, which specifies the current read/write location in the file opened by the open statement.

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/zhanghao0722/archive/2008/09/06/2893300.aspx