Excel中宏工具VBA的编程优化探讨

计算机与网络

Excel中宏工具VBA的编程优化探讨

扬州职业大学　张建南

[摘　要]Office软件在不断地更新,新功能不断涌现。但是有时候,这并不是用户心里想要得到的。他们真正需要的,可能就是一些小小的变化,从而让工作效率更高些。宏就是这样一个好的工具,它上手容易但功能强大,应用得当,收益非浅。本文针对MicrosoftExcel2003中宏工具VBA编程进行探讨,提出VBA编程的优化方法,提高程序运行的效率。

[关键词]Excel　宏　VBA

　　VBA(VisualBasicForApplications)是微软开发出来在其桌面应用程序中执行通用的自动化(OLE)任务的编程语言。VBA是VisualBasic的一个子集,VBA不同于VB,原因是VBA要求有一个宿主应用程序才能运行,而且不能用于创建独立应用程序。而VB可用于创建独立的应用程序。VBA可使常用的过程或者进程自动化,可以创建自定义的解决方案,最适用于来定制已有的桌面应用程序。

通常意义上的VBA就是在Office中包含着的一种加强Office功能的Basic语言。经过发展,在Office中,Word、Excel、Access、PowerPoint四个软件都有了自己的程序设计语言,通常统一称为VBA(VBforApplication)。本文主要针对Excel中VBA编程代码效率,做如下优化工作:1、选择合适的数学模型编程需要数学模型,数学模型好比汽车的发动机。在同等的情况下使用好的发动机速度会大幅度提升。

2、提高VBA代码执行效率的常用手段

2.1数据类型的选择:避免使用变体数据类型:变体类型需要16个字节的空间保存数据,而一个整数(Integer)只需要2个字节。变体数据类型存在的目的是为减少设计时的工作量或代码量。所以编程时可以使用变体数据类型,最后优化时统一修改变量类型。

2.2整数(Integer)和长整型(Long)的应用,因为VBA处理整数(Integer)和长整型(Long)的能力远远高于单精度浮点型(Single)、双精度浮点型(Double)和Currency数据类型,所以将单精度浮点型(Single)、双精度浮点型(Double)和Currency数据类型转为整数(Integer)和长整型(Long)可以大大提高执行效率。

2.3对象的调用方法,减少对象的激活和选择:绝大多数的人都是通过录制宏来学习VBA,这样就会养成一个不好的习惯。你的VBA程序里会有大量的Activate、Select。如Work2books(3333).Activate、Range(3333).Select等,其实多数情况下Activate、Select可以省略,因为Activate、Select占用程序的执行时间,所以减少Activate、Select的使用次数,可以一定程度上提高运行速度。

2.4减少调用对象,属性统一处理:每个Excel对象的属性、方法的调用都需要通过OLE接口的一个或多个调用,这些OLE调用都是需要时间的,减少使用对象引用能加快VBA代码的运行。

另外,我们还要尽量减少调用同一对象,尤其是在循环中,多使用With语句,擅用对象变量。如果一个对象引用被多次使用,则我们可以将此对象用Set设置为对象变量,以减少对对象的访问。

2.5模块的调用:VBA加载模块的方式是只有在模块中的函数或变量被调用时,VBA才将模块加载到内存中,当VBA退出时,模块才从内存中拆卸。VBA代码最好在一个模块中编写,模块多了VBA就会多次加载,VBA代码的效率可能会降低。

一个过程需要调用一个过程或函数,因为调用是费时间的。如果被调用的过程或函数代码较少(几行代码),建议将代码直接写进过程。虽然牺牲了代码的长度,但执行效率会提高。

2.6数组的应用

2.6.1类似的数据变量:在VBA中,数组是最高的数据结构之一。当需要保存类似数据的变量时,优先考虑将它们有一个

数组代替。

2.6.2动态数组定义数组需要浪费内存资源,而适当的应用动态数组可以节省出一部分资源空间。这些节省出空间对配置好的机器可能效果不大,但对于老爷车级的电脑效果还是有的。

2.6.3针对Excel的数组ExcelVBA每调用一次对象、方法就要耗费一定的时间。在ExcelVBA里情况会好点,如果是外部调用的如VB、C+等调用Excel对象情况会很糟,数据越多,表现就越明显。因为ExcelVBA本身可以很快的识别Excel的对象,而外部程序识别就要先识别Excel再识别Excel的对象(前绑定或迟绑定效果相差不大)。解决的办法就是减少调用对象的次数。这时我们应该考虑数组(这里不是指Excel工作表里的数组函数)。

data=Sheets(1).Range(\"A1:A3000\").Value

data就是数组,这句的意思是将A1:A3000的值传递给da2ta数组。

反之,Sheets(1).Range(\"A1:A3000\").Value=data就是将数组data写入A1:A3000。2.7使用VBA原有的属性、方法和Worksheet函数2.7.1使用VBA原有的属性、方法:尽量避免使用自己编的,而ExcelVBA原来就用的属性、方法,因为“夫妻”还是原配的好。尽管VBA的属性、方法多不胜数,但多看代码、多编程、多按F1,你最后还是会掌握的。例如:Range的属性CurrentRe2gion来返回Range对象,该对象代表当前区。(当前区指以任意空白行及空白列的组合为边界的区域)。如果你自己编的话要几十行,而直接使用CurrentRegion,不但减少编程,速度也可大大提高。

2.7.2Worksheet函数:直接调用Excel的工作表函数可以大大提高运行速度。工作表函数的调用方法Application.Work2sheetFunction.工作表函数。

3、VBA的其他编程优化技巧处理大量Excel函数关闭屏幕更新

避免在频繁的事件中写代码:如Activate事件中减少用代码处理图片、音乐的使用检查字符串是否为空。4、优化的方法

“过早的优化是一切麻烦的根源”这是Knuth在编程界有一句名言。最明智的做法是抑制过早优化的冲动,因为那样做可能遗漏多种有用的编程技术,造成代码更难理解和操控,并需更大的精力进行维护。注意代码中的每个细节,尽量将发挥其最大的性能,从而使整体性能得到一定的提升。

参考文献

[1]PatriciaCardoza,TeresaHennig.Access2003VBAProgrammer’sReference,WileyPublishing,Inc.2004

[2]JinjersSimon.ExcelProgramming,HungryMinds,Inc.2002

[3]BruceEckel.Java编程思想(第2版)机械工业出版社,2003

—220—