Excel_VBA_从入门到精通必备

Excel VBA程序开发自学通2014-5-11 第1页/共508页

为入门篇：VBA优势、功能与概念

第一章从Excel插件认识VBA

简单的说，Excel VBA是依附于Excel程序的一种自动化语言，它可以使常用的程序自动化，类似于DOS（磁盘操作系统）中的批处理文件（后缀名“.bat”）。那么它有什么具体的功能？在工作中与常规操作方式相比，具有哪些优势？笔者试图通过一个简单却实用的插件来展现。

本章要点：

●从身份证号获取个人信息

●在工作中如何发挥Excel插件的优势

1.1 从身份证号获取个人信息

制作人事资料时，通常需要录入职员身份证号码，以及生日、年龄、性别等等。除身份证号码需要手工逐一录入以外，其它三项信息的录入有四种方法：手工录入、内置公式、自定义函数法、插件法。手工输入方式效率极差，且出错机率也最高，本节通过后三种方式来实现并比较，从而让读者对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(B3)=15)*2,2)),NOW(),"Y")

（2）在单元格D3输入以下公式，用于计算出生日期：

Excel VBA 程序开发自学通 2014-5-11 第 2页 /共 508页

=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区域，将公式向下填充即完成身份证信息提取。效果如下：

图1.2 公式法获取身份证信息

点评：相对于手工输入法，利用公式从身份证号码获取个人信息有着效率更高、错误率更低之优点，人员越多时越能体现出其高效优势。

本例文件参见光盘：..\ 第一章\提取身份证信息.xlsm

1.1.2 自定义函数法

自定义函数是指利用VBA 编写的外置函数。在本例的随书光盘中已经录入了相关的VBA 代码，可以随时调用。对于代码的含义和录入方式在后面的章节后有详细介绍，本章仅通过具体应用了解其用法与优势。具体操作步骤如下：

（1）进入“自定义函数法”工作表；

（2）在C3:E3区域分别输入以下三个公式，用于计算年龄、出生日期和性别： =SFZ(B3,"NL")

=SFZ(B3,"SR")

=SFZ(B3)或者=SFZ(B3,"XB")

（3）选择C3:E3单元格，将公式向下填充，结果见图1.3所示。

Excel VBA程序开发自学通2014-5-11 第3页/共508页

图1.3 自定义函数法获取身份证信息

本例中的函数SFZ即身份证函数，用于从身份证号码中获取年龄、生日与性别等信息。它不属于Excel内置函数，需要利用VBA编写代码才可以使用。读者可以从随书光盘中获取该完整代码。

SFZ函数有两个参数，第一参数为单元格引用，第二参数为信息描述，即用于指定需要获取身份证中哪一部分信息。当它为“NL”（不区分大小写）时，获取年龄；当它为“SR”时，获取生日，当它为“XB”或者省略第二参数时，获取性别。

点评：相对于内置函数法/公式法，自定义函数法是借用VBA编写的外置函数完成，它的优势在于公式简短，且容易理解。任何不熟悉函数与VBA者皆可一分钟内学会操作并理解其公式含义。

1.1.3 插件法

插件法是指借用Excel插件操作工作表，该插件不隶属于当前工作簿，但却可以实现与当前工作簿交互的功能，批量、迅速完成身份证信息提取工作。

操作步骤如下：

（1）关闭Excel程序的前提下，将随书光盘中的插件（位置：..\第一章\批量获取身份证信息.xlam）复制到以下自启动文件夹中即安装完成：

C:\Program Files\Microsoft Office\Office12\XLSTART

注意：如果您的OFFICE没有装在C盘，那么上面的磁盘号需要根据实际情况做修改；如果您使用OFFICE 2003，则将其中“Office12”修改为“Office11”。

（2）打开光盘文件“提取身份证信息.xlsm”，进入“插件法”工作表；

（3）选择单元格区域B3:B6，单击右键，从右键中选择【批量获取身份证信息】菜单，程序将弹出一个对话框“确定计算区域”。该对话框中默认显示当前选区地址，如果需要修改地址，可以输入新的地址，也可以用鼠标在工作表中选择身份证存放区域，该区域的地址会自动产生在对话框中。见图1.4所示；

（4）单击“确定”按钮，程序在瞬间就会从选区的所有身份证中提取年龄、生日和性别等信息。

Excel VBA程序开发自学通2014-5-11 第4页/共508页

图1.4 插件法批量获取身份证信息

点评：插件法从身份证号码中获取信息的优点是速度快，通用性好。相对于内置函数法，它在操作上更简单，不需要任何函数知识，不需要输入长长的公式，只点几次鼠标即可；相对于自定义函数，它的优点是通用性好，在任何工作表、任何工作簿皆可使用本工具。而前一方法之自定义函数非插件方式存在，只能在当前工作簿中使用。

1.1.4 浅谈VBA优势

前面三个案例中我们可以看出，Excel具有强大的计算功能，但常规方式对于某些大型数据运算显得比较繁琐。用户需要学习复杂的函数知识，设置长长的公式才可以解决某些运算。而VBA可以使公式简化、易懂，甚至根本不需要公式，一个字母不用录入即可完成一些专业性较强的计算。

