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