您现在的位置:首页 > >

Excel制表实例操作之(8)宏与VBA编程运用

发布时间:

第八章
宏与VBA编程运用
8.1 表格制作中宏的运用 8.2 VBA编程应用

226

CHAPTER 8

宏与VBA编程运用

8.1 表格制作中宏的运用
例1 Excel加载宏的方法
我们都知道Excel是一个强大的数据处理工具,然而,当我们要进行 特殊操作时,Excel并不一定能满足我们独特的需要。还好,当我们感觉 Excel不够用时,可以利用加载宏来实现。

1.加载宏的含义
加载宏也就是利用外部程序来增加Excel的功能,加载宏程序就是 Excel的插件,可以补充Excel的各种命令或功能。这些功能往往都是不常 用的,但是在有特殊需要的时候很实用。比如说分析数据库加载宏程序提 供了一套数据分析工具,在进行复杂统计或工程分析时,可以简化操作步 骤,从而提高工作效率。

2.加载宏的类型
Excel有两种类型的加载宏程序可以使用,分别是Excel加载宏和自定 义的组件对象模型 (COM) 加载宏。后者是用各种微软的编程工具(如: Visual C++)或能够创建COM、DLL和可执行文件的其他编程工具编写 的,目前比较流行的是Excel加载宏。

3.载入加载宏
Excel载入宏的过程,实际上就是将以.xla为扩展名的文件加入到软件 中,选择“工具”菜单中的“加载宏”命令,然后点击“浏览”按钮,找 到要载入的加载宏,接着选择刚才加入的宏,按确定按钮,相关命令就添 加到Excel的对应菜单中。其实在安装Excel时已经附带了一些加载宏,只 要在对话框中选中复选框就可以使用相应的功能。

宏与VBA编程运用

CHAPTER 8

227

加载宏命令

例2 利用宏加载“扩展工具”
这里以实际例子介绍加载“扩展工具”的方法,到网络中下载“扩展 工具”加载程序。其中包含了货币换算、改变大小写、时间提示、中文大 写金额转换等辅助功能。这在某些时候会大大提高我们的工作效率。 启动Excel并运行“工具→加载宏”,在弹出的窗口中点击“浏览”, 找到刚才那个解压过的文件之后按下“确定”按钮即将其加载到Excel中。 加载之后,我们可以发现Excel增加了一个“新增”菜单,点击之后将 查看到各种新增的辅助功能,都可以方便地使用。

新增加载宏

228

CHAPTER 8

宏与VBA编程运用

例3 利用宏给文档自动添加密码
在Excel中给工作簿文档添加密码时,需要通过选项一个一个的设 置,比较麻烦。下面,我们利用一个自动运行的宏,让软件自动给文档 添加密码。 (1)启动Excel,执行“工具→宏→Visual Basic编辑器”命令,进 入VBA编辑状态。 (2)在左侧的“工程资源管理器”窗口中,选中“VBAproject(P ERSONAL.XLS)”(个人宏工作簿)选项。 (3)执行“插入→模块”命令,插入一个模块(模块1)。 (4)将下述代码输入到右侧的代码编辑窗口中: Sub Auto_close() ActiveWorkbook.Password = "123456" ActiveWorkbook.Save End Sub 退出VBA编辑状态。

输入代码

注意
这是一个退出Excel时自动运行的宏,其宏名称(Auto _close)不能修改。

(5)以后在退出Excel时,软件自动为当前工作簿添加上密码 (123456,可以根据需要修改),并保存文档。

宏与VBA编程运用

CHAPTER 8

229

例4 用窗体调用“宏”
通过运行“宏”对话框调用宏不太方便,通过快捷键调用宏,如果宏 多了又记不住,那就添加一个按钮吧。 如果宏仅对当前工作簿文档有效,我建议大家在工作簿文档中添加一 个“窗体”命令按钮,用来调用宏: (1)执行“视图工具栏窗体”命令,展开“窗体”工具栏。 (2)点击工具栏上的“命令按钮”按钮,然后在工作表中拖拉出一个 按钮来。

视图工具栏窗口

