Excel 經典教程 — VBA 與宏

比爾·耶倫是北美知名的Excel專家、Excel MVP和MrExcel 網站的負責人，自1985年起就開始使用電子表格，擁有12年的上市公司財務分析師工作經驗，於1998 年創建了MrExcel網站，其視頻播客“Learn Excel from MrExcel”至今已推出了1 200 多集，他編寫了30 本Microsoft Excel 方面的書，每個月還會為Strategic Finance雜誌撰寫Excel專欄。

第1章使用VBA釋放Excel的力量1
1．1 Excel的力量1
1．2入門難點1
1．2．1宏錄製器無法工作1
1．2．2 Visual Basic不同於BASIC 2
1．2．3好消息：非常容易入門2
1．2．4非常棒的消息：Excel VBA值得學習2
1．3了解你的工具：“開發工具”選項卡3
1．4宏安全性3
1．4．1添加受信任位置4
1．4．2使用宏設置啟用不在受信任位置的工作簿中的宏5
1．4．3使用設置“禁用所有宏，並發出通知” 5
1．5錄製宏、存儲宏和運行宏概述6
1．6運行宏7
1．6．1在功能區中創建宏按鈕8
1．6．2在快速訪問工具欄上創建宏按鈕8
1．6．3將宏關聯到窗體控件、文本框或形狀9
1．7了解VB編輯器11
1．7．1 VB編輯器設置11
1．7．2自定義VB編輯器選項設置11
1．7．3工程資源管理器12
1．7． 4屬性窗口13
1．8了解宏錄製器的不足之處13
案例分析：準備錄製宏13
錄製宏15
1．8．1在編程窗口中查看代碼15
1．8．2日後運行該宏時得到意外的結果18
1．8．3一種可能的解決方案：在錄製宏時使用相對引用18
案例分析：使用相對引用錄製宏18
1．8．4錄製宏時千萬不要使用“自動求和”按鈕22
1．8．5使用宏錄製器時的三點建議22

第2章聽起來像BASIC，但為什麼它們並不相似23
2．1我不理解這種代碼23
2．2了解VBA語言的組成部分24
2．3 VBA實際並不難27
2．3．1 VBA幫助文件：使用F1鍵獲取任何幫助27
2．3．2使用幫助主題28
2．4查看錄製的宏代碼：使用VB編輯器和幫助29
2．4．1可選參數30
2．4．2定義常量31
2．4．3可返回對象的屬性35
2．5使用調試工具幫助理解錄製的代碼36
2．5．1單步執行代碼36
2．5．2另一個調試選項：斷點39
2． 5．3在代碼中向前或向後移動40
2．5．4不逐步執行每行代碼40
2．5．5在逐句執行代碼時進行查詢40
2．5．6使用監視設置斷點45
2．5．7監視對象45
2．6對象瀏覽器：終極參考信息46
2．7整理所錄製代碼的7點建議48
2．8案例分析：綜合應用——修改錄製的代碼51

第3章引用區域54
3． 1 Range對象54
3．2指定區域的語法54
3．3命名區域55
3．4引用區域的快捷方式55
3．5在其他工作表中引用區域55
3．6引用相對於其他區域的區域56
3 ．7使用Cells屬性選擇區域57
3．8使用Offset屬性引用區域58
3．9使用Resize屬性改變區域的大小59
3．10使用Columns和Rows屬性指定區域60
3．11使用Union方法合併多個區域61
3．12使用Intersect方法在重疊區域創建新區域61
3．13使用ISEMPTY函數檢查單元格是否為空61
3．14使用CurrentRegion屬性選擇數據區域63
案例分析：使用SpecialCells方法選擇特定單元格63
3．15使用Areas集合返回非連續區域65
3．16引用數據表66

