Excel VBA程序開發自學寶典(第3版)

上 篇

第1章 初步感受VBA 的魅力1
1.1 批量任務一鍵執行 1
1.1.1 准備工作 1
1.1.2 程序測試 1
1.1.3 案例點評 2
1.2 多工作簿自動匯總 2
1.2.1 案例需求 2
1.2.2 程序測試 3
1.2.3 案例點評 3
1.3 淺談VBA 優勢 3
1.3.1 批量執行任務 3
1.3.2 將復雜的任務簡單化 3
1.3.3 提升工作表數據的安全性 4
1.3.4 提升數據的准確性 4
1.3.5 完成Excel 本身無法完成的任務 4
1.3.6 開發專業程序 4

第2章 VBA 程序入門 5
2.1 如何存放代碼 5
2.1.1 認識模塊 5
2.1.2 管理模塊 6
2.2 如何產生代碼 7
2.2.1 復制現有的代碼 7
2.2.2 錄制宏 7
2.2.3 手工編寫代碼 9
2.2.4 從模板中獲取代碼 10
2.3 如何調用代碼 10
2.3.1 F5 鍵 10
2.3.2 Alt+F8 組合鍵11
2.3.3 自定義快捷鍵 12
2.3.4 按鈕 13
2.3.5 菜單 13
2.4 如何保存代碼 13
2.4.1 工作簿格式 13
2.4.2 解決代碼丟失問題 14
2.4.3 顯示文件擴展名 14
2.5 如何放行代碼 15
2.6 如何查詢代碼幫助 17
2.6.1 調用幫助系統 17
2.6.2 為什麼查看不了幫助 18

第3章 VBA 的程序結構分析 19
3.1 子過程的結構 19
3.1.1 認識程序結構 19
3.1.2 為VBA 程序添加注釋 20
3.2 子過程的作用范圍 20
3.2.1 何謂作用范圍 21
3.2.2 公有過程與私有過程的區別 21
3.3 過程的命名規則 22
3.4 過程的參數 22
3.5 過程的執行流程 22
3.5.1 正常的執行流程 23
3.5.2 改變程序的執行流程 23
3.6 中斷過程 24
3.6.1 結束過程：End Sub 24
3.6.2 中途結束過程：Exit sub 24
3.6.3 中途結束一切：End 24
3.6.4 暫停過程：Stop 24
3.6.5 手動暫停程序：Ctrl+Break 25

第4章 VBA 四大基本概念 26
4.1 Excel 的對象 26
4.1.1 什麼是對象 26
4.1.2 對象與對象集合 27
4.1.3 對象的層次結構 29
4.1.4 父對象與子對象 30
4.1.5 活動對象 31
4.2 對象的方法和屬性 32
4.2.1 屬性與方法的區別 32
4.2.2 查詢方法與屬性的兩種方法 33
4.2.3 方法與屬性的應用差異 34
4.3 對象的事件 36
4.3.1 什麼是事件 36
4.3.2 事件的分類及其層級關系 37
4.3.3 工作簿事件與工作表事件一覽 37
4.3.4 工作簿與工作表事件的作用對象 39
4.3.5 快速掌握事件過程 41
4.3.6 何時需要使用事件過程 41

第5章 通過變量強化程序功能 42
5.1 數據類型 42
5.1.1 為什麼要區分數據類型 42
5.1.2 認識VBA 的數據類型 42
5.2 聲明變量 44
5.2.1 變量的定義 44
5.2.2 變量的聲明方式 44
5.2.3 變量的賦值方式與初始值 46
5.2.4 如何確定變量的數據類型正確 47
5.2.5 正確聲明變量的數據類型的優勢 47
5.2.6 變量的作用域 49
5.2.7 變量的生存周期 50
5.3 對象變量 50
5.3.1 如何區分對象變量和數據變量 51
5.3.2 對變量賦值 51
5.3.3 使用對象變量的優勢 52
5.4 聲明常量 53
5.4.1 常量的定義與用途 54
5.4.2 常量的聲明方式 54
5.4.3 常量的命名規則 55

