vba语言基础（VBA language foundation）

1、vba语言基础（VBA language foundation）VBA language foundationSection 1 identifierI. definitionAn identifier is a symbol that identifies variables, constants, processes, functions, classes, and so forth, and uses them to complete references to variables, constants, processes, functions, classes, and so for

2、th.Two. Naming rules1) initial letters, consisting of letters, numbers, and underscores, such as A987b_23Abc2) character length of less than 40, (more than Excel2002 Chinese version, etc., you can use Chinese characters, and the length of up to 254 characters)3) with VB reserved word names, such as

3、public, private, dim, goto, next, with, integer, single etc.Second section operatorDefinition: an operator is a symbol that represents the function of some VB operation.1) assignment operator =2) + (character, mathematical operator & connector), + (plus), - (minus), Mod (take over), (divisible),

4、 * (x) / (except), - (minus), (index)3) logical operators Not (non), And (), Or (or), Xor (Yi Huo), Eqv (equal), Imp (implicit)4) the relational operators (the same) =, < > > (range), (greater than), (less than) <, > = < = (no less than), (not more than Like, and Is)5) bitwise oper

5、ators Not (logical, non), And (logic and), Or (logic or), Xor (logical XOR), Eqv (logic, etc.), Imp (implicit)Third section data typeVBA has 12 data types, see the table below, and the user can customize the data type with Type, depending on the following type.Data type type identifier byteString ty

6、pe String $character length (0-65400)Byte type Byte with no 1Boolean Boolean No 2Integer type Integer% 2Long integer type Long & 4Single precision Single! 4!Double Double # 8Date type Date No 8 ad 100/1/1-9999/12/31Currency type Currency 8The decimal point type Decimal has no 14Variant Variant,

7、no more than any type, variableObject type Object, no 4Fourth variables and constants1) VBA allows undefined variables to be used, and defaults to variant variables.2) in the module general description section, adding the Option Explicit statement can force the user to define the variable.3) variabl

8、es define statements and variable scopesThe Dim variable as type is defined as a local variable, such as Dim, XYZ, as, integerThe Private variable as type is defined as a private variable, such as Private, XYZ, as, byteThe Public variable as type is defined as a public variable, such as Public, XYZ,

9、 as, singleThe Global variable as type 'is defined as a global variable, such as Globlal, XYZ, as, dateThe Static variable as type 'is defined as a static variable, such as Static, XYZ, as, doubleThe principle of the scope of the general variable is that the part of the definition is in that

10、 part, and the function in the module is defined in that module.4) the constant is a special case of variables, defined by Const and assigned when defined. The program can not change the value, and the scope is also like the scope of the variable. The following definitions: Const, Pi=3.1415926, as,

11、singleFifth section arrayAn array is a collection of variables containing the same data type, and a reference to a single variable in an array is performed by array index subscript. In memory, a contiguous block of memory must be defined by the Global or Dim statement. Define the following rules:Dim

12、 array name (lower, to,upper, lower, to,upper),. As type; Lower). The default value is 0. A two-dimensional array is arranged in rows, such as XYZ (rows, columns).In addition to the above fixed array, VBA has a powerful dynamic array, a definition of the size dimension statement; then use Redim stat

13、ement to change the size of the array in the program, the original contents of the array can be retained through adding the preserve keyword. Following example:昏暗的array1()双：ReDim array1（5）：2（3）= 250：ReDim Preserve array1（5,10）第六节注释和赋值语句1）注释语句是用来说明程序中某些语句的功能和作用；VBA中有两种方法标识为注释语句。ü单引号”；如：“定义全局变量；可

14、以位于别的语句之尾，也可单独一行üREM；如：REM定义全局变量；只能单独一行2）赋值语句是进行对变量或对象属性赋值的语句，采用赋值号=，如x = 123：1。标题=“我的窗口”对对象的赋值采用MyObject对象：集或MyObject对象：= =第七节书写规范1）VBA不区分标识符的字母大小写，一律认为是小写字母；2）一行可以书写多条语句，各语句之间以冒号：分开；3）一条语句可以多行书写，以空格加下划线_来标识下行为续行；4）标识符最好能简洁明了，不造成歧义。第八节判断语句1）如果那么其他语句如果条件再报表 其他elsestatements 1：如果如> B和CD = B +

15、 2人= C + 2如2：如果x > 250 x = X-100或者，可以使用块形式的语法：如果条件再声明“condition-n然后 elseifstatements 其他 elsestatements 最后如果如1：如果数字是10，那么位数= 1“数100则位数= 2其他的位数= 3最后如果2）选择案例案例案例语句结束如1：选择案例的PID“它”价格= 200案例“A102”价格= 300.其他案件价格= 900案例3）选择函数选择（指数，choce-1，choice-2，choice-n），可以用来选择自变量串列中的一个值，并将其返回，指数必要参数，数值表达式或字段，它的运算结果是

