本书是“Excel疑难千寻千解”丛书之一，为读者展示VBA编程的实战技巧，包括工作中的疑难处理方案、自定义函数技术、代码封装技术、功能区设计技术，以及插件开发实战与原理分析。本书侧重于“解决问题”，主要展示诸多常见疑难的解决方案。阅读本书，可以解决工作中的诸多疑难杂症，大大提高工作效率，且有助于提升编程能力、拓展思路，将理论向实战迈进。 本书有一个贯穿全文的思想：让代码同时具备准确、纠错、兼容和效率四个特性，所以本书所有案例都在准确性与高效性的基础上提供完善的错误处理措施与思路讲解，且所有案例都通用于Excel 2003、2007和2010。作者使用Excel 2010界面写作，但代码在多个版本中都可以正常运行，不需要任何修改。 本书包括251个案例，最后一个是VBA的大型综合应用—开发工资管理系统。书中每个案例采取疑难描述、解决方案、操作方法、原理分析、知识扩展五个步骤进行讲解，力图解决问题之外还让读者可以通晓其思路和原理。 本书配合《Excel VBA程序开发自学宝典》学习会有更好效果，该书侧重于VBA的基础理论教学，而本书则侧重于解决实际问题，属于该书配套的进阶应用。前者学习理论与语法，而通过本书学习思路与技巧。 本书既适合Excel VBA用户参考学习，又可以作为大中专院校或者企业的培训教材，同时对于在数据处理与分析方面有实践经验的用户也有较高的参考价值。

