vba編程取整

『壹』 excel vba常用代碼

Visual Basic for Applications（簡稱VBA）是新一代標准宏語言，是基於Visual Basic for Windows 發展而來的。它與傳統的宏語言不同，傳統的宏語言不具有高級語言的特徵，沒有面向對象的程序設計概念和方法。而VBA 提供了面向對象的程序設計方法，提供了相當完整的程序設計語言。VBA 易於學習掌握，可以使用宏記錄器記錄用戶的各種操作並將其轉換為VBA 程序代碼。這樣用戶可以容易地將日常工作轉換為VBA 程序代碼，使工作自動化。[1]

數據類型

基本數據類型

即Primary Type Data，下述列表的括弧內為位元組數：

• Byte(1)：無符號數類型，取值范圍0-255

• Boolean(2)

• Integer(2)

• Long(4)

• Single(4)

• Double(8)

• Currency(8)

• Decimal(14)

• Date(8)

• String

• Object(4)

• Variant（根據分配確定）[2]

自定義的數據類型

相當於C語言的struct，例如：[2]

Type 自定義類型名 元素名 As 類型 … [元素名 As 類型] End Type

數組

• Option Base 0 ：數組索引值從0開始[2]

• Option Base 1 ：數組索引值從1開始

• Dim MyArray(10) ：聲明一個數組變數，10是最大的可用的數組索引值

• MyArray(5) = 101 ：給數組的元素賦值

• Dim Data(10,5) ：聲明一個二維數組變數

• Data(1,1) = "A001" ：給數組元素賦值

• Dim cArr(-11 To 20, 1 To 3) As String ：聲明一個數組，定義數組索引值的上下界

• Dim dArr() As String ：聲明動態數組

• ReDim dArr(0 To 5, 1 To 2) ：改變動態數組的尺寸默認把原數據清除。如果保留原來的數據，必須加上參數

• Preserve：使用Preserve參數時只能改變最後一位的大小

• If UBound(vTemp) = -1 Then：判斷數組變數vTemp是否為空數組

• End If Erase MyArrar, Data Erase語句清除數組元素，釋放變數佔用的空間[2]

常量

編輯

系統定義常量

系統定義常量有3個：True、False和Null。[4]

固有常量

固有常量是編程時引用的對象庫定義的常量。所有固有常量都可以在宏或VBA代碼中使用。通常，固有常量通過前兩個字母來指明定義該常量。來自VB庫的常量則以「vb」開頭。來自Access的常量以「ac」開頭。可以使用對象瀏覽器來查看所有對象庫中的固有常量列表。[4]

在VBA中，常量的數據類型有整型、長整型、單精度型、雙精度型、位元組型、貨幣型、字元型、日期型和邏輯型。一個整型數據就是一個整型常量，一個長整型數據就是一個長整型常量。例如，12%、-1%是整型常量，32768&、10000000&是長整型常量，-2.5 1、3.14是單精度實型常量，3.1415926#是雙精度實型常量，China、Shanghai是字元型常量，#07/13/2001 11:45PM#是日期常量，由符號 「 # 」 將字元括起來。[4]

符號常量

可以自行定義的常量即符號常量，必須先定義，後使用。可見，需要聲明的常量都是符號常量。[4]

基本語法格式:

1
[Publicr/Private]Const常量名[As類型]=表達式

如：Global Const符號常量名稱 = 常量值[4]

語句功能:

定義一個符號常量，並將指定表達式的值賦給符號常量。[4]

語句說明如下:[4]

1)「常量名」指定符號常量的名字。符號常量名可以由字母、數字和下畫線組成，但只能以字母開頭，不能含有空格。[4]

2)「表達式」指定符號常量的值。該表達式通常由數值型、字元型、邏輯型或日期型數據以及各種運算符組成，但在表達式中不能出現變數和函數。[4]

3) public用來表示這個常量的作用范圍是整個資料庫的所有模塊。[4]

4) private則表示這個常量只在使用該聲明常量語句的模塊中起作用。[4]

說明：

1)除用戶定義的符號常量外，VBA還提供了許多符號常量，我們可以直接使用。[4]

2)對數碼比較長，並且在程序中多次使用的常量，通常使用符號常量代替。運行程序時，系統自動把程序中的所有符號常量換為賦給它的值。[4]

變數

編輯

與常量一樣，變數也是一塊內存空間，用於保存程序運行過程中可能變化的數據。變數的名稱是用戶定義的一個標識符。[5]

在代碼中需要使用該變數時，只需引用相應的標識符即可，而不用管變數當前的值具體是什麼。[5]