第6章 深入剖析常見對象的引用方式 56
6.1 關於對象 56
6.1.1 對象的結構 56
6.1.2 對象與對象的集合 57
6.1.3 引用集合中的單一對象 57
6.1.4 父對象與子對象 57
6.1.5 活動對象 58
6.2 對象的簡化引用 58
6.2.1 使用對象變量 58
6.2.2 使用With 語句 59
6.3 單元格對象 61
6.3.1 Range（”A1”）方式引用單元格 61
6.3.2 Cells（1，1）方式引用單元格 63
6.3.3 （a1）方式引用單元格 65
6.3.4 Range（”A1”）、Cells（1，1）與（a1）引用單元格方式比較 65
6.3.5 Selection 與ActiveCell：當前選區與活動單元格 66
6.3.6 已用區域與當前區域 67
6.3.7 SpecialCells：按條件引用區域 68
6.3.8 CurrentArray：引用數組區域 70
6.3.9 Resize：重置區域大小 70
6.3.10 Offset：根據偏移量引用新區域 71
6.3.11 Union：多區域合集 73
6.3.12 Intersect：單元格、區域的交集 74
6.3.13 End：引用源區域的區域尾端的單元格 75
6.3.14 RangeFromPoint：屏幕坐標下的單元格 77
6.4 圖形對象 78
6.4.1 Shapes：圖形對象集合 78
6.4.2 圖形對象的名稱 79
6.4.3 DrawingObjects：隱藏的圖形對象集合 80
6.5 表對象 81
6.5.1 表的類別 81
6.5.2 Worksheets：工作表集合 82
6.5.3 引用工作表子集 82
6.5.4 ActiveSheet：活動表 83
6.5.5 工作表的特性 83
6.6 工作簿對象 84
6.6.1 工作簿格式與特性 84
6.6.2 Workbooks：工作簿集合 84
6.6.3 引用工作簿子集 84
6.6.4 活動工作簿 85

第7章 常用語句的語法剖析 86
7.1 創建輸入框 86
7.1.1 Application.Inputbox 方法 86
7.1.2 基本語法 86
7.1.3 案例應用 87
7.2 條件判斷語句 91
7.2.1 IIF 函數的語法與應用 91
7.2.2 IIF 函數的限制 95
7.2.3 IF Then 語句的語法詳解 95
7.2.4 IF Then 應用案例 96
7.2.5 IF Then Else 語句的語法與應用 97
7.2.6 多條件嵌套的條件判斷語句 99
7.2.7 Select Case 語法詳解 103
7.2.8 Select Case 與IF Then Else 之比較 107
7.2.9 借用Choose 函數簡化條件選擇 107
7.3 循環語句 109
7.3.1 For Next 語句 109
7.3.2 For Each Next 語句 116
7.3.3 Do Loop 語法詳解 122
7.4 錯誤處理語句 129
7.4.1 錯誤類型與原因 130
7.4.2 Err 對象及其屬性、方法 130
7.4.3 認識Error 函數 131
7.4.4 On Error GoTo line 132
7.4.5 On Error Resume Next 135
7.4.6 On Error GoTo 0139
7.5 選擇文件與文件夾 140
7.5.1 認識FileDialog 對象 140
7.5.2 選擇路徑 141
7.5.3 選擇文件 142
7.5.4 按類型選擇文件 143

第8章 讓代碼自動執行 146
8.1 讓宏自動執行 146
8.1.1 Auto 自動宏 146
8.1.2 升級版自動宏：事件 147
8.1.3 事件的禁用與啟用 149
8.1.4 事件的特例 150
8.2 工作表事件應用案例 152
8.2.1 在狀態欄提示最大值的單元格地址 152
8.2.2 快速錄入出勤表 153
8.2.3 在狀態欄顯示選區的字母、數字、漢字個數 154
8.2.4 實時監控單元格每一次編輯的數據與時間 156
8.2.5 利用數字簡化公司名輸入 158
8.2.6 錄入數據時自動跳過帶公式的單元格 160
8.2.7 對選擇區域進行背景着色 161
8.3 工作簿事件應用案例 162
8.3.1 新建工作表時自動設置頁眉 163
8.3.2 未匯總則禁止打印與關閉工作簿 164
8.3.3 為所有工作表設計一個閱讀模式 165
8.3.4 設計未啟用宏就無法打開的工作簿 167