具体说来，相对于Excel自带的功能，VBA或者说VBA开发的插件具有以下优势：

●批量地对操作对象进行数据处理

以前一节插件法完成身份证信息进行例证，它可以瞬间完成多个单元格数据的运算，甚至多个工作表中存放的身份证号码也可瞬间完成信息提取。较传统的逐一处理方式在效率上有大幅提升。

●多任务一键完成

多任务是指对同一个对象需要进行多个操作，例如前一节是从身份证号码中获取三类信息，VBA可以单击一个按钮后瞬间完成，完全感觉不到它在分三步逐一完成任务。这是高效办公地最佳体现。

●将复杂的任务简化

Excel是很多很多小工具的综合体。这些工具可以嵌套运用，完成更强大的数据处理。但当嵌套过多时，就需要用户要较深的功底才能操纵或者理解。另一方面，对于某些特殊行业的工作、任务，也要经过很复杂的操作才可以完成，而对于某些只需要应用不需要深入研究、理解的普通办公文员们来说是一个技能考验。而通过VBA 进行二次开发可以将复杂的任务变得更简单。简单是指理解和操作上同时简化。

就像1.1.3节中通过右键菜单提取身份证号码三类信息一样，不需要用户去录入长长的公式，以及理解信息是如何提取出来的，单击菜单即可完成。再如企业中生成工资条，10000个人的资数用手工操作需要处理10000*N次，而利用Excel插件可以

Excel VBA程序开发自学通2014-5-11 第5页/共508页

单击按钮完瞬间成。

●将工作表数据提升安全性

利用VBA代码可以对数据进入多层保护，在某些特殊需求下，VBA可以保护数据让普通用户无法胡乱修改，或者不小心破坏数据及数组结构。

●提升数据准确性

准确性体现在数据录入和数据运算两方面。首先，通地VBA对输入的数据进入限制，可以防止用户意外录入不规范字符。如数字中有两个小数点，或者录入数值时不小心录入了标点或者字母，造成无法计算或者漏算。其次，在数据运算时，人工设置大量公式，或者每天在不同地方重复录入同一个公式。在大量地操作中难以避免不产生一次错误。而利用VBA可以让工作简化，工作量越小，出错的机率一定越小；同时，在大量重复性工作中VBA可以确保不产生错误。

●完成Excel本身无法完成的任务

弹出提示、警告对话框、行程安排与预告，或者到磁盘中查找需要的数据、修改注册表等等，Excel常规方式是不可能完成的。如果需要类似功能，VBA完全可以胜任。

●开发专业程序

利用VBA还可以开发一些专业型的程序，如报表汇总软件、进销存管理系统、人事管理系统等等，可以将界面设置成其它任何软件的显示方式媲美专业的程序软件

1.2插件特点及其如何发挥插件的优势

在前一节中，通过一个身份证信息获取的插件认识了Excel插件，那么在工作中应如何发挥Excel插件的优势呢？

1.2.1 Excel插件的特点

Excel插件是利用VBA程序开发的外置工具，通常是xla、xlam格式或者dll格式。其中xla和xlam插件直接用Excel就可以开发，而dll插件通常采用VB或者C++来编写。

不管何种软件开发的插件，它都需要在外观和功能两方面具有某些特征，以方便用户调用。

1.外观特征

●有若干个菜单或者工具按钮

在插件封装后，调用其代码有两种方式：用代码调用，用菜单或者工具栏按钮，显然菜单更方便。用户通过菜单单击即可完成相对于常规方式较复杂的操作或者运算。

●利用窗体实现与工作表数据交互

在弹出的窗体中可以调用工作表的数据，也可以将窗体中录入的数据导到工作表。而在窗体中录入数据时，相对于工作表中录入数据，可更好地控制。例如某个文字框中可以指定只能录入数字，而另一个文字框可以指定只能入日期。也可以设定录入某项目后自动跳转到指定目标位置，而不用手动去移动光标插入点。甚至可以在录

Excel VBA程序开发自学通2014-5-11 第6页/共508页

入时核对是否与工作表中数据是否重复等等……

●有一个帮助界面

对于开发者来说，不管自己开发的工具如何简单，都有必要向用户说明其功能和操作方式。所以在工具中通常加入一个窗体，进行文字说明或者动画演示。特别是工具没有提供菜单、而是通过函数调用或者快捷键调用时，更需要一个说明窗体。

●对函数做参数说明

对于函数类插件，必须对每个函数的参数进入详细说明，让用户插入函数时可以清晰明了地看到每个函数中每个参数的功能与使用方式。

2.功能特征

Excel插件中的代码和普通宏程序的代码在编写上具有一些差异，这是它们的设计目的不同造成的。其中宏代码通常用于解决某个具体的问题，它可能限用一次，也可能需要反复调用。但都只为解决自己的某个具体问题而录制。而开发Excel插件则通常是开发者开发后，给其他的终端用户使用。用户不确定，需要操作的区域对象不确定。所以插件有不同的需求，它需要具有以下特征：