(3)此时系统自动弹出“指定宏”对话框,选中需要调用的宏,确定 返回。 (4)将命令按钮上的字符修改为一个合适的内容,调整好命令按钮的 大小,将其定位在工作表合适位置上。 按一下该按钮,即可执行相应的宏。

例5 Excel禁止自动运行宏
你可能遇到过可恶的宏病毒,其中有一部分是在打开文件时自动运行 并产生危害。 在你打开一个Excel文件时,可以很容易地阻止一个用VBA写成的在 打开文件时自动运行的宏的运行。从“文件”菜单中选择“打开”,选择 你所要打开的文件的名字,在点击“打开”时按住“Shift”键,Excel将

230

CHAPTER 8

宏与VBA编程运用

在不运行VBA的情况下,打开该工作薄。 按住“Shift”键阻止宏运行的 方法同样适用于选择打开菜单底部的文件(最*打开的几个文件) 。 同样,在你关闭一个Excel文件时,也可以很容易地阻止一个宏的运 行。从“文件”菜单中选择“关闭”,在点击 “关闭”时按住“Shift” 键,Excel将在不运行VBA过程的情况下关闭这个工作薄。

注意
按住“ Shift ”键同样适用于点击窗口右上角的“×”关闭 工作薄时阻止宏的运行。

例6 Excel中宏的录制
在录制宏命令时,Excel将自动记录并储存用户所执行的一系列菜单命 令的信息。 (1)打开带有宏的原始文件,切换到自己编辑的工作表中。 (2)选择“工具→宏→录制新宏”菜单项。

录制新宏

(3)随即会弹出“录制新宏”对话框,然后在“宏名”文本框中输入 “标题”,将“快捷键”设置为“Ctrl+A”,并且在“保存在”下拉列表中 选择“当前工作簿”选项。

参数设置

宏与VBA编程运用

CHAPTER 8

231

(4)单击“确定”按钮即可开始宏的录制操作。 (5)选择“格式→单元格”菜单项,弹出“单元格格式”对话框, 切换到“对齐”选项卡中,然后分别在“水*对齐”和“垂直对齐”下拉 列表中选择“居中”选项,并且选中“文本控制”组合框中的“合并单元 格”复选框。

对齐选项卡

(6)切换到“字体”选项卡中,然后在“字体”列表框中选择一个自 己喜欢的字体。在颜色列表中选择自己喜欢的颜色。

字体的选择

(7)切换到“边框”选项卡中,在“线条”组合框中的“样式” 列表框中选择一种合适的样式,然后单击“预置”组合框中的“外边 框”按钮。

232

CHAPTER 8

宏与VBA编程运用

选择外边框

(8)选择“工具→宏→停止录制”菜单项。即完成了宏的录制操作。

停止录制

例7 如何在Excel中运行宏
对于刚刚录制好的宏,可以通过运行宏命令来验证下运行的效果。 (1)打开刚才录制的宏文件。 (2)单击“启动宏”按钮进入到工作表,然后选中单元格区域“A1: F1”。 (3)选择“工具→宏→宏”菜单项。

选择命令

(4)随即会弹出“宏”对话框,然后在“宏名”列表框中选择“标 题”选项。

宏与VBA编程运用

CHAPTER 8

233

设置宏名

(5)单击“执行”按钮,系统会自动地执行宏。

例8 编辑与修改定制的宏
其实宏就是VBA的一个子过程,他和VBA紧密相关。录制完毕后,我 们也可以对宏进行修改和编辑。具体操作步骤如下: (1)打开自己录制好的宏文件。 (2)单击“启动宏”按钮进入到工作表,然后选中单元格区域“A1: F1”。 (3)选择“工具→宏→宏”菜单项,弹出“宏”对话框,然后在“宏 名”列表中选择“标题”选项。 (4)单击“编辑”按钮,弹出刚才我们录制宏的名字的文件窗口。再 此窗口中显示了录制宏的命令。

编辑按钮弹出编 辑窗口

(5)查找出设置字体的代码,将字体“楷体-GD2312”改为“隶 书”,字号设置为“16”。

234

CHAPTER 8

宏与VBA编程运用

(6)单击“运行→运行子过程/用户窗体”按钮,运行此程序。然后 单击“视图Microsoft Excel”按钮。此时选中的单元格区域中的字体已 经变为你重新设置的大小了。

