當前位置:成語大全網 - 新華字典 - excel 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?條件1?then語句1elseif?條件2?then語句2elseif?......else語句nend?if

Select Case 語句

1 Select?Case?表達式Case?表達式列表1語句1Case?表達式列表2語句2...Case?表達式列表n語句nEnd?Select

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

表達式?例: "A"

用逗號分隔的壹組枚舉表達式例:2,4,6,8

表達式1 To?表達式2 例:60 To 100

Is?關系運算符表達式 例:Is < 60

Do...Loop 語句

1 Do?While或Until?條件語句塊1Exit?Do語句塊2Loop

For...Next語句

1 Do語句塊1Exit?Do語句塊2Loop?While或Until?條件

For Each … Next語句

1 For?循環控制變量=初值To?終值Step?步長語句塊?‘Exit?For語句可以跳出循環體Next

跳出本次循環的continue語句

1 For?循環控制變量=初值?To?終值?Step?步長Do?'用於模擬continue語句塊?If?條件?Then?Exit?Do?'用於模擬continue語句塊?Loop?While?False?'用於模擬continue?Next

With語句

1 With?對象引用語句塊End?With

On Error語句

1 On?Error?Goto?出錯處理語句的label?'跳轉到出錯處理語句

1 On?Error?Resume?Next?'遇到錯誤,不管錯誤,繼續往下執行

具有控制作用的函數

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

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

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

其他語句

編輯

註釋語句

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

語句的連寫與續行

如果壹行包括多條語句,用冒號分割各個語句。跨多行的語句,在行末用“空格加下劃線”表示續行?[6]?。

過程與函數

編輯

12 Sub?過程名(參數表)語句塊Exit?Sub語句塊End?Sub ?1 Function?函數名(參數表)?As?Type語句塊函數名=表達式Exit?FunctionEnd?Function

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

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

1 Dim?cn?As?ADODB.ConnectionSet?cn?=?CurrentProject.ConnectionDim?rs?As?New?ADODB.Recordsetrs.Open?"SELECT?*?FROM?myTable"?,?cnDim?ExcelApp?As?New?Excel.ApplicationDim?ExcelWst?As?WorksheetSet?ExcelWst?=?ExcelApp.Workbooks.Add.Worksheets(1)ExcelWst.Range("A2").CopyFromRecordset(rs)?'失敗,無法執行該行ExcelWst.Range("A2").CopyFromRecordset?rs?'可成功執行該行

常用內置函數

編輯

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]?