第4章用戶自定義函數67
4．1創建用戶自定義函數67
案例分析：用戶自定義函數——示例和解析67
4．2共享UDF 69
4．3有用的Excel自定義函數69
4．3．1在單元格中獲取當前工作簿的名稱70
4．3．2在單元格中獲取當前工作簿的名稱和文件名70
4．3．3檢查工作簿是否打開70
4．3．4檢查打開的工作簿中是否存在工作表71
4．3 ．5統計目錄中的工作簿數量72
4．3．6獲取USERID 73
4．3．7獲取最後一次保存的日期和時間74
4．3．8獲取固定的日期和時間74
4．3．9驗證E-mail地址75
4．3．10根據內部顏色對單元格求和77
4．3．11統計唯一值的數量78
4．3．12刪除區域中的重複值79
4．3．13在區域中查找第一個非空單元格81
4．3．14替換多個字符82
4．3．15從混合文本中獲取數值83
4．3．16將星期編號轉換為日期84
4．3．17從使用分隔符分離的字符串中提取元素84
4．3．18排序並連接85
4．3．19對數字和字符進行排序87
4．3．20在文本中查找字符串89
4．3．21顛倒單元格中內容的順序89
4．3．22多個最大值90
4．3．23返回超鏈接地址91
4．3．24返回單元格地址的列字母91
4．3 ．25靜態隨機92
4．3．26在工作表中使用Select Case 92

第5章循環和流程控制94
5．1 For．．．Next循環94
5．1．1在聲明語句For中使用變量97
5．1．2 For．．．Next循環的變體97
5．1．3在特定條件滿足時提前跳出循環98
5．1．4循環嵌套99
5．2 Do循環100
5．2．1在Do循環中使用While或Until語句102
5．2．2 While．．．Wend循環104
5．3 VBA循環：For Each 104
對像變量105
案例分析：遍歷文件夾中的所有文件106
5．4流程控制：使用If．．．Then．．．Else和Select Case 108
5．4．1基礎的流程控制：If．．．Then．．．Else 108
5．4．2條件108
5．4．3 If．．．Then．．．End If 109
5．4．4 Either/Or決策： If．．．Then．．．Else．．．End If109
5．4．5使用If．．．Else If．．．End If檢測多項條件109
5．4．6使用Select Case．．．End Select檢測多項條件110
5．4．7在Case語句中使用複雜表達式111
5．4．8嵌套If語句111

第6章R1C1引用樣式114
6．1引用單元格：A1和R1C1引用樣式的比較114
6．2將Excel切換到R1C1引用樣式114
6．3 Excel公式創造的奇蹟115
6．3．1輸入一次公式並複制1 000次116
6．3．2秘密：其實並不神奇117
案例分析：在VBA中使用A1樣式和R1C1引用樣式的比較118
6．4 R1C1引用樣式簡介119
6．4．1使用R1C1相對引用119
6．4． 2使用R1C1絕對引用120
6．4．3使用R1C1混合引用120
6．4．4使用R1C1引用樣式引用整行或整列121
6．4．5使用一個R1C1引用樣式替換多個A1公式121
6．4 ．6記住與列字母相關的列號123
6．5在數組公式中需要使用R1C1引用樣式124

第7章在VBA中創建和操作名稱125
7．1 Excel名稱125
7．2全局名稱和局部名稱125
7．3添加名稱126
7．4刪除名稱127
7．5添加備註127
7．6名稱類型128
7．6．1公式128
7．6．2字符串128
7．6．3數字130
7．6 ．4數據表130
7．6．5在名稱中使用數組131
7．6．6保留名稱131
7．7隱藏名稱132
7．8檢驗名稱是否存在133
案例分析：將命名區域用作函數VLOOKUP的參數133

第8章事件編程136
8．1事件級別136
8．2使用事件136
8．2．1事件參數137
8．2 ．2啟用事件137
8．3工作簿事件138
處於工作簿等級的工作表和圖表事件143
8．4工作表事件144
案例分析：在單元格中快速輸入24小時制時間147
8．5圖表事件148嵌入圖表148
8．6應用程序級事件151

第9章用戶窗體簡介157
9．1用戶交互方式157
9．1．1輸入框157
9．1．2消息框157
9．2創建用戶窗體158
9．3調用和隱藏用戶窗體159
9．4用戶窗體編程160
9．5控件編程161
案例分析：向現有窗體中添加控件時進行
錯誤修正162
9．6使用基本的窗體控件163
9．6．1使用標籤、文本框和命令按鈕163
9．6．2選擇在窗體中使用列錶框還是文本框164
9．6．3在用戶窗體中添加單選鈕167
9．6．4在用戶窗體中添加圖片169
9．6．5在用戶窗體中使用微調按鈕169
9．6．6使用多頁控件組合窗體171
9．7驗證用戶輸入173
9．8非法關閉窗口173
9．9獲取文件名174

