• 個人巨集活頁簿位置：C:\Users\使用者\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB

參考用書

• Excel VBA+巨集入門：實現零加班、光速般的工作效率, ISBN： 978-986-434-197-9, 博碩文化, http://www.drmaster.com.tw/Bookinfo.asp?BookID=MI11609 

巨集簡介

• 含巨集的Excel檔，副檔名為[*.xlsm]

• 含有巨集的檔案或網路下載的檔案，欲開啟時均須點選「啟用內容」，以免被惡意的程式入侵。這是一種對電腦的保護機制。

• 啟用「開發人員」功能區：檔案\選項\自訂功能區\[V]開發人員。

• 執行巨集的方法：

  • 使用「巨集視窗」執行→點選 巨集名稱→執行

  • 利用「配置巨集的 按鍵」執行

    • 建立按鍵：

      1. 開發人員\控制項\插入\表單控制項\按鈕

      2. 插入\圖例\圖案\矩形→右鍵\指定巨集

• 查看「巨集內容」，「VBE (Visual Basic Editor)」：開發人員\程式碼\Visual Basic。

  • VBE←→Excel：Alt+F11

  • 執行巨集：在巨集內任一點點選→「►執行鈕」或「F5」。

• 巨集可做的事：

  • 數值的輸入

  • 建立圖表

  • 資料計算

  • 資料取出與轉換

  • 須經判斷的工作

  • 須經大量資料且重複動作

• 資料型態：

  • 一個文字：字元

  • 多個文字串接：字串

  • 計算值：數值

• 巨集：多個小動作的集合。

• VBA (Visual Basic for Application)使用的字型均為「半形」，可使用「中文」於「巨集名稱」、「儲存格資料」、「註解」、「變數名稱」。

• 「VBA」就是「巨集」內所使用的程式語言。

巨集編輯

• 新增模組：VBE\插入\模組

• 複製模組：

  1. 來源Excel\VBE→選取欲複製的VBA程式碼→右鍵\複製

  2. 目的Excel\VBE→插入\模組→右鍵\貼上

• 其他物件：Sheet1、ThisWorkbook

• 不同檔案，確有相同名稱的巨集名稱命名法：「活頁簿名稱!巨集名稱」

• 變更巨集內容；修改VBA巨集內容：VBE\直接修改內容

• 變更巨集名稱：變更「sub」之後的巨集名稱

• 巨集名稱 命名規則： 可使用

• 註解：「單引號'」之後的文字均屬於註解，註解的文字會變成「綠色」

• 巨集結構：

Sub 巨集名稱()

巨集內容

End Sub

• VBA的多行巨集內容，成為「陳述式」

• 儲存 含巨集的活頁簿：檔案\另存新檔\ 存檔類型（Excel啟用巨集的活頁簿(*.xlsm)）

• VBA的除錯方法：

  • 以「紅色」顯示

• 接續行：「空白+底線」＝「 _」

• 執行時的錯誤：程式語法沒有錯誤，但卻無法執行。例如：程式中使用某個物件，程式的寫法完全沒錯，但程式執行時卻發現根本沒有該物件，因此程式無法執行。

• 邏輯錯誤：程式語法沒有錯誤，可執行，無錯誤訊息，但不是你要的結果。

• 逐行執行：F8或「 偵錯\逐行」。

• 程式錯誤無法停止的處理方法：

  • 執行時間太長，而凍結畫面：ESC鍵，可中斷程式執行。

  • 程式產生無限回圈，無法以ESC停止，可使用「Ctrl+Alt+Del」呼叫 工作管理員 停止Excel執行。再開啟，可自動利用「檔案修復」功能找回原檔，但也有可能找回不完整檔案。

• 養成「先存檔再執行」的習慣，防止因中斷Excel而喪失之前編寫的程式碼。

物件

• Excel常用的物件：

  • 活頁簿 物件：Workbook

  • 工作表 物件：Worksheet

  • 儲存格 物件：Range

  • 字型 物件：Font

  • 篩選 物件：Filter

  • 圖表 物件：Chart

• 命令物件：「物件.命令」，對「物件」做「命令」。

「集合」指定物件

• 活頁簿 集合：Workbooks

• 工作表 集合：Worksheets

• 儲存格沒有集合。

• 「集合」中的「物件」，稱為「成員」。

• 例題：

  • 第2個活頁簿：Workbooks(2)

  • Sheet2的工作表：Worksheets("Sheet2") 或 Worksheets(2)

儲存格物件管理

• Range

• 例題：

  • 單一儲存格 「C1」：Range("C1")

  • 連續多儲存格 「B1:D5」：Range("B1:D5")

  • 不連續多儲存格 「B1:D5,E10」：Range("B1:D5,E10")

• 取得任意儲存格的位址，可使用「Address屬性」回傳的位址為絕對位址「$A$2」。

指定集合的物件

• 語法：

  • 集合(索引編號)

  • 集合("物件名稱")

• 例題：

  • 指定 test.xlsm 活頁簿

    • Workbooks("test.xlsm") '注意呼叫時，需增加副檔名

    • Workbooks(1) '第1個開啟的活頁簿

  • 指定Sheet2工作表，[Link]

    • Worksheets(2)

    • Worksheets("Sheet2")