聲明

與用戶自定義的常量相似，變數在使用之前都需要聲明，在VBA中聲明變數的語法格式有以下幾種：[5]

• 關鍵字變數名 AS數據類型

• 關鍵字變數1，變數2 ... 變數n AS數據類型

• 關鍵字變數1 AS數據類型，變數2 As數據類型， ... ，變數n AS數據類型

在第二種語法格式中， 「變數n」的數據類型為AS關鍵字後定義的數據類型，而「變數1」、「變數2」、…的數據類型為變體型。[5]

在VBA中，可用Dim、Private、Public和Static這4個關鍵字來聲明變數，使用不同關鍵字聲明的變數其含義也有所不同。[5]

◆ 利用Dim關鍵字聲明變數：Dim關鍵字主要用來在內存中分配一塊空間，並為該空間命名，是VBA中聲明變數最常用的關鍵字。使用Dim關鍵字聲明的變數只能在當前過程或模塊中使用。[5]

◆ 利用Private關鍵字聲明變數：Private關鍵字用於在類模塊中聲明一個私有變數，它只能在當前的類模塊中使用。當定義變數的位置同處於類模塊中時，其使用效果與使用Dim關鍵字定義的變數相同。[5]

◆ 利用Public關鍵字聲明變數：利用Public關鍵字聲明的變數可以在程序的任何地方調用，而與聲明變數的位置無關。[5]

◆ 利用Static關鍵字聲明變數：Static關鍵字用於聲明靜態變數，即變數的值在整個代碼運行期間都能被保留。[5]

賦值

變數在使用時還需要對其進行賦值。在VBA中對變數進行賦值可通過 「 = 」 符號或 「 Set 」 關鍵字進行。通過 「 = 」 符號對變數賦值的語法格式有以下幾種。[5]

• 變數名=數據

• 變數1=變數2 運算符 數據

• 變數1=變數2 運算符 變數3 … 運算符 變數n

如果在定義變數時指定了變數的數據類型，則為變數所賦的值也必須是該數據類型的值。如果變數定義為Integer類型，而在賦值時卻給了變數一個String類型的數據，則在編譯運行的過程中將彈出錯誤彈框。[5]

運算符

編輯

運算符是在程序中執行計算功能的某些特殊符號，它是程序代碼的重要組成部分。在程序代碼中，運算符不能單獨使用，必須與其操作數共同組成表達式後才具有運算意義。VBA中的運算符包含算術運算符、連接運算符、比較運算符和邏輯運算符等。[4]

算術運算符

算術運算符主要用於執行四則運算，僅用算術運算符連接起來的表達式稱為算術表達式。算術運算符及其作用與示例如表所示。[5]

例子

名稱

結果

-$a

取反

$a 的負值。

$a + $b

加法

$a 和 $b 的和。

$a - $b

減法

$a 和 $b 的差。

$a * $b

乘法

$a 和 $b 的積。

$a / $b

除法

$a 除以 $b 的商，允許小數。

$a $b 除法 $a 除以 $b 的商，結果取整。

$a Mod $b

取余

$a 除以 $b 的余數

注意：

在執行算術運算時，運算符兩側操作的數據類型必須相同，否則會出現「類型不匹配」的錯誤提示。當「+」運算符左右兩側都是字元串類型的操作數時，執行的是連接運算，如表達式「"Pass+"word"的計算結果為字元串「 PassWord 」。[5]

連接運算符

連接運算符的作用是將運算符兩側的操作數連接成一個數，其操作數的數據類型通常都為String類型。VBA中的連接運算符有「&」和「+」兩種，由於使用「+」執行連接運算時，容易與加法運算混淆，所以通常都採用「&」進行連接運算。[5]

比較運算符

比較運算符可以對運算符兩側的操作數執行比較運算，其返回結果為Boolean類型的True或False。比較運算符的操作數通常為具體的數值，當操作數為字元串或其他符號時，是根據該符號的ASCII碼進行比較的。VBA中的比較運算符及其作用與示例如表所示。[5]

例子

名稱

結果

$a = $b

等於

TRUE，如果 $a 等於 $b。

$a <> $b

不等

TRUE，如果 $a 不等於 $b。

$a < $b

小於

TRUE，如果 $a 嚴格小於 $b。

$a > $b

大於

TRUE，如果 $a 嚴格大於 $b。

$a <= $b

小於等於

TRUE，如果 $a 小於或者等於 $b。

$a >= $b

大於等於