●没有具体的区域地址

由于开发插件通常是给其他的终端用户使用，所以不能指定数据区域地址，而是提供一个自由选择目标区域的选择对话框，或者利用代码计算目标工作表中的待计算区域。这是和录制宏最大的差异。

●不使用具体的工作表名或者工作簿名

原理与前一条一致。

●必须有通于菜单或者窗体供用户调用命令，而不是在工作表中建立按钮来调

用命令。

dll格式的插件不存在工作表，而xla和xlam格式插件的工作表是隐藏状态，工作表不可能在用户的界面呈现出来，所以必须建立一个通用的菜单栏，使其在打开任意工作簿都会显示出来供用户操作。如果使设置了快捷键，那么是可以不用菜单或工具栏的，界面将会更简洁。

●尽可能提供自定义选项

插件的针对性不强，即它需要有广泛性。插件通常不是为某一个固定用户开发，或者需要处理的数据并非永远一致，那么在不同用户使用同一功能时，需要有自定义其参数或者选项的空间，工具才能有更好的通用性。例如设计一个工资条制作插件，那么工资条的表头行数就有必要让用户选择，而非强制一行或者两行。这和编写一个解决临时性问题的编程思路不同。

●具有多版本适应能力

目前办公用户使用的Excel版本差异很大，有Excel 2000、Excel XP、Excel 2003，也有Excel 2007。开发者不会假定用户都用某个版本的Excel，而是通过代码判断当前用户的版本号，然后调用不同的代码，以适应当前版本，否则某些功能可以无法使用。

●防错机制

自用型宏程序通常不用防错，因为用户和开发者是同一人。而插件则必须有完善的防错机制，预先设置了遇到某种错误该如何反应的措施，避免破坏用户数据，或者进入死循环，消耗尽计算机的内存资源。

Excel VBA程序开发自学通2014-5-11 第7页/共508页

1.2.2 Excel插件的优势与限制

在工作中使用插件，可以使用工作更轻松，运算更快速、准确。当然前提是插件的代码编写足够优秀，不仅具有很强的通用性，还要有完善的防错机制，以及灵活的自定义选项。那么工作中使用优秀的插件进行工作具有哪些优势呢？

●简化操作：类似于bat批处理文件，可以一键执行多个任务

●强化功能：对Excel内置功能无法完成的一些任务，借用VBA代码可以实现

●美化界面：VBA用以调用Flash动画，也可以播放Gif动画，还可以直接对

单元格字符产生滚动效果。对于喜欢装点的用户，借用VBA可对工作表进行

很好地修饰

●固化格式：VBA可以对录入的数据进入检测，阻止输入不规范的数据；也可

以禁止新增、删除工作表，或者禁止缩放窗口，从而促使多用户文件能确保

格式一致，便于汇总

虽然插件在工作中有以上优势，但它在某些方面也具有一些限制：

●通用性方面：开发插件通常是个人行为，而非Office软件一样由一个大公司

主持。所以其通用性很可能不是很好，开发者测试的次数少以及测试条件不

足等等，导致工具具有某些隐含的缺陷

●防错方面：程序员不一定是终端用户，甚至可能从来没有成为办公用户，而

是直接学习插件开发。那么在程序编写时就可能思维受限，无法对可能出现

的所有错误进行防范

●移植方面：插件属于外置工具，它的所有功能都需要安装才能使用。所以如

果利用插件设计的表格有可能传用客户后无法正常开启，或者开启后无法正

常显示。最好的解决方法是将插件让客户端也安装一次

●独立方面：Excel的VBA是依附于Excel主体程序的附属程序，它可以开发

强化Excel功能的程序，但不能开发脱离Excel而单独存在的软件。如果需要

开发全新而专业的应用程序，VBA并非理想的程序

1.2.3 如何収挥插件的优势

可以确定的是，善用插件可以提升工作效率。但是插件也不可滥用，否则享用优势的同时，也会产生一些后患。

首先，需要明白插件相对于Excel的功能属于外置工具，它需要安装后才能使用。如果读者的文件非自用型，需要与他人共享、阅读，那么需要连插件一起共享；

其次，如果是简单的功能，尽量使用内置功能，少用插件。插件适用于处理复杂的或者Excel内置功能无法完成的工作；

宏有一个通用BUG，即使用宏代码后，内置撤消功能将禁用。为了让用户减少损失，针对某些会更新数据、修改（破坏）原有格式的工具，一定要提供一个恢复原状的程序。例如有制作工资的工具，就搭配一个删除工资条的工具。

最后，尽量将插件在同部门共享。即一个办公室为单位或者一个企业为单位，让整个单位都拥有相同的插件，才能更好地发挥插件优势。

Excel VBA程序开发自学通2014-5-11 第8页/共508页

1.2.4 开収Excel插件的条件

针对插件的开发者，他/她需要什么条件呢？现罗列如下：

●熟练撑握VBA技术

