-Excel-VBA在办公自动化中的应用

2014.22科技论坛105ExcelVBA在办公自动化中的应用李小遐（陕西国防工业职业技术学院电子信息系，西安,710302）摘要：Excel是办公软件Office中的一款，它以优秀的数据录入功能和强大的数据处理和分析功能深受广大办公人员的喜爱。在Excel中使用VBA，能够高效率地实现数据处理的自动化，将工作人员从简单而重复的数据处理工作中解脱出来，更能通过VBA编程对Excel进行二次开发，实现很多高级功能，提高办公效率。关键词：Excel;VBA;宏;办公自动化ApplicationofExcelVBAinofficeautomationLiXiaoxia(ShaanxiInstituteOfTechnology,Xi'an,710302)Abstract：ExcelisasoftwareOffice,itdeeplytothedatainputfunctionexcellentandpowerfuldataprocessingandanalysisfunctionoftheofficepersonnel'saffection.TheuseofVBAinExcel,caneffectivelyrealizetheautomationofdataprocessing,thedataprocessingworkreliefstafffromsimpleandrepeatedinandout,canthroughtheVBAprogrammingfortwoExceltodevelop,realizemanyadvancedfunctions,improveofficeefficiency.Keywords：Excel;VBA;Macro;Officeautomation1VBA概述VBA(VisualBasicforApplication)是新一代标准宏语言，由微软公司开发出来，在其桌面应用程序中执行通用的自动化（OLE）任务的编程语言。所谓应用程序自动化，是指通过编写程序让常规应用程序（如Excel、Word等）自动完成工作，例如在Excel里自动设置单元格格式或者多张工作表之间自动进行计算等等。VBA是基于VisualBasic（VB）发展而来的，是VB的一个子集，与VB一样是属于面向对象的编程语言，VBA继承了VB的开发机制，与VB有着相似的语言结构和开发环境。VBA与VB的不同之处在于，VBA是Office办公软件内嵌的编程语言，所以VBA代码必须“寄生”在宿主应用程序中运行，不能生成独立的应用程序。VBA根据其嵌入软件的不同，增加了对相应软件中的对象的控制功能，正是因为如此，VBA最适于定制已有的桌面应用程序。2ExcelVBA的办公应用Excel是最早支持VBA的组件，ExcelVBA作为一种扩展工具，在办公中得到了越来越广泛的应用，原因在于，很多实际应用中复杂的Excel操作都可以利用VBA编程得到简化。迄今为止，在Excel中使用VBA最常见的原因就是自动完成重复的工作，当然VBA不仅仅可用于重复任务，还可以构建Excel的新功能，例如可以开发新算法来分析数据，然后使用Excel中的图表功能显示结果，也可以执行将Excel与其他Office应用程序集成的任务。事实上，在所有Office应用程序中，Excel最常用作一个类似于常规开发平台，除了所有涉及列表和会计的显而易见的任务之外，从数据可视化到软件原型制作的大量任务中，开发人员都可使用VBA对Excel进行二次开发。3宏VBA是一种宏语言，是通过运行宏来工作的。如果需要在Excel中频繁执行某项工作，使用宏是最简单的方法。宏是可用于自动执行任务的一项或一组操作，其本质是由一系列VBA命令组成的程序，通过宏可以将一系列Excel操作组合在一起，形成一个命令，以实现任务的自动化。在Excel中创建宏有两种方法，即录制宏和用VBA语言编写宏代码。3.1录制宏代码在Excel2010中，利用“视图”选项卡中“宏”组中的“录制宏”按钮，在“录制新宏”对话框中，设置宏的名称、保存位置以及快捷键，再单击“确定”按钮，就可以开始宏录制，此时“录制宏”按钮状态变成了“停止制”，点击该按钮即可完成宏录制。宏录制好后，使用快捷键即可运行宏。在录制宏的过程中，Excel将操作转换为VBA代码保存在宏中，运行宏时，Excel驱动这些代码自动完成2014.22科技论坛106记录的操作。使用Alt+F11组合键可以打开VBE（VBA集成开发环境）窗口，查看到刚才录制的宏代码，但是这些代码中会有很多冗余指令，需要经过适当的修改才能得到高效的代码。以设置表头格式为黑体、16号字并加粗为例进行说明，通过录制宏得到的代码如下所示。Sub宏1()'快捷键:Ctrl+kWithSelection.Font.Name=黑体.Size=16.Strikethrough=False.Superscript=False.Subscript=False.OutlineFont=False.Shadow=False.Underline=xlUnderlineStyleNone.ThemeColor=xlThemeColorLight1.TintAndShade=0.ThemeFont=xlThemeFontNoneEndWithSelection.Font.Bold=TrueEndSub上述代码片段中加下划线的部分就是多余的代码，可以全部删除，仅保留需要设置效果的3个语句，执行的效果与之前是完全一致的。当然修改宏代码需要有一定的VBA基础，所以说，录制宏对不熟悉VBA编程的人员来说无疑是非常方便的，对于学习VBA编程也有很大帮助。3.2编写宏代码录制的宏可以忠诚地记录Excel操作，但有其自身的局限性，如录制的宏不够智能化，无法交互工作，代码冗长，没有判断和循环能力，只能简单执行等，因此对于一些无法录制的复杂操作，就需要在VBE窗口中使用VBA代码直接编写，然后将宏分配给VBA对象，如按钮、图形、控件和快捷键等，这样执行宏就像点击按钮或快捷键一样简单。以下通过一个具体的应用案例进行说明。实现功能：库存预期报警。问题描述：Excel工作表中存放着所有商品的库存信息，A列为名称，B列为计量单位，C列为目前库存数量，库管人员提供某个商品名称和最小库存数量时，低于这个数值的库存数量单元格自动变为红色、加粗，给出库存报警提示。实现这个功能的最佳途径是通过按钮运行宏。在库存工作表中添加一个命令按钮，把这个按钮指定给宏，用VBA编写宏代码，实现当库管人员点击按钮时会弹出对话框，在对话框里输入要查询的商品名称和报警数值，如果库存量不够的话给出提示，VBA代码如下所示。Sub库存_Click()DimMcAsStringDimx&,y&Mc=InputBox(请输入名称:)y=InputBox(请输入报警数量:)Forx=2ToRange(A65536).End(xlUp).RowIfCells(x,1)=McAndCells(x,3)=yThenCells(x,3).Font.ColorIndex=3Cells(x,3).Font.Bold=TrueEndIfNextxEndSub在Excel2010中，如果要编写宏、运行以前录制的宏或者用VBA创建与Office程序一起使用的应用程序，需要将“开发工具”选项卡显示出来，与早期的Excel版本有所不同，Excel2010的默认环境中，“开发工具”选项卡是隐藏的。操作方法是点击“文件-选项-自定义功能区”，选中“开发工具”复选框，确定后即可在Excel窗口中看到“开发工具”选项卡，然后在“控件”组的“插入”按钮里选择“按钮”窗体控件，即可在工作表中添加一个命令按钮。3.3自定义功能区日常办公应用中，除了经常使用快捷键和按钮运行宏以外，还有一个显为人知的快捷方法，即就是自定义功能区，然后把日常用到的一些功能定义成工具按钮，以后工作时就可以同标准工具按钮一样点击即可。操作方法是点击“文件-选项-自定义功能区”，在打开的“Excel选项”对话框中进行功能区和组的创建。操作时要在“主选项卡”中添加新建选项卡和新建组并重命名为一个合适的的名称，然后把“常用命令”文本框中的宏添加到新建组中。设置完成后即可在Excel窗口看到新建的功能区以及其中的工具按钮，并且这个设置对本地机的任何一个Excel工作薄都是有效的。以下通过一个具体的应用案例进行说明。实现功能：隔行格式化工作表。问题描述：Excel工作表中有多行数据，为了便于阅读和使用，需要把工作表设置为隔行一致的效果，比如隔行添加黄色底纹。这个工作用手工方法也能完成，但是比较枯燥还浪费时间，在此使用VBA编写宏代码，然后按照上述方法，把宏定义成工具按钮，操作时先选中要设置的区域，直接单击该按钮即可调用绑定的VBA代码，实现一键设置。VBA程序代码如下所示。（下转95页）2014.22测试工具与解决方案95设备间子系统通常设置在建筑物一层，既方便安装与维护，又能保证操作人员的工作效率，它包括系统输入设备、通讯终端、数据存储和其他主机设备。在进行安装的时候要将每种设备及其所用线缆进行有区别的标示，方便以后的维修和整理工作。设备间的面积要比较大，需根据已有设备的数量，并考虑后期加入的新的设备及维护工作的操作空间等诸多因素，来确定具体的面积，最终设定合理的管线分布及设备摆放。3.3管理间子系统管理间就是指配置在每层建筑的分线设备，它的作用是将水平子系统和垂直子系统连接起来。通常采用交叉连接和互联两种接线方式以提高管理效率，减少因线路问题而造成的数据传输不畅。交叉连接主要是通过单根导线将交叉连接处的两个端点连接起来；而互联则是通过插座或适配器等设备进行连接。3.4水平子系统水平子系统直接与工作区子系统相连，因此配有大量的插座、配线等设备。通过水平子系统将工作区与管理间连接起来，着重加强了对用户终端的信息收集整理工作。因此做好水平子系统的配线工作就显得十分重要，一般采用4对双绞线，长度不超过100米，以减少数据传输的故障率。有条件的话可以使用光纤与工作区相连，能极大地提高数据传输速度。水平子系统的设计与工作区子系统一样，需要考虑系统后期加入的新设备及维护工作等因素，保证后期的工作能够顺利开展。3.5垂直干线子系统垂直干线子系统主要是每层管理间的配线及设备，还包括连接每层管理间的主线缆。由于线缆极多，因此在安装垂直干线子系统的线路时，要根据最简短、最安全、最经济这三个“最”为基本原则进行铺设。线缆外部有管道进行保护，以保证线缆的安全和设备的稳定。3.6建筑群子系统建筑群子系统主要是在两个网络综合布线系统之间建立连接，除了设置在建筑外部的线缆、配线间和视频头等设备，还包括其铺设于地下的隐蔽线缆。在进行安装的时候同样要考虑后期加入新设备的可扩充性，特别是地下管道要留有备用管道。同时，地下管道由于管线多而复杂，要进行明显标示以区别于其他管线，方便后期的维护工作。4总结网络综合布线系统是当今最为先进的信息管理系统，直接影响着计算机和网络的运行效率。目前社会发展的速度越来越快，很多行业都需要建设此类系统，要根据自身情况，选择最合理的安装方式，还要将未来的发展趋势考虑在内。充分实践技术一流、品质过硬、安全可靠、简单实用的宗旨，为计算机技术和网络的发展贡献一份力量。参考文献[1]王巧巧.计算机网络综合布线系统设计.2008,(24)[2]王冬.浅谈综合布线信息化系统.2014,(4)[3]孙名成.网络综合布线技术浅析.2013,(34)[4]赵洁.综合布线系统浅析.2010,(36)（上接106页）SubColorsheet()DimiAsLongFori=1ToApplication.Selection.Rows.CountIfiMod2=1ThenSelection.Rows(i).Interior.Color=RGB(255,255,120)EndIfNextiEndSub4结论VBA语言要素非常多，不论是使用VBA代码创建宏，还是修改录