TRUE，如果 $a 大於或者等於 $b。

邏輯運算符

邏輯運算符用於對運算符兩側的操作數執行邏輯運算，參與邏輯運算的操作數本身可以是邏輯表達式(表達式的最終結果為True或 False)，也可以是算術表達式(表達式的最終返回值為0或非0)。[5]

在邏輯運算中，數值0與邏輯值 False相同，表示邏輯假；非0數值與True相同，表示邏輯真。邏輯運算符及其含義與示例如表所示。[5]

例子

名稱

結果

$a and $b

And（邏輯與）

TRUE，如果 $a 與 $b 都為TRUE。

$a or $b

Or（邏輯或）

TRUE，如果 $a 或 $b 任一為TRUE。

$a xor $b

Xor（邏輯異或）

TRUE，如果 $a 或 $b 同位相異。

Not(1>2)

Not（邏輯非）

TRUE。

(1>2) Eqv (1>2)

兩個操作數同為假，返回False;兩個操作數同為真，返回True；兩個操作數一真一假，返回False

TRUE。

2<>1 Imp 3<5

運算符左右同為真,返回True;左右同為假,返回True;左真右假,回返 False;左假右真,返回True

TRUE。

語法結構

編輯

if 語句

1
if條件1then語句1elseif條件2then語句2elseif......else語句nendif

Select Case 語句

1
SelectCase表達式Case表達式列表1語句1Case表達式列表2語句2...Case表達式列表n語句nEndSelect

其中的表達式列表可以為：[5]

• 表達式例： "A"

• 用逗號分隔的一組枚舉表達式例：2,4,6,8

• 表達式1 To表達式2 例：60 To 100

• Is關系運算符表達式 例：Is < 60

Do...Loop 語句

1
DoWhile或Until條件語句塊1ExitDo語句塊2Loop

For...Next語句

1
Do語句塊1ExitDo語句塊2LoopWhile或Until條件

For Each … Next語句

1
For循環控制變數=初值To終值Step步長語句塊『ExitFor語句可以跳出循環體Next

跳出本次循環的continue語句

1
For循環控制變數=初值To終值Step步長Do'用於模擬continue語句塊If條件ThenExitDo'用於模擬continue語句塊LoopWhileFalse'用於模擬continueNext

With語句

1
With對象引用語句塊EndWith

On Error語句

1
OnErrorGoto出錯處理語句的label'跳轉到出錯處理語句

或

1
OnErrorResumeNext'遇到錯誤，不管錯誤，繼續往下執行

具有控製作用的函數

• If(條件式,表達式1,表達式2)[5]

• Switch(條件式1,表達式1,[條件式2,表達式2[,...,條件式n,表達式n]])

• Choose(索引式,選項1[,選項2,...[,選項n]]) '這是基於1的索引

其他語句

編輯

注釋語句

使用REM或者單引號開始的行。[6]

語句的連寫與續行

如果一行包括多條語句，用冒號分割各個語句。跨多行的語句，在行末用「空格加下劃線」表示續行[6]。

過程與函數

編輯

12
Sub過程名(參數表)語句塊ExitSub語句塊EndSub
1
Function函數名(參數表)AsType語句塊函數名=表達式ExitFunctionEndFunction

可以是Private、Public、Friend、Static等修飾。[6]

調用函數/過程時，可以加括弧或者不加括弧。如果調用表達式作為一行的一部分，那麼必須用參數，例如函數調用的返回值賦給變數。 調用過程時， 可以使用/不使用call關鍵字。使用call語句調用過程，如果無參數，則不加括弧；如果有參數，必須加括弧。如果調用時用括弧包住單個參數，則該參數強行按值傳遞。需要特別注意，不用call不加括弧的調用，形參與實參是傳值(passed by value)而不是傳引用(passed by reference)，這會導致一些對象的方法調用失敗。例如：[6]

1
DimcnAsADODB.ConnectionSetcn=CurrentProject.ConnectionDimrsAsNewADODB.Recordsetrs.Open"SELECT*FROMmyTable",cnDimExcelAppAsNewExcel.=ExcelApp.Workbooks.Add.Worksheets(1)ExcelWst.Range("A2").CopyFromRecordset(rs)'失敗，無法執行該行ExcelWst.Range("A2").CopyFromRecordsetrs'可成功執行該行

常用內置函數

編輯

VBA的常用內置函數：[5]

• MsgBox

• InputBox

• 舍入函數：Fix 向0取整,Int向下取整, Round四捨五入

• Rnd： 返回0-1內的單精度隨機數

