data type
elementary data type
That is, the main type data and the number of bytes in parentheses in the following list:?
Bytes? (1): unsigned number type, with a value range of 0-255.
Bull? (2)
Integer (2)
Dragon? (4)
Single? (4)
Double? (8)
Currency? (8)
Decimal? ( 14)
Dating? (8)
line
Object? (4)
Variant? (according to distribution)? [2]?
Custom data type
Equivalent to c language struct, such as:? [2]?
Type Custom Type Name Element Name as Type ... [Element Name as Type] End Type
rank
Option cardinality 0: Array index value starts from 0? [2]?
Option Base 1: The array index value starts from 1.
Dim MyArray( 10): Declare an array variable, and 10 is the maximum array index value available.
MyArray(5) = 10 1: Assign values to the elements of the array.
Dimdata (10,5): Declare a two-dimensional array variable.
Data (1, 1) = "A00 1 ":Assign values to array elements.
Dimcarr (- 1 1to20, 1to3) as string: declares an array and defines the upper and lower bounds of the array index values.
Dim dArr() As String: Declare a dynamic array.
ReDim dArr(0 to 5, 1 to 2): Change the size of the dynamic array and clear the original data by default. If you keep the original data, you must add parameters.
Preserve: When using the Preserve parameter, you can only change the size of the last bit.
If UBound(vTemp) =-1, then: judge whether the array variable vTemp is an empty array.
End If Erase MyArrar, Data Erase statement clears array elements and releases the space occupied by variables? [2]?
constant
edit
System defined constant
There are three system-defined constants: True, False and Null. ? [4]?
Inherent constant
Inherent constants are constants defined by object libraries referenced during programming. All intrinsic constants can be used in macros or VBA code. Usually, the intrinsic constant is defined by the first two letters. Constants in vb library begin with "VB". Constants in Access begin with "ac". You can use the Object Browser to view a list of internal constants in all object libraries. ? [4]?
In VBA, the data types of constants are integer, long integer, single precision, double precision, byte, currency, character, date and logic. Integer data is an integer constant and long integer data is a long integer constant. For example, 12% and-1% are integer constants, and 32768 &,10000000 &; Is a long integer constant, -2.5 1, 3. 14 is a single-precision real number constant, 3. 14 15926# is a double-precision real number constant, and China and Shanghai are character constants, # 07/13/2006. ? [4]?
Symbolic constant
A self-defined constant, that is, a symbolic constant, must be defined before use. As you can see, the constant to be declared is a symbolic constant. ? [4]?
Basic syntax format:
1 [? Publicr/？ Private]? Const？ Constant name [As? Type] = expression such as: global constant? Symbolic constant name = constant value? [4]?
Statement function:
Defines a symbolic constant and assigns the value of the specified expression to it. ? [4]?
The statement is as follows: [4]?
1) Constant Name Specifies the name of the symbolic constant. Symbol constant names can consist of letters, numbers and underscores, but they can only start with letters and cannot contain spaces. ? [4]?
2) "Expression" specifies the value of the symbol constant. Expressions usually consist of numbers, characters, logical or date data and various operators, but variables and functions cannot appear in expressions. ? [4]?
3) public is used to indicate that the range of this constant is all modules of the whole database. ? [4]?
4) private means that the constant is valid only in modules that use statements declaring the constant. ? [4]?
Description:
1) In addition to user-defined symbolic constants, VBA also provides many symbolic constants, which we can use directly. ? [4]?
2) Symbolic constants are usually used to replace constants of long numbers, and are used many times in programs. When running the program, the system will automatically change all symbolic constants in the program to the values assigned to it. ? [4]?
changeable
edit
Like a constant, a variable is also a storage space for storing data that may change during the running of a program. A variable name is a user-defined identifier. ? [5]?
When you need to use this variable in your code, you only need to refer to the corresponding identifier, regardless of the current value of the variable. ? [5]?
statement
Similar to user-defined constants, variables need to be declared before use. There are several syntax formats for declaring variables in VBA:? [5]?
Keyword variable name as data type
Keyword variable 1, variable 2 ... variable n is? data type
Keywords variable 1 AS? Data type, variable 2 is? Data type, ..., variable n as data type.
In the second syntax format, the data type of "variable n" is the data type defined after the AS keyword, while the data types of "variable 1", "variable 2" and … are variables. ? [5]?
In VBA, four keywords Dim, Private, Public and Static can be used to declare variables. Variables declared with different keywords have different meanings. ? [5]?
◆ Declare variables with Dim keyword: the Dim keyword is mainly used to allocate a space in memory and name the space. This is the most commonly used keyword to declare variables in VBA. Variables declared with the Dim keyword can only be used in the current procedure or module. ? [5]?
◆ Declare variables with Private keyword: the Private keyword is used to declare a private variable in the class module, which can only be used in the current class module. When the position of the defined variable is in the class module, its use effect is the same as that of the variable defined by the Dim keyword. ? [5]?
◆ Declare variables with the Public keyword: variables declared with the Public keyword can be called anywhere in the program, regardless of the location of the declared variables. ? [5]?
◆ Declare variables with Static keyword: the Static keyword is used to declare static variables, that is, the value of variables can be kept during the whole code running process. ? [5]?
assess
Variables need to be assigned when they are used. In VBA, you can assign values to variables through the "=" symbol or the "Set" keyword. There are several syntax formats for assigning values to variables through the "=" symbol. ? [5]?
Variable name = data
Variable 1= Variable 2 Operator Data
Variable 1= variable 2 operator variable 3 … operator variable n
If the data type of the variable is specified when defining the variable, the value assigned to the variable must also be the value of that data type. If a variable is defined as an integer type, but the variable is given string type data when it is assigned, an error pop-up box will pop up at compile time and run time. ? [5]?
operational character
edit
Operators are some special symbols that perform calculation functions in programs and are an important part of program code. In the program code, the operator can not be used alone, but must form an expression with its operand * * * to have operational significance. Operators in VBA include arithmetic operators, join operators, comparison operators and logical operators. ? [4]?
arithmetic operator
Arithmetic operators are mainly used to perform four operations, and expressions connected only by arithmetic operators are called arithmetic expressions. Arithmetic operators, their functions and examples are shown in the following table. ? [5]?
example
name
result
-$a
take back
Negative value of $ a.
$a + $b
add
The sum of a dollar and b dollar.
$a - $b
subtraction
The difference between $a and $ b.
$a * $b
increase
The product of a and b.
$a / $b
separate
The quotient of $a divided by $b allows decimals.
$a \ $b Divide $a by the quotient of $b, and the result is rounded off.
$a Mod $b
take over
The remainder of $a divided by $b
note:
When performing arithmetic operations, the data types of the operations on both sides of the operator must be the same, otherwise an error message of "type mismatch" will appear. When the left and right sides of the "+"operator are operands of string type, the join operation will be performed. For example, the expression "PassWord" evaluates to the string "PassWord". ? [5]?
Join operator
The function of a concatenation operator is to concatenate the operands on both sides of the operator into a number, and the data type of its operands is usually a string. The connection operators in VBA are "&"and "+",because "+"is easily confused with addition in connection operation, so "&"is usually used. Perform a join operation. ? [5]?
comparison operator
Comparison operators can compare the operands on both sides of the operator, and the returned result is True or False of Boolean type. The operand of a comparison operator is usually a specific numerical value. When the operand is a string or other symbol, the comparison is made according to the ASCII code of the symbol. The following table shows the comparison operators in VBA, their functions and examples. ? [5]?
example
name
result
$a = $b
be qualified for sth
If a equals b, it is true.
$ a<& gtb dollars
different
TRUE if $a is not equal to $ b.
$ a<b dollars
absent
If a is strictly less than b, it is true.
$ a>b dollars
Bigger than ...
If $a is strictly greater than $ b, it is true.
$ a & lt= $b
less than or equal to
TRUE if $a is less than or equal to $ b.
$ a & gt= $b
greater than or equal to
TRUE if $a is greater than or equal to $ b.
Logical operator
Logical operators are used to perform logical operations on operands on both sides of operators. Operands involved in logical operations can be logical expressions (the final result of the expression is true or false) or arithmetic expressions (the final return value of the expression is 0 or not). ? [5]?
In logical operation, the value 0 is the same as the logical value False, indicating logical false; Non-zero values are the same as true, indicating logical truth. Logical operators and their meanings and examples are shown in the following table. ? [5]?
example
name
result
$a and $b
And (logical AND)
If $a and $b are both true, they are true.
$a or $b
Or (logical or)
If $a or $b is true, it is true.
$a exclusive or $b
Logical XOR
True if $a or $b are in different positions.
Not (1> 2)
Not (logical not)
That's right.
( 1 & gt； 2)Eqv( 1 & gt； 2)
Both operands are false, and false is returned; If both operands are true, it returns true; Two operands, one true and one false, return false.
That's right.
2 & lt& gt 1 Imp 3 & lt； five
Operators left and right are both true and return true; Left and right are false, return to truth; Left true and right false, return false; Left false right true, return true.
That's right.
syntactic structure
edit
If the statement
1 if? Condition 1? So the statement 1elseif? Condition two? Then declare 2elseif? ..... else statement nend? If Select Case statement
1 Select? Case? Expression case? Expression list 1 statement 1Case? Expression list 2 statement 2 ... case? Expression list n statement nEnd? Select the location of the expression list:? [5]?
Expression? For example: "a"
Examples of comma-separated sets of enumerated expressions: 2, 4, 6, 8.
Expression 1 to? Example of Expression 2: 60 to 100
Is it? Example of relational operator expression: is
Make a ... loop statement
1 Do what? When or until? Conditional statement block 1Exit? Do statement block 2 loops For ... next statement
1 Do statement block 1 exit? Is statement block 2 looping? When or until? Conditional For Each … Next statement
1 For？ Loop control variable = initial value To? Final value step? Step size statement block? Quit The For statement can then jump out of the loop body and jump out of the continue statement of this loop.
1 For？ Loop control variable = initial value? Where to? Final value? One step? What about the step size? Used to simulate a continue statement block. What if? Conditions? then what Quit Do what? Used to simulate a continue statement block. Cycle? What time? Fake? Used to simulate continue? Next With statement
1 and? End of object reference statement block? With On error statement
1 on? Mistakes? Goto？ Label of error handling statement? Jump to the error handling statement or
1 on? Mistakes? Resume? Next? Encountered an error, regardless of the error, continue to perform the control function.
If (conditional expression, expression 1, expression 2)? [5]?
Switch (conditional expression 1, expression 1, [conditional expression 2, expression 2[, ..., conditional expression n, expression n]])
Choose (index, option 1[, option 2, ... [,option n]])' This is an index based on 1
Other statements
edit
Annotation statement
Lines that start with REM or single quotes. ? [6]?
Continuation and continuation of sentences
If a line contains more than one statement, separate each statement with a colon. For statements that span multiple lines, adding "space underline" at the end of the line indicates the continuation of the line? [6]? .
Processes and functions
edit
12 Sub？ Process name (parameter table) statement block exit? End of sub-statement block? Sub？ 1 function? Function name (parameter table)? As? Type statement block function name = expression exit? Function End? Functions can be modified by Private, Public, Friend, Static, etc. ? [6]?
Functions/procedures can be called with or without parentheses. If you call an expression as part of a line, you must assign parameters, such as the return value of a function call, to variables. You can use/not use the call keyword when calling the procedure. Use the call statement to call the procedure. If there are no parameters, there are no parentheses. If there are parameters, they must be enclosed in parentheses. If a single parameter is enclosed in parentheses when it is called, the parameter is forced to be passed by value. It should be noted that there is no need to call without parentheses, and formal parameters are passed by value rather than by reference, which will lead to the failure of method calls of some objects. For example:? [6]?
1 Dim？ cn？ As? ADODB。 ConnectionSet？ cn？ =? Current project. ConnectionDim？ rs？ As? New? ADODB。 Record set. Open it? "choose? *? From where? My desk " ,? cnDim？ ExcelApp？ As? New? Excel。 ApplicationDim？ ExcelWst？ As? Worksheet set? ExcelWst？ =? ExcelApp。 workbooks . add . worksheets( 1)ExcelWst。 Range ("A2"). CopyFromRecordset(rs)？ Unable to execute row Excel wst. Range ("A2"). Copy from recordset? rs？ This line of commonly used built-in functions can be successfully executed.
edit
VBA commonly used built-in functions:? [5]?
dialog box
Input box
Rounding function: fix rounding to 0, Int rounds down, and round rounds.
Rnd: Returns a single-precision random number in the range of 0- 1
String function:? [5]?
Filter: filters an array of one-dimensional strings.
InStr([Start，]& lt； str 1 & gt； , & ltStr2 & gt[, Compare]) and InStrRev: Find substring.
Len: string length
Join: Join all substrings in a one-dimensional array.
Left, right and center: Intercept substring.
Space (numerical value): generates a space string.
Ucase, Lcase: case conversion function
Ltrim, Rtrim, trim: Delete leading and trailing spaces.
replace
Split: Split a string into a one-dimensional array.
StrComp: string comparison
StrConv: string conversion
String(number, character): specifies how many times the character is repeated.
StrReverse
Date/time related functions:? [5]?
Year, month, day, working day, hour, minute, second: cut off the date and time part.
DateAdd: date/time increment function
DateDiff(& lt； Interval type >,< date 1 >,< date 2>[, w1]] Distance function of date/time.
Time division function of date part (< segment type >, < date > [,w 1][, w2])
Date series (< expression1>; ,< expression 2>,< expression 3>) synthesis date; DateValue ("string expression") returns a date;
Date, Time, Now, Timer: Returns the date and time.
Date value
TimeSerial: Time object obtained from time series.
TimeValue: get the time object from the time string.
Weekday: Get the day of the week of the date.
Name of week
Transformation functions: CBool, CByte, CCur,? CDate，CDbl，CDec，CInt，CLng、CLngLng、CLngPtr、[5]? CSng、CStr、CVar、CVErr、Asc(& lt； String expression >) returns the Ascii value of the first character, Chr(ASCII code) returns the character, Hex, Oct, str () returns the string, Val(string), Format, Currency, FormatDateTime, FormatNumber, FormatPercent, MonthName.
If Nz (expression or field property value [,specified value]) is empty, return 0 or ""or the second parameter value of the function? [5]?
Verification functions: isNumeric, isDate, isNull, isEmpty, IsArray, IsError, IsMissing, IsObject? [5]?
Mathematical functions: Abs, Sqr, Tan, Atn (that is, atan), Sin, Cos, Exp(e-basis index), Log natural logarithm? [5]?
Array: Construct an array object? [5]?
CallByName: Gets or sets a property, or calls a method with a string name at runtime. ? [5]?
Control flow: Choose: similar to c language select statement, If is equivalent to IF-ELSE statement, Switch? [5]?
Command: Get command line parameters? [5]?
CreateObject: create ActiveX object
CurDir: Returns the current working path of the specified drive? [5]?
Functions derived from basic mathematical functions: Sec, Cosec, Cotangent, cotan, Arcsin, Arccos, Arcsec, Arccosec, Arccotan, HSin, HCos, HTan, HSec, HCosec, HCotan, harcisin, harcoss, HArctan, HArcsec, hHArccosec, HArccotan.
DoEvents: temporarily return CPU control to the system? [5]?
Environ: return the environment variable? [5]?
File operation:? [5]?
Dir: Returns the names of all files and directories that meet the conditions.
eof
FileAttr
File date and time
Filelen
Free file function
GetAttr: Returns the attribute values of files and directories.
Input: reading file
Loc: file pointer position
LOF: the pointer position when the file is opened.
Finding: locating file pointers
Spc: Use Print for Position Output
Tab: Used for printing function.
Error: What is the error message corresponding to the error number? [5]?
Data in Windows registry: GetAllSettings, SaveSetting, DeleteSetting, GetSetting? [5]?
Getobject: a reference to ActiveX components? [5]?
Return to the current Input Method Editor (IME)? [5]?
Macintosh platform: MacID, MacScript? [5]?
Financial function:? [5]?
DDB: Depreciation is calculated by double declining balance method.
FV: fixed-rate annuity.
IPmt: calculating interest rate
IRR: calculating interest rate
MIRR: calculating interest rates
NPer: counting period
NPV: calculating the net present value
Payment: calculate the payment amount
Calculate principal payment
PV: calculating present value
Interest rate: interest rate
SLN: straight-line depreciation method
SYD: Calculate the total depreciation of years.
Partition: Returns a string indicating that the numeric name falls within each range. Commonly used in SQL select statements? [5]?
QBColor: color value? [5]?
RGB: Color value? [5]?
TypeName: get the type name of the variable? [5]?
VarType: get the number of types of variables? [5]?