这是首要条件。必须对大部分常用对象及其属性熟练地掌握。且需要了解数据处理的常用方式，并从多种处理方式中找出最高效且通用的方式。如果在某些特殊情况下，程序的通用性与执行效率只能选择其一时，通用性优先于执行速度。

●具有一定的报表操作经验

仅学习VBA是可以熟练掌握VBA知识的，但是仅掌握VBA知识却不可能成为优秀的程序员。例如开发财务人员用的插件，那么需要懂得一些财务知识，不需要精通，但一定要对财务知识有所了解或者有财务报表的制作经验，才可能开发出适合于财务人员的插件。

●美化常识

这里的美化并不一定是漂亮的外观，而是要使自己开发的程序界面具有协调性、统一性，还需要了解普通用户的操作习惯，根据习惯设计人性化或者操作更便利的界面。当然，在不影响效率的前提下，将窗体设计得更美观，也是具有现实意义的。

●熟悉不同版本的Excel间的差异

终端用户们有可能使用多个版本的Excel，那么开发者也需要了解不同版本间的差异。例如Excel 2003中Application有一个属性FileSearch，用于在磁盘中查找文件，而Excel 2007取消了该属性，那么开发插件时就应尽量避免使用该属性，借用其它方法的代替。否则将产生兼容性问题，以致程序产生BUG。

●具有较强的耐心

编写程序是一个与字母相处的过程。对于大中型程序，可能长时间对着一堆字母或者数字，这需要有一定的忍耐力。甚至在程序开发完成后，仍然需要耐心对程序进行多角度、多版本的测试，以提升程序的通用性和纠错性。

1.2.5 本书架构

本书除VBA基本理论外，偏重于讲解插件开发的原理、思路与方法以及如何提升程序速度。在以后的章节中，主要从按以下方式进行编排：

（1）VBA历史与功能、安全性等等周边知识简要介绍

（2）认识VBE编辑器并对其它进行优化设置

（3）学习VBA中常用对象及属性、方法、事件

（4）VBA代码如何提升执行速度

（5）掌握VBA高级应用，包括窗体的认识，及磁盘、目录与文件操作

（6）开发VBE环境下的插件

（7）学习利用VB开发专业性的COM加载宏插件

（8）最后利用前面章节的知识开发一个大型Excel插件。从该插件的开发思路和过程让读者了解插件开发的常规流程及注意事项

本书以插件开发为重心，但对于VBA中常用知识，不一定与插件开发相关的知识但工作中较常用的功能也会进行详解，或者进行实例演示。

除插件开发外，程序的提升和防错在本书的多次强调的重点。

从第二章开始，让读者学习、掌握VBA理论知识，为插件开发提供基础。

Excel VBA程序开发自学通2014-5-11 第9页/共508页

第二章VBA概述

VBA是VB（VISUAL BASIC）的一个子集，是一种附属于Excel的程序软件。在学习VBA的语法之前有必要对其发展史、功能、特点等等方面进行了解。

本章要点：

Excel VBA程序开发自学通2014-5-11 第10页/共508页

●VBA的发展史与优缺点

●VBA能做什么

●VBA的安全性

●使用VBA帮助

2.1VBA的发展史与优缺点

VBA语言作VB家族成员，起步很早。发展至今已拥有非常广大的用户群，在日常办公中有着举足轻重的作用。

2.1.1 宏与VBA

Excel早在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应用程序编写代码后，Excel已完全征服了制表用户。可见宏语言在表格软件中影响之深远。

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

VBA是目前OFFICE系列通用的一种程序语言，它支持录制、执行、单步执行、调试等等操作，可以使用户从繁重的制表任务中解脱出来。VBA是一种面向对象的程序语言，由一种所见即所得的方式编写代码，这使它在学习和使用方面都相比其它语言更简单。事实上，几乎所有VBA程序员都由录制宏开始学习VBA，这是一个VBA速成的捷径。甚至VBA高手们仍然对录制宏乐此不倦，因为它可以完成VBA 程序的大部分代码，程序员仅需在录制的宏代码中稍加修改即可成为最后的合格程序；另一个最重要的因素是录制宏可以为程序员提供词典的作用，即忘记了某个对象单词，或者完全不明白某个属性的语法时，利用录制宏可以产生对应的代码，用户复制即可使用。

2.1.2 VBA历史与版本

VBA的前身是xlm宏语言，鉴于xlm宏功能有限，至今已经用VBA完全替代了xlm宏。但是为了体现兼容性，所有版本的VBA中皆可以调用以前的部分宏表函数。例如Excel 2007的application对象仍然保留了以下宏表相关的一个方法和两个属性，通过它们可以执行早期宏表所有函数：

●Application.ExecuteExcel4Macro

●Application.Excel4MacroSheets

Excel VBA程序开发自学通2014-5-11 第11页/共508页

●Application.Excel4IntlMacroSheets

在抛弃早期宏语语言后，VBA从1993年开始逐步在很多软件中出现，除OFFICE 办公软件外，Cad、Coreldraw等等软件也支持VBA。目前VBA的最高版本是6.05。但需要申明的是，VBA版本并非与与主体程序的版本对应升级，即Excel的多个版本有可能使用同一版本的VBA。如OFFICE 2003和OFFICE 2007都使用6.05版的VBA。

