vba語(yǔ)言基礎(chǔ)（VBA language foundation）

1、vba語(yǔ)言基礎(chǔ)（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）第六節(jié)注釋和賦值語(yǔ)句1）注釋語(yǔ)句是用來(lái)說(shuō)明程序中某些語(yǔ)句的功能和作用；VBA中有兩種方法標(biāo)識(shí)為注釋語(yǔ)句。ü單引號(hào)”；如：“定義全局變量；可

14、以位于別的語(yǔ)句之尾，也可單獨(dú)一行üREM；如：REM定義全局變量；只能單獨(dú)一行2）賦值語(yǔ)句是進(jìn)行對(duì)變量或?qū)ο髮傩再x值的語(yǔ)句，采用賦值號(hào)=，如x = 123：1。標(biāo)題=“我的窗口”對(duì)對(duì)象的賦值采用MyObject對(duì)象：集或MyObject對(duì)象：= =第七節(jié)書(shū)寫(xiě)規(guī)范1）VBA不區(qū)分標(biāo)識(shí)符的字母大小寫(xiě)，一律認(rèn)為是小寫(xiě)字母；2）一行可以書(shū)寫(xiě)多條語(yǔ)句，各語(yǔ)句之間以冒號(hào)：分開(kāi)；3）一條語(yǔ)句可以多行書(shū)寫(xiě)，以空格加下劃線_來(lái)標(biāo)識(shí)下行為續(xù)行；4）標(biāo)識(shí)符最好能簡(jiǎn)潔明了，不造成歧義。第八節(jié)判斷語(yǔ)句1）如果那么其他語(yǔ)句如果條件再報(bào)表 其他elsestatements 1：如果如> B和CD = B +

15、 2人= C + 2如2：如果x > 250 x = X-100或者，可以使用塊形式的語(yǔ)法：如果條件再聲明“condition-n然后 elseifstatements 其他 elsestatements 最后如果如1：如果數(shù)字是10，那么位數(shù)= 1“數(shù)100則位數(shù)= 2其他的位數(shù)= 3最后如果2）選擇案例案例案例語(yǔ)句結(jié)束如1：選擇案例的PID“它”價(jià)格= 200案例“A102”價(jià)格= 300.其他案件價(jià)格= 900案例3）選擇函數(shù)選擇（指數(shù)，choce-1，choice-2，choice-n），可以用來(lái)選擇自變量串列中的一個(gè)值，并將其返回，指數(shù)必要參數(shù)，數(shù)值表達(dá)式或字段，它的運(yùn)算結(jié)果是

16、一個(gè)數(shù)值，且界于1和可選擇的項(xiàng)目數(shù)之間。選擇必要參數(shù)，變表達(dá)式，包含可選擇項(xiàng)目的其中之一。如：getchoice =選擇（IND，“快捷”、“團(tuán)結(jié)”、“聯(lián)邦”）4）開(kāi)關(guān)函數(shù)開(kāi)關(guān)（expr-1，1值，expr-2，value-2 _ expr-n value-n ，）開(kāi)關(guān)函數(shù)和選擇函數(shù)類似，但它是以兩個(gè)一組的方式返回所要的值，在串列中，最先為真的值會(huì)被返回。expr必要參數(shù)，要加以計(jì)算的變異表達(dá)式。價(jià)值必要參數(shù)。如果相關(guān)的表達(dá)式為真實(shí)，則返回此部分的數(shù)值或表達(dá)式，沒(méi)有一個(gè)表達(dá)式為真空開(kāi)關(guān)會(huì)返回一個(gè)值。第九節(jié)循環(huán)語(yǔ)句1）下語(yǔ)句以指定次數(shù)來(lái)重復(fù)執(zhí)行一組語(yǔ)句反=開(kāi)始到結(jié)束的一步一步一步缺省值為 1 聲

17、明退出聲明下計(jì)數(shù)器如1：詞= 10到1步1建立10次循環(huán)對(duì)于字符= 0到9建立10次循環(huán)mystring = mystring &字符的將數(shù)字添加到字符串中下一個(gè)字符增量計(jì)數(shù)器mystring = mystring &“”'添加一個(gè)空格接下來(lái)的話2）每一語(yǔ)句主要功能是對(duì)一個(gè)數(shù)組或集合對(duì)象進(jìn)行，讓所有元素重復(fù)執(zhí)行一次語(yǔ)句組中的每個(gè)元素聲明退出聲明下一個(gè)元素如1：在每一rang2范圍與erior顏色= 6xlsolid模式=。結(jié)束下一個(gè)這上面一例中用到了以結(jié)束語(yǔ)句，目的是省去對(duì)象多次調(diào)用，加快速度；語(yǔ)法為：與對(duì)象聲明結(jié)束3）做環(huán)語(yǔ)句在條件為真時(shí)，重復(fù)執(zhí)行