运行子过程/用 户窗体

注意
如果你想删除宏,直接点击“宏”对话框中的删除按钮 即可。

8.2 VBA编程应用
例1 VBA中的数组应用
数组是包含相同数据类型的一组变量的集合,对数组中的单个变量 引用通过数组索引下标进行。在内存中表现为一个连续的内存块,必须用 Global或Dim语句来定义。定义规则如下: Dim数组名([lowerto]upper[,[lowerto]upper,….])astype;Lower缺 省值为0。二维数组是按行列排列,如XYZ(行,列)。 除了以上固定数组外,VBA还有一种功能强大的动态数组,定义时无 大小维数声明;在程序中再利用Redim语句来重新改变数组大小,原来数组 内容可以通过加preserve关键字来保留。如下例: Dimarray1()asdouble:Redimarray1(5):array1(3)=250:Redimprese rvearray1(5,10)

宏与VBA编程运用

CHAPTER 8

235

例2 使用注释和赋值语句
(1)注释语句是用来说明程序中某些语句的功能和作用;VBA中有两 种方法标识为注释语句。 (2)赋值语句是进行对变量或对象属性赋值的语句,采用赋值号=, 如X=123:Form1.caption=“我的窗口”。 对对象的赋值采用:setmyobject=object或myobject:=object。

例3 重要函数Choose和Switch
1.choose函数
choose(index,choce-1,choice-2,…,choice-n) 可以用来选择自变量串列中的一个值,并将其返回,index必要参数, 数值表达式或字段,它的运算结果是一个数值,且界于1和可选择的项目数 之间。

2.Switch函数
Switch(expr-1,value-1[,expr-2,value-2_[,expr-n,value-n]]) switch函数和Choose函数类似,但它是以两个一组的方式返回所要 的值,在串列中,最先为TRUE的值会被返回。 expr必要参数,要加以计算的Variant表达式。value必要参数。如果 相关的表达式为True,则返回此部分的数值或表达式,没有一个表达式为 True,Switch会返回一个Null值。

例4 VBA中循环语句的使用
结构化程序使用以上判断和循环语句已经足够,建议不要轻易使用下 面的语句。 (1)Gotoline语句为跳转到line语句行。 (2)Onexpressiongosubdestinatioinlist或者onexpressi ongotodestinat ionlist语句为根据exprssion表达式值来跳转到所要的行 号或行标记。 (3)Gosubline…line…Return语句,Return返回到Gosubline行, 如下例:

236
Dimnum

CHAPTER 8
Subgosubtry()

宏与VBA编程运用

Num=inputbox(“输入一个数字,此值将会被判断循环”) Ifnum>0thenGosubRoutine1:Debug.printnum:Exitsub Routine1: Num=num/5 Return Endsub

例5 在VBA中常见的文件操作
1.文件操作
Dir[(pathname[,attributes])];pathname可选参数,用来指定文件 名的字符串表达式,可能包含目录或文件夹、以及驱动器。 如果没有找到pathname,则会返回零长度字符串("");attributes可选 参数。 常数或数值表达式,其总和用来指定文件属性。如果省略,则会返回 匹配pathname但不包含属性的文件。

2.删除操作
Killpathname:从磁盘中删除文件,pathname参数是用来指定一个文 件名。 RmDirpathname:从磁盘中删除删除目录,pathname参数是用来指 定一个文件夹。

3.读入操作
Input#filenumber,varlist:从已打开的顺序文件中读出数据并将数据 指定给变量。 Get[#]filenumber,[recnumber],varname:将一个已打开的磁盘文 件读入一个变量之中。

4.写入操作
Write#filenumber,[outputlist]:将数据写入顺序文件。 Print#filenumber,[outputlist]:将格式化显示的数据写入顺序文 件中。

宏与VBA编程运用

CHAPTER 8

237

