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）第六節(jié)注釋和賦值語句1）注釋語句是用來說明程序中某些語句的功能和作用；VBA中有兩種方法標識為注釋語句。ü單引號”；如：“定義全局變量；可

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

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

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

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

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

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

20、立的功能。過程可以使程序更清晰、更具結(jié)構(gòu)性。VBA具有四種過程：子過程、功能函數(shù)、財產(chǎn)屬性過程和事件事件過程。一子過程。子過程的參數(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)用過程方式：1。電話過程名（參數(shù)1，參數(shù)2）；2。過程名參數(shù)1，參數(shù)2debug.print X1，Y1的結(jié)果是12、112，Y1按地址傳遞改變了值，而X1按

21、值傳遞，未改變原值端子二功能函數(shù)。函數(shù)實際是實現(xiàn)一種映射，它通過一定的映射規(guī)則，完成運算并返回結(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。作為一個表達式放在=右端；2。作為參數(shù)使用debug.print X1最后如果端子三財產(chǎn)事件過程屬性過程和事件。這是VB在

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

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

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

25、ng（表達）轉(zhuǎn)換為長型CSng（表達）轉(zhuǎn)換為單型CStr（表達）轉(zhuǎn)換為字符串型CVar（表達）轉(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