16、一个数值，且界于1和可选择的项目数之间。选择必要参数，变表达式，包含可选择项目的其中之一。如：getchoice =选择（IND，“快捷”、“团结”、“联邦”）4）开关函数开关（expr-1，1值，expr-2，value-2 _ expr-n value-n ，）开关函数和选择函数类似，但它是以两个一组的方式返回所要的值，在串列中，最先为真的值会被返回。expr必要参数，要加以计算的变异表达式。价值必要参数。如果相关的表达式为真实，则返回此部分的数值或表达式，没有一个表达式为真空开关会返回一个值。第九节循环语句1）下语句以指定次数来重复执行一组语句反=开始到结束的一步一步一步缺省值为 1 声

17、明退出声明下计数器如1：词= 10到1步1建立10次循环对于字符= 0到9建立10次循环mystring = mystring &字符的将数字添加到字符串中下一个字符增量计数器mystring = mystring &“”'添加一个空格接下来的话2）每一语句主要功能是对一个数组或集合对象进行，让所有元素重复执行一次语句组中的每个元素声明退出声明下一个元素如1：在每一rang2范围与erior颜色= 6xlsolid模式=。结束下一个这上面一例中用到了以结束语句，目的是省去对象多次调用，加快速度；语法为：与对象声明结束3）做环语句在条件为真时，重复执行

18、区块命令 而做|直到条件而为当型循环，直到为直到型循环，顾名思义，不多说啦声明退出声明环或者使用下面语法做先做再判断，即不论如何先干一次再说声明退出声明循环而|直到条件第十节其他类语句和错误语句处理一。其他循环语句结构化程序使用以上判断和循环语句已经足够，建议不要轻易使用下面的语句，虽然VBA还支持。1）转到线该语句为跳转到线语句行2）表达此时destinatioinlist或者表达去destinationlist语句为根据表达表达式值来跳转到所要的行号或行标记3）此时，行行返回语句，返回返回到转子线行，如下例：子gosubtry()昏暗的民Num = InputBox（“输入一个数字，此值将

19、会被判断循环”）如果num > 0然后跳转routine1：debug.print Num：退出routine1：5返回端子4）而走语句，只要条件为真实，循环就执行，这是以前VB老语法保留下来的，如下例：当“我50”时语句 i = i + 1外面的风二。错误语句处理执行阶段有时会有错误的情况发生，利用误差语句来处理错误，启动一个错误的处理程序。语法如下：OnErrorGoToLine”当错误发生时，会立刻转移到线行去错误恢复的当错误发生时，会立刻转移到发生错误的下一行去在不去0当错误发生时，会立刻停止过程中任何错误处理过程第十一节过程和函数过程是构成程序的一个模块，往往用来完成一个相对独

20、立的功能。过程可以使程序更清晰、更具结构性。VBA具有四种过程：子过程、功能函数、财产属性过程和事件事件过程。一子过程。子过程的参数有两种传递方式：按值传递（ByVal）和按地址传递（ByRef）。如下例：子密码（ByVal x为整数，ByRef y为整数）如果y = 100然后y = x + y否则y = x-yx100端子子call_password（）亮度为整数昏暗的Y1为整数X1 = 12Y1 = 100（x1，y1）电话密码的调用过程方式：1。电话过程名（参数1，参数2）；2。过程名参数1，参数2debug.print X1，Y1的结果是12、112，Y1按地址传递改变了值，而X1按

21、值传递，未改变原值端子二功能函数。函数实际是实现一种映射，它通过一定的映射规则，完成运算并返回结果。参数传递也两种：按值传递（ByVal）和按地址传递（ByRef）。如下例：功能的密码（ByVal x为整数，y为整型变量ByRef）布尔如果y = 100然后y = x + y否则y = x-yx100如果y = 150然后密码=真正的其他密码= false端功能子call_password（）亮度为整数昏暗的Y1为整数X1 = 12Y1 = 100如果密码然后调用函数：1。作为一个表达式放在=右端；2。作为参数使用debug.print X1最后如果端子三财产事件过程属性过程和事件。这是VB在

22、对象功能上添加的两个过程，与对象特征密切相关，也是VBA比较重要组成，技术比较复杂，可以参考相关书籍。第十二节内部函数在VBA程序语言中有许多内置函数，可以帮助程序代码设计和减少代码的编写工作。一。测试函数ISNUMERIC（x）的是否为数字，返回布尔结果，真或假IsDate（x）的是否是日期，返回布尔结果，真或假IsEmpty（X）的是否为空，返回布尔结果，真或假（x）的指出变量是否为一个数组ISArray。IsError（表达）的指出表达式是否为一个错误值IsNull（表达）的指出表达式是否不包含任何有效数据（空）。采用（标识符）的指出标识符是否表示对象变量二。数学函数sin（x）、cos