Put[#]filenumber,[recnumber],varname:将一个变量的数据写入 磁盘文件中。

5.关闭操作
Close[filenumberlist]关闭Open语句所打开的输入/输出(I/O)文件。

6.其他文件函数
LOF(filenumber):返回一个Long,表示用Open语句打开的文件的大 小,该大小以字节为单位。 EOF(filenumber):返回一个Integer,它包含Boolean值True,表明 已经到达为Random或顺序Input打开的文件的结尾。 Loc(filenumber):返回一个Long,在已打开的文件中指定当前读/写 位置。 Seek(filenumber):返回一个Long,在Open语句打开的文件中指定 当前的读/写位置。

例6 锁住数字以外的单元格
当一个表格成为固定格式之后,用户便只需要修改表格中的数据, 而不能修改表格的表头和其他说明信息,为了防止用户恶意修改或无意修 改,可以用VBA程序将数据以外的信息全部锁定。 代码如下: SubSet_Protection() OnErrorGoToerrorHandler DimmyDocAsWorksheet DimcelAsRange SetmyDoc=ActiveSheet myDoc.UnProtect ForEachcelInmyDoc.UsedRange IfNotcel.HasFormulaAnd_ NotTypeName(cel.Value)="Date"And_ Application.IsNumber(cel)Then cel.Locked=False cel.Font.ColorIndex=5 Else

238

CHAPTER 8
cel.Locked=True

宏与VBA编程运用

cel.Font.ColorIndex=xlColorIndexAutomatic EndIf Next myDoc.Protect ExitSub errorHandler: MsgBoxError EndSub

例7 用Excel打开所有类型的文件
1.宣告区代码
ConstSW_SHOW=5 PrivateDeclareFunctionShellExecuteLib"shell32.dll"Alias"ShellE xecuteA"(ByValhwndAsLong,ByVallpOperationAsString,ByVallpFil eAsString,ByVallpParametersAsString,ByVallpDirectoryAsString, ByValnShowCmdAsLong)AsLong

2.Moudle中的代码
Subtest() Filename=Application.GetOpenFilename(,,"打开文件",False) 'Excel档由Excel开,其他档由ShellExecute函数开 IfLCase(Right(Filename,3))<>"xls"Then ShellExecute0,"open",Filename,"","",SW_SHOW Else Workbooks.Open(Filename) EndIf EndSub

例8 DLL文件制作与在VBA调用初级进阶
本例用一简例来演示如何将一正常在VBA中运行的代码移植到VBA制

宏与VBA编程运用

CHAPTER 8

239

作的DLL中去,并介绍如何在VBA中进行调用。这仅仅是最初级的介绍, 希望能对此内容想了解的读者们一些帮助。 先来了解一下什么是DLL。DLL是DynamicLinkLibrary的缩写,意为 动态链接库。 在Windows中,许多应用程序并不是一个完整的可执行文件,它们被 分割成一些相对独立的动态链接库,即DLL文件,放置于系统中。 当我们执行某一个程序时,相应的DLL文件就会被调用。一个应用程 序可有多个DLL文件,一个DLL文件也可能被几个应用程序所共用,这样 的DLL文件被称为共享DLL文件。 DLL文件一般被存放在C:\Windows\System目录下。 因此,DLL文件可以看作是一个程序扩展函数库,成为应用软件程序 的外挂接口。就像我们常在程序中用的WINDOWSAPI,就是如此,它是微 软提供给程序员的函数宝库,用户只需了解函数功能与调用规则就可以拿 来使用,而不用知道也不需要知道具体源码。 也正因为此,现在许多VBA开发者在保护其代码时也多采用此法来维 护其合法权益。 假如我们要编这么一段代码:将工作表Sheet1的A1单元格的数值与 B1单元格的数值相加填写到C1单元格去,在VBA模块我们可以编写这样的 代码: Subtest() OnErrorResumeNext Dimi,jAsInteger WithWorksheets("Sheet1") i=.Cells(1,1).Value j=.Cells(1,2).Value .Cells(1,3)=i+j EndWith EndSub 执行上面的宏,会发现C1单元格自动会出现A1+B1的结果了。 下面我们在VB中生成包含以上宏功能的函数(子程序)的DLL文件。 打开VBA,“文件→新建工程→ActiveXDLL”,此时会弹出class1模块 代码窗口,将如下代码写入:

240

CHAPTER 8
Subtest() OnErrorResumeNext Dimi,jAsInteger DimEB

宏与VBA编程运用

SetEB=GetObject(,"ExcelApplication") WithEB.ActiveSheet i=.Cells(1,1).Value j=.Cells(1,2).Value .Cells(1,3)=i+j EndWith SetEB=Nothing EndSub 在生成DLL之前,注意确保VBA里“工程→引用”里“Microsoft Excel9.0ObjectLibrary”前面的勾已打上,还有就是正确设定好DLL描述 与名称。 菜单“工程→工程属性→工程描述”,里面可以写上你的DLL功能名 称或简介或版本号。 “视图→属性”,设定名称为你在其他开发环境中欲使用的引用名 称,如“Mytest”。现在点击菜单“文件→生成…dll”,会弹出路径与文 件取名窗,按你的意思选择与输入好路径和即将生成的DLL文件名,按确 定就会完成DLL的生成工作。 此时DLL已生成在你指定的目录下了,当前工程是否保存则根据使用 者自己的情况来定,如果以后还想再编辑修改的话,可以直接调用。 现在回到Excel VBA开发环境中来,先对刚生成的DLL进行一下引 用。关于引用,可以手工引用,也可以用代码来做。 手工引用的步骤是:在VBA窗口中,菜单“工具→引用”,会弹出如 下窗口,“浏览”到刚才生成的DLL,选择打勾确定即可。

宏与VBA编程运用

CHAPTER 8

241

工具引用

代码引用:“Shell"regsvr32/s"&"…………..dll",vbHide”,加s参 数是防止出现确认窗口,影响界面。取消引用则使用/u参数。 然后在VBA中写调用的代码: SubDLLtest() DimabcAsNewMytest‘定义abc为新类,即在DLL文件中定义的 abc.test‘调用DLL中提供的过程,来完成原来在VBA中的功能,起 到隐藏代码的效果 Setabc=Nothing EndSub

例9 用VBA编写计算销售佣金的函数
要求1:根据销售额和对应的佣金率计算=Sales×Rate 要求2:根据销售额和对应的佣金率以及工作年限计算,工作每满一年 佣金在原来的基础上增加一个百分点=Sales×Rate×(1+Year/100) 条件临界点佣金率 [0,10000)00.08 [10000,20000)100000.105 [20000,40000)200000.12 [40000,无穷)400000.14 计算方法: (1)利用vlookup函数的模糊查找:=VLOOKUP(B2,$B$14:$ C$17,2,TRUE)*B2定期维护佣金率。

242

CHAPTER 8

宏与VBA编程运用

(2)利用if函数结合&连接符突破if七层嵌套问题: =IF(AND(B2>=0,B2<$B$15),B2*$C$14,"")&IF(AND(B2>=$B$15, B2<$B$16),B2*$C$15,"")&IF(AND(B2>=$B$16,B2<$B$17),B2*$C$16, "")&IF(AND(B2>=$B$17),B2*$C$17,"") (3)利用自定义函数,代码如下: FunctionCommission1(Sales,years) '计算销售佣金,工作每满一年,销售佣金在原来的基础上增加一个百 分点 ConstRate1=0.08 ConstRate2=0.105 ConstRate3=0.12 ConstRate4=0.14 SelectCaseSales Case0To9999.99'Caseatob表示[a,b]两边都是闭区间 Commission1=Sales*Rate1 Case10000To19999.99 Commission1=Sales*Rate2 Case20000To39999.99 Commission1=Sales*Rate3 CaseElse Commission1=Sales*Rate4 EndSelect '每工作满一年,佣金在原来的基础上增加1个百分点 Commission1=Commission1*(1+years/100) EndFunction Sub计算销售佣金() ’在工作表中设计一个窗体按钮,执行此代码 DimSales DimyearsAsInteger Sales=Val(InputBox("请输入销售额:","计算销售佣金")) years=Val(InputBox("请输入工作年限:","计算销售佣金")) y=MsgBox("你的佣金为:"&Commission1(Sales,years),vbYesN

宏与VBA编程运用

CHAPTER 8

243

o,"计算销售佣金") Ify=vbYesThen'这里使用msgbox信息框,当单击是的时候,调用该 过程本身 计算销售佣金 EndIf EndSub