第10章創建圖表176
10 ．1 Excel中的圖表176
10．2在VBA代碼中引用圖表和圖表對象176
10．3創建圖表177
10．3．1指定圖表的大小和位置177
10．3．2日後引用特定圖表178
10． 4錄製“佈局”或“設計”選項卡中的命令180
10．4．1指定一個內置圖表類型181
10．4．2指定模板圖表類型183
10．4．3修改圖表的佈局或樣式184
10． 5使用SetElement模仿在“布局”選項卡中所做的修改186
10．6使用VBA修改圖表標題191
10．7模擬在“格式”選項卡中所做的修改191
使用Format方法訪問格式選項191
10 ．8創建高級圖表205
10．8．1創建真正的“開盤-盤高-盤低-收盤”股價圖205
10．8．2為頻數圖創建區間207
10．8．3創建堆積面積圖210
10．9將圖表導出為圖形215
在用戶窗體中創建動態圖表215
10．10創建數據透視圖217

第11章使用高級篩選進行數據挖掘220
11．1使用自動篩選代替循環220
11．1．1使用新增的自動篩選技術222
11．1．2只篩選可見單元格225
11．2案例分析：使用定位條件代替循環226
11．3在VBA中使用高級篩選比在Excel用戶界面中更容易227
通過Excel用戶界面創建一個高級篩選227
11．4使用高級篩選提取非重複值列表228
11．4．1通過用戶界面提取非重複值列表229
11．4．2使用VBA代碼提取非重複值列表229
11．4．3獲取多個字段的不重複組合233
11．5使用包含條件區域的高級篩選234
11．5．1使用邏輯or合併多個條件236
11．5．2使用邏輯and合併兩個條件236
11．5．3其他稍微複雜的條件區域237
11．5．4最複雜的條件：使用公式結果作為條件代替值列表237
11．6案例分析：使用非常複雜的條件237
11．7案例分析：在Excel用戶界面中使用基於公式的條件239
11．8在原有區域顯示高級篩選結果244
11．8．1在原有區域使用篩選卻沒有篩選出任何記錄245
11．8．2在原有區域篩選之後顯示所有記錄245
11．9最常用的功能：使用xlFilterCopy複製所有記錄而不只是非重複記錄246
11．9．1複製所有列246
11．9．2複製部分列並重新排序247
11．10案例分析：使用兩種高級篩選為每個顧客創建報表249
11．11在原區域篩選非重複記錄252
Excel實踐：在自動篩選時關閉部分下拉列表254

第12章使用VBA創建數據透視表255
12．1數據透視表簡介255
12．2版本介紹255
12．2．1自Excel 2010新增的功能256
12．2．2自Excel 2007新增的功能256
12．3在Excel用戶界面中創建數據
透視表258
壓縮佈局簡介261

12．4在Excel VBA中創建數據透視表262
12．4．1定義數據透視表緩存262
12．4．2創建並配置數據透視表263
12．4．3向數據區域添加字段264
12．4．4無法移動或修改部分數據透視表的原因267
12．4．5確定數據透視表的最終大小以便將其轉化為值267
12．5使用高級數據透視表功能270
12．5．1使用多個值字段270
12．5．2統計記錄的數量271
12．5．3將日期按月份、季度或年進行分組271
12．5．4修改計算方法顯示百分比274
12．5．5刪除值區域中的空單元格276
12．5．6使用“自動排序”控制排列順序276
12．5 ．7為每種產品複製報表277
12．6篩選數據集280
12．6．1手工篩選數據透視表字段中的多個記錄280
12．6．2使用概念篩選281
12．6．3使用搜索篩選器285
案例分析：使用篩選器篩選出前5或前10名記錄285
12．6．4創建切片器來篩選數據透視表288
12．6．5使用命名集篩選OLAP數據透視表290
12．7使用其他數據透視表功能292
12．7．1計算數據字段292
12．7．2計算項293
12．7．3使用ShowDetail篩選數據集293
12．7．4通過“設計”選項卡修改佈局293
12．7．5禁用多行字段的分類匯總294
案例分析：應用數據可視化295