第9章 綜合應用案例 170
9.1 Application 應用案例 170
9.1.1 計算字符表達式 170
9.1.2 合並相同且相鄰的單元格 171
9.1.3 在指定時間提示行程安排 173
9.1.4 模擬鍵盤快捷鍵打開高級選項 174
9.1.5 使用快捷鍵合並與取消單元格 175
9.1.6 查找至少兩月未付貨款的客戶名稱 177
9.2 Range 對象應用案例 179
9.2.1 合並工作表 179
9.2.2 合並區域且保留所有數據 181
9.2.3 合並計算多區域的值 183
9.2.4 模糊查找公司名稱並羅列出來 185
9.2.5 反向選擇單元格 187
9.2.6 插入圖片並調整為選區大小 189
9.2.7 提取唯一值 191
9.2.8 隱藏所有公式結果為錯誤的單元格 192
9.3 Comment 對象應用案例 194
9.3.1 在所有批注末尾添加指定日期 194
9.3.2 生成圖片批注 196
9.3.3 添加個性化批注 197
9.3.4 批量修改當前表的所有批注外觀 199
9.4 WorkSheet 對象應用案例 202
9.4.1 新建工作表且命名為今日日期 202
9.4.2 批量保護工作表與解除保護 203
9.4.3 為所有工作表設置水印 205
9.4.4 批量命名工作表 206
9.4.5 判斷篩選條件 209
9.5 Workbook 對象應用案例 211
9.5.1 拆分工作簿 212
9.5.2 每10 分鍾備份一次工作簿 213
9.5.35 分鍾未編輯工作簿則自動備份 215
9.5.4 記錄文件打開次數 216
9.5.5 不打開工作簿而提取數據 218
9.5.6 建立指定文件夾下所有工作簿目錄和工作表目錄 220

第10章 編程規則與代碼優化 223
10.1 代碼編寫規則 223
10.1.1 對代碼添加注釋 223
10.1.2 長代碼分行 226
10.1.3 代碼縮進對齊 227
10.1.4 聲明有意義的變量名稱 228
10.1.5 IF Then…End If 類配對語句的錄入方式 229
10.1.6 錄入事件代碼的方式 230
10.1.7 錄入屬性與方法的技巧 230
10.1.8 無提示的詞組的錄入技巧 231
10.1.9 善用公共變量 232
10.1.10 將比較大的過程分為多個再調用 232
10.1.11 減少過程參數 233
10.1.12 使用DoEvents 轉移控制權 233
10.1.13 使用常量名稱替代常數 233
10.1.14 盡可能兼容Excel 2003、2010 和2013 版本 233
10.2 優化代碼 234
10.2.1 強制聲明變量 234
10.2.2 善用常量 234
10.2.3 關閉屏幕更新 234
10.2.4 利用With 減少對象讀取次數 235
10.2.5 利用變量減少對象讀取次數 236
10.2.6 善用帶＄的字符串處理函數 236
10.2.7 利用數組代替單元格對象 237
10.2.8 不使用Select 和Activate 直接操作對象 237
10.2.9 將與循環無關的語句放到循環語句外 237
10.2.10 利用Instr 函數簡化字符串判斷 237
10.2.11 使用Replace 函數簡化字符串連接 238

第11章 利用參數強化過程 240
11.1 什麼是參數 240
11.1.1 參數的概念與用途 240
11.1.2 參數的語法結構 240
11.2 設計帶有參數的Sub 過程 241
11.2.1 必選參數 241
11.2.2 可選參數 243
11.2.3 不確定數量的參數 244
11.3 參數的賦值方式 245
11.3.1 按位置賦值 245
11.3.2 按名稱賦值 246
11.3.3 方法的參數 246

第12章 編程的捷徑 248
12.1 錄制宏 248
12.1.1 錄制宏的目的 248
12.1.2 錄制宏的方法 249
12.2 查看提示 251
12.2.1 屬性與方法列表 251
12.2.2 參數提示 252
12.3 調用筆記 252
12.3.1 筆記的對象 252
12.3.2 筆記的記錄方式 253
12.4 使用工具模板 254
12.4.1 代碼百寶箱 254
12.4.2 開發VBA 插件 255

下 篇