例10 用VBA随机抽取某区域中的单元格
(1)易失性函数:顾名思义该函数很容易改变,也就是无论何时在工 作表任意单元格输入数据,易失性函数都需要重新计算。 (2)非易失性函数:顾名思义该函数不容易改变,也就是只有在函数 中的参数值发生变化时,非易失性函数才重新计算,否则不计算,结合本 例,只有在a1:a10输入数据,非易失性函数才重新计算,否则不计算。 (3)Optional申明变量,表示该变量为可选参数。 (4)假如Region为一个range对象区域,那么Region(i)表示区域 Region中第i个对象。 代码如下: FunctionUnderstandVolatile(RegionAsRange,OptionalFlagBool eanAsBoolean=False) '利用optional定义变量表示该变量为可选参数 '理解非易失性函数 '函数功能:随机抽取Region区域中的一个单元格值 '当application.volatiletrue时,表示易失性函数 Application.VolatileFlagBoolean '产生[a,b]之间的随机整数Int(rnd()*(b-a+1)+1) UnderstandVolatile=Region(Int(Rnd()*(Region.Count)+1)) EndFunction

例11 用VBA程序颠倒字符串
(1)StrReverse(String)返回反向字符串,当string为空值时,则 函数返回空字符窜,如果无参数,则返回null (2)MID(String,i,n)从字符串string的第i个位置开始提取长度为n的

