疑難千尋千解叢書 Excel 2013 VBA編程與實踐pdf

2019年9月25日13:43:25 評論 10
摘要

《Excel 2013 VBA編程與實踐》讀者對象包含兩類:一:工作中涉及大量數據運算的用戶,通過VBA可以簡化操作步驟,提升運算效率。工作中數據越多越能體現本書的優勢。二:有VBA基礎想進一步提升VBA開發技術者。本書涉及VBA開發的方方面面,可為VBA愛好者提供更多的開發思路。

疑難千尋千解叢書 Excel 2013 VBA編程與實踐 內容簡介

《Excel 2013 VBA編程與實踐》為讀者展示Excel VBA編程的實戰技巧,包括工作中最常用的查詢、定位、格式轉換、報表拆分與合并、開發自定義函數、處理文件與文件夾、功能區設計,以及插件開發實戰與原理分析。本書側重于解決問題和展示解題思路,案例包含諸多常見疑難的解決方案。閱讀本書后,讀者可以解決工作中的諸多疑難雜癥,大大提高工作效率,且有助于提升編程能力,拓展思路,將理論向實戰邁進。

好的代碼應該同時具備準確、糾錯、兼容和效率四個特性,本書所有的案例都在準確性與高效性的基礎上提供完善的錯誤處理措施與思路講解。

《Excel 2013 VBA編程與實踐》包括205個實用案例和一個大型綜合應用――開發送貨單套打程序系統。書中的每個案例采取疑難描述、解決方案、操作方法、原理分析和知識擴展五個步驟進行講解,力圖在解決問題的同時讓讀者可以通曉其思路和原理。

疑難千尋千解叢書 Excel 2013 VBA編程與實踐 目錄

第1章 基礎理論 1

1.1 變量、常量與數據類型 1

疑難1 正確地定義變量和數據類型有何優勢 1

數據類型與對象類型 5

疑難2 公共變量和靜態變量都有何用處 6

1.2 程序防錯要點 8

疑難3 常見的代碼錯誤由哪些原因造成 8

疑難4 如何偵測代碼出錯,并將運行代碼的錯誤原因發給作者 12

疑難5 如何開發完善的程序 14

1.3 練習與思考 18

第2章 數據查找技巧 19

2.1 快速查找 19

疑難6 能否按范圍批量查找數值 19

疑難7 能否將符合多條件之一的所有數據提取到新表中 22

判斷工作表是否存在的方法 23

疑難8 可否按格式查找單元格,然后替換其格式 25

FindFormat的使用技巧 26

疑難9 如何找出A線的不達標人員信息 27

單列多條件與多列多條件篩選的區別 29

疑難10 如何查找所有的“#”并標識為上標 29

定位單元格任意字符的方法 30

疑難11 如何找出還款時間超過一年及未還款的客戶信息 31

日期函數Datedif的特性 32

疑難12 可以將查找到的所有數據串連并寫入剪貼板中嗎 33

疑難13 可以創建一個工具欄來方便查找嗎 35

如何區分精確匹配與模糊匹配 37

疑難14 能否按相似度查找所有的數據 37

利用Array向區域中一次性寫入多個常量 39

疑難15 如何在具有合并單元格的區域中多條件逐步查找 40

通過“MergeArea”屬性返回合并區域 41

疑難16 如何查找成績并分批發送郵件 42

VBA中郵件正文的換行符表示法 43

疑難17 如何在輸入時逐步查找 44

通過KeyUp事件自動執行查詢 46

2.2 跨表查找內容 47

疑難18 能否將所有表中的完成目標者匯總到“總表” 47

利用SpecialCells定位實現快速查找 48

疑難19 查找每月產量冠軍名單,在窗體中羅列顯示 49

不采用循環,一次性找出最大值所在行 50

疑難20 如何找出工作簿中所有的外部鏈接且將它們轉換成值 51

如何獲取工作簿中的外部鏈接 52

疑難21 可否模糊查找所有部門的電話信息 52

Target與Activecell的區別 54

疑難22 如何實現將所有未收貨款者在狀態欄隨機顯示 54

利用OnTime定時執行程序 56

疑難23 可否在單元格中創建多級下拉菜單 56

ActionControl對象的功能與限制 60

疑難24 可否在文件夾的所有文件中查找特定信息并匯總到新表 60

疑難25 如何統計文件夾中所有的成績工作簿中不及格人數 64

調用工作表函數時應如何書寫區域引用型參數 65

2.3 文件查找與轉換 66

疑難26 如何判斷指定的文件是否存在 66

判斷文件是否存在的函數 67

疑難27 如何進行深度查找且創建文件目錄 68

疑難28 可否在工作表中羅列出所有大于5MB的文件 70

