Excel VBA程序開發(fā)自學寶典（第4版）

目錄
第1章　初步感受VBA的魅力1
1.1  批量任務一鍵執(zhí)行1
1.1.1  準備工作1
1.1.2  程序測試1
1.1.3  案例點評2
1.2  數(shù)據(jù)匯總自動完成2
1.2.1  案例需求2
1.2.2  程序測試3
1.2.3  案例點評3
1.2.4  開發(fā)專業(yè)程序4
第2章　VBA程序入門5
2.1  如何存放代碼5
2.1.1  認識模塊5
2.1.2  管理模塊6
2.2  如何產(chǎn)生代碼7
2.2.1  復制現(xiàn)有的代碼7
2.2.2  錄制宏7
2.2.3  手工編寫代碼9
2.2.4  從模板中獲取代碼10
2.3  如何調(diào)用代碼10
2.3.1  ＜F5＞鍵10
2.3.2  組合鍵11 2.3.3  自定義快捷鍵12
2.3.4  按鈕12
2.3.5  菜單13
2.4  如何保存代碼13
2.4.1  工作簿格式13
2.4.2  解決丟失代碼問題14
2.4.3  顯示文件擴展名14
2.5  如何放行代碼15
2.5.1  調(diào)整“宏設置”16
2.5.2  添加受信任位置16
2.6  如何查詢代碼幫助17
2.6.1  Excel不同版本中的幫助差異17
2.6.2  如何調(diào)用幫助系統(tǒng)17
2.7  如何簡化開發(fā)難度19
2.7.1  錄制宏19
2.7.2  調(diào)用內(nèi)置提示19
2.7.3  調(diào)用筆記21
第3章　VBA的程序結(jié)構(gòu)詳解22
3.1  Sub過程基本語法22
3.1.1  認識程序結(jié)構(gòu)22
3.1.2  為VBA程序添加注釋23
3.2  過程的作用范圍24
3.2.1  何謂作用范圍24
3.2.2  公有過程與私有過程的區(qū)別24
3.3  過程的命名規(guī)則25
3.4  過程的參數(shù)25
3.5  過程的執(zhí)行流程26
3.5.1  正常的執(zhí)行流程26
3.5.2  改變程序的執(zhí)行流程26
3.6  中斷過程27
3.6.1  結(jié)束過程：End Sub27
3.6.2  中途結(jié)束過程：Exit Sub27
3.6.3  中途結(jié)束一切：End27
3.6.4  暫停過程：Stop27
3.6.5  手動暫停程序：Ctrl Break28
第4章　VBA四大基本概念29
4.1  Excel的對象29
4.1.1  什么是對象29
4.1.2  對象與對象集合30
4.1.3  對象的層次結(jié)構(gòu)32
4.1.4  父對象與子對象33
4.1.5  活動對象34
4.2  對象的方法和屬性35
4.2.1  屬性與方法的區(qū)別36
4.2.2  查詢方法與屬性的兩種方法36
4.2.3  方法與屬性的應用差異37
4.3  對象的事件40
4.3.1  什么是事件40
4.3.2  事件的分類及其層級關系41
4.3.3  工作簿事件與工作表事件一覽41
4.3.4  工作簿與工作表事件的作用對象42
4.3.5  快速掌握事件過程44
4.3.6  何時需要使用事件過程44
第5章　通過變量強化程序功能45
5.1  數(shù)據(jù)類型45
5.1.1  為什么要區(qū)分數(shù)據(jù)類型45
5.1.2  認識VBA的數(shù)據(jù)類型45
5.2  聲明變量47
5.2.1  變量的定義47
5.2.2  變量的聲明方式47
5.2.3  變量的賦值方式與初始值49
5.2.4  如何確定變量的數(shù)據(jù)類型正確50
5.2.5  正確聲明變量的數(shù)據(jù)類型的優(yōu)勢50
5.2.6  變量的作用域52
5.2.7  變量的生命周期53
5.3  對象變量53
5.3.1  如何區(qū)分對象變量和數(shù)據(jù)變量54
5.3.2  對變量賦值54
5.3.3  使用對象變量的優(yōu)勢55
5.4  聲明常量56
5.4.1  常量的定義與用途56
5.4.2  常量的聲明方式57
5.2.3  常量的命名規(guī)則57
第6章　詳解五大常用對象59
6.1  對象基礎知識59
6.1.1  對象的結(jié)構(gòu)59
6.1.2  對象與對象的集合60
6.1.3  引用集合中的單一對象60
6.1.4  父對象與子對象60
6.1.5  活動對象61
6.1.6  使用變量簡化對象的引用61
6.1.7  使用With語句簡化對象的引用62
6.2  單元格對象63
6.2.1  Range("A1")方式引用單元格64
6.2.2  Cells(1,1)方式引用單元格66
6.2.3  [a1]方式引用單元格67
6.2.4  Range（"A1"）、Cells（1,1）與[a1]比較68
6.2.5  Selection與ActiveCell：當前選區(qū)與活動單元格69
6.2.6  已用區(qū)域與當前區(qū)域69
6.2.7  SpecialCells：按條件引用區(qū)域70
6.2.8  CurrentArray：引用數(shù)組區(qū)域74
6.2.9  Resize：重置區(qū)域大小75
6.2.10  Offset：根據(jù)偏移量引用新區(qū)域76
6.2.11  Union：多區(qū)域合集78
6.2.12  Intersect：單元格、區(qū)域的交集79
6.2.13  End：引用源區(qū)域的區(qū)域尾端的單元格80
6.2.14  EntireRow/EntireColumn：擴展至整行、整列81
6.2.15  RangeSelection：工作表中的選定單元格82
6.2.16  VisibleRange：指定窗口的可見區(qū)域83
6.3  批注對象84
6.3.1  批注對象的特點84
6.3.2  判斷單元格中是否有批注85
6.3.3  AddComment：添加單元格批注85
6.3.4  AutoSize：讓批注框自動縮放86
6.3.5  AutoShapeType：修改批注的外形86
6.3.6  ClearComments：清空表中所有批注86
6.4  圖形對象87
6.4.1  Shapes：圖形對象集合87
6.4.2  圖形對象的名稱87
6.4.3  隱藏的圖形對象集合88
6.5  表對象90
6.5.1  表的類別90
6.5.2  Worksheets：工作表集合90
6.5.3  引用工作表子集91
6.5.4  ActiveSheet：活動表91
6.5.5  工作表的特性92
6.5.6  新建工作表93
6.6  工作簿對象93
6.6.1  工作簿格式與特性93
6.6.2  新建工作簿94
6.6.3  Workbooks：工作簿集合94
6.6.4  引用工作簿子集94
6.6.5  ActiveWorkbook：活動工作簿94
第7章　常用語句解析95
7.1  輸出/輸入語句95
7.1.1  MsgBox函數(shù)95
7.1.2  Application.InputBox方法98
7.2  條件判斷語句103
7.2.1  IIf函數(shù)的語法與應用103
7.2.2  IIf函數(shù)的限制105
7.2.3  If Then語句的語法詳解106
7.2.4  If Then應用案例106
7.2.5  If Then Else語句的語法與應用107
7.2.6  多條件嵌套的條件判斷語句110
7.2.7  Select Case語法詳解114
7.2.8  Select Case與If Then Else之比較117
7.2.9  借用Choose函數(shù)簡化條件選擇118
7.3  循環(huán)語句120
7.3.1  For Next語句120
7.3.2  For Each Next語句127
7.3.3  Do Loop語法詳解132
7.4  錯誤處理語句140
7.4.1  錯誤類型與原因140
7.4.2  err對象及其屬性、方法141
7.4.3  認識Error函數(shù)142
7.4.4  On Error GoTo line142
7.4.5  On Error Resume Next146
7.4.6  On Error GoTo 0151
7.5  預覽語句151
7.5.1  認識FileDialog對象151
7.5.2  選擇路徑152
7.5.3  選擇文件153
7.5.4  按類型選擇文件154
第8章　開發(fā)自啟動程序157
8.1  讓宏自動執(zhí)行157
8.1.1  Auto自動宏157
8.1.2  升級版自動宏：事件158
8.1.3  事件的禁用與啟用160
8.1.4  事件的特例161
8.2  工作表事件162
8.2.1  在狀態(tài)欄提示值的單元格地址162
8.2.2  快速輸入出勤表163
8.2.3  在狀態(tài)欄顯示選區(qū)的字母、數(shù)字、漢字個數(shù)164
8.2.4  實時監(jiān)控單元格每一次編輯的數(shù)據(jù)與時間166
8.2.5  利用數(shù)字簡化公司名輸入168
8.2.6  輸入數(shù)據(jù)時自動跳過帶公式的單元格170
8.3  工作簿事件171
8.3.1  新建工作表時自動設置頁眉171
8.3.2  未匯總則禁止打印與關閉工作簿172
8.3.3  自動選中相同值并計數(shù)174
8.3.4  設計未啟用宏就無法打開的工作簿176
8.4  定時執(zhí)行的程序177
8.4.1  基本語法178
8.4.2  每天在13:28語音提示開會178
8.4.3  在單元格中顯示當前時期179
第9章　綜合應用案例181
9.1  Application應用案例181
9.1.1  計算字符表達式181
9.1.2  合并相同且相鄰的單元格182
9.1.3  定時打印文件184
9.1.4  模擬鍵盤快捷鍵打開高級選項185
9.1.5  使用快捷鍵合并與取消合并單元格186
9.1.6  查找至少兩月未付貨款的客戶名稱188
9.2  Range對象應用案例190
9.2.1  合并工作表190
9.2.2  合并區(qū)域且保留所有數(shù)據(jù)193
9.2.3  合并計算多區(qū)域的值194
9.2.4  模糊查找公司名稱并羅列出來196
9.2.5  反向選擇單元格198
9.2.6  插入圖片并調(diào)整為選區(qū)大小200
9.2.7  提取值202
9.2.8  隱藏所有公式結(jié)果為錯誤的單元格203
9.3  Comment 對象應用案例205
9.3.1  在所有批注末尾添加指定日期205
9.3.2  生成圖片批注207
9.3.3  添加個性化批注208
9.3.4  批量修改當前表的所有批注外形210
9.4  WorkSheet對象應用案例213
9.4.1  新建工作表并且命名為今天日期213
9.4.2  批量保護工作表與解除保護214
9.4.3  為所有工作表設置水印216
9.4.4  批量命名工作表218
9.4.5  判斷篩選條件220
9.5  Workbook對象應用案例223
9.5.1  拆分工作簿223
9.5.2  每10分鐘備份一次工作簿225
9.5.3  5分鐘內(nèi)未編輯工作簿則自動備份226
9.5.4  記錄文件打開次數(shù)228
9.5.5  不打開工作簿而提取數(shù)據(jù)230
9.5.6  建立指定文件夾下所有工作簿目錄和工作表目錄232
第10章　編程規(guī)則與代碼優(yōu)化234
10.1  代碼編寫規(guī)則234
10.1.1  對代碼添加注釋234
10.1.2  長代碼分行237
10.1.3  代碼縮進對齊238
10.1.4  聲明有意義的變量名稱239
10.1.5  If Then End If類配對語句的輸入方式240
10.1.6  輸入屬性與方法的技巧240
10.1.7  無提示的詞組的輸入技巧242
10.1.8  善用公共變量242
10.1.9  使用常量名稱替代數(shù)值243
10.1.10  盡可能兼容Excel 2003到Excel 2019之間的所有版本243
10.2  優(yōu)化代碼思路244
10.2.1  強制聲明變量244
10.2.2  善用常量244
10.2.3  關閉屏幕更新244
10.2.4  利用With減少對象讀取次245
10.2.5  利用變量減少對象讀取次數(shù)245
10.2.6  善用帶$的字符串處理函數(shù)246
10.2.7  不使用Select和Activate直接操作對象246
10.2.8  將與循環(huán)無關的語句放到循環(huán)語句外247
10.2.9  利用Instr函數(shù)簡化字符串判斷247
10.2.10  使用Replace簡化字符串連接248
10.3  借用參數(shù)簡化程序調(diào)用248
10.3.1  參數(shù)的概念與用途248
10.3.2  參數(shù)的語法結(jié)構(gòu)249
10.3.3  設計帶有必選參數(shù)的過程249
10.3.4  設計帶有可選參數(shù)的過程251
10.3.5  設計帶有不確定數(shù)量參數(shù)的過程253
10.3.6  參數(shù)的賦值方式254
第11章　高階應用1：數(shù)組256
11.1  數(shù)組基礎256
11.1.1  何謂數(shù)組256
11.1.2  數(shù)組的特點256
11.1.3  一維數(shù)組257
11.1.4  二維數(shù)組259
11.1.5  數(shù)組的參數(shù)260
11.1.6  聲明數(shù)組變量261
11.1.7  動態(tài)數(shù)組與靜態(tài)數(shù)組的區(qū)別263
11.1.8  釋放動態(tài)數(shù)組的存儲空間268
11.2  數(shù)組函數(shù)268
11.2.1  用函數(shù)創(chuàng)建數(shù)組268
11.2.2  獲取數(shù)組元素270
11.2.3  判斷變量是否為數(shù)組270
11.2.4  轉(zhuǎn)置數(shù)271
11.2.5  獲取數(shù)組的上標與下標272
11.2.6  轉(zhuǎn)換文本與數(shù)組273
11.2.7  篩選數(shù)組275
11.3  數(shù)組實戰(zhàn)276
11.3.1  將指定區(qū)域的單詞統(tǒng)一為首字母大寫276
11.3.2  羅列不及格人員姓名、科目和成績277
11.3.3  跨表搜索學員信息278
11.3.4  將職員表按學歷拆分成多個工作表280
11.3.5  將選區(qū)的數(shù)據(jù)在文本與數(shù)值間互換281
11.3.6  獲取兩列數(shù)據(jù)的相同283
11.3.7  無人值守的多工作簿自動匯總285
第12章　高階應用2：正則表達式288
12.1  何謂正則表達式288
12.1.1  概念288
12.1.2  特點288
12.1.3  調(diào)用方式289
12.2  語法基礎290
12.2.1  調(diào)用正則表達式的基本格式290
12.2.2  正則表達式的對象、屬性和方法291
12.2.3  匹配的優(yōu)先順序294
12.2.4  借用元字符強化搜索功能295
12.3  正則表達式應用311
12.3.1  亂序字符串取值并匯總311
12.3.2  計算建筑面積312
12.3.3  計算括號中的數(shù)字合計313
12.3.4  刪除字符串首尾的空白字符314
12.3.5  將字符串中的多段數(shù)字分列315
12.3.6  提取E-mail地址315
12.3.7  提取文件的路徑和名稱316
12.3.8  匯總?cè)嗣駧?17
12.3.9  分列的高級應用318
12.3.10  刪除重復字詞319
第13章　高階應用3：字典321
13.1  Dictionary對象基礎321
13.1.1  Dictionary對象的調(diào)用321
13.1.2  Dictionary對象的特點323
13.1.3  Dictionary對象的屬性與方法323
13.2  Dictionary對象的應用技巧328
13.2.1  利用Dictionary創(chuàng)建三級選單328
13.2.2  分類匯總330
13.2.3  對多列數(shù)據(jù)相同者應用背景色331
13.2.4  按姓名計數(shù)與求產(chǎn)量平均值332
13.2.5  按品名統(tǒng)計半年的產(chǎn)量合計333
第14章　高階應用4：設計窗體335
14.1  UserForm簡介335
14.1.1  窗體與控件的用途335
14.1.2  插入窗體與添加控件的方法335
14.2  窗體控件一覽336
14.2.1  標簽336
14.2.2  文本框336
14.2.3  命令按鈕336
14.2.4  復合框337
14.2.5  列表框337
14.2.6  復選框337
14.2.7  選項按鈕337
14.2.8  分組框337
14.2.9  切換按鈕337
14.2.10  多頁控件337
14.2.11  滾動條338
14.2.12  圖像338
14.2.13  RefEdit338
14.2.14  附加控件338
14.3  設置控件屬性338
14.3.1  調(diào)整窗體控件位置與大小339
14.3.2  設置控件的順序339
14.3.3  共同屬性與非共同屬性339
14.3.4  設置顏色屬性339
14.3.5  設置控件的寬度與高340
14.3.6  設置Picture屬性340
14.3.7  設置RowSource屬性341
14.3.8  設置List屬性342
14.4  窗體與控件的事件343
14.4.1  UserForm對象的事件343
14.4.2  激活窗體時將所有工作表名稱導入到列表框344
14.4.3  雙擊或者按鍵關閉窗體345 14.4.4  窗體永遠顯示在上左角346
14.4.5  按下鼠標左鍵移動窗體，按下鼠標右鍵移動控件346
14.4.6  控件事件介紹348
14.4.7  在窗體中建立超鏈接349
14.4.8  將鼠標指針移過時切換列表框數(shù)據(jù)351
14.4.9  讓輸入學號的文本框僅能輸入6位數(shù)字352
14.4.10  運行窗體期間用鼠標調(diào)整文字框大小354
14.4.11  為窗體中所有控件設置幫助355
14.5  窗體的綜合應用案例357
14.5.1  設計登錄界面357
14.5.2  權(quán)限認證窗口358
14.5.3  設計計劃任務向?qū)?59
14.5.4  設計文字滾動的動態(tài)幫助信息，并且可單擊停止362
14.5.5  用窗體預覽圖片364
14.5.6  設計多表輸入面板365
14.5.7  多條件高級查詢367
第15章　高階應用5：VBA與注冊370
15.1  VBA對注冊表的控制方式370
15.1.1  什么是注冊表370
15.1.2  VBA操作注冊表的方法371
15.2  注冊表的應用372
15.2.1  記錄后一次打開工作簿的時間372
15.2.2  在窗體中預覽圖片，自動記憶上次的路373
15.2.3  調(diào)整所有表的零值顯示狀態(tài)375
15.2.4  插入簽名圖片到活動單元格376
15.3  注冊表函數(shù)的缺點與改善方法377
15.3.1  VBA操作注冊表的缺點377
15.3.2  借用腳本自由控制注冊表378
15.3.3  修改注冊表禁用QQ和記事本378
15.3.4  禁止使用U盤380
第16章　高階應用6：處理文件與文件夾382
16.1  認識FSO對象、屬性與方法382
16.1.1  FSO對象的調(diào)用方式382
16.1.2  FSO的對象383
16.1.3  FSO常用對象的方法與屬性383
16.2  用FSO處理文件與文件夾386
16.2.1  將D盤中所有隱藏的文件夾顯示出來386
16.2.2  遍歷子文件夾創(chuàng)建文件目錄387
16.2.3  刪除D盤中大小為0的文件夾388
16.2.4  羅列近三天修改過的所有文件的名稱389
16.3  讀/寫文本文件390
16.3.1  創(chuàng)建文本文件并輸入內(nèi)容390
16.3.2  讀取文本文件391
16.3.3  開發(fā)拆分工作簿工具，可加載參數(shù)391
第17章　高階應用7：開發(fā)自定義函數(shù)396
17.1  自定義函數(shù)的功能和語法396
17.1.1  Function過程與Sub過程的區(qū)別396
17.1.2  Function過程的語法396
17.1.3  自定義函數(shù)的命名規(guī)則398
17.2  開發(fā)不帶參數(shù)的Function過程398
17.2.1  判斷活動工作簿是否存在圖形對象398
17.2.2  計算公式所在單元格的頁數(shù)399
17.3  開發(fā)帶有一個參數(shù)的Function過程400
17.3.1  在不規(guī)則的合并單元格中執(zhí)行合計400
17.3.2  計算單元格中“元”前方的數(shù)值之和401
17.4  開發(fā)帶有兩個參數(shù)的Function過403
17.4.1  提取所有數(shù)值與匯總403
17.4.2  獲取值、小值或眾數(shù)的地址404
17.4.3  去除括號后計算表達式406
17.5  開發(fā)復雜的Function過程407
17.5.1  按條件串連字符407
17.5.2  按單元格背景顏色進行條件求和409
17.5.3  按顏色查找并返回數(shù)組410
17.5.4  計算兩列的相同項與不同項412
17.6  編寫函數(shù)幫助414
17.6.1  MacroOptions方法的語法414
17.6.2  為函數(shù)分類及添加說明415
第18章　高階應用8：ribbon功能區(qū)設計417
18.1  功能區(qū)開發(fā)基礎417
18.1.1  ribbon的特點417
18.1.2  功能區(qū)的組件圖示417
18.1.3  手工定制功能區(qū)418
18.1.4  認識ribbon代碼編輯器418
18.1.5  獲取內(nèi)置按鈕圖標419
18.2   ribbon定制之語法分析420
18.2.1  功能區(qū)代碼的結(jié)構(gòu)420
18.2.2  顯示與隱藏功能區(qū)：ribbon422
18.2.3  隱藏選項卡：tab423
18.2.4  創(chuàng)建新選項卡：tab424
18.2.5  創(chuàng)建新組：group425
18.2.6  創(chuàng)建對話框啟動器：dialogBoxLauncher26
18.2.7  在組中添加命令按鈕：butto428
18.2.8  創(chuàng)建切換按鈕：toggleButton430
18.2.9  標簽與復選框：labelControl/ checkBox431
18.2.10  在按鈕之間添加分隔條：separator432
18.2.11  創(chuàng)建彈出式菜單：men433
18.2.12  創(chuàng)建拆分按鈕：SplitButton434
18.2.13  創(chuàng)建下拉列表：DropDown436
18.2.14  創(chuàng)建編輯框：editBox437
18.2.15  鎖定或隱藏內(nèi)置命令438
18.3  使用回調(diào)函數(shù)強化功能區(qū)439
18.3.1  為什么需要使用回調(diào)函數(shù)439
18.3.2  回調(diào)函數(shù)詳439
18.3.3  創(chuàng)建在每月的1日到3日才能使用的按442
18.3.4  創(chuàng)建按下與彈起時自動切換圖標的按鈕443
18.3.5  創(chuàng)建一個能顯示圖形對象數(shù)量的標簽445
18.3.6  在功能區(qū)中快速查找446
18.3.7  在組的標簽處顯示問候語448
18.3.8  調(diào)用大圖片創(chuàng)建下拉菜449
18.3.9  通過復選框控制錯誤標識的顯示狀態(tài)452
18.3.10  在功能區(qū)創(chuàng)建工作表目錄454
18.4  使用模板455
18.4.1  模板的重要性455
18.4.2  模板的使用方法456
18.5  制作兩個模板456
第19章　高階應用9：與Word/PPT協(xié)同辦公460
19.1  操作Word和PPT文件的基本思路460
19.1.1  引用對象460
19.1.2  代碼模板461
19.1.3  改造代碼462
19.2  使用VBA操作Word465
19.2.1  批量替換Word文檔465
19.2.2  根據(jù)Excel成績表批量生成Word通知單467
19.2.3  將Word版的簡歷提取到Excel表471
19.2.4  批量插入圖片到Word并創(chuàng)建目錄474
19.3  使用VBA操作PPT476
19.3.1  根據(jù)明細表和圖片自動生成PPT477
19.3.2  批量從PPT中取值到Excel481
第20章　高階應用10：開發(fā)通用插件485
20.1  認識加載宏485
20.1.1  加載宏的特點485
20.1.2  為什么使用加載宏486
20.1.3  加載項管理器487
20.1.4  加載內(nèi)置的加載項487
20.1.5  安裝與卸載自定義加載宏487
20.1.6  開發(fā)加載宏應選哪種格式488
20.1.7  安裝加載宏后如何引用其數(shù)488
20.1.8  設計加載宏的附加工作488
20.2  開發(fā)合并工作表插件489
20.2.1  確認程序需具備的功能489
20.2.2  設計窗489
20.2.3  生成窗體代碼490
20.2.4  設計功能區(qū)菜單492
20.2.5  安裝插件并測試功能494
20.3  開發(fā)批量打印標簽插件496
20.3.1  確認程序需具備的功能496
20.3.2  設計模板496
20.3.3  編寫代碼496
20.3.4  設計功能區(qū)菜單499
20.3.5  安裝插件并測試功能500