• 儲存格指定方法

  • 單一儲存格 C1：Range("C1")

  • 多儲存格 B1:C5 ：Range("B1:C5")

分層結構指定方法

• 「不使用」結構分層 表示法

  • 目前工作表的C1儲存格：Range("C1").Value="AAA"

• 「使用」結構分層 表示法：以「.句點」表示「母物件」與「子物件」的關係，如「母物件.子物件」

  • 於活頁簿test.xlsm中，工作表Sheet1中的儲存格C1：Workbooks("test.xlsm").Worksheets("Sheet1").Range("C1")="AAA"

「目前」物件

• 作用中的 儲存格：ActiveCell

• 作用中的 工作表：ActiveSheet

• 作用中的 活頁簿：ActiveWorkbook

• 正在執行程式碼的 活頁簿；正在VBE編寫程式碼的活頁簿：ThisWorkbook

• 選取的範圍：Selection

• 範例：

  • 作用中儲存格的值：ActiveCell.Value="AAA"

  • 作用中工作表的名稱：ActiveSheet.Name="ABC"

  • 作用中活頁簿的名稱(包含副檔名)：ActiveWorkbook.Name

  • 選取範圍的值：Selection.Value="AAA"

表格篩選

• 語法：Range("A1:H15").AutoFilter Field:=篩選欄位的欄位序號 Criterial:=篩選條件

• 範例：

  • Range("A1:H15").AutoFilter Field:=2, Criteria1:="Man"

  • Range("A1:H15").AutoFilter Field:=4, Criteria1:=">=20"

  • 將「A1:H15」儲存格設定為篩選，及「資料\排序與篩選\篩選」：Range("A1:H15").AutoFilter

  • 設定完篩選完後，若要取消篩選，只要再下一次「Range("A1:H15").AutoFilter」，而不給篩選條件，即可。

屬性與方法

屬性

• 儲存格Range屬性

  • 儲存格的值：Range.Value

  • 儲存格位址：Range.Address (此屬性僅可讀取，不可設定)

  • 儲存格字型：Range.Font

• 讀取 語法：「物件.屬性」

  • 範例：MsgBox(Range("D1").Value)

• 設定 物件屬性質

  • 設定 語法：物件.屬性 = 新的值

  • 範例：「Range("D1").Value = "AAA"」

• 縮排：編寫程式碼時，通常會利用Tab鍵縮排，將同一層的的語法利用縮排，排在同一條直線上，以方便解讀程式。

方法

• 儲存格Range方法

  • 清除儲存格之值、格線、格式(例如：填入色彩)：Clear

  • 刪除儲存格：Range.Delete

  • 選取儲存格：Range.Select

• 語法：「物件.方法」

  • 範例：「Range("A1").Clear」

方法+引數

• Range.Delete方法：儲存格刪除。[Link]

  • 語法：「物件.方法 引數名稱:=設定值」

    • 範例：將儲存格A1刪除，再將左邊資料位移填補，「Range("A1").Delete Shift:=xlShiftToLeft」

  • 語法：省略 引述名稱，「物件.方法 設定值」

    • 範例：將儲存格A1刪除，再將左邊資料位移填補，「Range("A1").Delete xlShiftToLeft」

• Range物件的Find方法：搜尋資料

  • 參考資料：[Link], [Link], LookIn[Link], LookAt[Link]

  • 語法：Range("A1:C10").Find(What:="搜尋的資料",LookIn:=搜尋資料類型,LookAt:=全部搜尋文字或部分搜尋文字相符)

  • 範例：Range("A1:C10").Find(What:="李小龍",LookIn:=xlValues,LookAt:=xlWhole)，於儲存格A1:C10中的資料格值(LookIn:=xlValues)，尋找「李小龍(What:="李小龍")」，且需全部文字相符(LookAt:=xlWhole)。

「物件」、「屬性」、「方法」的搜尋方式