244
字符串

CHAPTER 8

宏与VBA编程运用

函数使用fori=len(string)to1step-1 n=mid(string,i,1) '遍历字符串中的每个字符,此方法可以运用到数字与字符分离或者字 符串中各数字求和等 nexti FunctionMstrReverse(Mstring)AsString '利用vba函数StrReverse返回反向字符串 MstrReverse=VBA.StrReverse(Mstring) EndFunction FunctionMstrreverse1(Mstring)AsString DimiAsInteger Fori=Len(Mstring)To1Step-1 Mstrreverse1=Mstrreverse1&Mid(Mstring,i,1) Nexti EndFunction SubMstrreverse2() Mstring=InputBox("请输入字符串:","反向字符串") IfMstring=""ThenExitSub MsgBox"字符串:"&Mstring&"的反向字符串为:"&vbCrLf&Mstr Reverse(Mstring),vbOKOnly,"反向字符串" EndSub

例12 借助FLASH美化VBA操作界面
算法优化了,程序编完了,调试过关了,接下来便是程序界面的设计 问题。许多人都在努力使自己开发的程序界面更加的美观,但除了绞尽脑 汁设计安排控件的摆放,使用颜色、背景图片和底纹,似乎没有其他的方 法了。 当然并不是说越花哨的界面越好,在软件具备优良功能前提下,如果 对软件进行适度包装,将一定会收到锦上添花的效果。 本例将演示FLASH与VBA的完美结合,即借助FLASH制作出漂亮的 动态按钮,在Excel中实现与VBA的调用。

宏与VBA编程运用

CHAPTER 8

245

实现原理:利用Flash的FSCommand()函数,实现信息发送,向 VBA程序中的Shockwaveflash控件的FSCommand()控制来完成通 信,VBA根据接收到的不同的命令及参数来作出相应控制处理。