疑難29 如何將所有的Word文件轉換成PDF文件 72

疑難30 如何將xlsm和xlsx格式的所有文件轉換成xls格式 76

疑難31 如何在網上鄰居的共享盤中查找并打開“單價表” 78

疑難32 如何實現全盤查找且播放音樂文件 79

疑難33 能否在文件夾的所有工作簿中執行批量替換 81

疑難34 能否根據成績表和模板生成Word成績通知單 84

2.4 圖片查找與引用 87

疑難35 如何瞬間刪除當前表中藝術字和圖表等以外的圖片 87

DrawingObjects與Shapes對象的區別 89

疑難36 可以將簽名圖片復制到表中所有的簽名處嗎 89

復制圖形對象與復制數據的區別 90

Selection代表什么 90

疑難37 如何像vlookup引用數據一樣引用圖片 91

循環彈出對話框直接到用戶正確操作為止的編程思路 93

疑難38 能否對材料表分頁且調用材料圖片 94

2.5 練習與思考 97

第3章 數據處理 98

3.1 按條件定位的技巧 98

疑難39 如何一次性定位產量大于1000的所有單元格 98

使用定位技術減少循環語句的循環次數 99

疑難40 可否全選至少三科不及格的學生姓名 100

通過SpecialCells定位錯誤值 103

疑難41 工作表中所有的錯誤值是否可隱藏起來 102

疑難42 能否定位數值區域并轉換成以“萬”為單位 104

VBA中如何處理雙引號 105

疑難43 如何定位當前表的最大值或最小值 105

調用工作表函數并配合Find方法查找最大值 107

疑難44 能否定位并標識高于平均值的單元格 107

VBA中數字、漢字的大小關系 109

疑難45 如何反向選擇工作表區域 109

輔助區的重要性 110

3.2 數據處理及格式轉換 111

疑難46 能否一鍵對選區橫向、縱向匯總 111

“R1C1”引用方式的優點 112

疑難47 如何將單詞在大寫、小寫與首字母大寫間任意切換 113

利用StrConv函數對英文進行大寫、小寫和首字母大寫轉換 114

疑難48 如何將二維的材料表轉換成一維表 115

使用數組函數Array簡化代碼 116

疑難49 如何開發一個通用的一維表轉二維表的工具 117

對數組和區域中的值去除重復值時的區別 120

疑難50 如何開發一個通用的二維表轉一維表的工具 121

疑難51 可否將字符串中的字母、數字和漢字分離到多單元格中 124

利用Like運算符區分漢字、數字和字母 125

疑難52 能否一鍵轉換表達式為計算結果 126

通過Evaluate方法轉換表達式 127

疑難53 數據有效性可以設置為關聯的三級下拉選單嗎 127

疑難54 能否將職工信息按自定義序列排序 130

新舊版本中Sort的差異 131

疑難55 可以不打開工作簿而提取其數據嗎 132

在VBA中調用公式實現從未打開的工作表中取值 134

疑難56 可否一鍵保護所有的公式 134

切換Locked 屬性實現公式保護 135

疑難57 如何實現發票金額分解 136

MID取文本的特點 137

疑難58 VBA可以破解工作表密碼嗎 138

表的分類 139

疑難59 如何將不規范的時間統一為“hh:mm:ss.00”格式 139

Format與Text函數的相同點和不同點 141

疑難60 可以生成指定范圍的不重復隨機數嗎 141

Collection對象的優勢 142

疑難61 如何對工作簿減肥 143

文件虛胖的常見原因 144

疑難62 可否讓數字在文本與數值之間快速切換 145

在文本與數值間切換的VBA思路 146

疑難63 如何將“/”分隔的數據進行匯總 147

以“/”為分隔符取其左右字符的思路優化 148

疑難64 可否一鍵刪除工作表中所有的空白行 148

SpecialCells方法的限制 150

疑難65 能實現粘貼數據時跳過隱藏區嗎 150

如何確定單元格是否被隱藏 153

疑難66 可否讓單元格的值真正地四舍五入 153

疑難67 如何對相同值進行標識著色 155

ColorIndex屬性的限制對VBA代碼的影響 157

疑難68 如何根據工資計算零鈔數量 158

獲取選區第一列及已用區域的交集 160

疑難69 可否將職工資料表一鍵轉換成打印格式 160

如何計算圖片所在單元格的地址 162

3.3 單元格合并技巧 162

疑難70 能否一鍵合并相同且相鄰的所有單元格 162

利用DisplayAlerts屬性關閉合并單元格時的提示加快代碼執行速度 164

疑難71 如何實現按產品合并產量數據表 164

利用變量暫存數據,代替輔助區 166

疑難72 能否改進“跨越合并”,使其居中保留所有的數據 166