检测当前OFFICE中VBA版本可以使用以下代码：

Sub 获取VBA版本号()

MsgBox Application.VBE.Version

End Sub

不同版本的VBA带有不同的函数，编程时需要根据VBA的版本调整体码，使之尽量通用。但在Excel中编写VBA程序时，Excel的版本号显得更为重要。因为不同的Excel版本有不同的对象和方法，而且差异较大。在本书的附录中有Excel 2007与早期版在VBA方面的差异，做为插件开发者有必要进行全面了解。

2.1.3 VBA优、缺点

VBA做为OFFICE办公套件的二次开发语言，它是一个很优秀的程序语言，从国内外OFFICE论坛中VBA相关的发贴量可以知道VBA用户群有多大，这也反证了VBA在工作中应用之广泛性。

总体来说，VBA语言具有以下优点：

●可以录制

早期的磁盘操作系统DOS不支持录制，虽然它是一门很简单的语言，但要让大多数用户学好DOS仍然是一件难事。它的每个命令，每个字母都面要手工录入，所有命令都需在大脑记忆。而VBA采用录制方式可以产生完整的代码，程序稍加优化即可取得最佳程序，摆脱死记代码的困扰。

●所见即所得

Excel VBA有窗体及工作簿、工作表等等对象，可以直接拖动产生对象，不需要编写创建对象的代码。而且可以调整为一边操作工作表数据或者图形对象，一边查看代码变化，即录制宏时同时查看工作簿窗口和代码窗口。

●调用现成对象

VB或者C++开发程序时需要自己设计窗体、对象，而Excel中有现成的工作簿对象、工作表对象、窗口对象、图形对象等等，开发者仅需对这些对象或者数据进行操作即可，不需要开发一个报表程序及各对数据存放介质。这也是VBA简单易学的原因之一。

●应用广泛

目前Excel、Word、Access、PowerPoint、FrontPage、Visio、Project、Outlook、AutoCAD、CorelDraw等等程序都支持VBA。而各程序间的代码可以相互移植，然后对代码中的引用对象稍加修改即可。

●交流方便

这里说了交流是指VBA用户与用户之间的交流。国内、国外都有很多大型的VBA 相关论坛，可以通过论坛交流心得、学习他人的编程思路，以及在线提问。OFFICE VBA 方面的论坛远比C++与.net等等语言的相关论坛更多。

相对于Excel内置功能，VBA也有它自己的缺点：

●学习周期长

Excel VBA程序开发自学通2014-5-11 第12页/共508页

学习VBA的时间至少两个月，而数据透视表、函数、图表等等其它内置功能则相对更快。

●专业词汇多

VBA中有几百个对象，每个对象有多个属性以及方法，虽然不需要死记硬背所有对象名称和属性，但仍然需要花很多精力来理解、消化。

●普及范围小

目前VBA用户群在一天天扩大，但相对于Excel的内置功能如公式、图表等等，仍然有待进一步提升普及率。在普及不够的情况下，程序员的插件需要做更完善的帮助系统，也需要更多的时间来测试，使未接触VBA的用户能更快地掌握其技巧。

2.2VBA能做什么

VBA是一门程序语言，工作中VBA的常见用途是什么？本节进行讨论。

2.2.1 VBA用途

可以肯定地说，VBA可以完成Excel常规功能可以完成的任何功能。但是事实上不可能有人用VBA来处理所有任务，而是有选择性、有针对性地使用VBA。

概括地说，VBA主要用在以下几方面：

●处理大型运算

Excel内置的函数嵌套也可以完成很多大型的数据运算，然而很多易失性函数会造成Excel程序启动缓慢，特别是数组公式。用VBA来处理数据运算则可以解决这个问题。

●工作簿/工作表折分/合并

对于工作簿/工作表按条件拆分成多个工作表之任务，手工完成效率极度的低，VBA则可以轻松完成，单击鼠标即可。也有部分企业需要每月汇总下属分公司的报表，多报表的汇总人工操作显然是事倍功半，而VBA插件则可一劳永逸。

●处理重复性任务

针对某些每天都需要进行且完全复重不变化的任务，利用VBA仅需要第一次手工操作、编写代码，后续的所有任务都全自动执行。它的优势在仅在于速度快，还更准确。人工操作的步骤越多，出错的机率一定相应的越大。

●简化内置公式

以第一章的公式为例，以下两个公式都可以从身份证号码获取年龄：

=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可以将Excel界面定制成更具有个性化的程序。类似于QQ换肤、播放器换肤等等，Excel 也可以通过VBA使用程序标题、状态栏、菜单个性化，例如产生滚动字幕，例如将

Excel VBA程序开发自学通2014-5-11 第13页/共508页

个人照片、邮箱地址加到菜单栏等等。

图2.1 定制个性界面

开发受保护的专业程序