23、（x）、tan（x）、Atan（x）三角函数，单位为弧度日志（X）返回X的自然对数exp（x）返回前ABS（X）返回绝对值Int（数）、修复（数）都返回参数的整数部分，区别：int将- 8.4 - 9转换成，而固定将转换成- 8 - 8.4SGN（数）返回一个变量（整数），指出参数的正负号SQR（数）返回一个双，指定参数的平方根VarType（varname）返回一个整数，指出变量的子类型返回0-1之间的单精度数据RND（x），x为随机种子三。字符串函数修剪（字符串）去掉字符串左右两端空白Ltrim（字符串）去掉字符串左端空白Rtrim（字符串）去掉字符串右端空白Len（字符串）计算字符串长度

24、左（字符串，x）取字符串左段X个字符组成的字符串右（字符串，x）取字符串右段X个字符组成的字符串中期（字符串，字符串开始，x）取从开始位开始的X个字符组成的字符串Ucase（字符串）转换为大写Lcase（字符串）转换为小写空间（X）返回X个空白的字符串ASC（字符串）返回一个整数，代表字符串中首字母的字符代码CHR（字符码）返回字符串，其中包含有与指定的字符代码相关的字符四。转换函数CBool（表达）转换为布尔型CByte（表达）转换为字节型CCur（表达）转换为货币型CDate（表达）转换为日期型CDbl（表达）转换为双型CDec（表达）转换为decemal型CInt（表达）转换为整数型CL

25、ng（表达）转换为长型CSng（表达）转换为单型CStr（表达）转换为字符串型CVar（表达）转换为变异型瓦迩（字符串）转换为数据型Str (number) is converted to StringFive. Time functionNow returns a Variant (Date) that specifies the date and time based on the date and time the computer system is set.Date returns the Variant (Date) that contains the date of the sy

26、stem.Time returns a Variant (Date) that indicates the current system time.Timer returns a Single representing the number of seconds passed from midnight to now.TimeSerial (hour, minute, second) returns a Variant (Date) containing time, minutes, and seconds.DateDiff (interval, date1, date2, firstdayo

27、fweek, firstweekofyear) returns the value of Variant (Long), indicating the number of time intervals between two specified datesSecond (time) returns a Variant (Integer) with an integer value between 0 and 59, representing a second in one minuteMinute (time) returns a Variant (Integer) with an integ

28、er value between 0 and 59, representing a minute in an hourHour (time) returns a Variant (Integer), whose value is an integer between 0 and 23, indicating an hour of the dayDay (date) returns a Variant (Integer) with an integer value between 1 and 31, indicating a day in one monthMonth (date) return

29、s a Variant (Integer), whose value is an integer between 1 and 12, indicating a month of the yearYear (date) returns Variant (Integer), containing an integer that represents the year.Weekday (date, firstdayofweek) returns a Variant (Integer) containing an integer representing the date of the weekThi

30、rteenth file operations1) documentsDir(pathname, attributes); pathname optional arguments, string expressions used to specify file names, and may contain directories or folders, and drives. If no pathname is found, the zero length string (") is returned; the attributes optional parameter. A con

31、stant or numeric expression whose sum is used to specify file attributes. If omitted, a file that matches pathname but does not contain attributes is returned.2) deleteKill pathname removes files from disk, and the pathname parameter is used to specify a file nameRmDir pathname removes the deleted d

32、irectory from disk, and the pathname parameter is used to specify a folder3) openOpen, pathname, For, mode, Access, access, lock, As, #filenumber, Len=reclength can be input / output (I/O) to the file.Pathname necessary. String expression. Specifies the file name. The file name may also include dire

33、ctories, folders, and drives.Mode necessary. Keyword specifies the file mode, Append, Binary, Input, Output, or Random. If not specified, open the file in Random access mode.Access optional. Keyword describes the operations that an open file can perform, Read, Write, or Read Write operations.Lock op

34、tional. Keyword that describes the operations that are restricted to files opened by other processes, such as Shared, Lock, Read, Lock, Write, and Lock Read Write operations.Filenumber necessary. A valid file number ranging from 1 to 511. Using the FreeFile function, you can get the next available f

35、ile number. Reclength optional. A number less than or equal to 32767 (bytes). For files that are opened by random access, the value is the length of the record. For sequential files, the value is the number of buffered characters.The document must be opened before any I/O operation is done. The Open

36、 statement allocates a buffer for the file to be used by the I/O and determines how the buffer will be used.If the file specified by pathname does not exist, you can build this file when you open the file in Append, Binary, Output, or Random mode. If the file has been opened by another process and d

37、oes not allow the specified access type, the Open operation fails and there is an error. If mode is the Binary method, the Len clause is ignored.It is important to open the same file with different file numbers in the Binary, Input, and Random modes without closing the file first. In Append and Outp

38、ut, if you want to open the same file with different file numbers, you must close the file before opening the file.4) read inInput, #filenumber, and varlist read data from the opened sequential file and assign data to variablesGet, #filenumber, recnumber, and Varname read an open disk file into a variable.5) writeWrite, #filenumber, and outputlist write data to the sequential filePrint, #filenumber, and outputlist write the displayed data to the sequential filePut, #filenumber, recnumber, and Varname write the data of a variable to