Merge方法的真正功能 167

疑難73 可以合并同類項且分類匯總嗎 168

通過選擇性粘貼格式實現合并單元格 170

疑難74 如何實現合并時保留所有的數據,而拆分時還原數據 171

看不見的特殊字符的應用 173

疑難75 如何一鍵選擇所有合并的單元格 174

定位合并單元格 175

查找設置對下一次查找結果有何影響 175

疑難76 如何取消所有的合并區域,并對所有的單元格填充數據 175

合并區域的賦值方式 176

疑難77 能否實現撤銷合并后能還原所有數據的多單元格合并 177

合并單元格后再取消合并不丟失數據的思路 178

疑難78 如何將指定的單元格合并到一個選區 178

將指定單元格鏈接到一個選區 180

疑難79 可以讓合并單元格自動換行嗎 180

工作表簿件代碼與普通過程的區別 182

3.4 報表合并與拆分 183

疑難80 如何合并所有工作表的數據到一個表中 183

在復制數據時既去除公式,又不影響數值的顯示狀態 185

疑難81 如何實現多表合并匯總 185

利用相對引用公式批量合并數據,避免使用循環 187

疑難82 如何將多個工作簿中的所有工作表合并到一個工作表中 187

通過禁用工作簿重算提升代碼效率 190

疑難83 如何將多個工作簿數據合并到一個工作簿 190

利用變量作為輔助進行條件判斷 194

疑難84 如何實現按條件將單個工作表拆分成多個工作表 194

Range.AutoFilter方法的特殊性 198

疑難85 如何將工作簿中的每個工作表轉換為獨立工作表 198

在不同的Excel版本中如何選擇文件格式 200

3.5 單元格顏色的綜合應用 201

疑難86 不同版本的Excel在顏色處理方面有區別嗎 201

不同的版本中顏色差異對程序的影響 202

疑難87 可以在Excel 2013中按顏色篩選再做擴展嗎 203

CurrentRegion與Usedrange對程序的影響 206

疑難88 如何實現將顏色排序 207

借用輔助區和調用老版本的Sort功能提升程序的通用性 208

疑難89 可以按顏色對選區的數據分類匯總嗎 209

修改顏色不觸發公式重算和任何VBA事件 211

疑難90 如何用函數對單元格的背景和字體按顏色匯總 212

如何聲明可選參數 214

3.6 重復數據處理 214

疑難91 可否清空重復值所在單元格并以背景色標示 214

Countif函數的限制 216

疑難92 可用紅圈標示重復出現的數據嗎 217

疑難93 如何提取兩列數據中的相同項與不同項 219

利用Transpose函數實現區域轉數組 220

疑難94 可否一鍵刪除重復行 221

Range.RemoveDuplicates方法中Columns參數的限制 222

3.6 練習與思考 223

第4章 報表打印 224

4.1 打印設置 224

疑難95 如何一次性設置“總表”以外工作表的頁腳 224

選擇工作表數量對頁腳的影響 225

疑難96 可否將所有工作表的打印區域設置為有數據的區域 225

更新打印區域設置的限制 227

疑難97 可否將訂單表轉換成每10行打印一頁 228

全自動批量插入分頁符 230

疑難98 如何讓跨頁的合并單元格在打印后能完整顯示 229

判斷合并單元格是否跨頁 232

4.2 特殊打印格式設計 231

疑難99 如何制作工資條 232

疑難100 如何打印工資卡 234

疑難101 VBA可以實現對工作表分頁小計嗎 237

利用宏表函數Get.Document(50)獲取工作表頁數 242

疑難102 如何同時打印頂端標題和底端標題 241

計算第一個分頁符所在的行號 249

疑難103 可以借用圖片實現底端標題打印嗎 248

Export與API方式將區域轉換成圖片的差異 251

疑難104 可否將訂單表轉換成適合針式多聯打印的報表格式 251

修改CopyObjectsWithCells屬性實現復制數據時忽略圖形對象 254

疑難105 如何實現雙面打印 254

疑難106 可以只打印活動單元格所在頁嗎 255

如何計算分頁符位置和當前頁的序號 257

4.3 思考與練習 257

第5章 借用事件讓程序自動化 258

5.1 工作表事件 258

疑難107 錄入M2和M3時可自動將2或3顯示為上標嗎 258

Target與ActiveCell的相同與不同點 259

疑難108 可否錄入產品規格時自動在右邊顯示表達式的值 260

修改EnableEvents屬性避免事件的連鎖反應 261

疑難109 可否實時記錄指定區域的修改記錄 262

讓批注框自動調整大小的必要步驟 264

疑難110 能否雙擊首行或首列時彈出工作表目錄 264