• 查詢說明文件

  • 說明文件-[Link]：VBE\說明\Microsoft Visual Basic for Application說明。

  • 於VBE編輯VBA程式時，於「欲查詢處」點選後，按「F1」。

  • 於網路(https://www.google.com.tw)，查詢關鍵字「Excel VBA 關鍵字」。

  • 查詢一本好的參考書籍。

• 錄製巨集

  • 開發人員\程式碼\錄製巨集→巨集名稱+(將巨集儲存於:現用活頁簿)→開始操作→開發人員\程式碼\停止錄製

  • 遇到不知道的物件、屬性、方法，可先利用巨集錄製產生範例提供參考。不過，電腦產生的程式碼，有可能會有過於冗長，會產生許多不必要的程式碼。

• 字串與數值

  • 字串需利用雙引號「"」，於字串的前後標示。例如："ABCD"

  • 數值直接表示即可。例如：5231

物件資訊指定

• 工作表的名稱 Name：

  • Worksheets(1).Name：第一個工作表的名字

  • 範例：

    • Worksheets(1).Name="TEST"

    • Worksheets(1).Name=Range("C1").Value

• 表格篩選 AutoFilter：[Link]

  • Range("A1:E10").AutoFilter Field:=2, Criteria1:="Man"

    • 引數Field：篩選的欄位序號

    • 引述Criteria1：篩選條件1

• 新增方法Add：[Link]

  • 新增活頁簿：Workbooks.Add

  • 新增工作表-在第2個工作表Worksheets的後面新增一個工作表：Worksheets.Add After:=Worksheets(2)

• ThisWorkbook.Names.Add 新增 範圍名稱：[Link]

  • 範例：ThisWorkbook.Names.Add "範圍名稱",Range("A1:D10")。

• Names.Add 新增儲存格範圍名稱：[Link]

• 刪除方法Delete

  • 刪除工作表：Worksheets(2).Delete

  • 刪除儲存格：Range("A1").Delete Shift:=xlShiftToLeft

  • 刪除範圍名稱：ThisWorkbook.Names("範圍名稱").Delete

運算式與運算子

• 資料輸入

  • 數值：直接輸入

  • 字串：字串前後都要加上「"雙引號」

  • 日期：日期前後都要將上「#」。日期(#月/日/西元年#)、時間(#時:分:秒 AM/PM#)。日期為「序列值」，1為「12/30/1899」

  • DateValue函數：可將字串轉成日期

  • TimeValue函數：可將字串轉成時間

  • 範例：

    • Range("A1").Value = #12/10/2019#

    • Range("A2").Value = #10/29/1973 11:10:59 PM#

    • Range("A3").Value = DateValue("2019/10/15")，將「文字日期」轉換成「日期」。

    • Range("A4").Value = TimeValue("23:15:59")，將「文字時間」轉換成「時間」。

• 算數運算子

  • 加(+)、減(-)、乘(*)、除(/)、取商(\)、取餘數(Mod)、次方(^)

  • 語法：數值1 運算子 數值2

• 字串連接運算子=「&」或「+」

  • 語法：字串1 & 字串2

  • 換行=「vbCrLf」

  • 「&」使用於數字連接時，可當字串連接，但「+」則當成數值計算。除此之外，「＆」和「+」的功能均相同。

• 常用常數 [Link]

• 「即時運算視窗」：VBE\檢視\即時運算視窗

  • 輸入：「? 5+2」

• 帶入運算子=「=」，將等號右邊的結果，存入左邊的變數或物件屬性裡。

• 邏輯判斷 等於「=」

練習

• 乘法：MsgBox(Range.Value("C1").Value*10)

• 除法：MsgBox(Range.Value("C1").Value/10)

• Range.End(方向)屬性：傳回端點儲存格

  • 參考資料 [Link]

  • 傳回 Range 物件，該物件代表包含來源範圍之區域結尾處的儲存格。 相當於按下 Ctrl + 向上鍵、Ctrl + 向下鍵、Ctrl + 向左鍵 或 Ctrl + 向右鍵。

  • 方向 參數：[Link]。 xlUp(上)、xlDown(下)、xlToLeft(左)、xlToRight(右)。

  • 範例：Range("A1000").End(xlUp)，由A1000往上找A欄中，最後一個有資料的儲存格。

• Rnge.Offset(RowOffset,ColumnOffset)：垂直方向移動RowOffset，水平方向移動ColumnOffset。

  • RowOffset：正數，向下移；負數，向上移。

  • ColumnOffset：正數，向右移；負數，向左移。

  • 範例：Range("A1").Offset(1,0)。傳回A1向下1格的儲存格，即A2。

變數應用

• 語法：宣告變數。若為未宣告變數資料型態，則為自動宣告為「Variant 多樣型」。

Dim 變數

變數=值

• 多變數宣告

Dim 變數1,變數2,變數3

• 變數名稱「不分英文大小寫」。

• 語法：宣告「變數」與儲存於變數的「資料型態」

Dim 變數1,變數2 As 資料型態

Dim 變數1 As 資料型態1,變數2 As 資料型態2

• 資料型態

  • 字串：String

  • 整數：Integer

  • 長整數：Long

  • 單精數：Single

  • 雙精數：Double

  • 日期：Date

  • 貨幣：Currency

  • 物件：Object

  • 多樣型：Variant

• 使用「小數」時，常用的資料型態有Single和Double。

• 事先指定資料變數類型的優點：

變數 代入

• 「值」的代入：利用「=」。

  • 變數=值

  • 範例：

    • Dim pr1 As Integer

• 「物件」的代入：需利用「Set」與「=」

  • Set 變數=物件

• 將變數內清除：Set 變數=Nothing

• Range.Copy方法：複製儲存格

  • 將第2個工作表中的「B4:E7」，複製至將第1個工作表中的「F9」：

    • Worksheets(2).Range("B4:E7").Copy Destination:=Worksheets(1).Range("F9")

• Range.AutoFilter方法：若為設定「引數」，即儲存格設為「資料\排序與篩選\篩選」，但沒有設定篩選條件。

  • 「A1:H15」儲存格設定為篩選，及「資料\排序與篩選\篩選」：Range("A1:H15").AutoFilter

• VBA使用變數可以不需要「事先宣告」。為宣告的變數，會以「Variant」型態處理。可代入「值」或「物件」。

• 「強制變數使用前要宣告」，在模組前增加「Option Explicit」指令。也可以直接在VBE內設定，「VBE\選項\編輯器\ V要求變數宣告」。

• 特別 變數宣告

  • Variant，可使用任意「值」和「物件」的資料型態：Dim 變數 As Variant

  • Object，可使用任意「物件」的資料型態：Dim 變數 As Object

• ActiveWorkbook.sheets：sheets代表工作表集合，與Worksheets相同。[Link]

  • 範例：MsgBox ActiveWorkbook.sheets(1).Name，輸出工作中活頁簿的第一個工作表明稱。

• TypeName(sheets)：回傳物件的名稱。[Link]

常數

• 常數：對「特定的固定值」給予「簡單的名稱」。

• 語法：Const 常數名稱 As 資料型態=值

• 常數有(1)自訂常數，(2)內建常數（常以xl或vb開頭）

• Range.Delete方法：刪除儲存格

  • 語法：Range("B2").Delete Shift:=引數

    • 引數=xlShiftToLeft：刪除儲存格，並由右邊儲存格左移填補。[Link]

    • 引數=xlShiftToUp：刪除儲存格，並由下邊儲存格上移填補。

With陳述式

• 語法：

With 物件

.屬性=值

.方法

其他使用物件的程式碼

End With

• 模組等級變數：在「模組的開頭」且「不屬於任何巨集的地方」，宣告變數後，則該變數可在模組內所有的巨集使用該變數。

條件判斷-分歧執行、反覆執行

If條件判斷

• 成立-語法：

If 條件式 Then

條件成立時執行的程式碼區塊

End If

• 成立-不成立-語法 (2個分歧)：

If 條件式 Then

條件成立時執行的程式碼區塊

Else

條件不成立時執行的程式碼區塊

End If

• 成立-不成立-語法 (3個分歧)：

If 條件式1 Then

條件1成立時執行的程式碼區塊

ElseIf 條件2 Then

條件1不成立，但條件2成立時，執行的程式碼區塊

Else

條件1與條件2均不成立

End If

依此類推，可以寫出多個分歧路徑的語法

• 「條件式」的寫法

  • 語法：運算式1/值1 比較運算子 運算式2/值2

  • 「值」比較運算子：

    • 小於：<

    • 小於等於：<=

    • 大於：>

    • 大於等於：>=

    • 等於：=

    • 不等於：<>

  • 「物件」運算子：

    • 等於/是：Is

    • 範例：

      • Worksheets(2) Is Worksheets("工作表2") ：成立

      • 變數 Is Nothing：(成立，變數內不存有物件)，(不成立，變數內存有物件)

  • 「字串」運算子

    • 等於：=

    • 不等於：<>

    • 像/類似：Like

      • Like與 萬用字元 的組合應用

        • 任意長度字串：*

        • 任意1個文字：?

        • 任意1個數值：#

        • []內其中一個文字：[xyz]

        • []外的文字：[!xyz]

        • 連續範圍[-]：[d-h]

  • 「多個」條件式之組合

    • 同時成立：條件式1 And 條件式2

    • 任一個條件式成立即可：條件式1 Or 條件式2

    • 反轉/反相 條件式的結果：Not 條件式

Select Case條件

• 語法：

Select Case 判斷/運算式/變數

Case 範圍1

符合範圍1時，執行的程式碼區塊

Case 範圍2

符合範圍2時，執行的程式碼區塊

Case Else

不符合範圍1和範圍2時，執行的程式碼區塊

End Select

• 「範圍」的寫法

  • 某一個值：Case 5

  • 某幾個值：Case 3,5,8

  • 比較運算子設定範圍：Case Is>60

  • To 範圍設定：Case 10 To 90

  • 其他 情況：Case Else

迴圈程序

• For Next程序，含有固定重複次數：

  • 語法：

For 變數=初始值 To 終值

重複執行的程式碼

Next

• For Each Next程序，集合或陣列的處理：

  • 語法：

For Each 處理單位變數 In 處理群組

重複執行的程式碼

Next

• • 其中「處理群組」為「集合」或「陣列」。

  • 「處理單位變數」需「先以變數宣告」。

• Do Loop程序，判斷「條件」迴圈：

  • 語法：

Do

重複執行的程式碼

Loop

• • 終止條件：

    • 終止條件可放在「Do」或「Loop」後面。

    • While 條件式：條件式為成立時，迴圈「繼續」執行。

    • Until 條件式：條件式為成立時，迴圈「停止」執行。

陣列

• 陣列宣告語法：

  • 語法：Dim 陣列名(參數) As 資料類型

  • Dim arr(3) As Integer：陣列有arr(0)、arr(1)、arr(2)、arr(3)共4個。

  • Dim arr(1 To 3) As Integer：陣列有arr(1)、arr(2)、arr(3)共3個。

  •  動態陣列，[Link]：定義陣列時，先不給定陣列大小，等要使用前再以ReDim定義陣列大小。如果之後要再更改陣列大小，可使用ReDim，但若要保留之前存在陣列數值，需加Preserve ，否則資料會被刪除。

Dim arr() As Integer

ReDim arr(2)

arr(0)=1

arr(1)=2

arr(2)=3

ReDim Preserve arr(3)

arr(3)=4

Debug.Print arr(0),arr(1),arr(2),arr(3)

• Array()函數

  • 使用方法：

    1. 先宣告變數為Variant型態。

    2. 變數=Array(元素1,元素2,元素3,元素4)

  • 範例：

Dim arr As Variant

arr=Array(1,2,3,4,5)

Debug.Print arr(0),arr(1),arr(2),arr(3),arr(4)

• • 範例：物件，不需以Set設定物件

Dim arr As Variant

arr=Array(Range("A1"),Range("C1"),Range("E1"))

Debug.Print arr(0).Address,arr(1).Address,arr(2).Address

• Format()函數應用：格式化資料顯示 [Link-官方網站], [Link-使用範例]

• Ubound(陣列)：陣列索引的上限

• Lbound(陣列)：陣列索引的下限

• Join(陣列,間隔元素)：[Link],將陣列裡的元素以「間隔元素」連接在一起，若沒有給「間隔元素」，則以「空白」間隔。

  • 範例：

Dim a As Variant

Dim b As String

a=Array("Red","Blue","Yellow")

b=Join(a)

MsgBox(b)

b=Join(a,"#")

MsgBox(b)

• Split(字串,分隔元素)函數：[Link],將「字串」以「分隔元素」分割成「陣列」。

  • 範例：

Dim myarr As Variant

myarr=Split("123#456#789","#")

Debug.Print myarr(0),myarr(1),myarr(2)

計算與資料蒐集 函數

• TypeName(參數)：可傳回「參數」的「資料型態」。

• 字串函數，[Link]，[Link2]。

• 日期與時間函數，[Link]。

• Round()銀行家四捨五入函數：[Link]。若可以進位，但進位後不是「偶數」，則不進位。

  • 例：

    • Round(10.51)→11

    • Round(10.5)→10

    • Round(10.4)→10

• 日期與時間 [Link]

Dim d As Date

    ' 現在日期

    d = Date

    MsgBox ("現在是：" & d)    

    ' 現在時間

    d = Time()

    MsgBox ("現在是：" & d)    

    ' 現在日期與時間

    d = Now()

    MsgBox ("現在是：" & d)

• Application.WorksheetFunction.函數()：可呼叫Excel的函數()使用。[Link]

• MsgBox函數, [Link]

  • 語法：MsgBox 訊息, Buttons:=按鍵格式, Title:="標題"

  • 範例：MsgBox (Round(8.51, 0), Buttons:=vbYesNo + vbCritical, Title:="TEST")

• Input函數, [Link]

  • 語法：InputBox(Prompt:="顯示內容",Title:="標題內容",Default:="預設輸入字串")

  • 時常配合函數：

    • 字串→數值：Val()函數

    • 字串→日期：DateValue()函數

• Appliction.IputBox()函數, [Link],[Link]

  • 語法：Application.InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextId, Type) 

  • 例：

