ExcelVBA 从入门到精通必备.doc

《ExcelVBA 从入门到精通必备.doc》由会员分享，可在线阅读，更多相关《ExcelVBA 从入门到精通必备.doc（513页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、.为入门篇：VBA 优势、功能与概念第一章 从 Excel 插件认识 VBA简单的说，Excel VBA 是依附于 Excel 程序的一种自动化语言，它可以使常用的程序自动化，类似于 DOS（磁盘操作系统）中的批处理文件（后缀名 “.bat”） 。那么它有什么具体的功能？在工作中与常规操作方式相比，具有哪些优势？笔者试图通过一个简单却实用的插件来展现。本章要点： 从身份证号获取个人信息 在工作中如何发挥 Excel 插件的优势1.1 从身份证号获取个人信息制作人事资料时，通常需要录入职员身份证号码，以及生日、年龄、性别等等。除身份证号码需要手工逐一录入以外，其它三项信息的录入有四种方法：手工录

2、入、内置公式、自定义函数法、插件法。手工输入方式效率极差，且出错机率也最高，本节通过后三种方式来实现并比较，从而让读者对 VBA 之优势与用法得以初步认知。1.1.1 常规公式法以图 1.1 数据为例，利用公式从身份证中提取生日、年龄、性别等信息，可以有多种方法。本例列举其中之一。图 1.1 根据身份证号提取职工年龄、生日与性别通过公式计算职工的年龄、出生日期与性别，步骤如下：.（1）在单元格 C3 输入以下公式，用于计算年龄：=DATEDIF(DATE(MID(B3,7,4-(LEN(B3)=15)*2),MID(B3,11-(LEN(B3)=15)*2,2),MID(B3,13-(LEN(

3、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 函数默认状态下无法使用，需要加载“分析工具库”才可以正常使用，为了使公式通用，通常改用 MOD 函数。即公式改为：=IF(MOD(MID(B3,15,3),2),“男“,“ 女“)（4）选择 C3:E3 区域，将公式向下填充即完成身份证信息提

4、取。效果如下：图 1.2 公式法获取身份证信息点评：相对于手工输入法，利用公式从身份证号码获取个人信息有着效率更高、错误率更低之优点，人员越多时越能体现出其高效优势。本例文件参见光盘：. 第一章 提取身份证信息.xlsm1.1.2 自定义函数法自定义函数是指利用 VBA 编写的外置函数。在本例的随书光盘中已经录入了相关的 VBA 代码，可以随时调用。对于代码的含义和录入方式在后面的章节后有详细介绍，本章仅通过具体应用了解其用法与优势。具体操作步骤如下：（1）进入“自定义函数法”工作表；（2）在 C3:E3 区域分别输入以下三个公式，用于计算年龄、出生日期和性别：=SFZ(B3,“NL“)=SF

5、Z(B3,“SR“)=SFZ(B3)或者=SFZ(B3,“XB“)（3）选择 C3:E3 单元格，将公式向下填充，结果见图 1.3 所示。.图 1.3 自定义函数法获取身份证信息本例中的函数 SFZ 即身份证函数，用于从身份证号码中获取年龄、生日与性别等信息。它不属于 Excel 内置函数，需要利用 VBA 编写代码才可以使用。读者可以从随书光盘中获取该完整代码。SFZ 函数有两个参数，第一参数为单元格引用，第二参数为信息描述，即用于指定需要获取身份证中哪一部分信息。当它为“NL” （不区分大小写）时，获取年龄；当它为“SR”时，获取生日，当它为 “XB”或者省略第二参数时，获取性别。点评：相

6、对于内置函数法/公式法，自定义函数法是借用 VBA 编写的外置函数完成，它的优势在于公式简短，且容易理解。任何不熟悉函数与 VBA 者皆可一分钟内学会操作并理解其公式含义。1.1.3 插件法插件法是指借用 Excel 插件操作工作表，该插件不隶属于当前工作簿，但却可以实现与当前工作簿交互的功能，批量、迅速完成身份证信息提取工作。操作步骤如下：（1）关闭 Excel 程序的前提下，将随书光盘中的插件（位置：. 第一章批量获取身份证信息.xlam）复制到以下自启动文件夹中即安装完成：C:Program FilesMicrosoft OfficeOffice12XLSTART注意：如果您的 OFFI

7、CE 没有装在 C 盘，那么上面的磁盘号需要根据实际情况做修改；如果您使用 OFFICE 2003，则将其中“Office12 ”修改为“Office11 ”。（2）打开光盘文件“提取身份证信息.xlsm” ，进入“插件法”工作表；（3）选择单元格区域 B3:B6，单击右键，从右键中选择 【批量获取身份证信息】菜单，程序将弹出一个对话框“确定计算区域” 。该对话框中默认显示当前选区地址，如果需要修改地址，可以输入新的地址，也可以用鼠标在工作表中选择身份证存放区域，该区域的地址会自动产生在对话框中。见图 1.4 所示；（4）单击“确定”按钮，程序在瞬间就会从选区的所有身份证中提取年龄、生日和性别

8、等信息。.图 1.4 插件法批量获取身份证信息点评：插件法从身份证号码中获取信息的优点是速度快，通用性好。相对于内置函数法，它在操作上更简单，不需要任何函数知识，不需要输入长长的公式，只点几次鼠标即可；相对于自定义函数，它的优点是通用性好，在任何工作表、任何工作簿皆可使用本工具。而前一方法之自定义函数非插件方式存在，只能在当前工作簿中使用。1.1.4 浅谈 VBA 优势前面三个案例中我们可以看出，Excel 具有强大的计算功能，但常规方式对于某些大型数据运算显得比较繁琐。用户需要学习复杂的函数知识，设置长长的公式才可以解决某些运算。而 VBA 可以使公式简化、易懂，甚至根本不需要公式，一个字母

9、不用录入即可完成一些专业性较强的计算。具体说来，相对于 Excel 自带的功能，VBA 或者说 VBA 开发的插件具有以下优势： 批量地对操作对象进行数据处理以前一节插件法完成身份证信息进行例证，它可以瞬间完成多个单元格数据的运算，甚至多个工作表中存放的身份证号码也可瞬间完成信息提取。较传统的逐一处理方式在效率上有大幅提升。 多任务一键完成多任务是指对同一个对象需要进行多个操作，例如前一节是从身份证号码中获取三类信息，VBA 可以单击一个按钮后瞬间完成，完全感觉不到它在分三步逐一完成任务。这是高效办公地最佳体现。 将复杂的任务简化Excel 是很多很多小工具的综合体。这些工具可以嵌套运用，完成

10、更强大的数据处理。但当嵌套过多时，就需要用户要较深的功底才能操纵或者理解。另一方面，对于某些特殊行业的工作、任务，也要经过很复杂的操作才可以完成，而对于某些只需要应用不需要深入研究、理解的普通办公文员们来说是一个技能考验。而通过VBA 进行二次开发可以将复杂的任务变得更简单。简单是指理解和操作上同时简化。就像 1.1.3 节中通过右键菜单提取身份证号码三类信息一样，不需要用户去录入长长的公式，以及理解信息是如何提取出来的，单击菜单即可完成。再如企业中生.成工资条，10000 个人的资数用手工操作需要处理 10000*N 次，而利用 Excel 插件可以单击按钮完瞬间成。 将工作表数据提升安全性

11、利用 VBA 代码可以对数据进入多层保护，在某些特殊需求下，VBA 可以保护数据让普通用户无法胡乱修改，或者不小心破坏数据及数组结构。 提升数据准确性准确性体现在数据录入和数据运算两方面。首先，通地 VBA 对输入的数据进入限制，可以防止用户意外录入不规范字符。如数字中有两个小数点，或者录入数值时不小心录入了标点或者字母，造成无法计算或者漏算。其次，在数据运算时，人工设置大量公式，或者每天在不同地方重复录入同一个公式。在大量地操作中难以避免不产生一次错误。而利用 VBA 可以让工作简化，工作量越小，出错的机率一定越小；同时，在大量重复性工作中 VBA 可以确保不产生错误。 完成 Excel 本

12、身无法完成的任务弹出提示、警告对话框、行程安排与预告，或者到磁盘中查找需要的数据、修改注册表等等，Excel 常规方式是不可能完成的。如果需要类似功能，VBA 完全可以胜任。 开发专业程序利用 VBA 还可以开发一些专业型的程序，如报表汇总软件、进销存管理系统、人事管理系统等等，可以将界面设置成其它任何软件的显示方式媲美专业的程序软件1.2 插件特点及其如何发挥插件的优势在前一节中，通过一个身份证信息获取的插件认识了 Excel 插件，那么在工作中应如何发挥 Excel 插件的优势呢？1.2.1 Excel 插件的特点Excel 插件是利用 VBA 程序开发的外置工具，通常是 xla、xlam

13、 格式或者 dll 格式。其中 xla 和 xlam 插件直接用 Excel 就可以开发，而 dll 插件通常采用 VB 或者C+来编写。不管何种软件开发的插件，它都需要在外观和功能两方面具有某些特征，以方便用户调用。1.外观特征 有若干个菜单或者工具按钮在插件封装后，调用其代码有两种方式：用代码调用，用菜单或者工具栏按钮，显然菜单更方便。用户通过菜单单击即可完成相对于常规方式较复杂的操作或者运算。 利用窗体实现与工作表数据交互在弹出的窗体中可以调用工作表的数据，也可以将窗体中录入的数据导到工作表。而在窗体中录入数据时，相对于工作表中录入数据，可更好地控制。例如某个.文字框中可以指定只能录入数

14、字，而另一个文字框可以指定只能入日期。也可以设定录入某项目后自动跳转到指定目标位置，而不用手动去移动光标插入点。甚至可以在录入时核对是否与工作表中数据是否重复等等 有一个帮助界面对于开发者来说，不管自己开发的工具如何简单，都有必要向用户说明其功能和操作方式。所以在工具中通常加入一个窗体，进行文字说明或者动画演示。特别是工具没有提供菜单、而是通过函数调用或者快捷键调用时，更需要一个说明窗体。 对函数做参数说明对于函数类插件，必须对每个函数的参数进入详细说明，让用户插入函数时可以清晰明了地看到每个函数中每个参数的功能与使用方式。2.功能特征Excel 插件中的代码和普通宏程序的代码在编写上具有一些

15、差异，这是它们的设计目的不同造成的。其中宏代码通常用于解决某个具体的问题，它可能限用一次，也可能需要反复调用。但都只为解决自己的某个具体问题而录制。而开发 Excel 插件则通常是开发者开发后，给其他的终端用户使用。用户不确定，需要操作的区域对象不确定。所以插件有不同的需求，它需要具有以下特征： 没有具体的区域地址由于开发插件通常是给其他的终端用户使用，所以不能指定数据区域地址，而是提供一个自由选择目标区域的选择对话框，或者利用代码计算目标工作表中的待计算区域。这是和录制宏最大的差异。 不使用具体的工作表名或者工作簿名原理与前一条一致。 必须有通于菜单或者窗体供用户调用命令，而不是在工作表中建

16、立按钮来调用命令。dll 格式的插件不存在工作表，而 xla 和 xlam 格式插件的工作表是隐藏状态，工作表不可能在用户的界面呈现出来，所以必须建立一个通用的菜单栏，使其在打开任意工作簿都会显示出来供用户操作。如果使设置了快捷键，那么是可以不用菜单或工具栏的，界面将会更简洁。 尽可能提供自定义选项插件的针对性不强，即它需要有广泛性。插件通常不是为某一个固定用户开发，或者需要处理的数据并非永远一致，那么在不同用户使用同一功能时，需要有自定义其参数或者选项的空间，工具才能有更好的通用性。例如设计一个工资条制作插件，那么工资条的表头行数就有必要让用户选择，而非强制一行或者两行。这和编写一个解决临时

17、性问题的编程思路不同。 具有多版本适应能力目前办公用户使用的 Excel 版本差异很大，有 Excel 2000、Excel XP、Excel 2003，也有 Excel 2007。开发者不会假定用户都用某个版本的 Excel，而是通过代码判断当前用户的版本号，然后调用不同的代码，以适应当前版本，否则某些功能可以无法使用。 防错机制自用型宏程序通常不用防错，因为用户和开发者是同一人。而插件则必须有完善的防错机制，预先设置了遇到某种错误该如何反应的措施，避免破坏用户数据，.或者进入死循环，消耗尽计算机的内存资源。1.2.2 Excel 插件的优势与限制在工作中使用插件，可以使用工作更轻松，运算更

18、快速、准确。当然前提是插件的代码编写足够优秀，不仅具有很强的通用性，还要有完善的防错机制，以及灵活的自定义选项。那么工作中使用优秀的插件进行工作具有哪些优势呢？ 简化操作：类似于 bat 批处理文件，可以一键执行多个任务 强化功能：对 Excel 内置功能无法完成的一些任务，借用 VBA 代码可以实现 美化界面：VBA 用以调用 Flash 动画，也可以播放 Gif 动画，还可以直接对单元格字符产生滚动效果。对于喜欢装点的用户，借用 VBA 可对工作表进行很好地修饰 固化格式：VBA 可以对录入的数据进入检测，阻止输入不规范的数据；也可以禁止新增、删除工作表，或者禁止缩放窗口，从而促使多用户文

19、件能确保格式一致，便于汇总虽然插件在工作中有以上优势，但它在某些方面也具有一些限制： 通用性方面：开发插件通常是个人行为，而非 Office 软件一样由一个大公司主持。所以其通用性很可能不是很好，开发者测试的次数少以及测试条件不足等等，导致工具具有某些隐含的缺陷 防错方面：程序员不一定是终端用户，甚至可能从来没有成为办公用户，而是直接学习插件开发。那么在程序编写时就可能思维受限，无法对可能出现的所有错误进行防范 移植方面：插件属于外置工具，它的所有功能都需要安装才能使用。所以如果利用插件设计的表格有可能传用客户后无法正常开启，或者开启后无法正常显示。最好的解决方法是将插件让客户端也安装一次 独

20、立方面：Excel 的 VBA 是依附于 Excel 主体程序的附属程序，它可以开发强化 Excel 功能的程序，但不能开发脱离 Excel 而单独存在的软件。如果需要开发全新而专业的应用程序，VBA 并非理想的程序1.2.3 如何发挥插件的优势可以确定的是，善用插件可以提升工作效率。但是插件也不可滥用，否则享用优势的同时，也会产生一些后患。首先，需要明白插件相对于 Excel 的功能属于外置工具，它需要安装后才能使用。如果读者的文件非自用型，需要与他人共享、阅读，那么需要连插件一起共享；其次，如果是简单的功能，尽量使用内置功能，少用插件。插件适用于处理复杂的或者 Excel 内置功能无法完成

21、的工作；宏有一个通用 BUG，即使用宏代码后，内置撤消功能将禁用。为了让用户减少损失，针对某些会更新数据、修改（破坏）原有格式的工具，一定要提供一个恢复原状的程序。例如有制作工资的工具，就搭配一个删除工资条的工具。最后，尽量将插件在同部门共享。即一个办公室为单位或者一个企业为单位，让整个单位都拥有相同的插件，才能更好地发挥插件优势。.1.2.4 开发 Excel 插件的条件针对插件的开发者，他/她需要什么条件呢？现罗列如下： 熟练撑握 VBA 技术这是首要条件。必须对大部分常用对象及其属性熟练地掌握。且需要了解数据处理的常用方式，并从多种处理方式中找出最高效且通用的方式。如果在某些特殊情况下，

22、程序的通用性与执行效率只能选择其一时，通用性优先于执行速度。 具有一定的报表操作经验仅学习 VBA 是可以熟练掌握 VBA 知识的，但是仅掌握 VBA 知识却不可能成为优秀的程序员。例如开发财务人员用的插件，那么需要懂得一些财务知识，不需要精通，但一定要对财务知识有所了解或者有财务报表的制作经验，才可能开发出适合于财务人员的插件。 美化常识这里的美化并不一定是漂亮的外观，而是要使自己开发的程序界面具有协调性、统一性，还需要了解普通用户的操作习惯，根据习惯设计人性化或者操作更便利的界面。当然，在不影响效率的前提下，将窗体设计得更美观，也是具有现实意义的。 熟悉不同版本的 Excel 间的差异终端

23、用户们有可能使用多个版本的 Excel，那么开发者也需要了解不同版本间的差异。例如 Excel 2003 中 Application 有一个属性 FileSearch，用于在磁盘中查找文件，而 Excel 2007 取消了该属性，那么开发插件时就应尽量避免使用该属性，借用其它方法的代替。否则将产生兼容性问题，以致程序产生 BUG。 具有较强的耐心编写程序是一个与字母相处的过程。对于大中型程序，可能长时间对着一堆字母或者数字，这需要有一定的忍耐力。甚至在程序开发完成后，仍然需要耐心对程序进行多角度、多版本的测试，以提升程序的通用性和纠错性。1.2.5 本书架构本书除 VBA 基本理论外，偏重于讲

24、解插件开发的原理、思路与方法以及如何提升程序速度。在以后的章节中，主要从按以下方式进行编排：（1） VBA 历史与功能、安全性等等周边知识简要介绍（2） 认识 VBE 编辑器并对其它进行优化设置（3） 学习 VBA 中常用对象及属性、方法、事件（4） VBA 代码如何提升执行速度（5） 掌握 VBA 高级应用，包括窗体的认识，及磁盘、目录与文件操作（6） 开发 VBE 环境下的插件（7） 学习利用 VB 开发专业性的 COM 加载宏插件（8） 最后利用前面章节的知识开发一个大型 Excel 插件。从该插件的开发思路和过程让读者了解插件开发的常规流程及注意事项本书以插件开发为重心，但对于 VBA

25、 中常用知识，不一定与插件开发相关的知识但工作中较常用的功能也会进行详解，或者进行实例演示。除插件开发外，程序的提升和防错在本书的多次强调的重点。.从第二章开始，让读者学习、掌握 VBA 理论知识，为插件开发提供基础。第二章 VBA 概述VBA 是 VB（VISUAL BASIC ） 的一个子集，是一种附属于 Excel 的程序软件。在学习 VBA 的语法之前有必要对其发展史、功能、特点等等方面进行了解。.本章要点： VBA 的发展史与优缺点 VBA 能做什么 VBA 的安全性 使用 VBA 帮助2.1 VBA 的发展史与优缺点VBA 语言作 VB 家族成员，起步很早。发展至今已拥有非常广大的

26、用户群，在日常办公中有着举足轻重的作用。2.1.1 宏与 VBAExcel 早在 1985 年就首次在 Machintosh 上出现，1987 年 Excel 开始引进到Windows 环境中。当时 Lotus 1-2-3 是计算机历史上最成功的软件系统之一，但它仅支持一些极其简单的宏，而 Excel 软件从 Excel 4 开始，可以使用相对复杂的 xlm 宏，完成更复杂的工作，慢慢的将 Lotus 1-2-3 挤出电子表格行业，迅速占领了市场。当Excel 5 中正式推出 VBA(Visual Basic for Applications)作为通用的宏语言来为 Office应用程序编写代码

27、后，Excel 已完全征服了制表用户。可见宏语言在表格软件中影响之深远。宏的英文名为 Macro，是自动执行某种操作的命令集合。它包括两个过程，即Excel 4 或者称为 xlm 的宏语言和 Excel 5 中的 VBA 宏。Excel 4 的宏由宏表函数构成，由录入在宏表中的函数来控制程序的执行。至 1993 年发布的 Excel 5 中，微软开始推广 VBA 做为宏语言，并同时引进 VBA 编辑器，即 VBE（Visual Basic Edirtor） 。用户可以通过录制宏来产生代码，代码储存在 VBE 环境的代码模块中，利用 Alt+F8可以反复调用录制的宏。VBA 是目前 OFFICE

28、 系列通用的一种程序语言，它支持录制、执行、单步执行、调试等等操作，可以使用户从繁重的制表任务中解脱出来。VBA 是一种面向对象的程序语言，由一种所见即所得的方式编写代码，这使它在学习和使用方面都相比其它语言更简单。事实上，几乎所有 VBA 程序员都由录制宏开始学习 VBA，这是一个 VBA 速成的捷径。甚至 VBA 高手们仍然对录制宏乐此不倦，因为它可以完成VBA 程序的大部分代码，程序员仅需在录制的宏代码中稍加修改即可成为最后的合格程序；另一个最重要的因素是录制宏可以为程序员提供词典的作用，即忘记了某个对象单词，或者完全不明白某个属性的语法时，利用录制宏可以产生对应的代码，用户复制即可使用。2.1.2 VBA 历史与版本VBA 的前身是 xlm 宏语言，鉴于 xlm 宏功能有限，至今已经用 VBA 完全替代了 xlm 宏。但是为了体现兼容性，所有版本的 VBA 中皆可以调用以前的部分宏表函数。例如 Excel 2007 的 application 对象仍然保留了以下宏表相关的一个方法和两个属性，通过它们可以执行早期宏表所有函数：