工作表事件與工作簿事件的分別 265

5.2 工作簿事件 266

疑難 111 能否在啟動工作簿時自動創建工作表目錄 266

Workbook_Open事件與Auto_open宏的相同點和不同點 267

疑難112 進入包含“貨款”的工作表時可語音提示未收款客戶名稱嗎 268

DateDiff函數與DateDif函數的相同和不同點 269

疑難113 能否自動記錄工作簿的已打印次數 270

BeforePrint事件的缺陷 271

疑難114 輸入重復的工號時能否提示“已重復” 271

創建讓用戶指定執行方式的對話框的三種方法 273

疑難115 能否在狀態欄顯示選區中的最大值和最小值地址 273

工作表函數Counta的參數應如何對待Range對象的默認屬性值 275

5.3 應用程序事件 275

疑難116 可否讓新建的工作簿包含“進庫”、“出庫”和“異常統計”三個工作表 275

借助類實現應用程序級的事件 277

安裝加載宏的兩種方法 278

疑難117 可否新建圖表時默認顯示為圓角加陰影效果 279

工作簿級與應用程序級的圖表事件差異 280

疑難118 可否選擇單元格時整行與整列自動著色 280

引用活動窗口的可見區域 282

5.4 思考與練習 283

第6章 開發自定義函數 284

6.1 自定義函數基礎 284

疑難119 如何開發自定義函數 284

如何跨工作簿調用自定義函數 288

疑難120 自定義函數在不同的版本中有何差異 288

顏色對自定義函數的影響 289

函數參數的數量上限 291

疑難121 如何對自定義函數添加功能描述與參數說明 291

通過Application.MacroOptions方法為自定義函數添加參數說明 292

6.2 開發自定義函數 293

疑難122 如何對區域內混雜字符串中的數字求和 293

Excel公式對“+”的處理方式 294

疑難123 如何分離字符串中的數值、英文和漢字 294

正則表達式在字符處理中的優勢 295

疑難124 如何將文本混雜字符串及表達式轉換為值 296

正則表達式中如何表示數字、字母和漢字范圍 297

疑難125 如何按時間段匯總金額 298

根據計算對象的規范性決定函數參數的個數 300

疑難126 如何對具有分隔符的數據分類匯總 300

Split函數產生的數組的特點 302

疑難127 超過15位的數字如何求和 302

Excel對數據計算的長度限制 304

疑難128 如何根據身份證號碼獲取出生日期、年齡和性別 304

疑難129 可否將發票格式的數字金額合并且轉換為大寫 307

疑難130 如何突破Rank函數排名的限制 308

去除重復值的常用方法 310

疑難131 如何實現按數據出現次數排序 310

Collection與字典的區別 312

疑難132 可以用函數改變引用區域的值嗎 312

利用自定義函數修改引用區域的值的兩種方法 314

疑難133 如何對兩個以逗號分隔的亂序字符串比較異同 315

將Split函數嵌套應用從字符串中分別獲取品名與數量 316

6.3 開發具有可選參數的自定義函數 317

疑難134 可以擴展Vlookup函數實現返回所有符合條件的值嗎 317

利用Find替代工作表函數Vlookup實現多個數據查找 318

疑難135 可以用一個函數將人民幣大小寫相互轉換嗎 319

通過拆分法理解代碼 321

疑難136 可以讓函數的兩個參數全是可選參數嗎 322

將單個參數聲明為可選參數的方法 323

疑難137 可以用函數連接內存數據和區域中所有的文本嗎 324

利用ParamArray聲明不確定個數的函數參數 325

6.4 思考與練習 326

第7章 文件與文件夾管理 327

7.1 文件管理 327

疑難138 能否在打開文件時自動備份文件 327

將過程命名為“Auto_Open”使其自動執行 328

疑難139 如何讓文件打開一次后就自我銷毀 328

文件“自殺”的條件 329

疑難140 如何一鍵刪除3年前創建的所有文件 329

DATEDIF與DATEDIFF的區別 330

疑難141 可以刪除18個月沒有打開過的文件嗎 331

用DATEDIF計算文件閑置時間的技巧 331

疑難142 可以在收藏夾中對當前工作簿創建快捷方式嗎 332

獲取收藏夾地址的方法 333

7.2 文件夾管理 334

疑難143 如何瞬間刪除D盤中所有的空文件夾 334

將代表磁盤的字符串轉換成磁盤對象的方法 334

疑難144 如何獲取指定目錄下的文件列表 335

Dir函數查找子文件夾的注意事項 337

疑難145 安裝多個版本的Excel時可以隨心所欲地選擇打開方式嗎 338

將Excel快捷方式導入“Sendto”文件夾 339

疑難146 如何批量創建以本月每日的日期命名的文件夾 341