网络上有很多Excel版的人事系统、学校成绩管理系统、考试座次安排系统等等，利用VBA可以编写很专业的程序，且能对其进行保护，以确保开发者的利益。即使是纯公式设计的报表，有时也需要借用VBA来设计程序注册功能或者登录界面等等。

2.2.2 VBA主要用户

根据VBA的特点，使用VBA主要有两类对象。

其中最主要的是开发者就是终端用户，即编写代码给自己用的的业余程序员。而且VBA也是所有程序中拥业余程序员最多的一类程序。利用VBA解决一个临时问题，或者处理某一个具体的重复性任务，是大家使用VBA最多的原因。只有少数用户不为解决当前问题而基于兴趣编写一些通用型插件，可以解决很多很多类似问题。

注意：普通宏程序和插件最主要的一个区别是：编写普通宏程序只为针对当前遇到的一个具体问题而编写，当前问题解决后，该程序也不再有存在的价值；而编写插件则通过针对具有大从性质的问题，例如工资条设计，很多企业、事业单位都需要，那么它的生存周期是很长的，用户也是不固定的，在编写时也就会产生更多的自定义选项留给终端用户。所以编写插件和自编自用型宏程序在困难度上有较大区别

VBA用户通常用于两个基本条件：其一为工作中某些任务利用常规方式处理太繁琐，需要VBA来简化工作；其二为因VBA兴而趣研究。笔者一直坚持的一种观点是：对VBA保持持续的兴趣是学好VBA很重要的条件。

另外一类VBA用户即为专业的VBA程序员，专为别人定制程序。这类用户除了需要熟悉VBA语法外，还需要对Excel各项操作有较多的经验。也有部分人员本身就是某个行业的资深主管、兼职程序员。例如一个精通VBA的财务主管，他/她开发的财务类VBA程序一定比只精通VBA不懂财务的专业程序员开发的程序更专业，或者说更具有易用性。

Excel VBA程序开发自学通2014-5-11 第14页/共508页

2.3VBA的安全性

上世纪90年代宏病毒泛滥，使人们对宏以及宏的安全性都有所了解。但事实上很多用户也就通过宏毒病的传播了解宏的部分特点，这自然是很片面的。

2.3.1 VBA安全性

事物都有双面性，程序语言犹其如此。程序的功能越强，那么同时意味着用它做破坏也可以具有更大的破坏力。VBA依附于Excel程序，但它做为病毒传播时，可以破坏的对象却不局限于Excel程序，磁盘中所有文件皆可以任意修改。正因如此，学习VBA时，有必要掌握好这把双刃剑。

在默认状态下，Excel 2003和2007都是禁用宏的，以确保用户数据不会因潜在的宏病毒而受破坏。但是同时也带来另一个问题——正常的VBA程序无法执行。所以通常有三种做法：

不用VBA程序的用户，彻底禁用宏，杜绝宏病毒蔓延。

常用VBA程序，包括自己开发和别人开发程序的用户，可以将宏的安全性稍加提高，即遇到宏时提示用户，当用户确定代码安全时再执行。

第三类自编自用型用户或者完全信任宏代码开发者的用户，则可以将宏的默认设置修改为无限制。即允许任何宏执行，从而提升工作的效率。笔者属于第三类，永远允许所有宏自动执行。

2.3.2 了解安全性对话框

Excel 2007有两个安全性对话框。一个是打开带有宏代码时提示用户的“安全选项”对话框，见图2.2所示；另一个是位于Excel选项中的“信任中心”对话框，见图2.3所示。

1.安全选项

当启动一个具有宏代码的工作簿或者启动一个COM加载宏时，默认状态下在Excel编辑栏上方会弹出一个“安全警告部分活动内容已被禁用”的提示框（根据加载宏的类型不同，显示的文字也会有所差异）。当用户单击“选项”按钮后再次弹出一个“安全选项”窗口，在窗口中将罗列出所有被阻止的加载项。本例中有两个，包括一个xla格式的加载宏，和一个dll格式的COM加载项。

如果确认需要在工作簿使用某个加载项，或者信任该加载项中的代码，则选择“启用此内容”，然后单击“确定”按钮，安全警告对话框消失，而相关加载项所携带的代码也可以任意调用了。

Excel VBA程序开发自学通2014-5-11 第15页/共508页

图2.2 安全选项

图2.3 Excel信任中心

2.信任中心

前面所说的每次在开启工作簿时启用自己信任的插件或者宏工作簿，虽然确保了安全，事实上效率不高，操作上较繁琐。而设置信任中心可以一劳永逸的解决上述问

Excel VBA程序开发自学通2014-5-11 第16页/共508页

题。

进入信任中心步骤如下：

（1）单击左上角的圆形OFFICE按钮；

（2）选择“Excel选项”按钮打开Excel选项对话框；

（3）单击左边的“信任中心”按钮即可显示“信任中心”对话框。见图2.3所示。

Excel选项的信任中心主要用于管理宏的安全性问题，它包括“受信任的发布者”、“受信任位置”、“加载项”、“ActiveX设置”、“宏设置”、“消息栏”、“外部内容”和“个人信息选项”8个选项，分别具有以下作用：