Dim selRange as Range

Set selRange=Application.InputBox(Prompt:="選擇範圍",Type:=8)

工作表函數

• 使用原始Excel內建的函數。

• 工作表函數一覽表, [Link1], [Link2]

• Application.WorksheetFunction.工作表函數(引數)

• 停止巨集：Exit Sub

事件處理

• 事件

  • 活頁簿

    • 開啟活頁簿：Workbook_Open

    • 關閉活頁簿：Workbook_BeforeClose

    • 儲存活頁簿：Workbook_BeforeSave

    • 列印活頁簿：Workbook_BeforePrint

    • 儲存格變更：Workbook_SheetChange

  • 工作表

    • 致能工作表：Worksheet_Activate

    • 非致能工作表(選取其他工作表)：Worksheet_Deactive

    • 刪除工作表：Worksheet_BeforeDelete

    • 選取儲存格：Worksheet_SelectionChange

    • 變更儲存格值：Worksheet_Change

    • 點2下儲存格：Worksheet_BeforeDoubleClick

    • 右鍵點1下儲存格：Worksheet_BeforeRightClick

• 存取修飾子有「Private」和「Public」。若設定為「Private」代表該巨集，僅能在該模組內使用。若設定為「Public」或「沒有設定」，則外部模組也可使用該巨集。

  • 範例