• 字元串函數：[5]

• Filter：對字元串的一維數組的過濾

• InStr([Start, ]<Str1>,<Str2>[, Compare])與InStrRev： 查找子串

• Len：字元串長度

• Join：連接一維數組中的所有子字元串

• Left,Right,Mid： 截取子字元串

• Space(數值) ：生成空格字元串

• Ucase,Lcase：大小寫轉換函數

• Ltrim, Rtrim,Trim ：刪除首尾空格

• Replace

• Split：分割一個字元串成為一維數組

• StrComp：字元串比較

• StrConv：字元串轉換

• String(number, character)：制定字元重復若干次

• StrReverse

• 日期/時間有關函數：[5]

• Year, Month, Day, WeekDay,Hour,Minute,Second： 截取日期時間分量

• DateAdd： 日期/時間增量函數

• DateDiff(<間隔類型>,<日期1>,<日期2>[,W1][,W2])日期/時間的距離函數

• DatePart(<分割類型>,<日期>[,w1][,w2])時間分割函數

• DateSerial(<表達式1>,<表達式2>,<表達式3>) 合成日期；DateValue(「字元串表達式」)返回日期；

• Date,Time,Now,Timer： 返回日期時間

• DateValue

• TimeSerial：由時間序列得到時間對象

• TimeValue：由時間字元串得到時間對象

• Weekday：獲得日期的周幾

• WeekdayName

• 轉換函數：CBool、CByte、CCur、CDate、 CDbl、CDec、CInt、CLng、CLngLng、CLngPtr、[5]CSng、CStr、CVar、CVErr、Asc(<字元串表達式>)返回第一個字元的Ascii編碼值、Chr(ASCII碼)返回字元、Hex、Oct、Str(<數值表達式>)返回字元串、Val(string)、Format、FormatCurrency、FormatDateTime、FormatNumber、FormatPercent、MonthName。

• Nz(表達式或欄位屬性值[,規定值])如果是空，則返回0或者""或者函數的第二個參數值[5]

• 驗證函數：isNumeric、isDate、isNull、isEmpty、IsArray、IsError、IsMissing、IsObject[5]

• 數學函數：Abs、Sqr、Tan、Atn（即atan）、Sin、Cos、Exp（e為基的指數）、Log自然對數[5]

• Array：構造一個Array對象[5]

• CallByName: get or set a property, or invoke a method at run time using a string name.[5]

• 控制流：Choose:類似於C語言的select語句、If相當於IF-ELSE語句、Switch[5]

• Command：獲取命令行參數[5]

• CreateObject：創建ActiveX對象

• CurDir：返回指定驅動器的當前工作路徑[5]

• 由基本數學函數導出的函數：Sec、Cosec、Cotangent、Cotan、Arcsin、Arccos、Arcsec、Arccosec、Arccotan、HSin、HCos、HTan、HSec、HCosec、HCotan、HArcsin、HArccos、HArctan、HArcsec、HArccosec、HArccotan、LogN

• DoEvents：暫時把CPU控制權交回給系統[5]

• Environ：返回環境變數的值[5]

• 文件操作：[5]

• Dir：返回滿足條件的所有文件、目錄的名字

• EOF

• FileAttr

• FileDateTime

• FileLen

• FreeFile Function

• GetAttr：返迴文件、目錄的屬性值

• Input：讀取文件

• Loc：文件指針位置

• LOF：文件打開時的指針位置

• Seek：文件指針定位

• Spc：使用Print做position output

• Tab：用於Print函數

• Error:錯誤號對應的錯誤消息[5]

• Windows Registry中的數據：GetAllSettings、SaveSetting、DeleteSetting、GetSetting[5]

• GetObject：ActiveX組建的引用[5]

• IMEStatus：返回當前Input Method Editor (IME)[5]

• Macintosh平台：MacID、MacScript[5]

• 金融函數：[5]

• DDB：使用double-declining balance計算貶值

• FV:計算固定利率的年金

• IPmt：計算利率

• IRR：計算利率

• MIRR：計算利率

• NPer：計算周期數

• NPV：計算net present value

• Pmt：計算支付數

• PPmt：計算本金支付數

• PV：計算present value

• Rate：利息率

• SLN：straight-line depreciation

• SYD：計算sum-of-years' digits depreciation

• Partition：返回字元串，表示一個數值名字落在各個range內。常用於SQL select語句[5]

• QBColor：顏色值[5]

• RGB：顏色值[5]

• TypeName：得到變數的類型名[5]

• VarType：得到變數的類型數[5]