受信任的发布者：罗列出本系统中所有数字签名证书。证书是文档中电子的、基于加密的安全验证戳。此签名确认该宏或文档来自签发者且没有被篡改，表明您认为该数据库是安全的并且其内容是可信的。这可以帮助数据库的用户确定是否信任该数据库及其内容。

受信任位置：表示该位置下存放的代码是受信任的，不受宏的安全性设置的影响，任何情况下都会执行其代码。在本对话框中罗列了Excel默认设置下的几个位置。用户也可以手工添加或者添新的受信任位置。通常可以将自己编写的代码所存放的目录设置为受信任位置，以避免每次手工启用宏。

加载项：添加或者删除加载项，包括xla、xalm、dll和xll格式的加载项。在本对话框中可以手工安装以及删除所有格式的插件。而笔者的习惯是xla格式或者xlam 格式的插件直接存到Excel自启动文件夹中，免除安装。对于COM加载项，即dll 格式的插件则可以使用本对话框中手工安装。获取自动启动的路径可以使用以下代码：

Sub 自启动路径()

MsgBox Application.StartupPath

End Sub

以上代码获取的是用户级自启动路径，即只对当前用户发生作用。如果需在任何用户打开Excel都可以启动插件中的宏，那么用可以将用以下路径：C:\Program Files\Microsoft Office\Office12\XLSTART

其中Office12表示Office 2007的路径，如果用户安装的是Office 2003，则修改为Office11；另外“C:”也需要根据皮实际情况修改，例如Office安装在D盘则用“D:”。

ActiveX设置：控制ActiveX控件的启动方式，可以让禁用ActiveX控件且不发出通知，达到时最高的安全性，也可以禁后通用户选择，还可默认允许执行，以提高效率。在此处，效率和安全性是相冲突的。

宏设置：宏设置类似于ActiveX设置，它是对携带宏代码的工作簿进行安全限制。同样包括与ActiveX设置相似的选项。不过最下边的“信任对VBA工程对象模型的访问”则不是控制宏的运行，而是用于控制代码对VBE环境的操作。默认状态下是禁用代码操作VBE环境中任何组件的，打勾后才允许读取或者修改VBE的任何组件，例如在VBE窗口中新建一个菜单，或者删除VBA代码模块。

消息栏：控制Excel是否弹出消息栏，即阻止宏运行时是否通知用户。默认状态是要发出通知。

外部内容：所谓外部内容指工作表中的公式引用发其它工作簿，包括加载宏中的数据。本选项决定是否禁止引用以及是否弹出提示。在确保数据安全的前提下尽量选择数据链接和自动更新，以提升效率。

个人信息选项：本选项包括几个与网络相关的信息，在网络不可用的情况下设置无效。而网络畅通的情况下数据引用也比较慢，建议不再启用所有选项。

Excel VBA程序开发自学通2014-5-11 第17页/共508页

2.3.3 让自己的VBA程序畅通无阻

根据前面的描述，Excel默认状态下是禁止宏代码和ActiveX控件运行的。而对于自己编写的完全可以信任的代码也产生了阻碍作用，这促使用户需要对其进行设置，让代码默认即可执行。

让自己的VBA程序畅通无阻有两种方式，签署数字证书和设置受信任位置。从操作程序比较，设置受信任位置是最快速、最方便的办法。下面以安装“D:\工具箱\批量获取身份证信息.xla”为例演示如何让程序自启动而不被Excel阻止。

（1）在D盘新建文件夹，命名为“工具箱”；

（2）将“批量获取身份证信息.xlam”插件复制到“工具箱”文件夹中；

（3）单击【Office按钮】\【Excel选项】\【信任中心设置】\【受信任位置】\【添加新位置】打开“Macrosoft Office受信任位置”对话框，该对话框中默认显示Office 临时文件夹路径；

（4）在“Macrosoft Office受信任位置”对话框的“路径”文字框中输入“D:\工具箱\”，并勾选“同时信任此位置的子文件夹”，在“说明”文字框中输入说“我的信任位置”，见图2.4所示：

图2.4 设置受信任位置对话框

（5）单击“确定”按钮后返回“受信任位置”，在列表中将会显示新增加的地址“D:\工具箱”；

（6）单击“确定”按钮后返回“Excel选项”对话框，单击左边的“加载项”按钮，再在底部的“管理”下拉列表中选择“Excel加载项”，单击“转到”按钮打开“加载宏对话框”。该对话框中罗列了所有已安装的内置加载宏和外置加载宏，见图 2.5所示；

（7）单击“浏览”按钮，从对话框中选择“D:\工具箱”中的“批量获取身份证信息.xla”，然后返回加载宏对话框，可以发现插件列表中已经新增了一项：“批量获取身份证信息”，见图2.6所示：

Excel VBA程序开发自学通2014-5-11 第18页/共508页

图2.5 加载宏对话框图2.6 加载xla格式的插件（8）经过以上设置后，重启Excel，可以看到插件已经可以正常运行，而不会弹出任何阻止宏执行的对话框。