利用DateSerial的糾錯功能計算本月天數 342

7.3 思考與練習 342

第8章 VBA操作圖表 343

8.1 利用VBA批量修改圖表格式 343

疑難147 如何批量修改圖表標簽 343

操作圖表標簽的條件 347

疑難148 如何批量移位標簽 347

手工移位圖表標簽的限制 350

疑難149 如何對圖表系列設置條件格式 350

填充圖表系列的兩種方法 354

疑難150 可否一鍵統一圖表大小并對齊 354

圖表名稱的特點 356

8.2 思考與練習 356

第9章 VBA操作圖形對象 357

9.1 圖形對象的批量操作 357

疑難151 如何刪除E列存放的圖表以外的圖形對象 357

通過TopLeftCell和BottomRightCell定位圖片位置 358

疑難152 如何讓所有的圖形對齊所在單元格左邊框 359

通過統一Left屬性對齊圖片 359

疑難153 如何一鍵導入工作簿所在路徑下的家具圖片 360

錄制“批量插入圖片”的宏的缺陷 361

9.2 批注的高級應用 362

疑難154 如何將指定列的數據批量追加到批注中 362

疑難155 如何批量導入圖片到單元格的批注中 365

在“打開”對話框中篩選文件格式的方法 366

疑難156 可以對批注進行替換嗎 367

Unload Me、End和Exit Sub的區別 370

疑難157 如何在所有的批注末尾追加日期 370

通過設置AutoSize屬性使批注框自動調整大小 371

疑難158 如何批量修改批注的外觀 372

聲明正確的變量類型及防錯 374

9.3 圖形對象綜合應用 374

疑難159 如何創建圖片目錄與批量插入圖片 374

單元格的行高與列寬限制 378

疑難160 如何實現輸入品名時查看當前路徑下的同名圖片 378

通過修改圖像控件的Picture屬性更新圖片 379

疑難161 如何實現批量導入圖片且自動排版打印 380

疑難162 可否以模糊匹配方式批量導入圖片 384

借用通配符“*”實現模糊匹配文件名稱 387

疑難163 如何開發批量刪除指定類型的圖形對象的工具 387

如何遍歷控件集合 390

9.4 練習與思考 390

第10章 窗體控件應用 391

10.1 ActiveX控件應用 391

疑難164 可以利用列表框強化數據有效性嗎 391

列表框相較數據有效性的靈活性 393

疑難165 如何利用復合框引用區域的唯一值 394

疑難166 能否利用復合框和列表框打造二級選單 396

MouseMove事件獲取組合框的值的技巧 399

10.2 窗體設計技巧 400

疑難167 如何用一個窗體展現多項不同的內容 400

分頁顯示的兩種方法 403

疑難168 如何實現利用快捷鍵啟用和關閉窗體 403

借用命令按鈕的Cancel屬性快捷關閉窗體 405

疑難169 如何用一個按鈕執行多個功能 406

利用PasswordChar屬性隱藏敏感字符 408

疑難170 如何讓日期輸入器窗體緊隨活動單元格 409

影響窗體Left、Top屬性的因素 411

疑難171 VBA可以設計彩蛋嗎 412

MouseUp事件中的Button與Shift參數的作用 414

疑難172 如何通過窗體控制實現工作表隔行插入行 415

通過Shift參數控制Insert方法的單元格移動方向 418

疑難173 如何在窗體中開發鼠標右鍵快捷菜單 419

創建自定義菜單的基本思路 420

知識擴展 420

疑難174 在窗體Show狀態下可以調整控件大小嗎 421

通過鼠標指針的坐標變化同步更新控件的寬度和邊距 423

疑難175 在窗體Show狀態下如何調整控件的位置 423

MouseMove事件中Button參數的應用技巧 425

疑難176 如何檢測窗體中文本框的數據有效性 426

對文本框設置數據有效性檢驗的優勢 427

疑難177 能否通過代碼生成窗體及控件和代碼 428

用代碼操作代碼的必要條件 430

10.3 窗體與工作表之數據交互 431

疑難178 可否將多工作表中符合條件的值顯示在窗體中 431

列表框的ColumnHeads屬性的特點 434

疑難179 如何實現通過窗體向多工作表中錄入數據 434

疑難180 如何開發日歷工具箱 438

類的主要應用領域 442

Excel對日期計算的限制 442

疑難181 如何實現多條件電話查詢 442

TextBox1_Change事件與TextBox1_Exit事件的異同 444

疑難182 如何開發復選框批量管理工具 445

區分三種復選框控件 447

疑難182 如何讓窗體控件自適應工作表數據變化 448

用代碼創建新控件 450

疑難184 可以讓拆分工作簿的列表框具有拖放功能嗎 451

