50.常用语句透析

1、【VBA 教材】 50. 常用语句透析（二十四）贡献者： 罗刚君日期： 2009-12-01 阅读： 1320相关标签： pub2007VBAWith 语句在前面的章节中已经多次提到，本节开始对With 语句的用途、语法及常见错误进行分析。With 语可以在一个单一对象或一个用户定义类型上执行一系列的语句。它的主要作用是简化代码、提升执行速度及减少变量的使用。1. 简化代码例如某个对象在一个过程需要多次调用，那么With 语句可以在编写代码时只写一次，却可以达成多次效用的效果。这对于代码的简化有很大的作用。例如对单元格添加数据有效性，不用With 语句的代码如下：_Sub 对 B1 设置数据有

2、效性1()Worksheets(生产表 ).B1.Validation.DeleteWorksheets(生产表 ).B1.Validation.Add Type:=xlValidateList, Formula1:=$A$1:$A$8Worksheets(生产表 ).B1.Validation.IgnoreBlank = trueWorksheets(生产表 ).B1.Validation.InCellDropdown = trueWorksheets(生产表 ).B1.Validation.InputTitle = 提示 Worksheets(生产表 ).B1.Validation.Inp

3、utMessage = 数据来源是A1:A8Worksheets(生产表 ).B1.Validation.ShowInput = trueWorksheets(生产表 ).B1.Validation.ShowError = trueEnd Sub_编辑版 word上面代码比较容易阅读，然而写法过于臃肿。用With 语句简化后代码如下：Sub 对 B1 设置数据有效性2()With Worksheets(生产表 ).B1.Validation.Delete.Add Type:=xlValidateList, Formula1:=$A$1:$A$8.IgnoreBlank = true.InCel

4、lDropdown = true.InputTitle = 提示 .InputMessage = 数据来源是 A1:A8.ShowInput = true.ShowError = trueEnd WithEnd Sub_很显然 With 语句在简化代码中有着不可小觑的贡献。2. 提升速度仍然以上面的两段代码为例，稍加修改，进行比较，可以证实With 语句对循环语句可以大大提速。_Sub 对 B1 设置数据有效性1()Dim tim As Longtim = Timer编辑版 wordFor i = 1 To 1000Worksheets(生产表 ).B1.Validation.DeleteWo

5、rksheets(生产表 ).B1.Validation.Add Type:=xlValidateList, Formula1: =$A$1:$A$8Worksheets(生产表 ).B1.Validation.IgnoreBlank = trueWorksheets(生产表 ).B1.Validation.InCellDropdown = trueWorksheets(生产表 ).B1.Validation.InputTitle = 提示 Worksheets(生产表 ).B1.Validation.InputMessage = 数据来源是A1:A8Worksheets(生产表 ).B1.V

6、alidation.ShowInput = trueWorksheets(生产表 ).B1.Validation.ShowError = trueNext iMsgBox Format(Timer - tim, 0.00) & 秒End Sub_Sub 对 B1 设置数据有效性2()Dim tim As Longtim = TimerFor i = 1 To 1000With Worksheets(生产表 ).B1.Validation.Delete.Add Type:=xlValidateList, Formula1:=$A$1:$A$8.IgnoreBlank = true.InCellD

7、ropdown = true.InputTitle = 提示 编辑版 word.InputMessage = 数据来源是 A1:A8.ShowInput = true.ShowError = trueEnd WithNext iMsgBox Format(Timer - tim, 0.00) & 秒End Sub_两段都是对 B1 单元格添加有效性设置，为了更容易看出区别，将程序循环执行1000次。分别执行以上两段代码，它们最后所报告的执行时间会差异两倍左右。3. 减少变量有一种特殊的对象，无法多次引用。如果第二次调用它，那么将产生不同的对象，而不是第一次引用的对象。这主要在新建一个对象时会涉

8、及。通常解决这个问题是采用变量来取代它，那么以后调用这个变量即可。但程序中变量太多会对程序的执行效率产生副作用。那么剩下的方案就是使用With 语句了。例如在工作簿中建立一个新工作表，并进行命名、移动处理。使用变量的处理方式是：_Sub 建立总表且移至最后 ()Dim sht As Worksheet声明一个工作表对象Set sht = Worksheets.Add将新表赋予与变量sht.Name = 总表 设置新表的名称sht.Move after:=Sheets(Sheets.Count)移动新表编辑版 wordEnd Sub_对于“Worksheets.Add”这类新建对象，是不能多次引

9、用的，如果直接对对象赋值，那么每个赋值都会产生一个新的对象。例如下面代码：_Sub 建立总表且移至最后2()Worksheets.Add.Name = 总表 设置新表的名称Worksheets.Add.Move after:=Sheets(Sheets.Count)移动新表End Sub_该过程中两次“Worksheets.Add”代表了两个对象，会产生两个新工作表。如果改用 With 语句则可以完美地处理以上所提到的两个问题。With 语句如下：Sub 建立总表且移至最后3()With Worksheets.Add创建一个工作表对象,后续可以多次调用这个对象.Name = 总表 设置 With 对象的名称.Move