Private Sub 巨集名稱()

巨集程式碼

End Sub

• 若以「Private」和「Public」取代「Dim」宣告變數，若以「Public」宣告變數，則該變數可於模組外的其他模組使用。[Link]

  • 範例：Public myvar As Integer

• 呼叫他巨集

  • 語法：Call 巨集名稱

• Application.Intersect()方法, [Link]

  • 語法：Application.Intersect(Range1,Range2)

  • 傳回Range1與Range2間 互相重疊的區域

• 事件觸發 致能/停止

  • Application.EnableEvents=False '停止事件觸發

  • Application.EnableEvents=True '致能事件觸發

• 點2下儲存格：Worksheet_BeforeDoubleClick [Link]

  • Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    • Target：回傳Range物件，傳回點2下的儲存格物件。

    • Cancel：布林值。Cancel=True，可取消輸入狀態。 

• 選取範圍 Application.Selection方法 或 省略寫為 Selection

  • 參考資料 [Link]

• Range.Interior屬性：儲存格背景色, [Link], [Link-Interior.Color]

  • Range("A1").Interior.Color=regRed '將儲存格A1填上紅色

  • Range("A1").Interior.Color=RGB(255,0,0) '將儲存格A1填上紅色, [Link]

  • Range("A1").Interior.ColorIndex=xlColorIndexNone  '將儲存格A1填上無顏色,[Link], [Link2]