第13章Excel的力量296
13．1文件操作296
13．1．1列出文件夾中的文件296
13．1．2導入CSV 299
13 ．1．3將整個TXT文件讀入內存並進行分析300
13．2合併、拆分工作簿301
13．2．1將工作表合併成工作簿301
13．2．2合併工作簿302
13．2 ．3篩選數據並將結果復製到新工作表中303
13．2．4將數據導出為Word文件304
13．3處理單元格批註305
13．3．1列表批註305
13．3．2調整批註框的大小306
13．3．3使用居中調整批註框的大小308
13．3．4將圖表加入批註框309
13．4讓客戶叫絕的程序310
13．4．1使用條件格式突出顯示單元格310
13．4．2在不使用條件格式的情況下突出顯示單元格312
13．4．3自定義轉置數據313
13．4．4選中/取消選中非連續單元格315
13．5 VBA專業技術318
13．5．1數據透視表深化318
13．5．2加速頁面設置319
13．5．3計算代碼的執行時間322
13．5． 4自定義排列順序323
13．5．5單元格進度指示器324
13．5．6密碼保護框325
13．5．7更改大小寫327
13．5．8使用SpecialCells進行選擇329
13．5．9 ActiveX右鍵菜單330
13．6一個出色的應用程序331

第14章數據可視化與條件格式334
14．1數據可視化簡介334
14．2 VBA中的數據可視化方法和屬性335
14．3向區域中添加數據條336
14．4在區域中添加色階340
14．5在區域中添加圖標集342
14．5．1指定圖標集342
14．5．2為每個圖標指定範圍344
14．6使用可視化技巧345
14．6．1為部分區域創建圖標集345
14．6．2在同一區域中應用兩種顏色的數據條347
14．7使用其他條件格式方法350
14．7． 1設置高於或低於平均值單元格的格式350
14．7．2設置值為前5名或後10名單元格的格式350
14．7．3設置非重複或重複單元格的格式351
14 ．7．4根據單元格的值設置其格式353
14．7．5設置包含文本的單元格格式353
14．7．6設置包含日期的單元格格式354
14．7．7設置包含空格或錯誤的單元格格式354
14．7．8使用公式確定要設置格式的單元格354
14．7．9突出顯示最大銷量所在的行356
14．7．10使用新增的NumberFormat屬性356

第15章在Excel中使用迷你圖繪製儀表板358
15．1創建迷你圖358
15．2設置迷你圖的範圍360
15．3設置迷你圖格式365
15．3．1應用主題顏色365
15．3．2應用RGB顏色368
15．3．3設置迷你圖元素的格式370
15．3 ．4設置盈/虧圖表的格式373
15．4創建儀表板374
15．4．1觀察迷你圖得到的結果375
15．4．2在儀表板中創建130多個獨立的迷你圖375

第16章自動控制Word 380
16．1前期綁定380
編譯錯誤：無法找到對像或庫382
16．2後期綁定383
16．3創建和引用對象383
16．3．1關鍵字New 384
16．3．2 CreateObject函數384
16．3．3 GetObject函數384
16．4使用常量386
16．4．1使用監視窗口檢索常量的真實值386
16．4．2使用對象瀏覽器檢索常量的真實值386
16．5 Word對象簡介387
16．5．1 Document對象388
16．5．2 Selection對象389
16．5．3 Range對象390
16．5．4書籤394
16．6控制Word窗體控件397

第17章數組400
17．1聲明數組400多維數組400
17．2填充數組401
17．3清空數組403
17．4使用數組提高代碼的執行速度404
17．5動態數組406
17．6傳遞數組407

第18章處理文本文件408
18．1導入文本文件408
18．1．1導入不超過1 048 576行的文本文件408
18． 1．2讀取多於1 048 576行的文件414
18．2寫入文本文件418

第19章將Access用作後端以改善多用戶數據訪問420
19．1 ADO與DAO421
案例分析：創建共享的Access數據庫421
19．2 ADO工具423
19．3向數據庫中添加記錄424
19．4在數據庫中檢索記錄425
19．5更新記錄427
19．6使用ADO刪除記錄430
19．7通過ADO匯總記錄430
19 ．8 ADO的其他實用程序431
19．8．1檢查表是否存在431
19．8．2檢驗字段是否存在432
19．8．3動態添加表433
19．8．4動態添加字段434
19．9 SQL Server示例434