18、區(qū)塊命令 而做|直到條件而為當(dāng)型循環(huán)，直到為直到型循環(huán)，顧名思義，不多說(shuō)啦聲明退出聲明環(huán)或者使用下面語(yǔ)法做先做再判斷，即不論如何先干一次再說(shuō)聲明退出聲明循環(huán)而|直到條件第十節(jié)其他類語(yǔ)句和錯(cuò)誤語(yǔ)句處理一。其他循環(huán)語(yǔ)句結(jié)構(gòu)化程序使用以上判斷和循環(huán)語(yǔ)句已經(jīng)足夠，建議不要輕易使用下面的語(yǔ)句，雖然VBA還支持。1）轉(zhuǎn)到線該語(yǔ)句為跳轉(zhuǎn)到線語(yǔ)句行2）表達(dá)此時(shí)destinatioinlist或者表達(dá)去destinationlist語(yǔ)句為根據(jù)表達(dá)表達(dá)式值來(lái)跳轉(zhuǎn)到所要的行號(hào)或行標(biāo)記3）此時(shí)，行行返回語(yǔ)句，返回返回到轉(zhuǎn)子線行，如下例：子gosubtry()昏暗的民Num = InputBox（“輸入一個(gè)數(shù)字，此值將

19、會(huì)被判斷循環(huán)”）如果num > 0然后跳轉(zhuǎn)routine1：debug.print Num：退出routine1：5返回端子4）而走語(yǔ)句，只要條件為真實(shí)，循環(huán)就執(zhí)行，這是以前VB老語(yǔ)法保留下來(lái)的，如下例：當(dāng)“我50”時(shí)語(yǔ)句 i = i + 1外面的風(fēng)二。錯(cuò)誤語(yǔ)句處理執(zhí)行階段有時(shí)會(huì)有錯(cuò)誤的情況發(fā)生，利用誤差語(yǔ)句來(lái)處理錯(cuò)誤，啟動(dòng)一個(gè)錯(cuò)誤的處理程序。語(yǔ)法如下：OnErrorGoToLine”當(dāng)錯(cuò)誤發(fā)生時(shí)，會(huì)立刻轉(zhuǎn)移到線行去錯(cuò)誤恢復(fù)的當(dāng)錯(cuò)誤發(fā)生時(shí)，會(huì)立刻轉(zhuǎn)移到發(fā)生錯(cuò)誤的下一行去在不去0當(dāng)錯(cuò)誤發(fā)生時(shí)，會(huì)立刻停止過(guò)程中任何錯(cuò)誤處理過(guò)程第十一節(jié)過(guò)程和函數(shù)過(guò)程是構(gòu)成程序的一個(gè)模塊，往往用來(lái)完成一個(gè)相對(duì)獨(dú)

20、立的功能。過(guò)程可以使程序更清晰、更具結(jié)構(gòu)性。VBA具有四種過(guò)程：子過(guò)程、功能函數(shù)、財(cái)產(chǎn)屬性過(guò)程和事件事件過(guò)程。一子過(guò)程。子過(guò)程的參數(shù)有兩種傳遞方式：按值傳遞（ByVal）和按地址傳遞（ByRef）。如下例：子密碼（ByVal x為整數(shù)，ByRef y為整數(shù)）如果y = 100然后y = x + y否則y = x-yx100端子子call_password（）亮度為整數(shù)昏暗的Y1為整數(shù)X1 = 12Y1 = 100（x1，y1）電話密碼的調(diào)用過(guò)程方式：1。電話過(guò)程名（參數(shù)1，參數(shù)2）；2。過(guò)程名參數(shù)1，參數(shù)2debug.print X1，Y1的結(jié)果是12、112，Y1按地址傳遞改變了值，而X1按