自訂巨集

• Range.CurrentRegion 自動擴展選取連續儲存格範圍, [Link], [Link]

  • 範例：Range("A1").CurrentRegion

• 變數宣告的「位置」與「習慣」：

• 1. 一般宣告變數時，希望在「巨集的開頭」處宣告，方便讓讓讀者瞭解該巨集有哪些變數需使用。

  2. 另有宣告變數時，希望在「第一次使用該變數」前，避免讓讀者不知道為什麼要使用該變數。

• Range.Rows：傳回指定範圍的列, [Link]

  • 範例：

    • 傳回選取範圍的第1列：Selection.Rows(1) 

    • Range("A1:B2").Rows(5).Select 會傳回儲存格 A5:B5 

• Range.Columns：傳回指定範圍的欄, [Link]

  • 範例：

    • Selection.Columns(1) 會傳回選取範圍的第一欄 

    • Range("A1:B2").Columns(5).Select 會傳回儲存格 E1:E2

• Exit 跳出

  • Exit Sub：跳出巨集Sub

  • Exit IF：跳出If判斷

  • Exit Do：跳出Do迴圈

• Range.FindNext：本方法尋找相符合同一條件的下儲存格，並傳回代表該儲存格的 Range 物件。  [Link]

  • 語法：Range.FindNext("儲存格位址") 於儲存格位置後，搜尋下一個符合條件的儲存格。

• WorksheetFunction.Max(範圍)：使用Excel內建函數Max，找出範圍中最大數值。

• Range.ClearContents：清除儲存格的「值」和「公式/算式」。

• Range.Clear：清除儲存格的「值」和「公式/算式」和「格式」。

• Range.Formula：以"字串"設定儲存格的公式，而公式裡面的儲存格位址以A1或C3，這種方式表示。

  • 例如：Range("A1").Formula="=B2*C3"

• Range.FormulaR1C1 [Link]：以「R1C1」標記法設定儲存格公式。

  • '以當時選取之儲存格為中心點

  • 'R[位置]：向上為減數，向下為正數

  • 'C[位置]：向左為減數，向右為正數 

  • 例如：Range("A1").FormulaR1C1="=R[1]C[1]*R[3]C[2]"。

    • 其中R[1]C[1]代表以A1為起點，R[1]為向下移動1行，C[1]為向右移動1列，即為B2。

    • 其中R[2]C[2]代表以A1為起點，R[2]為向下移動3行，C[2]為向右移動2列，即為C4。

多工作表資料統計

• 帶「引數」的自訂巨集

  • 語法：

Sub 巨集名稱(引數)

巨集內的命令

End Sub

• • 引數的命名方法與變數的命名方法一樣。

  • 呼叫帶引數的巨集，需使用「Call」指令。

• Ubound(陣列)：傳回陣列的索引值的「最大值」。[Link]

• Lbound(陣列)：傳回陣列的索引值的「最小值」。[Link]

• ActiveWindow.SelectedSheets：作用中的活頁簿中，被選取的工作表。

多活頁簿資料統計

• 開啟活頁簿：

  • 語法：物件.Open(活頁簿的路徑)

• 關閉活頁簿：

  • 語法：物件.Close

• Dir()函數：傳回符合搜尋目標 (路徑+檔名格式)，檔案的檔名。傳回 字串 表示檔案、 目錄或資料夾的名稱與指定模式、檔案屬性或是磁碟機的磁碟區標籤相符。 

  • 語法：Dir(檔案路徑&檔名規則)

  • Dir函數可以使用字串的萬用字元「*」。

  • 再繼續使用，可得到下一個檔案名稱。若沒有檔案，則會傳回「空白字串」。

• Applicatiov.ActiveWindow：使用中的Excel視窗[Link]

• Application.ActiveWindow.Caption：使用中的Excel視窗的名稱[Link]

• ActiveWindow.SelectedSheets：使用中的Excel視窗被選取的工作表集合。[Link]

• ActiveWorkbook.Path：工作中活頁簿所在的「路徑」。[Link]

• Application.GetOpenFilename：選取檔案對話視窗，可回傳完整檔案路徑與檔名。[Link]

  • 語法：Application.GetOpenFilename(引數)

    • 「檔案種類 FileFilter」引數，檔案篩選準則。 範例：

      • FileFilter := "Text Files (*.txt),*.txt,Add-In Files (*.xla),*.xla" 

      • FileFilter := "Visual Basic Files (*.bas; *.txt),*.bas;*.txt" 

      • FileFilter := "All Files (*.*),*.*" 

      • FileFilter := "Excel活頁簿(*.xlsx;*.xlsm),*.xlsx;*.xlsm"