第20章創建類、記錄和集合437
20．1插入類模塊437
20．2捕獲應用程序事件和插入圖表事件437
嵌入圖表事件439
20．3創建自定義對象440
20．4使用自定義對象441
20．5使用Property Let和Property Get
控制用戶使用自定義對象的方式442
20．6集合444
20．6．1在標準模塊中創建集合444
20．6．2在類模塊中創建集合445
案例分析：幫助按鈕447
20．7用戶自定義類型449

第21章高級用戶窗體技術453
21．1使用“用戶窗體”工具欄設計用戶窗體控件453
21．2其他用戶窗體控件453
21．2．1複選框453
21 ．2．2 Tab Strips 455
21．2．3 RefEdit 457
21．2．4切換按鈕458
21．2．5將滾動條用作滑塊來選擇值459
21．3控件和集合461
21．4非模態用戶窗體462
21．5在用戶窗體中使用超鏈接463
21．6在運行階段添加控件464
21．6．1動態地調整用戶窗體大小466
21．6．2動態地添加控件466
21．6．3動態地調整大小466
21．6．4添加其他控件467
21．6． 5動態地添加圖像467
21．7完整代碼468
21．7．1向用戶窗體中添加幫助470
21．7．2顯示快捷鍵470
21．7．3添加控件提示文本471
21．7．4指定Tab順序471
21．7．5為活動控件著色472
案例分析：多列列錶框473
21．8透明窗體474

第22章Windows應用程序編程接口476
22．1什麼是Windows API 476
22．2理解API聲明476
22．3使用API聲明477
22．4 API示例478
22．4．1檢索計算機名478
22．4．2確定Excel文件是否已在網絡上打開479
22．4．3獲取顯示器分辨率信息480
22．4．4自定義“關於”對話框481
22．4．5禁用用於關閉用戶窗體的“X”按鈕482
22．4．6連續時鐘483
22．4．7播放聲音483
22．4．8檢索文件路徑484
22．5查找更多API聲明487

第23章錯誤處理488
23．1錯誤所導致的後果488
令人費解的用戶窗體代碼錯誤調試489
23．2使用On Error GoTo進行基本錯誤處理491
23．3通用的錯誤處理程序492
23．3．1忽略錯誤493
案例分析：頁面設置問題通常可以忽略493
23．3．2禁止顯示Excel警告494
23．3．3利用錯誤495
23．4培訓用戶495
23．5開發階段錯誤和運行階段錯誤496
23．5．1運行錯誤9：下標越界496
23． 5．2運行錯誤1004：Global對象的Range方法失敗497
23．6保護代碼的缺點498
案例分析：破解密碼498
23．7密碼保護的其他問題499
23．8不同版本導致的錯誤499

第24章創建自定義選項卡以方便運行宏501
24．1辭舊迎新501
24．2將代碼加入到文件夾Customui中502
24．3創建選項卡和組503
24．4在組中添加控件504
24．5 Excel文件的結構509
24．6理解RELS文件510
24．7重命名Excel文件並將其打開511
自定義用戶界面編輯器工具511
24．8為按鈕指定圖像511
Microsoft Office圖標512
案例分析：將Excel 2003自定義工具欄轉換為Excel 2010自定義選項卡513
24．9排除錯誤515
24．9．1在DTD/架構中沒有找到指定屬性515
24．9．2非法的名稱字符516
24．9．3元素之間的父子關係不正確517
24．9．4 Excel發現不可讀取的內容517
24．9．5參數數量不正確或屬性值無效518
24．9．6自定義選項卡沒出現518
24．10其他運行宏的方式518
24．10．1快捷鍵519
24．10．2將宏關聯到命令按鈕520
24．10．3將宏關聯到形狀520
24．10．4將宏同ActiveX控件關聯起來521
24．10．5通過超鏈接運行宏522

第25章創建加載項524
25．1標準加載項的特點524
25．2將Excel工作簿轉換為加載項525
25．2．1使用“另存為”將文件轉換為加載項525
25．2．2使用VB編輯器將文件轉換為加載項526
25．3讓用戶安裝加載項527
25．3．1標準加載項並不安全529
25．3．2關閉加載項529
25．3． 3刪除加載項530
25．3．4使用隱藏工作簿代替加載項530
案例分析：使用隱藏工作簿存儲所有宏和窗體530
結束語532