21、值傳遞，未改變?cè)刀俗佣δ芎瘮?shù)。函數(shù)實(shí)際是實(shí)現(xiàn)一種映射，它通過(guò)一定的映射規(guī)則，完成運(yùn)算并返回結(jié)果。參數(shù)傳遞也兩種：按值傳遞（ByVal）和按地址傳遞（ByRef）。如下例：功能的密碼（ByVal x為整數(shù)，y為整型變量ByRef）布爾如果y = 100然后y = x + y否則y = x-yx100如果y = 150然后密碼=真正的其他密碼= false端功能子call_password（）亮度為整數(shù)昏暗的Y1為整數(shù)X1 = 12Y1 = 100如果密碼然后調(diào)用函數(shù)：1。作為一個(gè)表達(dá)式放在=右端；2。作為參數(shù)使用debug.print X1最后如果端子三財(cái)產(chǎn)事件過(guò)程屬性過(guò)程和事件。這是VB在

22、對(duì)象功能上添加的兩個(gè)過(guò)程，與對(duì)象特征密切相關(guān)，也是VBA比較重要組成，技術(shù)比較復(fù)雜，可以參考相關(guān)書(shū)籍。第十二節(jié)內(nèi)部函數(shù)在VBA程序語(yǔ)言中有許多內(nèi)置函數(shù)，可以幫助程序代碼設(shè)計(jì)和減少代碼的編寫(xiě)工作。一。測(cè)試函數(shù)ISNUMERIC（x）的是否為數(shù)字，返回布爾結(jié)果，真或假IsDate（x）的是否是日期，返回布爾結(jié)果，真或假IsEmpty（X）的是否為空，返回布爾結(jié)果，真或假（x）的指出變量是否為一個(gè)數(shù)組ISArray。IsError（表達(dá)）的指出表達(dá)式是否為一個(gè)錯(cuò)誤值IsNull（表達(dá)）的指出表達(dá)式是否不包含任何有效數(shù)據(jù)（空）。采用（標(biāo)識(shí)符）的指出標(biāo)識(shí)符是否表示對(duì)象變量二。數(shù)學(xué)函數(shù)sin（x）、cos

23、（x）、tan（x）、Atan（x）三角函數(shù)，單位為弧度日志（X）返回X的自然對(duì)數(shù)exp（x）返回前ABS（X）返回絕對(duì)值Int（數(shù)）、修復(fù)（數(shù)）都返回參數(shù)的整數(shù)部分，區(qū)別：int將- 8.4 - 9轉(zhuǎn)換成，而固定將轉(zhuǎn)換成- 8 - 8.4SGN（數(shù)）返回一個(gè)變量（整數(shù)），指出參數(shù)的正負(fù)號(hào)SQR（數(shù)）返回一個(gè)雙，指定參數(shù)的平方根VarType（varname）返回一個(gè)整數(shù)，指出變量的子類型返回0-1之間的單精度數(shù)據(jù)RND（x），x為隨機(jī)種子三。字符串函數(shù)修剪（字符串）去掉字符串左右兩端空白Ltrim（字符串）去掉字符串左端空白R(shí)trim（字符串）去掉字符串右端空白Len（字符串）計(jì)算字符串長(zhǎng)度

24、左（字符串，x）取字符串左段X個(gè)字符組成的字符串右（字符串，x）取字符串右段X個(gè)字符組成的字符串中期（字符串，字符串開(kāi)始，x）取從開(kāi)始位開(kāi)始的X個(gè)字符組成的字符串Ucase（字符串）轉(zhuǎn)換為大寫(xiě)Lcase（字符串）轉(zhuǎn)換為小寫(xiě)空間（X）返回X個(gè)空白的字符串ASC（字符串）返回一個(gè)整數(shù)，代表字符串中首字母的字符代碼CHR（字符碼）返回字符串，其中包含有與指定的字符代碼相關(guān)的字符四。轉(zhuǎn)換函數(shù)CBool（表達(dá)）轉(zhuǎn)換為布爾型CByte（表達(dá)）轉(zhuǎn)換為字節(jié)型CCur（表達(dá)）轉(zhuǎn)換為貨幣型CDate（表達(dá)）轉(zhuǎn)換為日期型CDbl（表達(dá)）轉(zhuǎn)換為雙型CDec（表達(dá)）轉(zhuǎn)換為decemal型CInt（表達(dá)）轉(zhuǎn)換為整數(shù)型CL

25、ng（表達(dá)）轉(zhuǎn)換為長(zhǎng)型CSng（表達(dá)）轉(zhuǎn)換為單型CStr（表達(dá)）轉(zhuǎn)換為字符串型CVar（表達(dá)）轉(zhuǎn)換為變異型瓦邇（字符串）轉(zhuǎn)換為數(shù)據(jù)型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