第13章 利用數組提升程序效率 256
13.1 基本概念 256
13.1.1 何謂數組 256
13.1.2 數組的特點 256
13.1.3 一維數組 257
13.1.4 二維數組 259
13.1.5 數組的參數 260
13.1.6 聲明數組變量 261
13.1.7 動態數組與靜態數組的分別 263
13.1.8 釋放動態數組的存儲空間 268
13.2 數組函數 268
13.2.1 用函數創建數組 268
13.2.2 獲取數組元素 270
13.2.3 判斷變量是否為數組 270
13.2.4 轉置數組 270
13.2.5 獲取數組的上標與下標 272
13.2.6 轉換文本與數組 273
13.2.7 篩選數組 275
13.3 案例分析 276
13.3.1 將指定區域的單詞統一為首字母大寫 276
13.3.2 羅列不及格學生的姓名、科目和成績 277
13.3.3 跨表搜索學員信息 278
13.3.4 將職員表按學歷拆分成多個工作表 280
13.3.5 將選區中的數據在文本與數值之間互換 282
13.3.6 獲取兩列數據的相同項 283
13.3.7 無人值守的多工作簿自動匯總 285

第14章 正則表達式與VBA 288
14.1 何謂正則表達式 288
14.1.1 概念 288
14.1.2 特點 288
14.1.3 調用方式 289
14.2 語法基礎 290
14.2.1 調用正則表達式的基本格式 290
14.2.2 正則表達式的對象、屬性和方法 291
14.2.3 匹配的優先順序 294
14.2.4 借用元字符強化搜索功能 295
14.3 正則表達式應用 311
14.3.1 亂序字符串取值並匯總 311
14.3.2 計算建築面積 312
14.3.3 取括號中的數字 313
14.3.4 去除字符串首尾的空白字符 314
14.3.5 將字符串中的多段數字分列 315
14.3.6 獲取E—mail 地址 315
14.3.7 提取文件的路徑與文件名 316
14.3.8 匯總人民幣 317
14.3.9 開發分列函數 318
14.3.10 刪除重復字詞 319

第15章 詳解字典應用 321
15.1 Dictionary 對象基礎 321
15.1.1 Dictionary 對象的調用 321
15.1.2 Dictionary 的特點 323
15.1.3 Dictionary 對象的屬性與方法 323
15.2 Dictionary 對象的應用技巧 328
15.2.1 利用字典創建三級選單 328
15.2.2 分類匯總 330
15.2.3 對多列數據相同者應用背景色 331
15.2.4 按姓名計數與求產量平均值 332
15.2.5 按品名統計半年內的產量合計 334

第16章 開發自定義函數 335
16.1 自定義函數的功能和語法 335
16.1.1 Function 過程與Sub 過程的區別 335
16.1.2 Function 過程的語法 335
16.1.3 自定義函數的命名規則 337
16.2 開發不帶參數的Function 過程 337
16.2.1 判斷活動工作簿是否存在圖形對象 337
16.2.2 計算公式所在單元格的頁數 338
16.3 開發帶有一個參數的Function 過程 339
16.3.1 在不規則的合並單元格中執行合計 339
16.3.2 建立活動工作簿的表目錄 341
16.4 開發帶有兩個參數的Function 過程 342
16.4.1 分段提取數值 342
16.4.2 獲取最大值、最小值或眾數的地址 343
16.4.3 匯總前N 大值 344
16.5 開發復雜的Function 過程 345
16.5.1 合並區域的值或者數組 345
16.5.2 按單元格背景顏色進行條件求和 347
16.5.3 按顏色查找並返回數組 348
16.5.4 合計分隔符左邊的所有數值 350
16.6 編寫函數幫助 351
16.6.1 MacroOptions 方法的語法 351
16.6.2 為函數分類及添加說明 352

