第十三章 Excel_VBA程序开发自学通正文1-100页.pdf

Excel VBA 程序开发自学通 2012-12-18 第 1 页 /共 471 页 入门篇：VBA 优势、功能与概念 第一章 从Excel 插件认识VBA 简单的说，Excel VBA 是依附于Excel 程序的一种自动化语言，它可以使常用的程序自动化， 类似于 DOS （磁盘操作系统）中的批处理文件（后缀名“.bat”）。那么它有什么具体的功能？在 工作中与常规操作方式相比，具有哪些优势？笔者试图通过一个简单却实用的插件来展现。 本章要点： l 从身份证号获取个人信息 l 在工作中如何发挥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 输入以下公式，用于计算出生日期： =TEXT(RIGHT(19MID(B3,7,LEN(B3)/2-1),8),#年##月## 日) （3 ）在单元格E3 输入以下公式，用于计算性别： =IF(ISODD(MID(B3,15,3)),男,女) Excel VBA 程序开发自学通 2012-12-18 第 2 页 /共 471 页 注意：在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 所示。 图1.3 自定义函数法获取身份证信息 本例中的函数SFZ 即身份证函数，用于从身份证号码中获取年龄、生日与性别等信息。它不 属于Excel 内置函数，需要利用VBA 编写代码才可以使用。读者可以从随书光盘中获取该完整代 码。 Excel VBA 程序开发自学通 2012-12-18 第 3 页 /共 471 页 SFZ 函数有两个参数，第一参数为单元格引用，第二参数为信息描述，即用于指定需要获取 身份证中哪一部分信息。当它为“NL ”（不区分大小写）时，获取年龄；当它为“SR”时，获取 生日，当它为“XB ”或者省略第二参数时，获取