控件拖放相關的事件 454

疑難185 如何設計對文件批量重命名的工具箱 454

10.4 練習與思考 460

第11章 功能區菜單與backstage視圖設計 461

11.1 創建功能區菜單 461

疑難186 如何創建新選項卡及子菜單 461

創建功能區菜單的語法與代碼結構 463

疑難187 如何在“開始”選項卡中插入彈出式菜單 466

創建彈出式菜單的語法 467

疑難188 如何創建對話框啟動器 469

在標簽控件中讓多段文字換行的技巧 471

疑難189 如何在“視圖”選項卡中插入“閱讀模式”的切換按鈕 472

切換按鈕與命令按鈕的區別 474

疑難190 如何將常用卻又未顯示在功能區中的按鈕顯示在“插入”選項卡中 475

如何查看Excel內置命令的id 476

疑難191 如何制作功能區代碼模板 477

模板的功能與制作技巧 479

11.2 backstage視圖設計 481

疑難192 如何在“文件”菜單中添加命令按鈕 481

如何調用外部程序 483

疑難193 能否在“文件”菜單中創建多組命令按鈕 483

11.3 思考與練習 486

第12章 用VBA訪問網絡資源 487

12.1 導入網頁列表 487

疑難194 如何一鍵導入最近三個月人民幣與美元的歷史匯率 487

通過QueryTables.Add方法和QueryTable.Refresh方法導入網頁數據 488

疑難195 可否錄入城市名稱后自動列出火車票信息 489

讓Worksheet_Change事件僅作用于固定區域的技巧 491

12.2 導入不規范的網頁數據 491

疑難196 能否批量計算手機號碼的歸屬地、區號與卡類型 491

分析網頁源代碼的基本思路 493

疑難197 可否讀取網頁的天氣預報數據 496

查看網頁源代碼的方法 497

12.3 練習與思考 499

第13章 設計Excel通用工具 500

13.1 開發Excel插件 500

疑難198 如何開發定位工具箱擴展Excel定位的功能 500

Excel定位的限制 506

疑難199 可以利用插件一鍵錄入常用VBA代碼嗎 507

計算當前代碼的行號 511

疑難200 如何開發帶功能區菜單的中英文互譯插件 512

用公式實現中英互譯 514

13.2 封裝代碼 515

疑難201 如何將已編好的兩段代碼封裝為COM加載項 515

安裝COM加載項的三種方法 522

疑難202 如何封裝包含工作簿事件的代碼 522

封裝事件過程的基本思路 525

疑難203 如何將自定義函數封裝為COM加載項 525

VBA中調用COM加載項中的函數的方法 528

13.3 設計安裝程序 529

疑難204 如何將COM加載項封裝為EXE格式的安裝程序 529

InnoSetup軟件的參數說明 533

疑難205 可否開發具有撤銷功能且可單擊卸載的插件 533

使用回調函數動態更新菜單的指定屬性 537

13.4 練習與思考 538

第14章 送貨單套打程序的系統開發 539

14.1 羅列需求 539

14.2 設計表格 540

14.2.1 工作表職能分配 540

14.2.2 設計“配置”工作表 540

14.2.3 設計“打印界面”工作表 541

14.2.4 設計“明細表”工作表 543

14.3 編寫代碼 543

14.3.1 為品名、品牌和型號創建下拉列表 543

14.3.2 開發小寫金額轉大寫的函數 546

14.3.3 生成送貨單號 546

14.3.4 打印并保存數據 547

14.3.5 批量打印歷史數據 548

14.3.6 保存并清空數據 550

14.3.7 創建數據透視表 550

14.3.8 功能說明 551

14.4 設計菜單 551

14.5 測試功能 552

14.5.1 測試“生成送貨單號” 553

14.5.2 測試品名、品牌與型號的錄入方式 553

14.5.3 測試“打印當前貨單” 554

14.5.4 測試“打印歷史記錄” 555

14.5.5 測試“創建透視表” 556

14.5.6 測試對話框啟動器 557

14.5.7 總結 557

疑難千尋千解叢書 Excel 2013 VBA編程與實踐 精彩文摘

疑難5 如何開發完善的程序

編寫代碼解決一個工作問題是很簡單的,但是如何讓程序完善,可以適應所有的環境,且通用、兼容、可防錯,這是一門相當復雜的學問。那么如何開發一個完善的程序?如何及時地防止過程中的所有錯誤呢?

解決方案

通過常規思路開發一段程序完成基本需求,然后查找存在的問題并進行完善;再對新的過程審核是否有新問題,繼續完善,直到代碼可以應對一切外部環境。

