Excel_VBA.zip_Excel VBA_excel_trunk5wf

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

第一章 从 Excel 插件认识 VBA

简单的说，Excel VBA 是依附于 Excel 程序的一种自动化语言，它可以使常用的

程序自动化，类似于 DOS（磁盘操作系统）中的批处理文件（后缀名“.bat”）。那么

它有什么具体的功能？在工作中与常规操作方式相比，具有哪些优势？笔者试图通过

一个简单却实用的插件来展现。

本章要点：

节通过后三种方式来实现并比较，从而让读者对 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 输入以下公式，用于计算出生日期：

（4）选择 C3:E3 区域，将公式向下填充即完成身份证信息提取。效果如下：

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

点评：相对于手工输入法，利用公式从身份证号码获取个人信息有着效率更高、

错误率更低之优点，人员越多时越能体现出其高效优势。

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

1.1.2 自定义函数法

自定义函数是指利用 VBA 编写的外置函数。在本例的随书光盘中已经录入了相

关的 VBA 代码，可以随时调用。对于代码的含义和录入方式在后面的章节后有详细

介绍，本章仅通过具体应用了解其用法与优势。具体操作步骤如下：

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

本例中的函数 SFZ 即身份证函数，用于从身份证号码中获取年龄、生日与性别等

信息。它不属于 Excel 内置函数，需要利用 VBA 编写代码才可以使用。读者可以从

随书光盘中获取该完整代码。

SFZ 函数有两个参数，第一参数为单元格引用，第二参数为信息描述，即用于指

定需要获取身份证中哪一部分信息。当它为“NL”（不区分大小写）时，获取年龄；

当它为“SR”时，获取生日，当它为“XB”或者省略第二参数时，获取性别。

点评：相对于内置函数法/公式法，自定义函数法是借用 VBA 编写的外置函数完

成，它的优势在于公式简短，且容易理解。任何不熟悉函数与 VBA 者皆可一分钟内

学会操作并理解其公式含义。

1.1.3 插件法

注意：如果您的 OFFICE 没有装在 C 盘，那么上面的磁盘号需要根据实际情况做修改；如果您

使用 OFFICE 2003，则将其中“Office12”修改为“Office11”。

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

（3）选择单元格区域 B3:B6，单击右键，从右键中选择【批量获取身份证信息】

菜单，程序将弹出一个对话框“确定计算区域”。该对话框中默认显示当前选区地址，

如果需要修改地址，可以输入新的地址，也可以用鼠标在工作表中选择身份证存放区

域，该区域的地址会自动产生在对话框中。见图 1.4 所示；

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

日和性别等信息。

前面三个案例中我们可以看出，Excel 具有强大的计算功能，但常规方式对于某

些大型数据运算显得比较繁琐。用户需要学习复杂的函数知识，设置长长的公式才可

以解决某些运算。而 VBA 可以使公式简化、易懂，甚至根本不需要公式，一个字母

不用录入即可完成一些专业性较强的计算。

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

势：

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

以前一节插件法完成身份证信息进行例证，它可以瞬间完成多个单元格数据的运

算，甚至多个工作表中存放的身份证号码也可瞬间完成信息提取。较传统的逐一处理

方式在效率上有大幅提升。

处理。但当嵌套过多时，就需要用户要较深的功底才能操纵或者理解。另一方面，对

于某些特殊行业的工作、任务，也要经过很复杂的操作才可以完成，而对于某些只需

要应用不需要深入研究、理解的普通办公文员们来说是一个技能考验。而通过 VBA

进行二次开发可以将复杂的任务变得更简单。简单是指理解和操作上同时简化。

就像 1.1.3 节中通过右键菜单提取身份证号码三类信息一样，不需要用户去录入

长长的公式，以及理解信息是如何提取出来的，单击菜单即可完成。再如企业中生成

工资条，10000 个人的资数用手工操作需要处理 10000*N 次，而利用 Excel 插件可以

据让普通用户无法胡乱修改，或者不小心破坏数据及数组结构。

 提升数据准确性

准确性体现在数据录入和数据运算两方面。首先，通地 VBA 对输入的数据进入

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

注册表等等，Excel 常规方式是不可能完成的。如果需要类似功能，VBA 完全可以胜

任。

 开发专业程序

利用 VBA 还可以开发一些专业型的程序，如报表汇总软件、进销存管理系统、

人事管理系统等等，可以将界面设置成其它任何软件的显示方式媲美专业的程序软件

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

在前一节中，通过一个身份证信息获取的插件认识了 Excel 插件，那么在工作中

应如何发挥 Excel 插件的优势呢？

1.2.1 Excel 插件的特点

Excel 插件是利用 VBA 程序开发的外置工具，通常是 xla、xlam 格式或者 dll 格

在插件封装后，调用其代码有两种方式：用代码调用，用菜单或者工具栏按钮，

显然菜单更方便。用户通过菜单单击即可完成相对于常规方式较复杂的操作或者运算。

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

在弹出的窗体中可以调用工作表的数据，也可以将窗体中录入的数据导到工作表。

而在窗体中录入数据时，相对于工作表中录入数据，可更好地控制。例如某个文字框

中可以指定只能录入数字，而另一个文字框可以指定只能入日期。也可以设定录入某

项目后自动跳转到指定目标位置，而不用手动去移动光标插入点。甚至可以在录入时

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