1.制作FLASH文件
这一步关键就是建立包含按钮及按钮代码的FLASH文件。我们完全可 以从共享库里拖几个FLASH提供的漂亮按钮出来直接使用,因为不管怎样 都比VBA里那种方方正正、灰灰的按钮强百倍啊。把按钮放好后,给其加 入代码: on(release){ fscommand("这里写你命名的字符串");//如"open""saveas"等, 一个按钮对应一个,该字串将发送至VBA程序}

2.在Excel中播放FLASH
这个简单,使用ShockwaveFlash.ocx这个控件就可以了。 假如把该控件拖放至Sheet1工作表上,那么在工作簿打开事件中: PrivateSubWorkbook_Open() Sheet1.ShockwaveFlash1.Movie="D:\test.swf"'指定要播放 的FLASH文件路径 Sheet1.ShockwaveFlash1.Menu=False'关闭FLASH菜单 Sheet1.ShockwaveFlash1.Playing=True'自动播放 EndSub

3.编写VBA代码完成交互
在设计状态下双击工作表中的ShockwaveFlash控件,进入代码编辑 窗口,加入如下语句: PrivateSubShockwaveFlash1_FSCommand(ByValcommandAs String,ByValargsAsString) SelectCasecommand'这里接收在FLASH制作中给每个按钮代码设 定的字符串 Case"open" ...... Case"saveas" ......

246
......

CHAPTER 8
case......

宏与VBA编程运用

EndSelect EndSub 完成以上三步后,你就可以在工作表中用鼠标点击FLASH中的按钮去 控制整个VBA程序了。

例13 使用VBA的Worksheet函数
由于Excel对象多达百多个,对象的属性、方法、事件多不胜数,对于 初学者来说可能对它们不全部了解,这就产生了编程者经常编写与Excel对 象的属性、方法相同功能的VBA代码段,而这些代码段的运行效率显然与 Excel对象的属性、方法完成任务的速度相差甚大。 例如用Range的属性CurrentRegion来返回Range对象,该对象代表 当前区。(当前区指以任意空白行及空白列的组合为边界的区域)。同样 功能的VBA代码需数十行。因此编程前应尽可能多地了解Excel对象的属 性、方法。 充分利用Worksheet函数是提高程序运行速度的极度有效的方法。如 求*均工资的例子: ForEachcInWorksheet(1).Range(″A1:A1000″) Totalvalue=Totalvalue+c.value Next Averagevalue=Totalvalue/Worksheet(1).Range(″A1: A1000″).Ro ws.Count 而下面代码程序比上面例子快得多: Averagevalue=Application.WorksheetFunction. Average(Worksheets(1).Range(″A1:A1000″)) 其他函数如Count、Counta、Countif、Match、Lookup等,都能 代替相同功能的VBA程序代码,提高程序的运行速度。

例14 通过减少使用对象引用提高效率
每一个Excel对象的属性、方法的调用都需要通过OLE接口的一个或 多个调用,这些OLE调用都是需要时间的,减少使用对象引用能加快VBA

宏与VBA编程运用

CHAPTER 8

247

代码的运行。例如

1.使用With语句
Workbooks(1).Sheets(1).Range(″A1:A1000″).Font.Name=″ Pay″ Workbooks(1).Sheets(1).Range(″A1:A1000″).Font. FontStyle=″Bold″... 则以下语句比上面的快 WithWorkbooks(1).Sheets(1).Range(″A1:A1000″).Font .Name=″Pay″ .FontStyle=″Bold″ ... EndWith

2.使用对象变量
如果你发现一个对象引用被多次使用,则你可以将此对象用Set设置 为对象变量,以减少对对象的访问。如: Workbooks(1).Sheets(1).Range(″A1″).value=100 Workbooks(1).Sheets(1).Range(″A2″).value=200 则以下代码比上面的要快: SetMySheet=Workbooks(1).Sheets(1) MySheet.Range(″A1″).value=100 MySheet.Range(″A2″).value=200

例15 减少对象的激活和选择
如果你通过录制宏来学*VBA的,则你的VBA程序里一定充满了对 象的激活和选择,例如Workbooks(XXX).Activate、Sheets(XXX). Select、Range(XXX).Select等,但事实上大多数情况下这些操作不是必 需的。 例如: Sheets(″Sheet3″).Select Range(″A1″).value=100 Range(″A2″).value=200 可改为:

248

CHAPTER 8
WithSheets(″Sheet3″) .Range(″A1″).value=100 .Range(″A2″).value=200 EndWith

宏与VBA编程运用

例16 在VBA程序中关闭屏幕更新
关闭屏幕更新是提高VBA程序运行速度的最有效的方法,可以缩短运 行时间2/3左右。关闭屏幕更新的方法: Application.ScreenUpdate=False 请不要忘记VBA程序运行结束时再将该值设回来: Application.ScreenUpdate=True



热文推荐
猜你喜欢
友情链接: 医学资料大全 农林牧渔 幼儿教育心得 小学教育 中学 高中 职业教育 成人教育 大学资料 求职职场 职场文档 总结汇报