本節以“讓用戶從對話框輸入一個值,并對該值開平方后寫入活動單元格”為例,展示利用VBA解決此問題且逐步完善程序的過程。

操作方法

步驟1 按組合鍵打開VBE窗口,然后插入模塊,并在模塊中錄入以下代碼:

Sub 獲取平方根1() '第一次編寫的代碼,直接解決問題

Dim Value As Long '聲明一個Long型變量

'讓用戶錄入一個值,將該值賦予變量Value

Value = InputBox("請輸入數值:", "待開方之數值", 0)

ActiveCell.Value = Sqr(Value) '對變量計算平方根

End Sub

步驟2 按鍵執行以上過程,并輸入100或者789、123.455等數據進行測試,可以發現當前代碼已實現需求的功能。然而,如果用戶在對話框中單擊“取消”按鈕,那么程序會出錯。用戶很難通過出錯提示了解出錯的原因,而且假設后面還有其他代碼,程序將不再執行。為了解決以上問題,修改代碼為:

Sub 獲取平方根2() '解問按“取消”鍵問題

Dim Value As Variant

Value = InputBox("請輸入數值:", "待開方之數值", 0)

If Len(Value) = 0 Then Exit Sub '如果變量的值長度為0,那么結束過程,不彈 '出錯誤提示

ActiveCell.Value = Sqr(Value)

End Sub

步驟3 再次執行程序,單擊“取消”按鈕后會發現程序自動退出,不彈出錯誤提示,具有防錯功能。也可以將“Exit sub”語句修改為其他代碼,從而實現單擊“取消”按鈕后程序得以繼續執行。

不過當輸入一個負數時,程序仍然會出錯,且自動中斷,因此需要繼續改進代碼。改進后的代碼如下:

Sub 獲取平方根3() '解決負數問題

Dim Value

Value = InputBox("請輸入數值:", "待開方之數值", 0)

If Len(Value) = 0 Then Exit Sub '如果變量的值長度為0,那么結束過程,不彈 '出錯誤提示

'若變量Value的值大于或等于0,則對變量開平方,且將結果存放在活動單元格,否則提示用戶

If Value >= 0 Then ActiveCell.Value = Sqr(Value) Else MsgBox "不能小于0"

End Sub

步驟4 當輸入負數后,程序會提示用戶且自動結束過程,但如果用戶輸入文本,那么程序仍然會出錯,所以再次對代碼做優化:

Sub 獲取平方根4() '解決文本問題

Dim Value

Value = InputBox("請輸入數值:", "待開方之數值", 0)

If Len(Value) = 0 Then Exit Sub '如果變量的值長度為0,那么結束過程,不'彈出錯誤提示

If IsNumeric(Value) Then '如果變量Value的值是數值

'若變量Value的值大于或等于0,則對變量開平方,且將結果存放在活動單元格,否則提示用戶

If Value <= 0 Then ActiveCell.Value = Sqr(Value) Else MsgBox "不能小于0"

Else '否則,提示不能輸入文本

MsgBox "不能輸入文本", 64, "提示"

End If

End Sub

步驟5 如果輸入文本,程序具有了識別并警告用戶的功能。然而,活動表是圖表時,執行程序時仍然會出錯。完善的程序需要處理所有的意外,那么程序可以做如下改進:

Sub 獲取平方根5() '解決圖表問題

Dim Value

'如果活動表是圖表,那么提示用戶,而且結束過程

If TypeName(ActiveSheet) = "Chart" Then MsgBox "不要選擇圖表": Exit Sub

Value = InputBox("請輸入數值:", "待開方之數值", 0)

If Len(Value) = 0 Then Exit Sub '如果變量的值長度為0,那么結束過程,不'彈出錯誤提示

If IsNumeric(Value) Then '如果變量Value的值是數值

'若變量Value的值大于或等于0,則對變量開平方,且將結果存放在活動單元格,否則提示用戶

If Value <= 0 Then ActiveCell.Value = Sqr(Value) Else MsgBox "不能小于0"

Else '否則,提示不能輸入文本

MsgBox "不能輸入文本", 64, "提示"

End If

End Sub

步驟6 如果工作表在被保護狀態下執行以上程序仍然會出錯,繼續完善代碼:

Sub 獲取平方根6() '解決工作表保護問題

Dim Value

'如果活動表是圖表,那么提示用戶,而且結束過程

If TypeName(ActiveSheet) = "Chart" Then MsgBox "不要選擇圖表": Exit Sub

'如果活動表處于保護狀態,那么提示用戶,然后結束過程

If ActiveSheet.ProtectContents Then MsgBox "工作表已保護": Exit Sub

Value = InputBox("請輸入數值:", "待開方之數值", 0)

If Len(Value) = 0 Then Exit Sub '如果變量的值長度為0,那么結束過程,不'彈出錯誤提示