• Application.FileDialog(檔案對話類型)：檔案對話視窗。[Link]

  • 檔案對話類型：

• • 1. msoFileDialogFilePicker：可讓使用者選取檔案。

    2. msoFileDialogFolderPicker： 可讓使用者選取資料夾。

    3. msoFileDialogOpen：可讓使用者開啟檔案。

    4. msoFileDialogSaveAs：可讓使用者儲存檔案。

• • Application.FileDialog(msoFileDialogFolderPicker).Show：顯示資料夾選取視窗。

  • Application.FileDialog(msoFileDialogFolderPicker).Title：「資料夾選取視窗的標題」屬性。

  • Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1)：回傳第1個選取的資料夾名稱。

練習

1.

• Range.PrintOut：列印指定範圍的儲存格。[Link]

  • 例如：Range("A1:F10").PrintOut

• Range.Select：選取儲存格。

  • 例如：Range("A1:F10").Select

• Range.Rows.Count：儲存格範圍的「行數」。

• Range.Columns.Count：儲存格範圍的「列數」。

• Range.Rows(序列數)：回傳第「序列數」的列

• Range.RowHeight 屬性：回傳列高

• Range.ColumnWidth 屬性：回傳行寬

• Range.Copy 方法：複製儲存格。[Link]

  • 語法：Range.copy Destination:=目標

  • 範例：orksheets("Sheet1").Range("A1:D4").Copy destination:=Worksheets("Sheet2").Range("E5") 

• Range.Cells屬性：回傳範圍內的儲存格，回傳Range物件。[Link], [Link-範例]

  • Cells(列索引,欄索引)： 關鍵字後面緊接著指定列索引及欄索引 。

  • 範例：Cells(3,2) 傳回儲存格「$B$3」，Cells(3,"B")傳回儲存格「$B$3」。

• Range.Row屬性：傳回範圍內第1列的「列號」。[Link]

  • 例如：Range("C5").Row，回傳「5」。

• Range.Column屬性：傳回範圍內第1欄的「欄號」。[Link]

  • 例如：Range("C5").Column，回傳「3」。

• Range.SpecialCells(引數)：傳回Range物件，代表與指定的類型及值相符的所有儲存格。 [Link]

  • 範例：Range("A1:F20").SpecialCells(xlCellTypeFormulas )，傳回含有公式的儲存格物件。

2.

• ListObjects集合：「表格」集合。[Link]

  • ListObjects("表格名稱")，表格物件。

• ListObject.ListRows：回傳ListRows物件。表格物件的所有資料列，不包含 標題列、總計列、插入列。

  • 範例：Worksheets("工作表1").ListObjects("表格1").ListRows.Count，傳回工作表1內的表格1不包含 標題列的列數。

  • 範例：Worksheets("工作表1").ListObjects("表格1").ListRows(1).Range，工作表1內的表格1，其內的第1列中的儲存格物件。

• ListObject.DataBodyRange：回傳Range物件。表格內的值，不包含標題列。[Link]

• Range.Sort：儲存格物件排序。[Link]

  • Range("A1:F20").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes，資料範圍A1:F20進行排序，以欄位A1地增排序，有列標題且不加入排序。

• ListRows.Add(位置,AlwaysInsert)：將資料新增至ListObject物件內。[Link]

  • 範例：dataTable.ListRows.Add(5,True).Range.Value=Array("abc",10,20) '將陣列("abc",10,20)插入第5列，(True) 並將原第5列下移。

3.

• Worksheet.Move：移動工作表[Link]

  • 範例：Worksheets(3).Move Before:=Worksheets(1)，將第3個工作表移到第1個工作表前。

• Application.ScreenUpdating=False：Excel執行時，不要更新畫面。 [Link]

• 選擇性 引數 Optional: [Link]

  • 範例：Sub name(ByVal parameter1 As datatype1, Optional ByVal parameter2 As datatype2 = defaultvalue)

• Range.AdvancedFilter方法：根據準則範圍，從清單中篩選或複製資料。 [Link]

  • 範例：

    • 對「儲存格B3:I51的第4欄」進行篩選，將篩選的資料複製到新位置 (Action:=xlFilterCopy)，新位置為第2個工作表的儲存格A1 (CopyToRange:=Worksheets(2).Range("A1"))，且只篩選出唯一的項目(Unique:=True)，即相同的項目只留下一個。

    • Range("B3:I51").Columns(4).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Worksheets(2).Range("A1"), Unique:=True

  • 範例：

    • dataRange.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=tmpSheet.Range("A1:A2"), CopyToRange:=baseRange.CurrentRegion

  • AdvancedFilter方法：即「資料\排序與篩選\進階」，但使用此功能時，「篩選資料來源」、「篩選條件」、「結果目的地」需在同一個工作表中的工作表內。