第17章 設計窗體 354
17.1 UserForm 簡介 354
17.1.1 窗體與控件的用途 354
17.1.2 插入窗體與控件的方法 354
17.1.3 使用Excel 5.0 對話框 355
17.2 窗體控件一覽 355
17.2.1 標簽 355
17.2.2 文本框 356
17.2.3 命令按鈕 356
17.2.4 復合框 356
17.2.5 列表框 356
17.2.6 復選框 356
17.2.7 選項按鈕 357
17.2.8 分組框 357
17.2.9 切換按鈕 357
17.2.10 多頁控件 357
17.2.11 滾動條 357
17.2.12 圖像 357
17.2.13 RefEdit 357
17.2.14 附加控件 357
17.3 設置控件屬性 358
17.3.1 調整窗體控件位置與大小 358
17.3.2 設置控件的順序 358
17.3.3 共同屬性與非共同屬性 358
17.3.4 設置顏色屬性 359
17.3.5 設置控件的寬度與高度 360
17.3.6 設置Picture 屬性 360
17.3.7 設置RowSource 屬性 361
17.3.8 設置Flash 動畫 362
17.4 窗體與控件的事件 362
17.4.1 UserForm 對象的事件 362
17.4.2 激活窗體時將所有工作表名稱導入到列表框中 363
17.4.3 雙擊時關閉窗體 365
17.4.4 窗體永遠顯示在屏幕的左上角 365
17.4.5 按下左鍵移動窗體、按下右鍵移動控件 366
17.4.6 控件事件介紹 368
17.4.7 在窗體中建立超鏈接 368
17.4.8 鼠標移過時切換列表框數據 370
17.4.9 讓輸入學號的文字框僅能錄入6 位數字 372
17.4.10 運行窗體期間用鼠標調整文字框大小 373
17.4.11 為窗體中所有控件設置幫助 375
17.5 窗體的綜合應用案例 377
17.5.1 設計登錄界面 377
17.5.2 權限認證窗口 378
17.5.3 設計計划任務向導 380
17.5.4 設計動畫幫助 383
17.5.5 用窗體瀏覽圖片 383
17.5.6 設計多表錄入面板 385
17.5.7 多條件高級查詢 387

第18章 處理文件與文件夾 390
18.1 認識FSO 對象、屬性與方法 390
18.1.1 FSO 對象的調用方式 390
18.1.2 FSO 的對象 391
18.1.3 FSO 常用對象的方法與屬性 391
18.2 用FSO 處理文件與文件夾 394
18.2.1 讓D 盤中所有隱藏的文件夾顯示出來 394
18.2.2 遍歷子文件夾創建文件目錄 395
18.2.3 刪除D 盤中大小為0 的文件夾 396
18.2.4 羅列最近3 天修改過的所有文件的名稱 397

第19章 認識類和類模塊 399
19.1 類模塊基礎 399
19.1.1 類的概念與用途 399
19.1.2 聲明與調用類 399
19.2 類與應用程序級事件 401
19.2.1 在狀態欄顯示當前行的最大值與最小值地址 401
19.2.2 錄入數據時自動將「M」后面的數字「2」顯示為上標 403
19.3 類模塊與窗體控件 404
19.3.1 何時需要使用類 404
19.3.2 為按鈕批量指定MouseMove 事件 404
19.3.3 開發顏色面板 406

第20章 VBA 與注冊表 409
20.1 VBA 對注冊表的控制方式 409
20.1.1 什麼是注冊表 409
20.1.2 VBA 操作注冊表的方法 409
20.2 注冊表的應用 411
20.2.1 記錄當前工作簿最后一次打開時間 411
20.2.2 創建文件目錄時自動記憶上一次的路徑 412
20.2.3 讓是否顯示零值的設置適用於所有工作表 413
20.3 注冊表函數的缺點與改善方法 415
20.3.1 VBA 操作注冊表的優缺點 415
20.3.2 借用腳本自由控制注冊表 415
20.3.3 禁止使用U 盤 416

