1、Excel VBA 程序开发自学通 2018-10-9 第 1 页 /共 513 页为入门篇：VBA 优势、功能与概念第一章 从 Excel 插件认识 VBA简单的说，Excel VBA 是依附于 Excel 程序的一种自动化语言，它可以使常用的程序自动化，类似于 DOS（磁盘操作系统）中的批处理文件（后缀名 “.bat”） 。那么它有什么具体的功能？在工作中与常规操作方式相比，具有哪些优势？笔者试图通过一个简单却实用的插件来展现。本章要点： 从身份证号获取个人信息 在工作中如何发挥 Excel 插件的优势1.1 从身份证号获取个人信息制作人事资料时，通常需要录入职员身份证号码，以及生日、年龄
2、、性别等等。除身份证号码需要手工逐一录入以外，其它三项信息的录入有四种方法：手工录入、内置公式、自定义函数法、插件法。手工输入方式效率极差，且出错机率也最高，本节通过后三种方式来实现并比较，从而让读者对 VBA 之优势与用法得以初步认知。1.1.1 常规公式法以图 1.1 数据为例，利用公式从身份证中提取生日、年龄、性别等信息，可以有多种方法。本例列举其中之一。图 1.1 根据身份证号提取职工年龄、生日与性别通过公式计算职工的年龄、出生日期与性别，步骤如下：Excel VBA 程序开发自学通 2018-10-9 第 2 页 /共 513 页（1）在单元格 C3 输入以下公式，用于计算年龄：=D
3、ATEDIF(DATE(MID(B3,7,4-(LEN(B3)=15)*2),MID(B3,11-(LEN(B3)=15)*2,2),MID(B3,13-(LEN(B3)=15)*2,2),NOW(),“Y“)（2）在单元格 D3 输入以下公式，用于计算出生日期：=TEXT(RIGHT(19&MID(B3,7,LEN(B3)/2-1),8),“#年#月#日“)（3）在单元格 E3 输入以下公式，用于计算性别：=IF(ISODD(MID(B3,15,3),“男“,“女“)注意：在 Excel 2003 中，ISODD 函数默认状态下无法使用，需要加载“分析工具库”才可以正常使用，为了使公式通用，
4、通常改用 MOD 函数。即公式改为：=IF(MOD(MID(B3,15,3),2),“男“,“ 女“)（4）选择 C3:E3 区域，将公式向下填充即完成身份证信息提取。效果如下：图 1.2 公式法获取身份证信息点评：相对于手工输入法，利用公式从身份证号码获取个人信息有着效率更高、错误率更低之优点，人员越多时越能体现出其高效优势。本例文件参见光盘： 第一章 提取身份证信息.xlsm1.1.2 自定义函数法自定义函数是指利用 VBA 编写的外置函数。在本例的随书光盘中已经录入了相关的 VBA 代码，可以随时调用。对于代码的含义和录入方式在后面的章节后有详细介绍，本章仅通过具体应用了解其用法与优势。
5、具体操作步骤如下：（1）进入“自定义函数法”工作表；（2）在 C3:E3 区域分别输入以下三个公式，用于计算年龄、出生日期和性别：=SFZ(B3,“NL“)=SFZ(B3,“SR“)=SFZ(B3)或者=SFZ(B3,“XB“)（3）选择 C3:E3 单元格，将公式向下填充，结果见图 1.3 所示。Excel VBA 程序开发自学通 2018-10-9 第 3 页 /共 513 页图 1.3 自定义函数法获取身份证信息本例中的函数 SFZ 即身份证函数，用于从身份证号码中获取年龄、生日与性别等信息。它不属于 Excel 内置函数，需要利用 VBA 编写代码才可以使用。读者可以从随书光盘中获取该
6、完整代码。SFZ 函数有两个参数，第一参数为单元格引用，第二参数为信息描述，即用于指定需要获取身份证中哪一部分信息。当它为“NL” （不区分大小写）时，获取年龄；当它为“SR”时，获取生日，当它为 “XB”或者省略第二参数时，获取性别。点评：相对于内置函数法/公式法，自定义函数法是借用 VBA 编写的外置函数完成，它的优势在于公式简短，且容易理解。任何不熟悉函数与 VBA 者皆可一分钟内学会操作并理解其公式含义。1.1.3 插件法插件法是指借用 Excel 插件操作工作表，该插件不隶属于当前工作簿，但却可以实现与当前工作簿交互的功能，批量、迅速完成身份证信息提取工作。操作步骤如下：（1）关闭
7、Excel 程序的前提下，将随书光盘中的插件（位置： 第一章批量获取身份证信息.xlam）复制到以下自启动文件夹中即安装完成：C:Program FilesMicrosoft OfficeOffice12XLSTART注意：如果您的 OFFICE 没有装在 C 盘，那么上面的磁盘号需要根据实际情况做修改；如果您使用 OFFICE 2003，则将其中“Office12 ”修改为“Office11 ”。（2）打开光盘文件“提取身份证信息.xlsm” ，进入“插件法”工作表；（3）选择单元格区域 B3:B6，单击右键，从右键中选择 【批量获取身份证信息】菜单，程序将弹出一个对话框“确定计算区域” 。
8、该对话框中默认显示当前选区地址，如果需要修改地址，可以输入新的地址，也可以用鼠标在工作表中选择身份证存放区域，该区域的地址会自动产生在对话框中。见图 1.4 所示；（4）单击“确定”按钮，程序在瞬间就会从选区的所有身份证中提取年龄、生日和性别等信息。Excel VBA 程序开发自学通 2018-10-9 第 4 页 /共 513 页图 1.4 插件法批量获取身份证信息点评：插件法从身份证号码中获取信息的优点是速度快，通用性好。相对于内置函数法，它在操作上更简单，不需要任何函数知识，不需要输入长长的公式，只点几次鼠标即可；相对于自定义函数，它的优点是通用性好，在任何工作表、任何工作簿皆可使用本工
9、具。而前一方法之自定义函数非插件方式存在，只能在当前工作簿中使用。1.1.4 浅谈 VBA 优势前面三个案例中我们可以看出，Excel 具有强大的计算功能，但常规方式对于某些大型数据运算显得比较繁琐。用户需要学习复杂的函数知识，设置长长的公式才可以解决某些运算。而 VBA 可以使公式简化、易懂，甚至根本不需要公式，一个字母不用录入即可完成一些专业性较强的计算。具体说来，相对于 Excel 自带的功能，VBA 或者说 VBA 开发的插件具有以下优势： 批量地对操作对象进行数据处理以前一节插件法完成身份证信息进行例证，它可以瞬间完成多个单元格数据的运算，甚至多个工作表中存放的身份证号码也可瞬间完成
10、信息提取。较传统的逐一处理方式在效率上有大幅提升。 多任务一键完成多任务是指对同一个对象需要进行多个操作，例如前一节是从身份证号码中获取三类信息，VBA 可以单击一个按钮后瞬间完成，完全感觉不到它在分三步逐一完成任务。这是高效办公地最佳体现。 将复杂的任务简化Excel 是很多很多小工具的综合体。这些工具可以嵌套运用，完成更强大的数据处理。但当嵌套过多时，就需要用户要较深的功底才能操纵或者理解。另一方面，对于某些特殊行业的工作、任务，也要经过很复杂的操作才可以完成，而对于某些只需要应用不需要深入研究、理解的普通办公文员们来说是一个技能考验。而通过VBA 进行二次开发可以将复杂的任务变得更简单。
11、简单是指理解和操作上同时简化。就像 1.1.3 节中通过右键菜单提取身份证号码三类信息一样，不需要用户去录入长长的公式，以及理解信息是如何提取出来的，单击菜单即可完成。再如企业中生Excel VBA 程序开发自学通 2018-10-9 第 5 页 /共 513 页成工资条，10000 个人的资数用手工操作需要处理 10000*N 次，而利用 Excel 插件可以单击按钮完瞬间成。 将工作表数据提升安全性利用 VBA 代码可以对数据进入多层保护，在某些特殊需求下，VBA 可以保护数据让普通用户无法胡乱修改，或者不小心破坏数据及数组结构。 提升数据准确性准确性体现在数据录入和数据运算两方面。首先，
12、通地 VBA 对输入的数据进入限制，可以防止用户意外录入不规范字符。如数字中有两个小数点，或者录入数值时不小心录入了标点或者字母，造成无法计算或者漏算。其次，在数据运算时，人工设置大量公式，或者每天在不同地方重复录入同一个公式。在大量地操作中难以避免不产生一次错误。而利用 VBA 可以让工作简化，工作量越小，出错的机率一定越小；同时，在大量重复性工作中 VBA 可以确保不产生错误。 完成 Excel 本身无法完成的任务弹出提示、警告对话框、行程安排与预告，或者到磁盘中查找需要的数据、修改注册表等等，Excel 常规方式是不可能完成的。如果需要类似功能，VBA 完全可以胜任。 开发专业程序利用
13、VBA 还可以开发一些专业型的程序，如报表汇总软件、进销存管理系统、人事管理系统等等，可以将界面设置成其它任何软件的显示方式媲美专业的程序软件1.2 插件特点及其如何发挥插件的优势在前一节中，通过一个身份证信息获取的插件认识了 Excel 插件，那么在工作中应如何发挥 Excel 插件的优势呢？1.2.1 Excel 插件的特点Excel 插件是利用 VBA 程序开发的外置工具，通常是 xla、xlam 格式或者 dll 格式。其中 xla 和 xlam 插件直接用 Excel 就可以开发，而 dll 插件通常采用 VB 或者C+来编写。不管何种软件开发的插件，它都需要在外观和功能两方面具有某
14、些特征，以方便用户调用。1.外观特征 有若干个菜单或者工具按钮在插件封装后，调用其代码有两种方式：用代码调用，用菜单或者工具栏按钮，显然菜单更方便。用户通过菜单单击即可完成相对于常规方式较复杂的操作或者运算。 利用窗体实现与工作表数据交互在弹出的窗体中可以调用工作表的数据，也可以将窗体中录入的数据导到工作表。而在窗体中录入数据时，相对于工作表中录入数据，可更好地控制。例如某个Excel VBA 程序开发自学通 2018-10-9 第 6 页 /共 513 页文字框中可以指定只能录入数字，而另一个文字框可以指定只能入日期。也可以设定录入某项目后自动跳转到指定目标位置，而不用手动去移动光标插入点。
15、甚至可以在录入时核对是否与工作表中数据是否重复等等 有一个帮助界面对于开发者来说，不管自己开发的工具如何简单，都有必要向用户说明其功能和操作方式。所以在工具中通常加入一个窗体，进行文字说明或者动画演示。特别是工具没有提供菜单、而是通过函数调用或者快捷键调用时，更需要一个说明窗体。 对函数做参数说明对于函数类插件，必须对每个函数的参数进入详细说明，让用户插入函数时可以清晰明了地看到每个函数中每个参数的功能与使用方式。2.功能特征Excel 插件中的代码和普通宏程序的代码在编写上具有一些差异，这是它们的设计目的不同造成的。其中宏代码通常用于解决某个具体的问题，它可能限用一次，也可能需要反复调用。但
16、都只为解决自己的某个具体问题而录制。而开发 Excel 插件则通常是开发者开发后，给其他的终端用户使用。用户不确定，需要操作的区域对象不确定。所以插件有不同的需求，它需要具有以下特征： 没有具体的区域地址由于开发插件通常是给其他的终端用户使用，所以不能指定数据区域地址，而是提供一个自由选择目标区域的选择对话框，或者利用代码计算目标工作表中的待计算区域。这是和录制宏最大的差异。 不使用具体的工作表名或者工作簿名原理与前一条一致。 必须有通于菜单或者窗体供用户调用命令，而不是在工作表中建立按钮来调用命令。dll 格式的插件不存在工作表，而 xla 和 xlam 格式插件的工作表是隐藏状态，工作表不
17、可能在用户的界面呈现出来，所以必须建立一个通用的菜单栏，使其在打开任意工作簿都会显示出来供用户操作。如果使设置了快捷键，那么是可以不用菜单或工具栏的，界面将会更简洁。 尽可能提供自定义选项插件的针对性不强，即它需要有广泛性。插件通常不是为某一个固定用户开发，或者需要处理的数据并非永远一致，那么在不同用户使用同一功能时，需要有自定义其参数或者选项的空间，工具才能有更好的通用性。例如设计一个工资条制作插件，那么工资条的表头行数就有必要让用户选择，而非强制一行或者两行。这和编写一个解决临时性问题的编程思路不同。 具有多版本适应能力目前办公用户使用的 Excel 版本差异很大，有 Excel 2000
18、、Excel XP、Excel 2003，也有 Excel 2007。开发者不会假定用户都用某个版本的 Excel，而是通过代码判断当前用户的版本号，然后调用不同的代码，以适应当前版本，否则某些功能可以无法使用。 防错机制自用型宏程序通常不用防错，因为用户和开发者是同一人。而插件则必须有完善的防错机制，预先设置了遇到某种错误该如何反应的措施，避免破坏用户数据，Excel VBA 程序开发自学通 2018-10-9 第 7 页 /共 513 页或者进入死循环，消耗尽计算机的内存资源。1.2.2 Excel 插件的优势与限制在工作中使用插件，可以使用工作更轻松，运算更快速、准确。当然前提是插件的代
19、码编写足够优秀，不仅具有很强的通用性，还要有完善的防错机制，以及灵活的自定义选项。那么工作中使用优秀的插件进行工作具有哪些优势呢？ 简化操作：类似于 bat 批处理文件，可以一键执行多个任务 强化功能：对 Excel 内置功能无法完成的一些任务，借用 VBA 代码可以实现 美化界面：VBA 用以调用 Flash 动画，也可以播放 Gif 动画，还可以直接对单元格字符产生滚动效果。对于喜欢装点的用户，借用 VBA 可对工作表进行很好地修饰 固化格式：VBA 可以对录入的数据进入检测，阻止输入不规范的数据；也可以禁止新增、删除工作表，或者禁止缩放窗口，从而促使多用户文件能确保格式一致，便于汇总虽然
20、插件在工作中有以上优势，但它在某些方面也具有一些限制： 通用性方面：开发插件通常是个人行为，而非 Office 软件一样由一个大公司主持。所以其通用性很可能不是很好，开发者测试的次数少以及测试条件不足等等，导致工具具有某些隐含的缺陷 防错方面：程序员不一定是终端用户，甚至可能从来没有成为办公用户，而是直接学习插件开发。那么在程序编写时就可能思维受限，无法对可能出现的所有错误进行防范 移植方面：插件属于外置工具，它的所有功能都需要安装才能使用。所以如果利用插件设计的表格有可能传用客户后无法正常开启，或者开启后无法正常显示。最好的解决方法是将插件让客户端也安装一次 独立方面：Excel 的 VBA
21、 是依附于 Excel 主体程序的附属程序，它可以开发强化 Excel 功能的程序，但不能开发脱离 Excel 而单独存在的软件。如果需要开发全新而专业的应用程序，VBA 并非理想的程序1.2.3 如何发挥插件的优势可以确定的是，善用插件可以提升工作效率。但是插件也不可滥用，否则享用优势的同时，也会产生一些后患。首先，需要明白插件相对于 Excel 的功能属于外置工具，它需要安装后才能使用。如果读者的文件非自用型，需要与他人共享、阅读，那么需要连插件一起共享；其次，如果是简单的功能，尽量使用内置功能，少用插件。插件适用于处理复杂的或者 Excel 内置功能无法完成的工作；宏有一个通用 BUG，
22、即使用宏代码后，内置撤消功能将禁用。为了让用户减少损失，针对某些会更新数据、修改（破坏）原有格式的工具，一定要提供一个恢复原状的程序。例如有制作工资的工具，就搭配一个删除工资条的工具。最后，尽量将插件在同部门共享。即一个办公室为单位或者一个企业为单位，让整个单位都拥有相同的插件，才能更好地发挥插件优势。Excel VBA 程序开发自学通 2018-10-9 第 8 页 /共 513 页1.2.4 开发 Excel 插件的条件针对插件的开发者，他/她需要什么条件呢？现罗列如下： 熟练撑握 VBA 技术这是首要条件。必须对大部分常用对象及其属性熟练地掌握。且需要了解数据处理的常用方式，并从多种处理
23、方式中找出最高效且通用的方式。如果在某些特殊情况下，程序的通用性与执行效率只能选择其一时，通用性优先于执行速度。 具有一定的报表操作经验仅学习 VBA 是可以熟练掌握 VBA 知识的，但是仅掌握 VBA 知识却不可能成为优秀的程序员。例如开发财务人员用的插件，那么需要懂得一些财务知识，不需要精通，但一定要对财务知识有所了解或者有财务报表的制作经验，才可能开发出适合于财务人员的插件。 美化常识这里的美化并不一定是漂亮的外观，而是要使自己开发的程序界面具有协调性、统一性，还需要了解普通用户的操作习惯，根据习惯设计人性化或者操作更便利的界面。当然，在不影响效率的前提下，将窗体设计得更美观，也是具有现
24、实意义的。 熟悉不同版本的 Excel 间的差异终端用户们有可能使用多个版本的 Excel，那么开发者也需要了解不同版本间的差异。例如 Excel 2003 中 Application 有一个属性 FileSearch，用于在磁盘中查找文件，而 Excel 2007 取消了该属性，那么开发插件时就应尽量避免使用该属性，借用其它方法的代替。否则将产生兼容性问题，以致程序产生 BUG。 具有较强的耐心编写程序是一个与字母相处的过程。对于大中型程序，可能长时间对着一堆字母或者数字，这需要有一定的忍耐力。甚至在程序开发完成后，仍然需要耐心对程序进行多角度、多版本的测试，以提升程序的通用性和纠错性。1.
25、2.5 本书架构本书除 VBA 基本理论外，偏重于讲解插件开发的原理、思路与方法以及如何提升程序速度。在以后的章节中，主要从按以下方式进行编排：（1） VBA 历史与功能、安全性等等周边知识简要介绍（2） 认识 VBE 编辑器并对其它进行优化设置（3） 学习 VBA 中常用对象及属性、方法、事件（4） VBA 代码如何提升执行速度（5） 掌握 VBA 高级应用，包括窗体的认识，及磁盘、目录与文件操作（6） 开发 VBE 环境下的插件（7） 学习利用 VB 开发专业性的 COM 加载宏插件（8） 最后利用前面章节的知识开发一个大型 Excel 插件。从该插件的开发思路和过程让读者了解插件开发的常
26、规流程及注意事项本书以插件开发为重心，但对于 VBA 中常用知识，不一定与插件开发相关的知识但工作中较常用的功能也会进行详解，或者进行实例演示。除插件开发外，程序的提升和防错在本书的多次强调的重点。Excel VBA 程序开发自学通 2018-10-9 第 9 页 /共 513 页从第二章开始，让读者学习、掌握 VBA 理论知识，为插件开发提供基础。第二章 VBA 概述VBA 是 VB（VISUAL BASIC ） 的一个子集，是一种附属于 Excel 的程序软件。在学习 VBA 的语法之前有必要对其发展史、功能、特点等等方面进行了解。Excel VBA 程序开发自学通 2018-10-9 第
27、 10 页 /共 513 页本章要点： VBA 的发展史与优缺点 VBA 能做什么 VBA 的安全性 使用 VBA 帮助2.1 VBA 的发展史与优缺点VBA 语言作 VB 家族成员，起步很早。发展至今已拥有非常广大的用户群，在日常办公中有着举足轻重的作用。2.1.1 宏与 VBAExcel 早在 1985 年就首次在 Machintosh 上出现，1987 年 Excel 开始引进到Windows 环境中。当时 Lotus 1-2-3 是计算机历史上最成功的软件系统之一，但它仅支持一些极其简单的宏，而 Excel 软件从 Excel 4 开始，可以使用相对复杂的 xlm 宏，完成更复杂的工作
28、，慢慢的将 Lotus 1-2-3 挤出电子表格行业，迅速占领了市场。当Excel 5 中正式推出 VBA(Visual Basic for Applications)作为通用的宏语言来为 Office应用程序编写代码后，Excel 已完全征服了制表用户。可见宏语言在表格软件中影响之深远。宏的英文名为 Macro，是自动执行某种操作的命令集合。它包括两个过程，即Excel 4 或者称为 xlm 的宏语言和 Excel 5 中的 VBA 宏。Excel 4 的宏由宏表函数构成，由录入在宏表中的函数来控制程序的执行。至 1993 年发布的 Excel 5 中，微软开始推广 VBA 做为宏语言，并同
29、时引进 VBA 编辑器，即 VBE（Visual Basic Edirtor） 。用户可以通过录制宏来产生代码，代码储存在 VBE 环境的代码模块中，利用 Alt+F8可以反复调用录制的宏。VBA 是目前 OFFICE 系列通用的一种程序语言，它支持录制、执行、单步执行、调试等等操作，可以使用户从繁重的制表任务中解脱出来。VBA 是一种面向对象的程序语言，由一种所见即所得的方式编写代码，这使它在学习和使用方面都相比其它语言更简单。事实上，几乎所有 VBA 程序员都由录制宏开始学习 VBA，这是一个 VBA 速成的捷径。甚至 VBA 高手们仍然对录制宏乐此不倦，因为它可以完成VBA 程序的大部分
30、代码，程序员仅需在录制的宏代码中稍加修改即可成为最后的合格程序；另一个最重要的因素是录制宏可以为程序员提供词典的作用，即忘记了某个对象单词，或者完全不明白某个属性的语法时，利用录制宏可以产生对应的代码，用户复制即可使用。2.1.2 VBA 历史与版本VBA 的前身是 xlm 宏语言，鉴于 xlm 宏功能有限，至今已经用 VBA 完全替代了 xlm 宏。但是为了体现兼容性，所有版本的 VBA 中皆可以调用以前的部分宏表函数。例如 Excel 2007 的 application 对象仍然保留了以下宏表相关的一个方法和两个属性，通过它们可以执行早期宏表所有函数：Excel VBA 程序开发自学通
31、2018-10-9 第 11 页 /共 513 页 Application.ExecuteExcel4Macro Application.Excel4MacroSheets Application.Excel4IntlMacroSheets在抛弃早期宏语语言后，VBA 从 1993 年开始逐步在很多软件中出现，除OFFICE 办公软件外，Cad 、Coreldraw 等等软件也支持 VBA。目前 VBA 的最高版本是 6.05。但需要申明的是，VBA 版本并非与与主体程序的版本对应升级，即 Excel的多个版本有可能使用同一版本的 VBA。如 OFFICE 2003 和 OFFICE 2007
32、 都使用6.05 版的 VBA。检测当前 OFFICE 中 VBA 版本可以使用以下代码：Sub 获取 VBA 版本号()MsgBox Application.VBE.VersionEnd Sub不同版本的 VBA 带有不同的函数，编程时需要根据 VBA 的版本调整体码，使之尽量通用。但在 Excel 中编写 VBA 程序时，Excel 的版本号显得更为重要。因为不同的 Excel 版本有不同的对象和方法，而且差异较大。在本书的附录中有 Excel 2007 与早期版在 VBA 方面的差异，做为插件开发者有必要进行全面了解。2.1.3 VBA 优、缺点VBA 做为 OFFICE 办公套件的二次
33、开发语言，它是一个很优秀的程序语言，从国内外 OFFICE 论坛中 VBA 相关的发贴量可以知道 VBA 用户群有多大，这也反证了 VBA 在工作中应用之广泛性。总体来说，VBA 语言具有以下优点： 可以录制早期的磁盘操作系统 DOS 不支持录制，虽然它是一门很简单的语言，但要让大多数用户学好 DOS 仍然是一件难事。它的每个命令，每个字母都面要手工录入，所有命令都需在大脑记忆。而 VBA 采用录制方式可以产生完整的代码，程序稍加优化即可取得最佳程序，摆脱死记代码的困扰。 所见即所得Excel VBA 有窗体及工作簿、工作表等等对象，可以直接拖动产生对象，不需要编写创建对象的代码。而且可以调整
34、为一边操作工作表数据或者图形对象，一边查看代码变化，即录制宏时同时查看工作簿窗口和代码窗口。 调用现成对象VB 或者 C+开发程序时需要自己设计窗体、对象，而 Excel 中有现成的工作簿对象、工作表对象、窗口对象、图形对象等等，开发者仅需对这些对象或者数据进行操作即可，不需要开发一个报表程序及各对数据存放介质。这也是 VBA 简单易学的原因之一。 应用广泛目前Excel、 Word、Access、PowerPoint、FrontPage 、Visio、Project 、Outlook、AutoCAD、CorelDraw 等等程序都支持 VBA。而各程序间的代码可以相互移植，然后对代码中的引用
35、对象稍加修改即可。 交流方便这里说了交流是指 VBA 用户与用户之间的交流。国内、国外都有很多大型的Excel VBA 程序开发自学通 2018-10-9 第 12 页 /共 513 页VBA 相关论坛，可以通过论坛交流心得、学习他人的编程思路，以及在线提问。OFFICE VBA 方面的论坛远比 C+与.net 等等语言的相关论坛更多。相对于 Excel 内置功能， VBA 也有它自己的缺点： 学习周期长学习 VBA 的时间至少两个月，而数据透视表、函数、图表等等其它内置功能则相对更快。 专业词汇多VBA 中有几百个对象，每个对象有多个属性以及方法，虽然不需要死记硬背所有对象名称和属性，但仍然
36、需要花很多精力来理解、消化。 普及范围小目前 VBA 用户群在一天天扩大，但相对于 Excel 的内置功能如公式、图表等等，仍然有待进一步提升普及率。在普及不够的情况下，程序员的插件需要做更完善的帮助系统，也需要更多的时间来测试，使未接触 VBA 的用户能更快地掌握其技巧。2.2 VBA 能做什么VBA 是一门程序语言，工作中 VBA 的常见用途是什么？本节进行讨论。2.2.1 VBA 用途可以肯定地说，VBA 可以完成 Excel 常规功能可以完成的任何功能。但是事实上不可能有人用 VBA 来处理所有任务，而是有选择性、有针对性地使用 VBA。概括地说，VBA 主要用在以下几方面： 处理大型
37、运算Excel 内置的函数嵌套也可以完成很多大型的数据运算，然而很多易失性函数会造成 Excel 程序启动缓慢，特别是数组公式。用 VBA 来处理数据运算则可以解决这个问题。 工作簿/工作表折分/合并对于工作簿/工作表按条件拆分成多个工作表之任务，手工完成效率极度的低，VBA 则可以轻松完成，单击鼠标即可。也有部分企业需要每月汇总下属分公司的报表，多报表的汇总人工操作显然是事倍功半，而 VBA 插件则可一劳永逸。 处理重复性任务针对某些每天都需要进行且完全复重不变化的任务，利用 VBA 仅需要第一次手工操作、编写代码，后续的所有任务都全自动执行。它的优势在仅在于速度快，还更准确。人工操作的步骤
38、越多，出错的机率一定相应的越大。 简化内置公式以第一章的公式为例，以下两个公式都可以从身份证号码获取年龄：=DATEDIF(DATE(MID(B3,7,4-(LEN(B3)=15)*2),MID(B3,11-(LEN(B3)=15)*2,2),MID(B3,13-(LEN(B3)=15)*2,2),NOW(),“Y“)=SFZ(B3,“NL“)很显然，第二个公式输入效率高，且更易让用户理解。其第一参数为引用的身份证号，第二参数“NL”表示年龄。Excel VBA 程序开发自学通 2018-10-9 第 13 页 /共 513 页 定制程序界面对于某些喜好个性化的用户来说，Excel 是支持全面
39、定制的程序。利用 VBA 可以将 Excel 界面定制成更具有个性化的程序。类似于 QQ 换肤、播放器换肤等等，Excel 也可以通过 VBA 使用程序标题、状态栏、菜单个性化，例如产生滚动字幕，例如将个人照片、邮箱地址加到菜单栏等等。图 2.1 定制个性界面 开发受保护的专业程序网络上有很多 Excel 版的人事系统、学校成绩管理系统、考试座次安排系统等等，利用 VBA 可以编写很专业的程序，且能对其进行保护，以确保开发者的利益。即使是纯公式设计的报表，有时也需要借用 VBA 来设计程序注册功能或者登录界面等等。2.2.2 VBA 主要用户根据 VBA 的特点，使用 VBA 主要有两类对象。
40、其中最主要的是开发者就是终端用户，即编写代码给自己用的的业余程序员。而且 VBA 也是所有程序中拥业余程序员最多的一类程序。利用 VBA 解决一个临时问题，或者处理某一个具体的重复性任务，是大家使用 VBA 最多的原因。只有少数用户不为解决当前问题而基于兴趣编写一些通用型插件，可以解决很多很多类似问题。注意：普通宏程序和插件最主要的一个区别是：编写普通宏程序只为针对当前遇到的一个具体问题而编写，当前问题解决后，该程序也不再有存在的价值；而编写插件则通过针对具有大从性质的问题，例如工资条设计，很多企业、事业单位都需要，那么它的生存周期是很长的，用户也是不固定的，在编写时也就会产生更多的自定义选项
41、留给终端用户。所以编写插件和自编自用型宏程序在困难度上有较大区别VBA 用户通常用于两个基本条件：其一为工作中某些任务利用常规方式处理太繁琐，需要 VBA 来简化工作；其二为因 VBA 兴而趣研究。笔者一直坚持的一种观点是：对 VBA 保持持续的兴趣是学好 VBA 很重要的条件。另外一类 VBA 用户即为专业的 VBA 程序员，专为别人定制程序。这类用户除了需要熟悉 VBA 语法外，还需要对 Excel 各项操作有较多的经验。也有部分人员本Excel VBA 程序开发自学通 2018-10-9 第 14 页 /共 513 页身就是某个行业的资深主管、兼职程序员。例如一个精通 VBA 的财务主管
42、，他/她开发的财务类 VBA 程序一定比只精通 VBA 不懂财务的专业程序员开发的程序更专业，或者说更具有易用性。2.3 VBA 的安全性上世纪 90 年代宏病毒泛滥，使人们对宏以及宏的安全性都有所了解。但事实上很多用户也就通过宏毒病的传播了解宏的部分特点，这自然是很片面的。2.3.1 VBA 安全性事物都有双面性，程序语言犹其如此。程序的功能越强，那么同时意味着用它做破坏也可以具有更大的破坏力。VBA 依附于 Excel 程序，但它做为病毒传播时，可以破坏的对象却不局限于 Excel 程序，磁盘中所有文件皆可以任意修改。正因如此，学习 VBA 时，有必要掌握好这把双刃剑。在默认状态下，Exc
43、el 2003 和 2007 都是禁用宏的，以确保用户数据不会因潜在的宏病毒而受破坏。但是同时也带来另一个问题正常的 VBA 程序无法执行。所以通常有三种做法：不用 VBA 程序的用户，彻底禁用宏，杜绝宏病毒蔓延。常用 VBA 程序，包括自己开发和别人开发程序的用户，可以将宏的安全性稍加提高，即遇到宏时提示用户，当用户确定代码安全时再执行。第三类自编自用型用户或者完全信任宏代码开发者的用户，则可以将宏的默认设置修改为无限制。即允许任何宏执行，从而提升工作的效率。笔者属于第三类，永远允许所有宏自动执行。2.3.2 了解安全性对话框Excel 2007 有两个安全性对话框。一个是打开带有宏代码时提
44、示用户的“安全选项”对话框，见图 2.2 所示；另一个是位于 Excel 选项中的“信任中心”对话框，见图 2.3 所示。1.安全选项当启动一个具有宏代码的工作簿或者启动一个 COM 加载宏时，默认状态下在Excel 编辑栏上方会弹出一个 “安全警告 部分活动内容已被禁用”的提示框（根据加载宏的类型不同，显示的文字也会有所差异） 。当用户单击“选项”按钮后再次弹出一个“安全选项”窗口，在窗口中将罗列出所有被阻止的加载项。本例中有两个，包括一个 xla 格式的加载宏，和一个 dll 格式的 COM 加载项。如果确认需要在工作簿使用某个加载项，或者信任该加载项中的代码，则选择“启用此内容” ，然后
45、单击“确定”按钮，安全警告对话框消失，而相关加载项所携带的代码也可以任意调用了。Excel VBA 程序开发自学通 2018-10-9 第 15 页 /共 513 页图 2.2 安全选项图 2.3 Excel 信任中心2.信任中心前面所说的每次在开启工作簿时启用自己信任的插件或者宏工作簿，虽然确保了安全，事实上效率不高，操作上较繁琐。而设置信任中心可以一劳永逸的解决上Excel VBA 程序开发自学通 2018-10-9 第 16 页 /共 513 页述问题。进入信任中心步骤如下：（1）单击左上角的圆形 OFFICE 按钮；（2）选择“Excel 选项” 按钮打开 Excel 选项对话框；（3
46、）单击左边的“信任中心”按钮即可显示“信任中心”对话框。见图 2.3 所示。Excel 选项的信任中心主要用于管理宏的安全性问题，它包括“受信任的发布者”、 “受信任位置” 、 “加载项” 、 “ActiveX 设置” 、 “宏设置 ”、 “消息栏” 、 “外部内容”和“个人信息选项”8 个选项，分别具有以下作用：受信任的发布者：罗列出本系统中所有数字签名证书。证书是文档中电子的、基于加密的安全验证戳。此签名确认该宏或文档来自签发者且没有被篡改，表明您认为该数据库是安全的并且其内容是可信的。这可以帮助数据库的用户确定是否信任该数据库及其内容。受信任位置：表示该位置下存放的代码是受信任的，不受宏
47、的安全性设置的影响，任何情况下都会执行其代码。在本对话框中罗列了 Excel 默认设置下的几个位置。用户也可以手工添加或者添新的受信任位置。通常可以将自己编写的代码所存放的目录设置为受信任位置，以避免每次手工启用宏。加载项：添加或者删除加载项，包括 xla、xalm、dll 和 xll 格式的加载项。在本对话框中可以手工安装以及删除所有格式的插件。而笔者的习惯是 xla 格式或者xlam 格式的插件直接存到 Excel 自启动文件夹中，免除安装。对于 COM 加载项，即 dll 格式的插件则可以使用本对话框中手工安装。获取自动启动的路径可以使用以下代码：Sub 自启动路径()MsgBox Ap
48、plication.StartupPathEnd Sub以上代码获取的是用户级自启动路径，即只对当前用户发生作用。如果需在任何用户打开 Excel 都可以启动插件中的宏，那么用可以将用以下路径：C:Program FilesMicrosoft OfficeOffice12XLSTART其中 Office12 表示 Office 2007 的路径，如果用户安装的是 Office 2003，则修改为 Office11；另外 “C:”也需要根据皮实际情况修改，例如 Office 安装在 D 盘则用“D:”。ActiveX 设置：控制 ActiveX 控件的启动方式，可以让禁用 ActiveX 控件且
49、不发出通知，达到时最高的安全性，也可以禁后通用户选择，还可默认允许执行，以提高效率。在此处，效率和安全性是相冲突的。宏设置：宏设置类似于 ActiveX 设置，它是对携带宏代码的工作簿进行安全限制。同样包括与 ActiveX 设置相似的选项。不过最下边的 “信任对 VBA 工程对象模型的访问”则不是控制宏的运行，而是用于控制代码对 VBE 环境的操作。默认状态下是禁用代码操作 VBE 环境中任何组件的，打勾后才允许读取或者修改 VBE 的任何组件，例如在 VBE 窗口中新建一个菜单，或者删除 VBA 代码模块。消息栏：控制 Excel 是否弹出消息栏，即阻止宏运行时是否通知用户。默认状态是要发出通知。外部内容：所谓外部内容指工作表中的公式引用发其它工作簿，包括加载宏中的数据。本选项决