• CStr()函數轉換為字串String：[Link], [Link2]

• Replace()函數，搜尋目標字串後取代：[Link], [Link2]

  • 範例：Replace("Welcome~ Everyone.","~","!")，搜尋「~」取代為「!」。

• Left()函數，取回字串左邊n各字元：[Link]。

  • 範例：Left("Welcome~ Everyone.",7)，得「Welcome」。

• Range.EntireColumn屬性：[Link], 傳回Range物件，傳回所有範圍內所有的欄位。

• Range.AutoFit方法：[Link]，將範圍內的表格欄位寬度與列高調整到最適大小。

• ListObjects.Add方法：[Link]，建立新的清單物件。

  • 範例：Worksheets(1).ListObjects.Add(SourceType:=xlSrcRange,Source:=Range("B2:F12"),XlListObjectHasHeaders :=xlYes)

  • 於第1個工作表內「Worksheets(1)」，新增一個表格「ListObjects.Add」，表格資料來源是Range物件「SourceType:=xlSrcRange」，資料來源儲存格範圍為B2:F12「Source:=Range("B2:F12")」，資料來源有欄位標題「XlListObjectHasHeaders :=xlYes」。

• Worksheets.Delete方法：[Link]，刪除工作表。

  • 當在刪除工作表時，均會顯示提示視窗，若要預設不顯示該視窗，可加一行指令「Application.DisplayAlerts = False」，即可。

4.

• Intersect(Range1,Range2,...)函數：[Link1], [Link2]。回傳Range物件，即回傳互相重疊的儲存格範圍。

• On Error陳述式：[Link], [Link2],[圖解說明], [圖解說明]

  • On Error Resume Next會執行緊接在陳述式會導致執行時期錯誤，此陳述式繼續執行] 或 [緊接在最新的陳述式通話超出含有On Error 的程序繼續下一步陳述式。 

  • On Error Resume Next 表示當一個執行階段錯誤產生時，程式控制立刻到發生錯誤陳述式接下去的陳述式，而繼續執行下去。 

  • On Error GoTo 0 停止現在程序裏任何已啟動的錯誤處理程式，但會跳出錯誤訊息框。 

• Val()函數：字串→數值

• Trim()函數：[Link1], [Link2]。去除字串前後的空格。

• Join(陣列,分隔符號)函數：[Link1], [Link2]。將陣列中的元素以「分隔符號」串接起來，若未給分隔符號則以「空白」串接。

• 程式除錯，Error Handling錯誤處理：[Link]。

• Err.Number錯誤號碼、Err.Description 錯誤敘述：[Link]

• Application.Undo方法：[Link]。取消最後一個使用者介面動作。 

• Application.EnableEvent方法：[官方], [Link-使用時機], [Link-使用時機2]。啟動/取消 物件觸發事件發生。

• DateAdd(interval, number, date) 函數：[官方], [Link]。傳回設定間隔時間距離後的日期。

  • 範例：DateAdd("m", 1, "31-Jan-95") ，1995/1/31後1個月的日期。

• Day()函數：[Link]。傳回日期(年月日)中的「日」。

  • 範例：Day(#February 12, 1969# ) ，回傳「12」

• Range.Row屬性：傳回範圍內第1列的「列號」。[Link]

  • 例如：Range("C5").Row，回傳「5」。

• Range.Cells屬性：回傳範圍內的儲存格，回傳Range物件。[Link], [Link-範例]

  • Cells(列索引,欄索引)： 關鍵字後面緊接著指定列索引及欄索引 。

  • 範例：Cells(3,2) 傳回儲存格「$B$3」，Cells(3,"B")傳回儲存格「$B$3」。

• Range.Interior屬性：儲存格背景色, [Link]

  • Range("A1").Interior.Color=regRed '將儲存格A1填上紅色

  • Range("A1").Interior.Color=RGB(255,0,0) '將儲存格A1填上紅色, [Link]

  • Range("A1").Interior.ColorIndex=xlColorIndexNone  '將儲存格A1填上無顏色,[Link], [Link2]

• Application.WorksheetFunction.函數()：可呼叫Excel的函數()使用。[Link]

• Excel 函數

  • Transpose()函數：[官方]。轉置/旋轉 儲存格。

  • Match()函數：[官方], [Link-範例]。MATCH(搜尋值,搜尋範圍,搜尋類型)。

    • 搜尋值：想要尋找的值。

    • 搜尋範圍：搜尋的範圍。

    • 搜尋類型：指定搜尋類型，若至指定為 0 則代表尋找完全一樣的值；若指定為 1 則代表尋找小於或等於搜尋值的最大值；若指定為 -1 則代表尋找大於或等於搜尋值的最小值。

• CLng()函數：[官方]，將數值轉會為長整數Long。

• ActiveWindow物件：[官方]。代表 目前在上方/目前在使用 的Excel視窗。

• ActiveWindow.RangeSelection方法：[官方]。傳回Range物件，代表所指定視窗中工作表上選取的儲存格 。

• Range.Column屬性：傳回範圍內第1欄的「欄號」。[Link]

  • 例如：Range("C5").Column，回傳「3」。