【VB开源代码栏目提醒】:网学会员VB开源代码为您提供(最新)Excel_VBA程序开发自学通正文 - 毕业设计参考,解决您在(最新)Excel_VBA程序开发自学通正文 - 毕业设计学习中工作中的难题,参考学习。
Excel VBA程序开发自学通 2012-9-4 第 1页 /共 472页
入门篇VBA优势、功能与
概念 第一章 从Excel插件认识VBA 简单的说Excel VBA是依附于Excel程序的一种自动化语言它可以使常用的程序自动化类似于DOS磁盘操作系统中的批处理文件后缀名“.bat”。
那么它有什么具体的功能在工作中与常规操作方式相比具有哪些优势笔者试图通过一个简单却
实用的插件来
展现。
本章要点 从身份证号获取个人信息 在工作中如何发挥Excel插件的优势 1.1 从身份证号获取个人信息 制作
人事资料时通常需要录入职员身份证号码以及生日、年龄、性别等等。
除身份证号码需要手工逐一录入以外其它三项信息的录入有四种方法手工录入、内置公式、自定义函数法、插件法。
手工输入方式效率极差且出错机率也最高本节通过后三种方式来实现并比较从而让读者对VBA之优势与用法得以
初步认知。
1.1.1 常觃公式法 以图1.1数据为例利用公式从身份证中提取生日、年龄、性别等信息可以有多种方法。
本例列举其中之一。
图1.1 根据身份证号提取职工年龄、生日与性别 通过公式计算
职工的年龄、出生日期与性别步骤如下 1在单元格C3输入以下公式用于计算年龄 DATEDIFDATEMIDB374-LENB3152MIDB311-LENB31522MIDB313-LENB31522NOWquotYquot 2在单元格D3输入以下公式用于计算出生日期 TEXTRIGHT19ampMIDB37LENB3/2-18quot年月日quot 3在单元格E3输入以下公式用于计算性别 Excel VBA程序开发自学通 2012-9-4 第 2页 /共 472页 IFISODDMIDB3153quot男quotquot女quot 注意在Excel 2003中ISODD函数默认状态下无法使用需要加载“
分析工具库”才可以正常使用为了使公式通用通常改用MOD函数。
即公式改为IFMODMIDB31532quot男quotquot女quot 4选择C3:E3区域将公式向下填充即完成身份证信息提取。
效果如下 图1.2 公式法获取身份证信息 点评相对于手工输入法利用公式从身份证号码获取个人信息有着效率更高、错误率更低之优点人员越多时越能体现出其高效优势。
本例文件参见光盘.. 第一章提取身份证信息.xlsm 1.1.2 自定义函数法 自定义函数是指利用VBA编写的外置函数。
在本例的随书光盘中已经录入了相关的VBA
代码可以随时调用。
对于
代码的含义和录入方式在后面的章节后有详细介绍本章仅通过具体应用了解其用法与优势。
具体操作
步骤如下 1进入“自定义函数法”工作表 2在C3:E3区域分别输入以下三个公式用于计算年龄、出生日期和性别 SFZB3quotNLquot SFZB3quotSRquot SFZB3或者SFZB3quotXBquot 3选择C3:E3单元格将公式向下填充结果见图1.3所示。
图1.3 自定义函数法获取身份证信息 本例中的函数SFZ即身份证函数
用于从身份证号码中获取年龄、生日与性别等信息。
它不属于Excel内置函数需要利用VBA编写
代码才可以使用。
读者可以从随书光盘中获取该
完整代Excel VBA程序开发自学通 2012-9-4 第 3页 /共 472页 码。
SFZ函数有两个参数第一参数为单元格引用第二参数为信息描述即用于指定需要获取身份证中哪一部分信息。
当它为“NL”不区分大小写时获取年龄当它为“SR”时获取生日当它为“XB”或者省略第二参数时获取性别。
点评相对于内置函数法/公式法自
定义函数法是借用VBA编写的外置函数完成它的优势在于公式简短且容易理解。
任何不熟悉
函数与VBA者皆可一分钟内
学会操作并理解其公式含义。
1.1.3 插件法 插件法是指借用Excel插件操作工作表该插件不隶属于当前工作簿但却可以实现与当前工作簿交互的功能批量、迅速完成身份证信息提取工作。
操作步骤如下 1关闭Excel程序的前提下将随书光盘中的插件位置..第一章批量获取身份证信息.xlam复制到以下自启动文件夹中即安装完成 C:Program FilesMicrosoft OfficeOffice12XLSTART
注意如果您的OFFICE没有装在C盘那么上面的磁盘号需要根据实际情况做修改如果您使用OFFICE 2003则将其中“Office12”修改为“Office11”。
2打开光盘文件“
提取身份证信息.xlsm”进入“插件法”工作表 3选择单元格区域B3:B6单击右键从右键中选择【批量获取身份证信息】菜单程序将弹出一个对话框“确定计算区域”。
该对话框中默认显示当前选区地址如果需要修改地址可以输入新的地址也可以用鼠标在工作表中选择身份证存放区域该
区域的地址会自动产生在
对话框中。
见图1.4所示 4单击“确定”按钮程序在瞬间就会从选区的所有身份证中提取年龄、生日和性别等信息。
图1.4 插件法批量获取身份证信息 点评插件法从身份证号码中获取信息的优点是速度快通用性好。
相对于内置函数法它在操作上更简单不需要任何函数知识不需要输入长长的公式只点几次鼠标即可相对于自定义函数它的优点是通用性好在任何工作表、任何工作簿皆可使用本工具。
而前一方法之自定义函数非插件方式存在只能在
当前工作簿中使用。
Excel VBA程序开发自学通 2012-9-4 第 4页 /共 472页 1.1.4 VBA优势 前面三个
案例中
我们可以看出Excel具有强大的计算功能但常规方式对于某些大型数据运算显得
比较繁琐。
用户需要学习复杂的函数知识设置长长的公式才可以解决某些运算。
而VBA可以使公式简化、易懂甚至根本不需要公式一个字母不用录入即可完成一些专业性较强的计算。
具体说来相对于Excel自带的功能VBA或者说VBA开发的插件具有以下
优势 批量地对操作对象进行数据处理 以前一节插件法完成身份证信息进行例证它可以瞬间完成多个单元格数据的运算甚至多个工作表
中存放的身份证号码也可瞬间完成信息提取。
较
传统的逐一处理方式在效率上有大幅提升。
多任务一键完成 多任务是指对同一个对象需要进行多个操作例如前一节是从身份证号码中获取三类信息VBA可以单击一个按钮后瞬间完成完全感觉不到它在分三步逐一完成任务。
这是高效办公地最佳体现。
将复杂的任务简化 Excel是很多很多小工具的
综合体。
这些工具可以嵌套
运用完成更强大的数据处理。
但当嵌套过多时就需要用户要较深的功底才能操纵或者理解。
另一方面对于某些特殊行业的工作、
任务也要经过很复杂的操作才可以完成而对于某些只需要应用不需要
深入研究、
理解的普通办
公文员们来说是一个技能考验。
而通过VBA进行二次开发可以将复杂的任务变得更简单。
简单是指理解和操作上同时简化。
就像1.1.3节中通过右键菜单提取身份证号码三类信息一样不需要用户去录入长长的公式以及理解信息是如何提取出来的单击菜单即可完成。
再如企业中
生成工资条10000个人的资数用手工操作需要处理10000N次而利用Excel插件可以单击按钮完瞬间成。
将工作表数据提升安全性 利用VBA
代码可以对数据进入多层保护在某些
特殊需求下VBA可以
保护数据让普通用户无法胡乱
修改或者不小心破坏数据及数组结构。
提升数据准确性 准确性体现在数据录入和数据运算两方面。
首先通地VBA对输入的数据
进入限制可以防止用户意外录入不规范字符。
如数字中有两个小数点或者录入数值时不小心录入了标点或者字母造成无法计算或者漏算。
其次在数据运算时
人工设置大量公式或者每天在不同
地方重复录入同一个公式。
在大量地操作中难以避免不产生一次错误。
而利用VBA可以让工作简
化工作量越小出错的机率一定越小同时在大量重复性工作中VBA可以确保不产生
错误。
完成Excel本身无法完成的任务 弹出提示、警告对话框、行程
安排与预告或者到
磁盘中查找需要的数据、修改
注册表等等Excel常规方式是不可能完成的。
如果需要类似功能VBA完全可以胜任。
开发专业程序 利用VBA还可以开发一些专业型的程序如报表汇总软件、
进销存
管理系统、
人事管理系统等等可以将界面设置成其它任何软件的显示方式媲美
专业的程序软件 Excel VBA程序开发自学通
2012-9-4 第 5页 /共 472页 1.2 插件特点及其如何发挥插件的优势 在前一节中通过一个身份证信息获取的插件认识了Excel插件那么在工作中应如何
发挥Excel插件的优势呢 1.2.1 Excel插件的
特点 Excel插件是利用VBA程序开发的外置工具通常是xla、xlam格式或者dll格式。
其中xla和xlam插件直接用Excel就可以开发而dll插件通常采用
VB或者C来编写。
不管何种
软件开发的插件它都需要在外观和功能两方面具有某些特征以方便用户调用。
1.外观特征 有
若干个菜单或者工具按钮 在插件封装后调用其
代码有两种方式用
代码调用用菜单或者工具栏按钮显然菜单更方便。
用户通过菜单单击即可完成相对于常规方式较复杂的操作或者运算。
利用窗体实现与工作表数据交互 在弹出的窗体中可以调用工作表的数据也
可以将窗体中录入的数据导到
工作表。
而在窗体中录入数据时相对于工作表中录入数据可更好地控制。
例如某个文字框中可以指定只能录入数字而另一个
文字框可以指定只能入日期。
也可以设定录入某
项目后自动跳转到指定目标位置而不用手动去
移动光标插入点。
甚至可以在录入时核对是否与工作表中数据
是否重复等等…… 有一个帮助界面 对于开发者来说不管自己开发的工具如何简单都有必要向用户说明其功能和操作方式。
所以
在工具中通常
加入一个窗体进行文字说明或者动画演示。
特别是
工具没有提供菜单、而是通过函数调用或者快捷键调用时更
需要一个
说明窗体。
对函数做参数说明 对于函数类插件必须对每个函数的参数进入
详细说明让用户插入函数时可以清晰明了地看到每个函数中每个参数的功能与使用方式。
2.功能特征 Excel插件
中的代码和
普通宏程序的
代码在编写上具有一些差异这是它们的设计目
的不同造成的。
其中宏
代码通常用于解决某个具体
的问题它可能限用
一次也可能需要反复调用。
但都只为
解决自己的某个具体问题而录制。
而开发Excel插件则通常是开发者开发后给其他的终端用户使用。
用户不
确定需要操作的区域对象不确定。
所以插件有不同的需求它需要具有以下特征
没有具体的区域地址 由于开发插件通常是给其他的终端用户使用所以不能指定数据区域地址而是提供一个
自由选择
目标区域的选择对话框或者利用
代码计算目标工作表中的待计算区域。
这是和录制宏最大的差异。
不使用具体的工作表名或者工作簿名
原理与前一条一致。
必须有通于菜单或者窗体供用户调用命令而不是在工作表中建立按钮来调用
命令。
dll格式的插件不存在工作表而xla和xlam
格式插件的工作表是隐藏
状态工作表不可能在用户的界面呈现出来所以
必须建立一个通用的菜单栏使其在打开任意工作簿都会
显示出来供用户操作。
如果使设置了快捷键那么是可以不用菜单或工具栏的
界面将会更简洁。
Excel VBA程序开发自学通
2012-9-4 第 6页 /共 472页 尽可能提供自定义选项 插件的针对性不强即它需要有广泛性。
插件通常不是为某一个
固定用户开发或者需要处理的数据并非永远一致那么在不同用户使用同一功能时需要有自定义其参数或者选项的空间工具才能有更好的通用性。
例如设计
一个工资条制作插件那么
工资条的表头行数就有
必要让
用户选择而非
强制一行或者两行。
这和编写一个解决临时性
问题的
编程思路
不同。
具有多版本适应
能力 目前办公用户使用的Excel版本差异很大有Excel
2000、Excel
XP、Excel
2003也有Excel 2007。
开发者不会假定用户都用某个版本的
Excel而是通过
代码判断当前用户的版本号然后调用不同的
代码以
适应当前版本否则某些功能可以无法使用。
防错机制 自用型宏程序通常不用防错因为用户和开发者是同一人。
而插件则必须有完善的防错
机制预先设置了遇到某种错误该如何反应的
措施避免破坏用户数据或者进入死
循环消耗尽计算机的
内存资源。
1.2.2 Excel插件的优势与限制 在工作中使用插件可以使用工作更轻松运算更快速、准确。
当然前提是插件的
代码编写足够优秀不仅
具有很强的通用性还要有
完善的防错机制
以及灵活的自定义选项。
那么工作中使用优秀的插件进行工作具有
哪些优势呢 简化操作类似于bat批处理
文件可以一键执行多个任务 强化功能对Excel内置功能无法完成的一些任务借用VBA
代码可以实现 美化界面VBA用以调用Flash动画也可以播放Gif动画还可以
直接对单元格字符产生滚动
效果。
对于喜欢装点的用户借用VBA可对工作表进行很好地修饰 固化格式VBA可以对录入的数据进入检测阻止输入不规范的数据也可以禁止新增、删除工作表或者禁止缩放窗口从而促使多用户文件能确保格式一致便于汇总 虽然插件在工作中有以上优势但它在某些方面也具有一些限制 通用性方面开发插件通常是个人
行为而非Office软件一样由一个大
公司主持。
所以其
通用性很可能不是
很好开发者测试的次数少以及测试条件不足等等导致工具具有某些隐含的
缺陷 防错
方面程序员不一定是终端用户甚至可能从来没有成为办公用户而是直接学习插件开发。
那么在程序编写时就可能
思维受限无法对可能出现的所有错误
进行防范 移植方面插件属于外置工具它的所有功能都需要安装才能使用。
所以如果利用插件
设计的表格有可能传用
客户后无法正常开启或者开启后无法正常显示。
最好的解决方法是将插件让
客户端也
安装一次
独立方面Excel的VBA是依附于Excel
主体程序的附属程序它可以开发强化Excel功能的程序但
不能开发脱离Excel而单独
存在的软件。
如果需要开发全新而专业的应用程序VBA并非
理想的程序 1.2.3 如何収挥插件的优势 可以确定的是善用插件可以提升工作
效率。
但是插件也不可滥用否则享用优势的同时也会
产生一些后患。
首先需要明白插件相对于Excel的功能属于外置工具它需要安装后才能使用。
如果读者Excel VBA
程序开发自学通 2012-9-4 第 7页 /共 472页
的文件非自用型需要与他人共享、
阅读那么需要连插件一起
共享 其次如果是简单的功能尽量使用内置功能少用插件。
插件
适用于处理复杂的或者Excel内置功能无法完成的工作 宏有一个通用BUG即使用宏
代码后内置撤消功能将禁用。
为了让用户减少损失针对某些会
更新数据、修改破坏原有格式的工具一定要提供一个恢复原状的程序。
例如有制作工资的工具就搭配一个删除工资条的工具。
最后尽量将插件在同
部门共享。
即一个
办公室为单位或者一个
企业为单位让整个单位都拥有相同的插件才能更好地发挥插件优势。
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
高级应用包括窗体的认识及磁盘、
目录与
文件操作 Excel VBA程序开发自学通 2012-9-4 第 8页 /共 472页 6 开发VBE环境
下的插件 7 学习利用
VB开发专业性的
COM加载宏插件 8
最后利用前面章节的知识开发一个
大型Excel插件。
从该插件
的开发思路和
过程让读者了解插件开发的常规流程及注意
事项 本书以插件开发为重心但对于VBA中常用知识不一定与插件开发
相关的知识但工作中较常用的功能也会进行详解或者进行实例
演示。
除插件开发外程序的提升和防错在本书的多次强调的
重点。
从第二章开始让读者学习、掌握VBA
理论知识为插件开发提供基础。
Excel VBA程序开发自学通 2012-9-4 第 9页 /共 472页 第二章 VBA概述 VBA是VBVISUAL BASIC 的一个子集是一种附属于Excel的程序
软件。
在学习VBA的语法之前有必要对其发展史、功能、特点等等方面进行了解。
本章要点 VBA
的发展史与优缺点 VBA能做什么 VBA
的安全性 使用VBA帮助 2.1 VBA的
发展史与优缺点 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中正式推出VBAVisual Basic for Applications
作为通用的宏语言来为Office应用程序编写
代码后Excel已完全征服了制表用户。
可见宏语言在表格软件中
影响之深远。
宏的英文名为Macro是自动执行某种操作的命令集合。
它包括两个过程即Excel 4或者称为xlm的宏语言和Excel 5中的VBA宏。
Excel 4的宏由宏表函数
构成由录入在宏表中的函数来控制程序的执行。
至1993年发布的Excel 5中微软
开始推广VBA做为宏
语言并同时引进VBA
编辑器即VBEVisual
Basic Edirtor。
用户可以通过录制宏来产生
代码代码储存在VBE环境的
代码模块中利用AltF8可以反复调用录制的宏。
VBA是目前OFFICE
系列通用的
一种程序语言它支持录制、执行、单步执行、调试等等操作可以使用户从繁重的制表任
务中解脱出来。
VBA是一种
面向对象的程序语言由一种所见即
所得的方式编写
代码这使它在学习和使用方面都相比其它语言更简单。
事实上几乎
所有VBA程序员都由录制宏开始学习VBA这是一个VBA速成的捷径。
甚至VBA高手们仍然对录制宏乐此不倦因为它可以完成VBA程序的大部分
代码程序员仅需在录制的宏
代码中稍加修改即可成为最
后的合格程序另一个最重要的
因素是录制宏可以为程序员提供词典
的作用即忘记了某个对象
单词或者完全不明白某个属性的
语法时利用录制宏可以产生对应的
代码用户复制即可使用。
2.1.2 VBA历史与版本 VBA的前身是xlm宏语言鉴于xlm宏功能
有限至今已经用VBA完全替代了xlm宏。
但是为了体现兼容性所有版本的VBA中皆可以调用以前的部分宏表函数。
例如Excel 2
007的Excel VBA程序开发自学通 2012-9-4 第 10页 /共 472页 application对象仍然保留了以下宏表相关的一个方法和两个属性通过它们可以执行早期宏表所有函数
Application.ExecuteExcel4Macro
Application.Excel4MacroSheets Application.Excel4
IntlMacroSheets 在抛弃早期宏语语言后VBA从
1993年开始逐步在很多软件中出现除OFFICE办公软件外Cad、Coreldraw等等软件也支持VBA。
目前VBA的.