If IsNumeric(Value) Then '如果變量Value的值是數值

'若變量Value的值大于或等于0,那么對變量開平方,且將結果存放在活動單元格,否則提示用戶

If Value <= 0 Then ActiveCell.Value = Sqr(Value) Else MsgBox "不能小于0"

Else '否則,提示不能輸入文本

MsgBox "不能輸入文本", 64, "提示"

End If

End Sub

步驟7 如果活動單元格處于數組區域之間,程序仍然會產生錯誤,所以最后將代碼優化為:

Sub 獲取平方根7() '解決數組區域問題

Dim Value

'如果活動表是圖表,那么提示用戶,而且結束過程

If TypeName(ActiveSheet) = "Chart" Then MsgBox "不要選擇圖表": Exit Sub

'如果活動表處于保護狀態,那么提示用戶,然后結束過程

If ActiveSheet.ProtectContents Then MsgBox "工作表已保護": Exit Sub

Value = InputBox("請輸入數值:", "待開方之數值", 0)

If Len(Value) = 0 Then Exit Sub '如果變量的值長度為0,那么結束過程,不'彈出錯誤提示

If IsNumeric(Value) Then '如果變量Value的值是數值

On Error Resume Next '如果代碼出錯,繼續執行下一步

Debug.Print ActiveCell.CurrentArray '將活動單元格的當前數據區域地址輸'出到立即窗口

'如果沒有錯誤(表示處于數組區域中),那么提示用戶,然后結束過程

If Err = 0 Then MsgBox "請不要選擇數組區域": Exit Sub

'若變量Value的值大于或等于0,則對變量開平方,且將結果存放在活動單元格,否則提示用戶

If Value <= 0 Then ActiveCell.Value = Sqr(Value) Else MsgBox "不能小于0"

Else '否則,提示不能輸入文本

MsgBox "不能輸入文本", 64, "提示"

End If

End Sub

原理分析

編程的基本條件是準確性。然而程序除了準確以外,還必須具備防錯和通用的特性,否則代碼在當前狀態下能正確執行,環境稍加變化就出現錯誤,將會增加維護成本。一個好的程序應該盡量通用于所有的狀況,而本例正是通過一個典范來展示程序的完善過程,讓讀者了解程序可能出現的錯誤,并提供所有錯誤的解決之道。在實際工作中,都應該嚴格按此思路編寫代碼,提升程序的準確性、糾錯性,同時也減少維護成本。

知識擴展

數組區域對VBA程序的影響 ?

(1)區域數組公式是同時存在于多個連續單元格中帶有“{}”標志的公式,將它輸入到工作表后會占據一個區域的空間,而非單個單元格,該區域即為數組區域。它的特性是不能單獨修改區域中任意一個單元格,如果代碼修改其中一個單元格,程序會因出錯而中斷。

(2)本例其實也可以利用“On Error Resume Next”語句一次性解決所有的問題,其代碼如下。不過如果執行程序后得不到結果,就無法知道出錯的原因。

Sub 獲取平方根8() '解決所有的問題

On Error Resume Next

ActiveCell.Value = Sqr(Application.InputBox("請輸入數值:", "開平方", 0, , , , , 1))

End Sub

注意

本書中編程的主題是準確性、效率、防錯性和兼容性,代碼一定要對所有的錯誤進行防范,確保程序通用。然而為了節約篇幅,讓書中展現更多的內容,我們盡量減少重復代碼,對工作表是否保護、當前表是否為圖表,以及活動單元格是否處于數組區域之間就不再對每個案例都進行判斷了,只對其他出錯的可能性進行防錯。但讀者在實際工作中應該全面防錯。

練習與思考

1.在VBA編程過程中為什么要聲明變量的數據類型及定義數據類型?

2.變量與常量的區別是什么?

3.執行代碼時出錯,都是因為代碼書寫有誤造成的?

4.使用什么語句可以使代碼在執行過程中出錯后仍然可以繼續執行下去?

5.執行代碼過程中出現“下標越界”錯誤,通常是什么原因造成的?

圖書網:疑難千尋千解叢書 Excel 2013 VBA編程與實踐pdf

恭喜,此資源為免費資源,請先
本站所有資源收集于互聯網,只做學習和交流使用,版權歸著作人和出版社所有,請在下載后24小時之內自覺刪除,若作商業用途,請購買正版,由于未及時購買和付費發生的侵權行為,與本站無關。本站發布的內容若侵犯到您的權益,請聯系站長刪除,我們將及時處理!
  • 我的微信
  • 掃一掃加好友
  • weinxin
  • 微信公眾號
  • 掃一掃關注(網站備用地址)
  • weinxin

發表評論

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: