EXCEL VBA筆記

    不怕學得雜，就怕學得雜之後過段時間什麼都忘掉了，還是把筆記貼這裡吧

一．        EXCEL VBA 基礎文法

1．  對變量或對象屬性指派使用等号（=），對變量進行指派使用set或:=，如：

Set myobj=oldobj或myobj:=oldobj

2．  基本語句

長語句可以用 空格+下劃線 換行

(1) If then  [else]

或If then  elseif then  elseif then …..else  end if

(2) Select case …case …end case，如：

Select case PID

Case “a101”

        Price=100

Case “a102”

        Price=200

……..

Case else

        Price=900;

End case

(3) choose(index,choice1,hoice2….)

        用來選擇由index指定的後面的一列資料，下标由1開始，如：

gotchoice = Choose(1, "11111", "2222")，傳回“11111”

    (4) Switch(expr-1,value-1[,expr-2,value-2_[,expr-n,value-n]])

              如果expr-1為真，則傳回value-1，否則傳回expr-2

    (5) For counter=start to end [Step step]               ' step預設值為1

[statements]

[Exit For]                      '在這裡在達到某種條件是退出循環

[statements]

Next counter

如：

For Words=10To1 Step-1               ' 建立10次循環

    For Chars=0 To 9                  ' 建立10次循環

      MyString=MyString & Chars       ' 将數字添加到字元串中

    Next Chars                       ' Incrementcounter

    MyString=MyString & " "            ' 添加一個空格

Next Words

    (6) For Each…in Next語句 主要功能是對一個數組或集合對象進行，讓所有元素重複執行一次語句

For Each element In group

Statements

[Exit for]

Statements

Next[element]

    (7) Do {while |until} condition

Statements

Exitdo

Loop

或者是

Do

Statements

Exitdo

Loop {while |until} condition  如：

Dim a As Integer

a = 90

Do While a < 100

a = a + 6

Loop

      (8) on expression goto

              On error goto

3．  資料類型：string,integer,…..

字元串的合并采用關鍵字：&，如

        Dim myStr as String

        Dim i as integer

        i=10

        myStr= “the I is “ & i     ‘輸出the I is 10

系統會自動将其他類型的資料轉換為字元串形式

常用的字元串函數有：

Strcomp(str1,str2)，傳回的值有-1,0,1

Mid(strSource,startIndex,len)，取出字元串strSource第startIndex開始的Len個字元，下标是從1開始的

Left(strSource,len)，取strSource裡從左邊開始的Len個字元

Vba.trim(strSource)，去除strSource裡面的空格,對應類似的函數還有vba.trimLeft(strSource),vba.trimRight(strSouce)，分别去除左邊的空格和右邊的空格

split(strSouce,strDelimiter),分割字元串

類型轉換：

CInt(sourceContent)，将sourceContent裡的資料轉換成整形

CSng(sourceContent)，将sourceContent裡的資料轉換成浮點型

CDate（sourceContent），将sourceContent裡的資料轉換成日期型資料，日期型資料可以進行加減運算

自定義資料類型，相當于C裡面的結構體，使用Type typeName …..end Type，應将其定義到子產品中，如：

Type DealInfo

strDate As String

startIndex As Integer

endIndex As Integer

End Type

4．  過程和函數

VBA具有四種過程：Sub過程、Function函數、Property屬性過程和Event事件

1Sub過程

Sub過程的參數有兩種傳遞方式：按值傳遞(ByVal)和按位址傳遞(ByRef)。如下例：

    Sub password(ByVal x as integer,  ByRef y as integer)

       If y=100theny=x+ y else y=x-y

       x=x+100

End sub

預設的情況是按byRef傳遞的

       2function 和sub過程類似，但是可以有傳回值，傳回值傳遞給函數名

       Function password(ByVal x as integer,byref y as integer)as boolean

       If y=100then y=x+y else y=x-y

       x=x+100

       if y=150 then password=true else password=false

End Function

       函數和過程的級别有工作表級和工作簿級，寫在工作表上的函數和過程一般開頭都有private限定符，這表示這個函數（或過程）隻能由目前工作表裡的其他函數調用，而工作簿級的函數則一般寫在子產品中，限定符使用Public，這時它可以被所有的函數或過程調用，還可以在EXCEL的表單裡使用這個函數。

       調用過程：call subName(…)

       調用函數：

                     Dim retureVal as type

                     returnVal=FuncName(…)

5．  數組：包括靜态數組和動态數組

靜态數組定義：dim arrayName( length) as type       一維數組

         dim arrayName(row,colum) as type      二維數組

預設的情況下，數組的下标是從0開始的

動态數組定義：dim arrayName( ) as type

這樣定義的時候沒有配置設定任何記憶體空間，在使用之前，必須用redim對其進行初始化，如：

Dim stuName() as string

Redim stuName(10)

……….指派

Redim preserve stuName(20)    ‘增加長度為20，并保留原來前10個資料

二．        操作EXCEL 對象

EXCEL裡面的對象根據不同級别有：application，workbook，worksheet，range，cells

Application：

是可用的頂級對象，由于所有的EXCEL VBA程式都是以EXCEL作為容器來運作的，是以Application對象就是EXCEL這個程式本身

       Workbook：

              是一個EXCEL檔案

       Worksheet：

是EXCEL檔案裡的一個工作表，可以使用worsheets（”sheetName”）或worksheets(index)即可獲得相應的工作表對象

       Range：

是一個工作表裡的區域，可以為不互相連通的多個區域集合，通過指定區域來獲得，如Range(“A1:B3”)則獲得目前工作表裡的A1到B3的所有單元格的集合，構成一個區域。如果是多個區域，可以用逗号分開，如Range(“A1:B3,C4:D5”)，使用Range可以對一個區域進行指派，如Range(“A2:D10”)=””，為将A2到D10的内容清空

       Cells：

工作表裡的單元格，根據Cells(rowIndex,columIndex)獲得指定的單元格，下标是從1開始的，而非0。如Cells(1,2).value=”test”，是将B1的值設為test

Selection：

        表示目前被選中對象

Excel裡的對象的獲得都是通過OLE接口調用獲得的，而OLE接口的調用是費時的，是以在代碼裡，如果要多次用到一個從其上級對象下溯到的對象，應該先取出這個的對象的引用，後面的操作都在這個引用上面進行。同時如果要在一個對象上設定多個屬性的值，應該使用with語句，如：

With thisObj

       .property1=val1

       .property2=val2

       ………………..

End with

使用EXCEL表單函數，在excel資料界面可以使用的函數，在程式裡面也可以使用，都被封裝在WorksheetFunction對象中，例如要獲得A列的非空單元個數，可以使用

WorksheetFunction.Counta(Range("A:A"))

三．        學習EXCEL VBA程式設計的方法

EXCEL裡面幾乎所有的操作都可以錄制成宏，是以如果要用代碼實作那些可以用錄制宏實作的功能，但是又不知道一些屬性和方法，就可以先把這些操作錄制成宏，然後檢視其宏代碼，了解其所用到的屬性和方法，然後自己就可以寫出精簡版的代碼了