自 序 寻，是一种尺度。古时八尺为一寻，王安石诗云“飞来峰上千寻塔，闻说鸡鸣见日升”，形容飞来峰上塔之极高。 寻，是一种技巧。今日网络为自学者提供了非常巨大的资源平台，但缺乏技巧的查询和求助，往往“众里寻他千百度”却难有“蓦然回首”的顿悟。而善用关键词搜索，则常可使人体会到“灯火阑珊”之美景。 寻，是一种追求。网络上活跃着这么一群人，他们曾经也是懵懂的菜鸟，在寻解获助时心存感激，在寻师问道时心怀崇敬。渐渐地，他们因互助的理念而聚集，相互扶持，坚持不懈地学习、实践与创新。面对一个个技术的据高点，他们是睿智而无私的“寻者”，壁立千寻敢登攀，寻根究底解疑难！ 寻，是一种心境。求知探秘当志存华山论剑之高远，解决问题应意守无剑无招之寻常，这便是“寻者”修炼的心境。来者是客，有问必答，不慕华丽的招式，但求直击问题之根源，用简单的方法完成复杂的事情。 本丛书的作者就是这样的一群“寻者”。在长期的线上答疑和线下交流、培训活动中，他们接触过针对同一类问题千奇百怪的提问，深知许多人苦寻而不得的要害，甚至练就了一手仅根据含混不清的提问进行猜题、解题的本领。通过不断地积累与整理，Excel 技巧网（ExcelTip.Net技术社区）的版主们推出了“Excel 疑难千寻千解”系列丛书，这些“疑难”都是许多人曾经搜索和提问过的，也是大多数用户在工作中可能会遇到的一些寻常问题。书中案例涉及行政办公、人力资源、金融财务、经济统计、学校教育、工程技术、医疗卫生、质量管理、生产管理等多个领域，并根据Excel 的主要功能分册地整理成书。 在丛书编著过程中，“实用为主”的原则贯彻始终，所有案例都遵循实际工作场景和知识点解说紧密结合的主线，抛开一些华而不实、艰难晦涩的高深解法，而将基础理论和解决方案寓于寻常技巧之中，让读者能得其用、明其理，以期实现为更多的人解决更多问题的简单愿景。 寻者 前 言 感谢您选择“Excel 疑难千寻千解”丛书之《Excel 2010 VBA 编程与实践》！ Excel 2010 具有强大的运算与分析能力。从Excel 2007 开始，改进的功能区使操作更直观、更快捷，实现了质的飞跃。如果需要进一步提高效率及实现自动化仅靠功能区的菜单功能是远远不够的，正因如此，微软公司在Excel 中引进了VBA 程序，且首次在Excel 中升级VBA 到 7.0版本。通过VBA 可以让复杂的报表简单化，也可以让部分操作全自动完成，十倍或者百倍的制表效率成为可能。 丛书介绍 “Excel 疑难千寻千解”丛书是由Excel 技巧网（ExcelTip.Net 技术社区）的负责人、微软最有价值专家（MVP）黄朝阳先生策划并组织编写的一套系列书籍。 Excel 技巧网旨在重点推广Excel 2010 及今后更高版本的应用，并为广大的Excel 爱好者提供一个内容充实、分类清晰、经典实用、精彩纷呈、互惠学习、友好交流的平台。社区成立不久便吸引了众多微软MVP 和有志于推广电子表格应用技术的高手驻足，长期为网友免费解答疑难问题，并勤于将精彩的答疑帖子按其所涉及的知识和应用两个维度，整理成为原创技术文章。 社区设有讨论、文章、资源三大技术专区，并按照Excel 的功能归属将主题划分为基础应用、数据透视表、条件格式、函数与公式、图表与图形、VBA、SQL in Excel、综合应用等方面，本丛书的各读本正是基于相近的主题分类。 本丛书由黄朝阳、王建发、陈国良等微软MVP 以及罗刚君、荣胜军、李术彬、林盘生、陈树青、章兰新等多位社区资深版主联手打造，将来还将邀请更多高级会员加盟参与丛书其他读本的创作。其中《Excel 2010 操作与技巧》、《Excel 2010 函数与公式》、《Excel 2010 VBA 编程与实践》已先于第一批出版，近期还将陆续推出《Excel 2010 图表与图形》、《SQL in Excel 2010》等其他读本。 得益于多年来在社区答疑中的磨砺，作者们将丰富的实战经验付诸笔端，精心设计了上千案例，将Excel 方方面面的知识点融入其中。每个案例都采用一般用户遇到疑难时最可能的提问方式和应用场景对问题进行描述，提供具有针对性的解决方案和详尽的操作步骤，并配合以原理分析和知识扩展环节，既授人以鱼，又授人以渔。丛书全系提供“疑难”加“知识点”的特色目录索引，方便读者根据需要快速地查找。对于渴望学习和掌握新知识的Excel 用户来说，与其千百次地找寻问题答案，不如静心地读此丛书一卷。 编著本丛书的全过程中，我们得到了电子工业出版社计算机图书分社张慧敏等多位编辑的大力支持，特在此表示深切的谢意！ 读者对象 本书使用Microsoft Office Excel 2010 界面进行编写，但是在编写过程中作者特意注重程序的通用性，确保每个案例的解决方案都通用于Excel 2003、2007 和2010。而且为了方便读者，随书光盘中主要有两种格式的案例文件，包括xls 格式和xlsm 格式，如果是加载宏则包括xla格式和xlam 格式。不管读者使用Excel 2003、2007 还是2010 都可以直接使用，不需要转换，也不需要修改代码就可以顺利地在多版中使用本书的所有案例。 本书通过250 个案例和1 个大型综合应用展示VBA 编程技巧，适合所有办公制表人士，特别是需要提升工作效率及需要深入研究VBA 者学习之用。如果您没有VBA 基础，那么本书可以为您解决工作中的疑难。书中对常见问题有一个或者多个解决方案，并辅以解题思路与原理分析，读者可以通过书中的现有案例进行拓展，解决更多的类似问题。而对于已有VBA 基础者，则可以学习本书的解题思路与代码防错技术、代码优化之道。本书的案例涉及基础应用、 查找引用、数据处理、报表打印、函数开发、文件目录、图表设计、图形对象、窗体控件、插件开发、代码封装和在工资管理系统等诸多方面，可以全方位地提升VBA 编程的功底，让您将VBA 理论投入到工作中转换成效率。 阅读指南 本书包括11 章，涉及制表的每个方面。前10 章分别为基础理论、查找引用、数据处理、报表打印、函数开发、文件目录、图表设计、图形对象、窗体控件、插件开发，每章有数十个案例，且为了让读者能学以致用，每章提供3 个思考与练习题。最后一章为综合应用，讲述通过VBA 开发工资管理系统的思路。 详细的章节内容如下： 第 1 章基础理论 本章包括4 个小节——快速获取帮助、程序防错要点、代码优化之道、提升代码的兼容性。主要介绍VBA 编程的一些基础知识、代码的纠错与优化，以案例形式呈现出来，通过实际的应用让理论描述更有说服力。 第 2 章查找引用 本章包括4 个小节——本表查找、跨表及多表查找、文件查找与转换和图片查找与引用。主要讲述数据引用、图片引用的经典案例。其中对于查找提供了多种思路，尽量展示效率最高且具有容错性的代码与技巧。本章是全书的三大重点之一，通过大量的实例传授瞬间查找数据的技巧。 第 3 章数据处理 本章包括7 个小节——定位的技巧、数据处理及格式转换、单元格合并技巧、报表合并与拆分、单元格颜色的综合应用、重复数据处理和数据透视。本章是第二个重点，全面阐述VBA对数据处理的技巧。Excel 本身具备数据处理的功能，而利用VBA 可以大大提高数据处理的速度，本章属于办公制表人员必学范畴。 第 4 章报表打印 本章包括2 个小节——打印设置和特殊打印格式设计。主要讲述与打印或者打印格式设置方面相关的疑难处理技巧，包括工资条设计、分页小计、批量打印和底端标题等相当常用的一些疑难处理。 第 5 章函数开发 本章包括3 个小节——自定义函数基础、开发自定义函数和自定义函数的管理。本章阐述开发函数的技巧与函数的管理思路，从而强化Excel 的运算能力。 第 6 章文件目录 本章包括2 个小节——文件管理和目录管理。本章内容与制表本身无直接关系，但可以对文件和文件夹进行删除、备份、创建、转换、命名等操作，对工作大有裨益。 第 7 章图表设计 本章包括2 个小节——图表编辑和多表管理。设计基本的图表比较适合手工操作，而对于图表或者图表中的标签批量操作时，VBA 就有用武之地了，可使操作效率大大提高。 第 8 章图形对象 本章包括 3 个小节——图形对象的基本操作、批注的高级应用和图形对象综合应用。与图形对象相关的高级应用不适合手工操作，本章通过21 个案例演示VBA 操作图形对象的技巧，包括导入、导出、引用、浏览等操作。 第 9 章窗体控件 本章包括 4 个小节——ActiveX 控件应用、窗体设计技巧、窗体与工作表之数据交互和开发动画窗体。其中，重点在于窗体与工作表数据交互的案例演示，通过窗体处理数据并导入、导出，可以实现很多Excel 本身无法完成的功能。最后对窗体的美化技巧提供6 种思路。 第 10 章插件开发 本章包括3 个小节——定制菜单与工具栏、开发通用插件和封装DLL 插件。本章是本书第三个重点章节，通过案例展示VBA 开发插件的强大功能，并附带开发功能区的步骤与技巧，使读者对中文功能区的开发有进一步认识。通过本章的案例，读者可以开发自己需要的插件，包括将插件封装，从而实现代码隐藏。 第 11 章工资管理系统 通过综合案例的讲解，让读者提高自己的综合实战能力。 本书约定 为了熟悉本书对鼠标操作、键盘指令等描述方式，从而更顺畅地阅读本书，您有必要了解以下内容： · 菜单 本书中表示连续多个菜单指令时，使用右箭头“→”进行连接，例如：单击“文件”选项卡→“选项”→“公式”，表示依次单击“文件”选项卡中的“选项”按钮，在弹出的“Excel 选 项”对话框中单击“公式”选项卡。 􀁹 · 鼠标 本书中表示鼠标操作时，均使用标准的表示方法。其中“指向”表示将鼠标指针移到对象之上，且不单击任何按钮；而“单击”特指利用鼠标左键单击目标；“右键单击”表示利用鼠标的右键单击对象；“双击”表示快速按下鼠标左键两次；“拖放”则表示按下鼠标左键不松开的状态下拖动鼠标，拖到目标区域后再放开鼠标左键。 · 键盘 本书中表示按键时使用“【】”号包含，例如“按【Ctrl+Shift+Enter】组合键”表示同时按下键盘上的【Ctrl】键、【Shift】键和【Enter】键。其他按键的表示方法相同，如果需要特殊操作方法，会在相关内容中详细说明。 􀁹 · 代码 本书的 VBA 代码均不区分大小写，在编写代码时可以随意，但代码录入到代码窗口后，VBA 会将代码自动转换成首字母大写形式，带引号的字符串和变量名除外。 本书特点 本书与市场上其他Excel 书籍有很大的不同，文体结构新颖，案例贴近实际，讲解深入透彻，表现为以下几个方面： · 场景式提问 本书从成千上万网友提问中精心归纳、提炼出各类问题，并还原为贴近真实的求助语言及案例，方便读者搜寻与实际工作相似的问题。 􀁹 · 增强式目录 本书总体章节划分以“基础理论→分类应用→综合实战”为主线，将VBA 的主要知识点和经典解决方案设计到每个疑难的案例中，并采用“疑难”加“知识点”的特色目录索引，方便读者根据需要翻阅和查找。 􀁹 · 开创式结构 本书案例中的“解决方案”环节是对问题的思路解说，结合“操作方法”环节中的步骤让人更容易理解。“原理分析”环节则主要解释所使用代码的工作原理。“知识扩展”环节包括与案例相关的知识点补充，可拓展读者的视野，同时也有利于理解案例本身的解决思路。 本书的代码还有两个显著的特点，有别于市场上其他同类书籍，即处处确保代码的防错与通用性。 􀁹 · 防错 防错是指编写代码时尽可能考虑所有可能出错的情况，并在代码中加以防范。或者根据不同潜在的运行环境提供不同的执行方案，让代码可以多种情况下顺利执行，而不会弹出错误提示框导致程序中断。 􀁹 · 通用 通用性是指代码可以适应不同环境。环境是指软件的不同版本，例如Windows XP、VISTA和 Windows 7 操作系统都可以顺序执行本书所有案例的代码，Excel 2003、2007 和2010 版本的用户也可以顺利执行每个案例的代码，且不需要做任何修改。 环境还包括表格的区域。本书尽可能不采用硬编码引用工作表对象和区域地址，而采用具备自动适应变化的代码作为数据处理的引用源，从而使代码适应不同用户或虽是同一用户但工作表数据区域变化时皆可使用。例如Range("A1:D10")引用区域属于硬编码，当数据区域变化后，代码必须修改才可执行；而采用ActiveSheet.UsedRange 或者ActiveCell.CurrentRegion 两种 方法引用区域则可以适应数据区域的增减变化，从而提升代码的通用性。 学习方法 本书是 VBA 编程的案例集粹，其宗旨在于为读者解决实际工作的疑难，以及对同类的问题提供解决思路。 书中各章节的结构虽然是从易到难，但章节之间相互独立，即可以从任意章节开始学习，不需要遵照从前至后的顺序阅读。 本书每个案例中的“解决方案”部分表示对问题的思路解说，配合“操作方法”中的步骤和代码更容易理解。“原理分析”部分则主要包括两方面内容：代码中部分方法或者语句的语法解释，以及解决本疑难的理论依据。“知识扩展”部分包括与本例问题或者本代码相关的知识补充，用于扩展读者的知识范围，同时也有利于理解本例代码的思路。 本书是“Excel 疑难千寻千解”丛书之一，可为读者解决VBA 编程的疑难，同时也是作者罗刚君的另一本著作《Excel VBA 程序开发自学宝典》的升级版与配套教材。《Excel VBA 程序开发自学宝典》重点在于讲述VBA 编程的基础理论和语法，并配有“Excel 百宝箱”大型插件的设计过程详解，该书适合完全没有VBA 基础者学习，而本书是该书配套的案例，可以使读者将理论付诸实践，解决实际工作中的疑难及开发属于自己的插件。 售后服务 本书由 Excel 技巧网（ExcelTip.Net 技术社区）出品，读者在阅读中有任何疑难或者建议、BUG 反馈等都可以到社区发帖，包括求助、交流，也可以在社区下载与本书相关的文档。社区为本丛书开辟了一个专门的版面用于编读往来，地址如下： http://www.exceltip.net/forum-75-1.html 同时本书也配备了专属的QQ 群作为售后服务的联系方式，群号公布于上述网址，读者可以申请到群中与作者面对面交流。 购买本书除可以获取随书光盘中的所有案例文件外，还有两项服务，包括实用插件和Excel百宝箱。 实用插件是指随书光盘中的8 个插件，位于“赠品”文件夹中。8 个插件可以实现8 个功能，同时学习插件的开发思路。每个插件附有源代码，每句代码都有含义注释，且配有使用说明。插件名称如下： 1．二级选单 可以像数据有效性一样产生下拉列表，但更强大，可以产生二级选单。 2．批注替换工具 可以批量替换批注文本的工具，且可设定区分大小写与匹配方式。 3．选项菜单设计 通过它既可控制某些视图对象的显示与隐藏，又可学习选项菜单的设计思路。 4．一键录入代码 用于提高编写代码的速度与准确度，单击菜单即可录入常见代码。 5．复选框管理 可以批量生成复选框，也可以批量删除、勾选复选框，功能强大，操作方便。 6．历史数据记录器 将指定区域中每一个单元格的修改都记录在案备查，且同时记录修改时间。 7．增强型视图控制 是内置的视图工具的强化版本，更重要的是可以学习如何开发功能区。 8．自定义函数封装 自定义函数 Connect 是&运算的强化版本，功能强大，且配备代码封装教材。 除以上服务外，如果读者同时购买本书和《Excel VBA 程序开发自学宝典》，那么额外赠送三个实用插件和一个百宝箱8.0 正式版。插件包括： 1．隐藏磁盘 可以将指定的磁盘隐藏起来，并禁止使用，从而确保文件安全。 2．隐藏错误值 可以将工作表中的所有错误值在隐藏与显示之间瞬间切换。 3．工作簿标签 在菜单产生工作簿标签的插件，让工作簿切换更方便、快捷。 百宝箱 8.0 是利用VBA 开发的大型插件，官网地址为： http://excelbbx.cn/ 百宝箱正式版包括105 个功能，可以大大强化Excel 的功能，原价50 元销售。对于读者可以免费赠送，可进入作者QQ 群后向作者免费申请。 作者与致谢 本书由黄朝阳策划，罗刚君和章兰新负责编写。其中第1、2、3、4、8、9、10 和11 章由罗刚君编写；第 5、6、7 章由章兰新编写，最后由罗刚君、黄朝阳完成统稿。 在编写过程中，得到了多位ExcelTip.Net 技术社区版主和会员的支持与帮助，在此向他们表示由衷的感谢。 尽管作者对书中的案例精益求精，但疏漏仍然在所难免，如果您发现书中的错误或某个案例有更好的解决方案，敬请登录售后服务的网址向作者反馈。我们将尽快在社区回复，且在本书再次印刷时予以修正。 再次感谢您的支持！ 编著者 2010 年8 月30 日