注意：将插件直接放到自启动路径中也可以实现不弹出阻止宏运行的对话框，且默认允许所有代码运行。但是它的缺点是不在“加载宏”列表中出现，不便于随时卸载与安装。而利用“加载宏”对话框安装的工具可以在方框中打勾或者不打勾来表示安装与卸载。

2.4 使用VBA帮助

Excel的VBA具有详尽的帮助供用户学习、参考。通过帮助可以使用用户学得更快，对对象、属性、方法等也掌握更准。

2.4.1 利用帮助学习VBA语法

VBA有三种帮助：即时提示、本地帮助和在线帮助。

1.即时提示

即时提示是指用户录入代码时，VBA弹出的选项，也称快速信息。它可以对属性、对象、方法和参数等等进行提示，使用户可以快速而准确地录入代码。例如需要输入获取单元格地址的代码，但又无法确定记忆中的单词是否正确，就可以借用提示来完成。从快速信息录入代码不仅快速，而且绝对正确。具体步骤如下：（1）录入代码“Range ("A1")”；

（2）继续录入一个半角状态下的小数点，程序弹出即时信息——下拉列表，其中“Address”即表示区域的地址，见图2.7所示；

（3）按下键盘上的下箭头，移动到“Address”处再按下Tab键，完整的单词即自动追加到代码中。如果手工录入，可能会录入为诸如“Adress”或者“Addrees”等等错误代码。

如果在代码中录入函数，以及左括号时，仍然会产生即时帮助信息提示。不过不是下拉列表，而是参数所有参数的提示，及每个参数的类型，见图2.8所示。

Excel VBA程序开发自学通2014-5-11 第19页/共508页

图2.7 即时帮助之属性示图2.8即时帮助之参数提示

2.本地帮助

本地帮助是指安装Office时安装的帮助文件，存放在本地磁盘中。

调用本地帮助的步骤如下：

（1）打开Excel 2007，利用快捷键【Alt+F11】打开VBA编辑器；

（2）按下快捷键【F1】打开VBA帮助窗口。窗口的标题是Excel帮助，和在工作表中按下【F1】调出的帮助窗口标题一致，然而其功能却大大不同。

（3）在帮助窗口右下角可以看到“脱机”二字，表示当前调用的帮助信息是本地帮助。在左上角的的文字框中输入待了解的信息“names”，并单击回车或者左键单击“搜索”按钮，立即出现关于“names”的所有帮助信息，包括“names”的对象、方法、属性和实例。从窗口中可以看出关于“names”的帮助有30条，本页仅显示1到25条；

图2.9调用names的本地帮助

（4）单击其中第三项“5840cf41bb68a98270fefa0ds”，窗口中立即出现工作簿级名称集合的相关帮助信息，包括语法、说明和示例，见图2.10所示；

（5）如果需查看关于“names ”的其它帮助，则单击工栏具的图标返回，再从

Excel VBA程序开发自学通2014-5-11 第20页/共508页

列表中选择目标帮助信息。也可单击“下一页”按钮查看其它帮助。

图5840cf41bb68a98270fefa0ds的帮助

3.在线帮助

在线帮助是指来自网络的在线信息。单击帮助窗口右下角的“脱机”，将弹出“连接状态”提示框，见图2.11所示。从中选择“显示来自office online的内容”即可调用在线帮助。

在图2.12中显示了关于“names”的在线帮助。

Excel VBA程序开发自学通2014-5-11 第21页/共508页

图2.11 调整为显示在线帮助图2.12显示关于names的在线帮助当然，除了上述三种帮助，可以有到国内外的Office论坛获取专家们的在线指导。例如Excelhome论坛（5840cf41bb68a98270fefa0d）、Officefans论坛（5840cf41bb68a98270fefa0d/cdb/index.php）或者太平洋软件论坛Office专区（5840cf41bb68a98270fefa0d/f251）等等。

2.4.2 捕捉错误

VBA对于代码出错时具有良好的捕捉功能，可以提示用户代码有误，甚至给出错误类型、突出标示错误语句，对于部分错误还会提示或者定位于出错的代码上，使用户可以及时更正。

错误捕捉体现在编译错误和运行时错误两方面：

1.编译错误

编译错误指在VBA内部无法编译代码时产生的错误，大部分时候在编写代码时就能出现编译错误的提示，而不需要等到执行代码。

产生编译错误时，VBA会进行提示，让用户对有误的代码做修正。现例举四个编译错误，其中红色代码表示出错的语句。

（1）按下快捷键【Alt+F11】进入VBE窗口，单击菜单【插入】\【模块】，然后在模块代码窗口中输入以下代码“Sub 11（）”，当敲下回车键后，VBA会弹出一个错误提示，见图2.13所示。该错误是使用了不正确的标示符做为宏名称引起的。宏名称不以数字开头。

（2）在模块代码窗口中输入代码“Sub 我的宏()”，然后回车，程序会自动将Sub 程序之外壳补充完整，成为：

Sub 我的宏()

End Sub

然后在中间录入以下语句：

本文来源：https://www.bwwdw.com/article/acyq.html