没有合适的资源?快使用搜索试试~ 我知道了~
Excel_VBA_从入门到精通必备.doc
需积分: 0 3 下载量 132 浏览量
2023-02-25
13:11:43
上传
评论
收藏 8.29MB DOC 举报
温馨提示
试读
509页
Excel_VBA_从入门到精通必备.doc
资源推荐
资源详情
资源评论
Excel VBA 程序开发自学通 2023-2-25 第 1 页 /共 509 页
为入门篇: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 程序开发自学通 2023-2-25 第 2 页 /共 509 页
=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 程序开发自学通 2023-2-25 第 3 页 /共 509 页
图 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 程序开发自学通 2023-2-25 第 4 页 /共 509 页
图 1.4 插件法批量获取身份证信息
点评:插件法从身份证号码中获取信息的优点是速度快,通用性好。相对于内置
函数法,它在操作上更简单,不需要任何函数知识,不需要输入长长的公式,只点几
次鼠标即可;相对于自定义函数,它的优点是通用性好,在任何工作表、任何工作簿
皆可使用本工具。而前一方法之自定义函数非插件方式存在,只能在当前工作簿中使
用。
1.1.4 浅谈 VBA 优势
前面三个案例中我们可以看出,Excel 具有强大的计算功能,但常规方式对于某
些大型数据运算显得比较繁琐。用户需要学习复杂的函数知识,设置长长的公式才可
以解决某些运算。而 VBA 可以使公式简化、易懂,甚至根本不需要公式,一个字母
不用录入即可完成一些专业性较强的计算。
具体说来,相对于 Excel 自带的功能,VBA 或者说 VBA 开发的插件具有以下优
势:
批量地对操作对象进行数据处理
以前一节插件法完成身份证信息进行例证,它可以瞬间完成多个单元格数据的运
算,甚至多个工作表中存放的身份证号码也可瞬间完成信息提取。较传统的逐一处理
方式在效率上有大幅提升。
多任务一键完成
多任务是指对同一个对象需要进行多个操作,例如前一节是从身份证号码中获取
三类信息,VBA 可以单击一个按钮后瞬间完成,完全感觉不到它在分三步逐一完成
任务。这是高效办公地最佳体现。
将复杂的任务简化
Excel 是很多很多小工具的综合体。这些工具可以嵌套运用,完成更强大的数据
处理。但当嵌套过多时,就需要用户要较深的功底才能操纵或者理解。另一方面,对
于某些特殊行业的工作、任务,也要经过很复杂的操作才可以完成,而对于某些只需
要应用不需要深入研究、理解的普通办公文员们来说是一个技能考验。而通过 VBA
进行二次开发可以将复杂的任务变得更简单。简单是指理解和操作上同时简化。
就像 1.1.3 节中通过右键菜单提取身份证号码三类信息一样,不需要用户去录入
长长的公式,以及理解信息是如何提取出来的,单击菜单即可完成。再如企业中生成
工资条,10000 个人的资数用手工操作需要处理 10000*N 次,而利用 Excel 插件可以
Excel VBA 程序开发自学通 2023-2-25 第 5 页 /共 509 页
单击按钮完瞬间成。
将工作表数据提升安全性
利用 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.外观特征
有若干个菜单或者工具按钮
在插件封装后,调用其代码有两种方式:用代码调用,用菜单或者工具栏按钮,
显然菜单更方便。用户通过菜单单击即可完成相对于常规方式较复杂的操作或者运算。
利用窗体实现与工作表数据交互
在弹出的窗体中可以调用工作表的数据,也可以将窗体中录入的数据导到工作表。
而在窗体中录入数据时,相对于工作表中录入数据,可更好地控制。例如某个文字框
中可以指定只能录入数字,而另一个文字框可以指定只能入日期。也可以设定录入某
项目后自动跳转到指定目标位置,而不用手动去移动光标插入点。甚至可以在录入时
核对是否与工作表中数据是否重复等等……
剩余508页未读,继续阅读
资源评论
天晴~永
- 粉丝: 0
- 资源: 1
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功