Basic understanding of VBA language created by Vietdung90, last modified 2016-10-18 "go from W3cschool" first section: Identifiers
First, the definition
An identifier is a symbol that identifies variables, constants, procedures, functions, classes, and other language constituent units that can be used to reference variables, constants, procedures, functions, classes, and so on.
Second, naming rules
(1) The beginning of the letter, consisting of letters, numbers, and underscores, such as A987B_23ABC
(2) The character length is less than nine, (Excel2002 above Chinese version, can be used in Chinese characters and the length can be up to 254 characters)
(3) can not be with the name of VB reserved words, such as Public,private,dim,goto,next,with,integer,single
Section II: Operator Definitions: operators are symbols that represent some of the arithmetic functions of VB. (1) Assignment operator = (2) mathematical operator &, + (character connector), + (plus),-(minus), Mod (remainder), \ (divisible), * (multiply),/
(except),-(minus), ^ (exponent) (3) logical operator not (non), and (with), or (or), Xor (XOR), EQV (equal), IMP (implied)
(4) Relational operators = (same), <> (unequal), > (greater than), < (less than), >= (not less than), <= (not greater than),
The like, is (5) bitwise operator NOT (logical), and (logical AND), or (logical OR), Xor (Logical XOR), EQV (logical
etc), IMP (implied)
Section three: Data types VBA has 12 data types, as shown in the following table, and users can customize the data type with type based on the following types. Fourth: Variables and Constants (1) VBA allows the use of undefined variables, the default is Variant variables (2) in the General Description section of the module, the Option Explicit statement can force the user to define the variable (3) Variable definition statements and variable scope general variable scope principle is that That part definition functions in that part, in which the module defines in that module that function. (4) A constant is a special case of a variable, defined with a const, and when defined, the value can not be changed in the program, the scope is like a variable scope. The definition is as follows: Const pi=3.1415926 as single fifth: array arrays are collections of a set of variables that contain the same data type, and the individual variable references in an array are indexed by the index of the arrays. The memory is represented as a contiguous block of memory that must be defined with a global or Dim statement. Define the rules as follows:
Dim array name ([Lower to]upper [, [Lower to]upper, ...]) as type; The default value of lower is 0. Two
A dimension array is arranged in rows and columns, such as XYZ (row, column).
In addition to the above fixed array, VBA also has a powerful dynamic array, the definition of no size dimension declaration, in the program again using the ReDim statement to re-change the size of the array, the original array content can be reserved by adding preserve keywords.
The following example:
Dim array1 () as Double:redim array1 (5): Array1 (3) =250:redim Preserve array1 (5,10) Section Sixth: Comments and Assignment Statements (1) Comment statements are used to describe the functions of certain statements in a program and to make In VBA, there are two ways to identify a comment statement.
√ Single quotation mark '; if: ' defines global variable; can be at the end of another statement or a single row
√rem: Rem defines global variables; only a single row
(2) An assignment statement is a statement that assigns a value to a variable or an object property, using an assignment number =, such as x=123:form1.caption= "
My Window "
Assignment to an object: set Myobject=object or Myobject:=object seventh: Writing specification (1) VBA does not distinguish the letter case of identifiers, which are considered to be lowercase letters;
(2) A line can write more than one statement, between the statements with a colon: separate;
(3) A statement can be more lines of writing, with a space underlined _ to identify the continuation of the following behavior;
(4) The identifier is best to be concise and unambiguous, without causing ambiguity. Section Eighth: Judgment statements
(1) If ... Then ... Else Statement
If condition then [Statements][else elsestatements]
such as 1:if a>b and c<d then a=b+2 Else a=c+2
Like 2:if x>250 then x=x-100
Alternatively, you can use the syntax in block form:
If condition Then
[Statements]
[ElseIf Condition-n Then
[Elseifstatements] ...
[Else
[Elsestatements]]
End If
such as 1:
If Number < Ten Then
Digits = 1
ElseIf Number <
Digits = 2
Else
Digits = 3
End If
(2) Select case ... Case ... END Case Statement
such as 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), can be used to select a value in the argument string column and return it, index the necessary argument, numeric expression, or field, its operation result is a numeric value, and bounded by 1 and the number of items can be selected. Choice required parameter, variant expression, contains one of the selectable items. such as: Getchoice = Choose (Ind, "Speedy", "All", "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, but it returns the desired value in a group of two, in the String column, the first
A value of TRUE will be returned. The necessary parameters of expr, the Variant expression to be computed. Value the necessary parameters. If the related expression is True, returns the numeric value or expression for this part, and no expression of True,switch returns a null value. Section Nineth: Loop statement (1) For Next statement repeats a set of statements with a specified number of times
For counter = start to end [step step] ' step default value is 1
[Statements]
[Exit for]
[Statements]
Next [counter]
As 1:for Words = 1 Step-1 ' Build 10 cycles
For Chars = 0 to 9 ' Build 10 cycles
MyString = MyString & Chars ' Add numbers to a string
Next Chars ' Increment counter MyString = MyString & "" ' Add a space
Next Words (2) for Each ... Next statement The main function is to make an array or a collection object, so that all elements repeat the statement once
For each element in group
Statements
[Exit for]
Statements
Next [element]
such as 1:
For each rang2 in Range1
With Range2.interior
. colorindex=6
. pattern=xlsolid
End with
Next
The above example uses a with ... End With statement, the purpose is to save the object multiple calls, speed up;
The syntax is:
With Object
[Statements]
End with
(3) Do...loop statement repeat the chunk command when the condition is true
do {and |until} condition ' While is a type loop, until is until the type loop, as the name implies, not much to say
Statements
Exit do
Statements
Loop
or use the following syntax:
Do ' Do before you judge, that is, no matter how you do it first
Statements
Exit do
Statements
Loop {while |until} condition the tenth section other class statements and Error statement handling
A. Other loop Statements
Structured programs that use the above judgment and looping statements are sufficient, and it is recommended not to use the following statements easily, although VBA also supports them.
(1) Goto line The statement is to jump to lines
(2) on expression GoSub destinatioinlist or on Expression goto destinationlist statement to jump to the desired line number or row marker based on the value of the exprssion expression
(3) Gosub line...line ... Return statement returns to Gosub line, as in the following example:
Sub Gosubtry ()
Dim num
Num=inputbox ("Enter a number, this value will be judged loop")
If num>0 then Gosub Routine1:Debug.print num:exit Sub
Routine1:
Num=num/5
Return
End Sub
(4) While...wend statement, as long as the condition is true, the loop is executed, this is the old VB syntax is preserved, as follows
Cases:
While condition ' while i<50
[Statements] ' I=i+1
Wend ' Wend
Two Error statement handling
Sometimes an error occurs in the execution phase, using the ONERROR statement to handle the error and starting an error handler.
The syntax is as follows:
On error Goto line ' When an error occurs, it is immediately transferred to lines
On error Resume next ' When an error occurs, it is immediately transferred to the next line where the error occurred.
On Erro Goto 0 ' When an error occurs, any error handling process in the process is immediately stopped
The 11th section process and function process is a module that makes up a program, often used to complete a relatively independent function. Process can make the program clearer and more structured. VBA has four procedures: Sub procedures, function functions, property properties procedures, and event events procedures.
A. Sub procedure
The parameters of a Sub procedure are passed in two ways: by Value (ByVal) and by Address (BYREF). The following 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
Callpassword (x1,y1) ' Call procedure mode: 1. Call Procedure Name (parameter 1, parameter 2 ...); 2. Procedure name parameter 1, parameter 2 ...
Debug.Print X1,y1 ' result is 12, 112,y1 by Address change value, and X1 by value, unchanged original value
End Sub
two. function Functions
The function is actually implementing a mapping, which completes the operation and returns the result through a certain mapping rule. There are two types of parameter passing: pass-by-value (ByVal) and pass-by-address (BYREF). The following 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 function: 1. As an expression placed at = right end; 2. Use as a parameter
Debug.Print X1
End If
End Sub
three. Property properties Procedures and event events procedures
This is the VB in the object function to add two processes, and the object features are closely related to VBA is an important component, the technology is more complex, you can refer to related books. The 12th section intrinsic functions have many built-in functions in the VBA programming language that can help program code design and reduce code writing.
A. Test function
IsNumeric (x) ' is a number that returns a Boolean result, True or False
IsDate (x) ' is a date, returns a Boolean result, True or False
IsEmpty (x) ' is empty, returns a Boolean result, True or False
IsArray (x) ' Indicates whether the variable is an array.
IsError (expression) ' Indicates whether the expressions are an error value
IsNull (expression) ' Indicates whether the expression does not contain any valid data (Null).
IsObject (identifier) ' Indicates whether an identifier represents an object variable
two. Mathematical Functions
Sin (x), Cos (x), Tan (x), Atan (x) trigonometric functions in radians
Log (x) returns the natural logarithm of X
EXP (x) returns
Abs (x) returns absolute value
the int (number) and fix (number) all return the integer part of the argument, the difference: int converts-8.4 to-9, and fix will
-8.4 Conversion to 8
SGN returns a Variant (Integer) that indicates the sign of the parameter
SQR (number) returns a Double that specifies the square root of the parameter
VarType (varname) returns an Integer that indicates the subtype of the variable
RND (x) returns the single-precision data between 0-1 and X as a random seed
three. String Functions
Trim (String) remove the left and right sides of string blank
Ltrim (String) remove left blank of string
Rtrim (String) Remove the right-side whitespace from the string
Len (String) calculates the string length
Left (string, x) takes a string of x characters
Right (string, x) takes a string of x characters
Mid (String, start,x) takes a string of x characters starting from the start bit
Convert Ucase (string) to uppercase
Convert Lcase (string) to lowercase
Space (x) returns a string of x blanks
ASC (String) returns an Integer that represents the character code of the first letter in the string
CHR (CharCode) returns a string that contains characters associated with the specified character code
Four. Conversion Functions
CBool (expression) converted to Boolean
CByte (expression) conversion to byte type
CCur (expression) converted to currency type
CDate (expression) conversion to date type
CDBL (expression) converted to double type
CDec (expression) converted to Decemal type
CInt (expression) converted to an integer type
CLng (expression) converted to long type
CSng (expression) conversion to single type
CSTR (expression) converted to String type
CVar (expression) converted to Variant type
Val (string) converted to data type
Str (number) converted to string
Five. Time function
Now returns a Variant (date) that specifies the date and time according to the date and time set by the computer system.
Date returns a Variant (date) that contains the system date.
Time returns a Variant (Date) that indicates the current system times.
The Timer returns a single that represents the number of seconds from midnight to now.
TimeSerial (hour, minute, second) returns a Variant (date) that contains time, minutes, and seconds.
DateDiff (interval, date1, date2[, firstdayofweek[, FirstWeekOfYear]]) returns the value of Variant (long) that represents the number of time intervals between two specified dates
Second (Time) returns a Variant (integer) with a value from 0 to 59, representing a second in a minute
Minute (Time) returns a Variant (integer) with a value from 0 to 59 that represents the minute of the hour
Hour (Time) returns a Variant (integer) with a value from 0 to 23 that represents an hour of the day
Day Returns a Variant (integer) whose value is an integer between 1 and 31, representing the day of the one month
Month (date) returns a Variant (integer) with a value from 1 to 12 that represents the month of the year
Year (date) returns a Variant (integer) that contains an integer representing the years.
Weekday (date, [FirstDayOfWeek]) returns a Variant (integer) containing an integer representing the day of the week
Section 13th file Operation file
dir[(Pathname[,attributes])]; pathname optional parameter, a string expression that specifies the file name, possibly containing a directory or folder, and a drive. If pathname is not found, a 0-length string ("") is returned;
Attributes Optional Parameters. A constant or numeric expression whose sum is used to specify a file property. If omitted, a file that matches pathname but does not contain a property is returned.
Delete
Kill Pathname Delete the file from the disk, the pathname parameter is used to specify a file name
RmDir Pathname Remove directory from disk, pathname parameter is used to specify a folder
Open it
Open pathname for mode [access access] [lock] as [#]filenumber [Len=reclength] is capable of file input/output (I/O).
Pathname necessary. A string expression that specifies the file name, which may also include directories, folders, and drives.
Mode necessary. keyword, specifying the file mode, with Append, Binary, Input, Output, or Random mode. If no way is specified, the file is opened as Random access.
Access is optional. Keyword that describes the actions that open files can take, with Read, Write, or ReadWrite operations.
Lock is optional. Keyword that describes operations that are limited to files opened by other processes, with the Shared, Lockread, Lockwrite, and Lock Read write operations.
FileNumber necessary. A valid file number, ranging from 1 to 511. Use the FreeFile function to get the next available file number. Reclength is optional. A number less than or equal to 32,767 (bytes). For files opened with random access, the value is the record length. For sequential files, this value is the number of buffered characters.
Indicates that the file must be opened before any I/O operations are made to the file. The Open statement allocates a buffer for file I/O and determines how the buffer is used for the access. If the file specified by pathname does not exist, this file can be created when the file is opened in Append, Binary, Output, or Random mode. If the file is already open by another process and the specified access type is not allowed, the open operation fails with an error. If mode is Binary mode, the Len clause is ignored.
Important in Binary, Input, and Random mode, you can open the same file with different file numbers without having to close the file first. In Append and Output mode, if you want to open the same file with a different file number, you must close the file before you open the file.
Read in
Input #filenumber, varlist read data from an open sequential file and assign data to a variable
Get [#]filenumber, [Recnumber], VarName reads an open disk file into a variable.
Write
Write #filenumber, [outputlist] writes data to the sequential file
Print #filenumber, [outputlist] writes formatted data to a sequential file
put [#]filenumber, [Recnumber], VarName writes data from a variable to a disk file.
Shut down
Close [filenumberlist] closes the input/output (I/O) file opened by the Open statement
Note If you want to use the Input # statement to read the file's data in the future, use the Write # statement instead of the Print # statement to write the data to the file. Because data domain demarcation ensures the integrity of each data domain when using write#, it is possible to read the data input#. Using write# also ensures that data from any region is read correctly. Unlike the Print # statement, when you write data to a file, the Write # statement inserts a comma between the item and the quotation marks used to mark the string. The Write # statement inserts a new line character after the last character in Outputlist is written to the file, which is the carriage return line break (CHR + CHR (10)).
Other file functions
LOF (filenumber) returns a Long that represents the size of the file opened with the Open statement, which is a single-byte size
-bit
EOF (filenumber) returns an Integer that contains the Boolean value True to indicate that it has reached the Random or
The end of the file that the sequential Input opens.
LOC (filenumber) returns a Long that specifies the current read/write position in the opened file
Seek (filenumber) returns a Long that specifies the current read/write bit in the file opened by the Open statement
Go to VBA get started (i)