第21章 Ribbon 功能區設計 418
21.1 功能區開發基礎 418
21.1.1 Ribbon 的特點 418
21.1.2 功能區的組件圖示 418
21.1.3 手工定制功能區 419
21.1.4 認識Ribbon 代碼編輯器 419
21.1.5 獲取內置按鈕圖標 420
21.2 Ribbon 定制之語法分析 421
21.2.1 功能區代碼的結構 421
21.2.2 顯示與隱藏功能區：ribbon 422
21.2.3 隱藏選項卡：tab 423
21.2.4 創建新選項卡：tab 424
21.2.5 創建新組：group 425
21.2.6 創建對話框啟動器：dialogBoxLauncher 427
21.2.7 在組中添加命令按鈕：button 429
21.2.8 創建切換按鈕：toggleButton 430
21.2.9 標簽與復選框：labelControl／checkbox 432
21.2.10 在按鈕之間添加分隔條：separator 433
21.2.11 創建彈出式菜單：menu 434
21.2.12 創建拆分按鈕：SplitButton 435
21.2.13 創建下拉列表：DropDown 437
21.2.14 創建編輯框：editBox 438
21.2.15 鎖定或隱藏內置功能 439
21.3 使用回調函數強化功能區 440
21.3.1 為什麼需要使用回調函數 440
21.3.2 回調函數詳解 440
21.3.3 創建在每月的1 日到3 日才能使用的按鈕 443
21.3.4 創建按下與彈起時自動切換圖標的按鈕 444
21.3.5 創建一個能顯示圖形對象數量的標簽 446
21.3.6 在功能區中快速查找 447
21.3.7 在組的標簽處顯示問候語 449
21.3.8 調用大圖片創建下拉菜單 451
21.3.9 通過復選框控制錯誤標識的顯示狀態 454
21.3.10 在功能區中創建工作表目錄 455
21.4 使用模板 457
21.4.1 模板的重要性 457
21.4.2 模板的使用方法 458
21.5 制作兩個模板 458

第22章 開發通用插件 462
22.1 關於加載宏 462
22.1.1 加載宏的特點 462
22.1.2 為什麼使用加載宏 462
22.1.3 加載宏管理器 463
22.1.4 加載內置的加載宏 464
22.1.5 安裝與卸載自定義加載宏 464
22.2 關於加載項 465
22.2.1 加載項的分類 465
22.2.2 加載項的開發方式 465
22.3 開發插件的准備工作 466
22.3.1 加載宏的格式 466
22.3.2 引用加載宏的數據 466
22.3.3 設計加載宏的附加工作 466
22.4 開發公／農歷日歷控件 467
22.4.1 確認程序需要具備的功能 467
22.4.2 定義公歷轉農歷的函數 467
22.4.3 設計日期輸入器窗體 468
22.4.4 編寫窗體初始化代碼 469
22.4.5 實現輸入器與工作表交互 471
22.4.6 設計幫助 472
22.4.7 定制功能區菜單 473
22.4.8 測試並發布插件 474
22.5 開發文本與數值互換插件 475
22.5.1 確認所需具備的功能 475
22.5.2 編寫主程序 475
22.5.3 定制功能區菜單 476
22.5.4 測試代碼並發布插件 477

第23章 代碼封裝技巧 478
23.1 封裝自定義函數 478
23.1.1 安裝VB 6.0 企業版 478
23.1.2 封裝自定義函數 479
23.1.3 安裝自定義函數 480
23.2 封裝Sub 過程 480
23.2.1 建立VB 工程 481
23.2.2 添加引用 481
23.2.3 寫入代碼 482
23.2.4 發布COM 加載項 484
23.2.5 安裝COM 加載項 484
23.3 設計安裝軟件 485
23.3.1 程序選擇 485
23.3.2 使用程序向導制作安裝軟件 485
23.3.3 測試安裝軟件 488

第24章 開發逐步提示的數據錄入助手 490
24.1 羅列需求 490
24.1.1 插件功能描述 490
24.1.2 插件格式需求 490
24.2 設計窗體 491
24.2.1 設計選項窗體 491
24.2.2 設計數據錄入助手窗體 491
24.3 編寫代碼 492
24.3.1 選項窗體代碼 492
24.3.2 數據錄入助手窗體代碼 494
24.3.3 應用程序級事件代碼 499
24.4 創建功能區菜單 500
24.4.1 創建功能區菜單 500
24.4.2 回調過程 501
24.5 發布插件與測試功能 502
24.5.1 發布插件 502
24.5.2 測試插件功能 503

附錄（見本書光盤）
附錄A Msgbox 函數用法說明
附錄B Excel 2010 對象大全
附錄C Exce 2010 的新增事件
附錄D Excel 2010 所有內置常數枚舉
附錄E 命令按鈕屬性一覽
附錄F 文本框屬性一覽
附錄G 列表框屬性一覽
附錄H 365 個常見問題答疑