第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
Excel　經典教程—VBA 與宏
2　|
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
目錄
|　3
對象變量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
Excel　經典教程—VBA 與宏
4　|
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
目錄
|　5
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
Excel　經典教程—VBA 與宏
6　|
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
目錄
|　7
第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
Excel　經典教程—VBA 與宏
8　|
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