第1 章基础理论 1 1.1 快速获取帮助1 疑难1 如何获取所有控件的属性及属性值1 􀃚 前期绑定与后期绑定4 疑难2 部分对象在输入时没有成员列表提示，如何解决5 􀃚 没有成员列表的对象6 疑难3 如何找出错误语句及获取错误原因并发送到开发者邮箱6 􀃚 利用 ShellExecute 函数调用程序的方式8 1.2 程序防错要点8 疑难4 程序出错有哪些原因9 􀃚 常见错误分析9 疑难5 如何获取VBA 所有运行时错误的详细说明10 􀃚 通过 Error 函数获取错误信息12 疑难6 如何在执行完毕后报告出错次数及原因13 􀃚 捕捉所有错误信息14 疑难7 如何开发完善的程序15 􀃚 数组区域对 VBA 程序的影响17 疑难8 防错语句在程序或者函数中应用有哪些优势17 􀃚 利用 Err.Number 属性判断错误类型19 1.3 代码优化之道19 疑难9 如何优化过程“隐藏偶数行” 19 􀃚 关闭 ScreenUpdating 提升程序效率20 疑难10 如何优化过程“设置字体属性” 21 􀃚 利用 With 简化对象引用22 疑难 11 如何优化过程“隔三行求和” 22 􀃚 减少单元格的写入次数提升程序效率 23 疑难12 如何优化过程“B 列所有图片右移” 23 􀃚 有针对性地使用 Select 方法24 疑难13 如何优化过程“建工作表目录” 25 􀃚 VBA 中数组的作用26 疑难14 如何优化过程“成绩评语” 26 􀃚 区分适合置于循环体中的语句27 疑难15 如何优化过程“删除空单元格所在行” 27 􀃚 不用循环而选择多个符合条件的单元格 28 疑难16 如何优化过程“批量修改批注背景色” 28 􀃚 区分父对象与子对象29 疑难17 如何优化过程“隔一行插入一行” 30 􀃚 计算模式对程序效率的影响31 疑难18 如何优化过程“获取外网IP 地址31 􀃚 使用变量的条件32 1.4 提升代码的兼容性32 疑难19 程序的兼容性体现在哪些方面33 􀃚 操作系统对 VBA 的影响33 疑难20 如何让程序适应不确定对象34 􀃚 使用动态区域引用让代码具有更强的通用性 37 疑难21 如何让程序兼容Excel 多版本37 􀃚 识别 Excel 的版本号39 疑难22 如何让程序兼容英文和中文系统40 􀃚 利用 API 函数识别简体、繁体中文与英文41 疑难23 如何让程序兼容简体与繁体中文41 􀃚 让代码适应简体与繁体系统42 1.5 练习与思考42 第2 章查找引用 44 2.1 本表查找44 疑难24 如何查找不及格学生姓名并突出显示44 􀃚 利用 Union 方法合并多区域46 疑难25 如何一次性罗列表中三省员工的姓名47 􀃚 Find 方法的使用技巧49 疑难26 如何在输入时逐步查找50 􀃚 通过 KeyUp 事件自动执行查询52 疑难27 如何按指定的格式查找53 􀃚 FindFormat 的使用技巧54 疑难28 如何实现字体格式替换54 􀃚 替换格式的条件55 疑难29 如何查找所有“#”并标为上标56 􀁸 区分上标、下标的应用对象57 疑难30 如何找出还款时间超过一年及未还款的客户信息57 􀃚 日期函数 Datedif 的特性59 疑难31 可以将查找到的所有数据串连并写入剪贴板中吗59 􀃚 引用“Microsoft Forms 2.0 Object Library”的两种方法60 疑难32 可以创建一个工具栏来方便查找吗61 􀃚 设置 lookat 参数实现模糊查找63 疑难33 如何快速罗列出每个产品最新报价63 􀃚 Collection 对象的应用技巧65 疑难34 如何在具有合并单元格的区域中多条件逐步查找65 􀃚 通过“MergeArea”属性返回合并区域67 疑难35 如何查找成绩并分批发邮件67 􀃚 VBA 中发编写邮件正文时的换行符的表示法68 2.2 跨表及多表查找69 疑难36 如何引用数据表创建多级下拉菜单69 􀃚“ActionControl”对象的功能与限制72 疑难37 如何将所有表中查找的完成数汇总到总表73 􀃚 利用 SpecialCells 定位实现快速查找74 疑难38 在窗体中罗列每月产量冠军名单74 􀃚 不采用循环，一次性找出最大值所在行 76 疑难39 如何将具有外部数据引用的单元格转换成值76 􀃚 通过“[]”和“’！”定位具有外部链接的公式77 疑难40 如何让链接到其他表中隐藏单元格的超链接生效77 􀃚 通过 xlSheetVisible 属性切换工作表显示状态78 疑难41 如何实现多部门电话资料模糊查询79 􀃚 Target 与Activecell 的区别80 疑难42 如何实现所有未收货款者在状态栏随机显示80 􀃚 利用 OnTime 定时执行程序82 2.3 文件查找与转换82 疑难43 如何判断指定文件是否存在82 􀃚 利用 DIR 函数判断文件是否存在83 疑难44 如何进行深度查找且创建文件目录84 􀃚 FileDialog 对象的应用85 疑难45 如何查找并备份所有“3 月生产表” 86 􀃚 CopyFile 方法对文件复制88 疑难46 如何将所有Excel 文件转换成XPS 或PDF 文件88 􀃚 利用 PrintOut 方法将工作表转换成XPS 文件90 疑难47 如何在网上邻居的共享盘中查找并打开“单价表91 􀃚 利用 FileExists 方法判断文件是否存在92 疑难48 如何实现全盘查找“上海滩.MP3”，有则自动翻放92 􀃚 播放音乐的三种方式94 疑难49 如何在网上自动搜索与当前单元格同名的歌曲并播放95 􀃚 利用网页地址索引歌曲并自动播放 96 2.4 图片查找与引用96 疑难50 如何瞬间删除当前表中所有图片96 􀃚 DrawingObjects 与Shape 对象的区别97 疑难51 可以将签名图片复制到表中所有签名处吗97 􀃚 复制图形对象与复制数据的区别 98 􀃚 Selection 代表什么99 疑难52 如何像vlookup 引用数据一样引用图片99 􀃚 对图片设置公式引用其他图片101 疑难53 如何通过窗体预览指定目录中的所有图片101 􀃚 通过 LoadPicture 函数加载图片103 2.5 练习与思考103 第3 章数据处理 104 3.1 定位的技巧104 疑难54 如何选择当前表0～100 之外的数据104 􀃚 使用定位技术减少循环语句的循环次数 105 疑难55 如何定位奇数行105 􀃚 MOD 配合SpecialCells 定位奇数行106 疑难56 可以瞬间定位并隐藏所有错误值吗107 􀃚 通过 SpecialCells 定位错误值108 疑难57 如何在输入数据时自动定位下一行的目标单元格109 􀃚 限制删除单元格的数据时不触发工作表的 Change 事件110 疑难58 可以将标题以外的区域转换成“万”为单位吗110 􀃚 VBA 中如何处理双引号111 疑难59 如何定位当前表最大值和最小值112 􀃚 调用工作表函数并配合 Find 方法查找最大值113 疑难60 能定位并标识高于平均值的单元格吗113 􀃚 VBA 中数字、汉字的大小关系115 疑难61 如何反向选择工作表区域115 􀃚 辅助区的重要性116 3.2 数据处理及格式转换116 疑难62 如何将单词在大写、小写与首字母大写间任意切换116 􀃚 利用 StrConv 函数对英文进行大写、小写和首字母大写转换117 疑难63 如何将二维的入库表转换成一维排版方式118 􀁸 利用数组提升代码执行效率119 疑难64 能否不用循环对选区进行横向与纵向汇总120 􀃚“R1C1”引用方式的优点121 疑难65 如何批量地将字符串中的字母、数字和汉字分离出来122 􀃚 利用 Like 运算符区别汉字、数字和字母123 疑难66 可以将表达式转换成运算结果吗124 􀃚 通过 Evaluate 计算表达式125 疑难67 如何快速填充所有空单元格125 􀃚 利用 Intersect 引用区域提升代码通用性126 疑难68 如何将产品销售地区瞬间转换成下拉列表126 􀃚 利用代码设置有效性序列127 疑难69 数据有效性可以设置三级关联下拉列表吗128 􀃚 利用 Dictionary 对象获取唯一值130 疑难70 可以对职员信息表按学历排序吗130 􀃚 新旧版本中 Sort 的差异131 疑难71 可以不打开工作簿而提取其数据吗132 􀃚 VBA 中调用公式实现从未打开的工作表中取值133 疑难72 如何保护所有公式不能修改134 􀃚 切换 Locked 属性实现公式保护135 疑难73 如何实现金额数据分解135 􀃚 MID 取文本的特点137 疑难 74 VBA 可以破解工作表密码吗137 􀃚 表的分类 138 疑难75 如何对分段编号进行合并转换138 􀃚 减少数组变量空间分配次数的方法 140 疑难76 如何将5 个大小相同选区的数据互换141 􀃚 Areas 与CurrentRegion 的区别142 疑难77 如何取得1000 之内的质数142 􀃚 质数的特性143 疑难78 可以生成指定范围的不重复随机数吗144 􀃚 Collection 对象的优势145 疑难79 如何利用VBA 对工作簿进行减肥145 􀃚 正确删除单元格，减少文件体积 146 疑难80 如何让数字在文本与数值之间快速切换147 􀃚 在文本与数值间切换的 VBA 思路148 疑难81 如何将“/”分隔的数据进行汇总149 􀃚 以“/”为分隔符取其左右字符的思路优化150 疑难82 可以对指定区域进行自动更正吗150 􀃚 利用 Replace 方法实现限定区域的自动更正151 疑难83 可以对包括“星期”的所有工作表同时添加公式吗151 􀃚 VBA 在单表和多表中输入公式的区别152 疑难84 在VBA 中如何控制字符朗读153 􀃚 利用 Speech 朗读字符153 疑难85 能实现粘贴数据时跳过隐藏区吗154 􀃚 如何确定单元格是否隐藏156 疑难86 如何对相同值进行标识着色156 疑难87 如何根据工资计算零钞数量159 􀃚 获取选区第一列及已用区域的交集 160 疑难88 如何批量实现区域数据真正四舍五入161 􀃚 通过 round 解决数据显示的假象162 疑难89 如何优化具有大量公式引用的数据计算163 􀃚 公式与 VBA 在计算上的区别164 􀃚 ClearContents 与Clear 的区别164 3.3 单元格合并技巧164 疑难90 可以在表格中合并同类项吗165 􀃚 利用 DisplayAlerts 属性关闭合并单元格的提示及加快执行速度166 疑难91 如何实现按产品合并产量数据表166 􀃚 利用变量暂存数据，代替辅助区 168 疑难92 如何实现合并时保留所有数据，而拆分时可以还原168 􀃚 看不见的特殊字符的应用171 疑难93 可以选择当前表所有合并单元格吗171 􀃚 定位合并单元格172 疑难94 如何取消所有合并区域并且对所有单元格填充数据172 􀃚 合并区域的赋值方式174 疑难95 合并单元格后仍然可以正常引用区域中的所有值吗174 􀃚 复制合并属性175 疑难96 如何将指定单元格合并链接到一个选区175 􀃚 对一个区域追加链接177 疑难97 跨页合并单元格可以重新合并从而适应分页打印吗178 􀃚 判断合并单元格是否跨页179 疑难98 可以让合并单元格自动换行吗180 􀃚 工作表事件代码与普通过程的区别 181 3.4 报表合并与拆分181 疑难99 如何合并所有工作表到一个表中182 􀃚 让复制数据时既去除公式又不影响数值的显示状态 183 疑难 100 如何实现多表合并汇总184 􀃚 利用相对引用公式批量合并数据，避免使用循环 185 疑难 101 如何将多个工作簿中的所有工作表合并到一个工作表中186 􀁸 通过禁用工作簿重算提升代码效率 189 疑难 102 如何将多个工作簿数据合并到一个工作簿中189 􀃚 利用变量作为辅助进行条件判断 193 疑难 103 如何实现报表拆分193 􀃚 利用复制格式和复制数值实现完美复制，避免复制后显示错误195 疑难 104 如何将工作簿中每个工作表转换为独立工作表197 􀃚 根据版本号决定文件格式198 3.5 单元格颜色的综合应用199 疑难 105 Excel 不同版本在颜色处理方面有分别吗199 􀃚 不同版本中颜色差异对程序的影响 200 疑难 106 可以对Excel 2010 的按颜色筛选再做扩展吗201 􀃚 CurrentRegion 与Usedrange 对程序的影响204 疑难 107 如何让按颜色排序可以在Excel 多版本间通用204 􀃚 借用辅助和调用老版本的 Sort 功能提升程序的通用性206 疑难 108 如何将颜色分类重排且通用于Excel 多个版本206 􀃚 将颜色属性转换成数据，辅助程序执行 208 疑难 109 可以按颜色对选区数据分类汇总吗208 􀃚 修改数据源与修改颜色在 VBA 事件中的差异211 疑难 110 如何用函数对数据背景和字体按颜色汇总212 􀃚 通过 Volatile 让函数声明为易失性函数213 3.6 重复数据处理213 疑难 111 如何清空重复值单元格并标识214 􀃚 Countif 函数在去重复值中的应用215 􀃚 Countif 函数的限制216 疑难 112 可以对重复出现的数据进行圈释吗216 􀃚 利用 CircleInvalid 对单元格加圈，以及CircleInvalid 的限制217 疑难 113 如何筛选唯一值218 􀃚 使用错误处理语句配合 Collection 取唯一值219 疑难 114 可以在输入重复值时提示用户吗219 􀃚 通过 EnableEvents 提高程序的执行效率220 疑难 115 如何对两列数据提取相同项与不同项221 􀃚 利用 Transpose 函数实现区域转数组222 疑难 116 如何将多行多列相同者保留最后一个数据223 􀃚 双层 Transpose 配套Join 实现横向区域转字符串224 3.7 数据透视224 疑难 117 如何按职务或部门进行工资数据的透视分析225 􀃚 利用 Excel 2003 的透视表代码提升程序兼容性226 疑难 118 如何实现多重数据透视227 􀃚 利用 Array 嵌套创建多重透视228 􀁸 XXII 􀁸 疑难 119 如何实现同时透视多工作簿中的多工作表数据229 􀃚 解决 VBA 创建透视表名称冲突的两种方法230 3.8 练习与思考231 第4 章报表打印 232 4.1 打印设置232 疑难 120 如何一次性设置“总表”以外工作表的页脚232 􀃚 选择工作表数量对页脚的影响233 疑难 121 如何设置所有工作表的已用区域为打印区域233 􀃚 设置打印区域对数据打印的影响 234 疑难 122 可以记录工作簿的打印次数吗235 􀃚 借用文件自定义属性记录打印次数 235 4.2 特殊打印格式设计236 疑难 123 如何设计工资条打印格式237 􀃚 借用辅助列提升程序的容错性240 疑难 124 如何打印工资卡240 􀃚 引用普通工作簿和加载宏工作簿的区别 243 疑难 125 VBA 可以实现对工作表分页小计吗243 􀃚 利用宏表函数 Get.Document(50)获取工作表页数246 疑难 126 如何同时打印顶端标题和底端标题行246 􀃚 计算第一个分页符的所在行号253 疑难 127 可以借用图片实现底端标题打印吗253 􀃚 Export 与API 方式将区域转换成图片的差异255 疑难 128 如何制作针式多联打印机打印格式的报表256 􀃚 利用 HPageBreaks.Add 实现手动分页258 疑难 129 如何实现双面打印259 􀃚 中断与禁止中断程序260 疑难 130 可以仅仅打印活动单元格所在页吗260 􀃚 利用 Location 计算分页符位置，配合循环判断当前页序号261 4.3 练习与思考262 第5 章函数开发 263 5.1 自定义函数基础263 疑难 131 如何开发自定义函数263 􀃚 Office 版本对自定义函数参数的影响267 疑难 132 函数与参数名规则如何267 􀃚 自定义函数命名规则267 疑难 133 自定义函数在不同版本中有何差异269 􀁸 颜色对自定义函数的影响271 5.2 开发自定义函数271 疑难 134 如何对区域内混杂字符串中的数字求和271 􀃚 Excel 公式对连续“+”的处理方式273 疑难 135 如何分离字符串中的数值、英文和汉字273 􀃚 正则表达式在字符处理中的优势 274 疑难 136 如何将文本混杂字符串及表达式转换为值274 􀃚 正则表达式中数字、汉字的表达方式 276 疑难 137 如何按时间段计算汇总金额277 􀃚 根据计算对象的规范性决定函数参数的个数 278 疑难 138 如何对具有分隔符的数据分类汇总279 􀃚 Split 函数产生的数组的特点280 疑难 139 可以取姓名的汉语拼音首字母吗280 􀃚 汉字与拼音首字母的对应关系282 疑难 140 超过15 位的数字如何求和282 􀃚 Excel 对数据计算的长度限制284 疑难 141 可以扩展Vlookup 函数实现返回多个结果吗284 􀃚 利用 Find 替代Vlookup 实现多个数据查找285 疑难 142 如何利用函数创建文件目录286 􀃚 自定义函数中如何表示活动单元格 287 疑难 143 可以用函数连接内存数据和区域中所有文本吗288 􀃚 利用 ParamArray 声明不确定个数的函数参数290 疑难 144 如何根据身份证号码获取出生日期、年龄和性别290 􀃚 身份证号码所包含的个人信息292 疑难 145 如何将多个单元格中的人民币数字合并且转为大写金额293 􀃚 声明正确的类型优化函数效率294 疑难 146 如何计算两个区域的不同项294 􀃚 自定义函数对大区域运算的限制 296 疑难 147 如何突破Rank 函数排名的限制296 􀃚 去重复值的 3 个方法297 疑难 148 如何实现按数据出现次数排序298 􀃚 Collection 与字典的区别300 疑难 149 可以用一个函数将人民币大小写相互转换吗300 􀃚 通过拆分法理解代码302 疑难 150 可以用函数改变引用区域的值吗303 􀃚 利用自定义函数修改引用区域的值的两种方法 305 疑难 151 如何对两个以逗号分隔的乱序字符串比较异同306 􀃚 Split 函数嵌套应用从字符串中分别获取品名与数量307 疑难 152 可以利用函数添加数据监控吗308 􀁸 借用函数的易失性实现自动执行，类似于工作表事件309 疑难 153 可以让函数的两个参数全是可选参数吗310 􀃚 将单个参数声明为可选参数的方法 311 疑难 154 可以利用函数批量对工作表命名吗312 􀃚 借用类模块将工作簿级事件提升为应用程序级事件 314 疑难 155 如何让自定义函数运算结果是一个数组314 􀃚 函数返回数组的条件316 5.3 自定义函数的管理316 疑难 156 如何对自定义函数添加说明316 􀃚 通过 MacroOptions 定义函数的帮助信息318 疑难 157 如何对自定义函数的参数添加帮助318 􀃚 借用 REGISTER 为自定义函数添加参数说明及分类321 疑难 158 可以将加载宏中的自定义函数复制到活动工作簿吗322 􀃚 判断用户的设置是否允许访问 VBA 工程325 疑难 159 如何封装自定义函数，使其代码不可查看325 􀃚 利用 VB 6.0 企业版封装自定义函数328 5.4 练习与思考328 第6 章文件目录 329 6.1 文件管理329 疑难 160 可以实现文件双向备份吗329 􀃚 利用 SaveCopyAs 备份文件330 疑难 161 如何让文件使用一次后即自我销毁331 􀃚 文件“自杀”的条件331 疑难 162 文件有哪些属性，如何控制它们332 􀃚 GetAttr 函数搭配and 运算符判断文件是否具备某属性333 疑难 163 如何删除指定目录中一年前创建的文件334 疑难 164 可以删除18 个月没有使用的文件吗335 􀃚 用 DATEDIF 计算文件闲置时间的技巧336 疑难 165 如何对文件批量重命名336 􀃚 获取文件名的方法338 疑难 166 可以在收藏夹中对当前工作簿创建快捷方式吗338 􀃚 获取收藏夹地址的方法339 疑难 167 文本文件与Excel 如何批量转换341 􀃚 文本文件与工作表的相互转换342 疑难 168 如何实现将指定文件作为附件发送邮件343 􀃚 Outlook 的前期绑定与后期绑定344 6.2 目录管理345 􀁸 疑难 169 如何瞬间删除D 盘中所有空文件夹345 􀃚 将代表磁盘的字符串转换成磁盘对象的方法 346 疑难 170 如何获取C 盘以外所有磁盘的文件目录列表346 􀃚 通过 FSO 对象判断某磁盘是否存在347 疑难 171 如何让多版本Excel 可以随心所欲选择打开方式348 􀃚 将 Excel 快捷方式导入“Sendto”文件夹349 疑难 172 如何批量创建以本月每日日期命名的文件夹350 􀃚 利用 DateSerial 的纠错功能计算本月天数351 6.3 练习与思考351 第7 章图表设计 352 7.1 图表编辑352 疑难 173 如何利用快捷键对选区创建图表352 􀃚 对宏指定组合键的方式及特点355 疑难 174 如何批量修改图表标签355 􀃚 操作图表标签的条件359 疑难 175 如何批量移位标签359 􀃚 移位图表标签的限制362 疑难 176 如何对图表系列设置条件格式362 􀃚 填充图表系列的两种方法365 7.2 多表管理366 疑难 177 如何将所有图表大小调整一致且对齐366 􀃚 图表名称的特点367 疑难 178 如何将所有图表转换成JPG 文件保存到文件夹368 􀃚 将图片转换为图片的思路369 7.3 练习与思考369 第8 章图形对象 370 8.1 图形对象的基本操作370 疑难 179 可以仅仅删除E 列存放的图表以外的图形对象吗370 􀃚 通过 TopLeftCell 和BottomRightCell 定位图片位置371 疑难 180 如何将工作簿所在路径的图片全部导入工作表371 􀃚 Pictures.Insert 与Shapes.AddPicture 的分别373 疑难 181 如何让所有图形对齐所在单元格左边框374 􀃚 通过统一 TopLeftCell 属性对齐图片374 疑难 182 如何实现所有图片刚好适应所在单元格大小与边距375 􀃚 行高与列宽的特点376 疑难 183 如何将选区转换成图片并保存到桌面377 􀁸借用图表中介实现区域转图片379 8.2 批注的高级应用379 疑难 184 如何利用快捷键在单元格插入当前日期的批注379 􀃚 通过设置 AutoSize 属性使批注框自动调整大小380 疑难 185 如何将指定列的数据批量追加到批注中381 􀃚 强制返回选区第一列的 3 种方法383 疑难 186 如何实现指定区域批量创建历史记录于批注中383 􀃚 让代码应用于所有工作簿的方法 386 疑难 187 如何批量导入图片到单元格的批注中386 􀃚 对批注框设置背景的条件388 疑难 188 可以对批注进行替换吗388 􀃚 “Unload Me”、“End”和“Exit Sub”的区别391 疑难 189 如何在所有批注末尾追加当前日期391 􀃚 利用 Isdate 判断批注最后一行是否为日期392 疑难 190 如何批量修改批注的外观392 􀃚 声明正确的变量类型及防错394 疑难 191 可以改造默认的插入批注功能吗394 􀃚 正确引用右键菜单395 8.3 图形对象综合应用396 疑难 192 可以让图片跟随鼠标移动吗396 􀃚 利用 DoEvents 函数展示动画过程398 疑难 193 如何创建图片目录与批量插入图片398 􀃚 单元格的行高与列宽限制401 疑难 194 如何利用窗体预览图片402 􀃚 控件背景图片的 3 种显示方式404 疑难 195 VBA 中的窗体能否转换成图片保存在工作表中404 􀃚 调用 API 函数调用截图的快捷键405 疑难 196 可以在工作表中导入GIF 动画吗406 􀃚 播放 GIF 文件时不能将文件嵌入工作表408 疑难 197 如何实现输入品名时查看当前路径中的同名图片408 􀃚 利用 API 计算图片文件的长与宽409 疑难 198 可以让鼠标指定图片时放大图片吗410 􀃚 利用 API 函数获取鼠标指针坐标413 疑难 199 如何实现图片批量导入并自动排版打印413 􀃚 利用 FSO 技术中的Getextensionname 获取文件后缀名417 8.4 练习与思考417 第9 章窗体控件 418 9.1 ActiveX 控件应用418 疑难 200 可以利用列表框强化数据有效性吗418 􀃚 列表框相较数据有效性的灵活性 420 疑难 201 如何利用复合框引用区域的唯一值421 􀃚 复合框调用数据的两种方法及各自优势 423 疑难 202 利用复合框和列表框实现二级列表423 􀃚 MouseMove 事件获取列表框的值的技巧426 9.2 窗体设计技巧426 疑难 203 如何实现利用快捷键启用和关闭窗体426 􀃚 KeyPress 事件可以检查的按键及例外项目428 疑难 204 如何用一个窗体展现多个不同内容429 􀃚 分页显示的两种方法432 疑难 205 如何用一个按钮执行多个功能433 􀃚 利用 PasswordChar 属性隐藏敏感字符435 疑难 206 如何让日期输入器窗体紧跟活动单元格435 􀃚 影响窗体 Left、Top 属性的因素436 疑难 207 如何设计五角星窗体437 􀃚 显示五角星窗体的方法440 疑难 208 VBA 可以设计彩蛋吗440 􀃚 MouseUp 事件中Shift 参数的作用442 疑难 209 如何通过窗体控制实现工作表隔行插入行442 􀃚 通过 Shift 参数控制Insert 方法的单元格移动方向446 疑难 210 如何在窗体中开发右键快捷菜单446 􀃚 创建自定义菜单的基本思路448 疑难 211 窗体可以利用鼠标自由调整大小吗449 􀃚 利用 API 让窗体可以自由拖动的思路449 疑难 212 在窗体Show 状态下可以调整控件大小吗450 􀃚 MouseMove 事件中各参数的作用452 疑难 213 在窗体Show 状态下如何批量设置控件格式452 􀃚 可修改控件字体格式的几种对话框 454 疑难 214 在窗体Show 状态下如何调整控件位置455 􀃚 MouseMove 事件中Button 参数的应用技巧456 疑难 215 如何检测窗体中文本框的数据有效性456 􀃚 对文本框设置数据有效性检验的优势 457 疑难 216 如何设计悬浮感应式窗体458 􀃚 通过调整边距控制窗体的动态效果 460 疑难 217 可否瞬间备份所有窗体460 􀃚 VBA 工程中的子对象分类461 疑难 218 能否对窗体中所有控件同步缩放462 􀃚 窗体的 Zoom 属性的特点464 􀁸 疑难 219 能否通过代码生成窗体及控件464 􀃚 用代码操作代码、窗体和模块的优缺点 466 9.3 窗体与工作表之数据交互467 疑难 220 可以实现多工作表筛选并将结果导入窗体吗467 􀃚 列表框的 ColumnHeads 属性的特点469 疑难 221 多工作表数据输入窗体如何设计469 􀃚 利用 SetFocus 方法控制焦点的转移472 疑难 222 如何开发日历工具箱472 􀃚 Excel 对日期计算的限制476 疑难 223 如何实现多条件电话查询476 􀃚 “TextBox1_Change”事件与“TextBox1_Exit”事件的异同478 疑难 224 如何开发复选框批量管理工具478 􀃚 认识复选框481 疑难 225 如何让窗体控件自适应工作表数据变化481 􀃚 用代码创建新控件483 疑难 226 可以让列表框具有拖放功能吗484 􀃚 本例仅支持一次拖放一行数据486 疑难 227 如何开发四则运算自动出题机486 􀃚 通过 Rnd 函数产生指定范围的随机数488 9.4 开发动画窗体489 疑难 228 如何让窗体中的文本框循环滚动489 􀃚 控制动画速度的两种方式491 疑难 229 窗体中可以播放GIF 和Flash 动画吗491 􀃚 Excel 播放GIF 的原理493 疑难 230 如何让窗体产生百叶窗动画493 疑难 231 如何设计精美边框且带动感的窗体495 􀃚 网页设计与 VBA 496 疑难 232 如何设计动态的启动画面497 􀃚 网页动画的基本语法499 疑难 233 如何让窗体中产生跟随鼠标指针的文字动画500 􀃚 VBA 设计动画的限制501 9.5 练习与思考501 第10 章插件开发502 10.1 定制菜单与工具栏、功能区502 疑难 234 如何定制新菜单、工具栏与功能区502 􀃚 加载宏的特性与安装方法506 疑难 235 可以任意组合内置命令吗507 􀁸 CommandBars 对象的ID 参数的作用508 疑难 236 如何改造内置命令的功能509 􀃚 修改 OnAction 属性替换内置功能510 疑难 237 可以提取所有内置图标及其编号吗511 􀃚 ActionControl 属性的作用513 疑难 238 可利用菜单控制分页符、零值和批注的显隐吗513 􀃚 Excel 不同版本的自启动路径515 疑难 239 如何仅仅利用菜单创建工作表目录516 􀃚 让一个过程执行多个菜单执行多个功能的方法 518 疑难 240 如何让菜单具有感应功能518 􀃚 State 属性的特点与应用520 疑难 241 如何让菜单和功能区中显示自己的照片521 疑难 242 如何设计可单选与复选的新工具栏菜单525 􀃚 具有单选与复选特效的图标组合 528 10.2 开发通用插件528 疑难 243 如何开发定位工具箱扩展Excel 定位的功能528 疑难 244 如何开发通用的多区域复制插件534 疑难 245 如何开发单元格批量合并插件535 􀃚 操作大面积区域时利用警告框提示用户 538 疑难 246 可以利用插件输入常用VBA 代码吗538 􀃚 计算当前代码行的行号542 疑难 247 如何利用插件删除当前工作簿所有代码543 􀃚 ActiveWorkbook 与Thisworkbook 的分别544 疑难 248 如何利用TreeView 控件打造工作簿浏览器545 􀃚 ImageList 控件的特点与作用547 10.3 封装DLL 插件548 疑难 249 如何封装数值与文本互换的DLL 插件548 􀃚 使用 VB 6.0 封装Dll 的4 个重点554 疑难 250 如何设计字符串分离与表达式计算插件555 􀃚 利用 VB 开发功能区的优势563 10.4 练习与思考563 第11 章工资管理系统564