【VBA教材】50.常用语句透析(二十四).doc

【VBA教材】50.常用语句透析（二十四）贡献者：罗刚君 日期：2009-12-01阅读：1320 相关标签：pub2007VBA With语句在前面的章节中已经多次提到，本节开始对With语句的用途、语法及常见错误进行分析。 With语可以在一个单一对象或一个用户定义类型上执行一系列的语句。它的主要作用是简化代码、提升执行速度及减少变量的使用。 1. 简化代码 例如某个对象在一个过程需要多次调用，那么With语句可以在编写代码时只写一次，却可以达成多次效用的效果。这对于代码的简化有很大的作用。 例如对单元格添加数据有效性，不用With语句的代码如下： _ Sub 对B1设置数据有效性1() Worksheets(生产表).B1.Validation.Delete Worksheets(生产表).B1.Validation.Add Type:=xlValidateList, Formula1:=$A$1:$A$8 Worksheets(生产表).B1.Validation.IgnoreBlank = true Worksheets(生产表).B1.Validation.InCellDropdown = true Worksheets(生产表).B1.Validation.InputTitle = 提示 Worksheets(生产表).B1.Validation.InputMessage = 数据来源是A1:A8 Worksheets(生产表).B1.Validation.ShowInput = true Worksheets(生产表).B1.Validation.ShowError = true End Sub _ 上面代码比较容易阅读，然而写法过于臃肿。用With语句简化后代码如下： Sub 对B1设置数据有效性2() With Worksheets(生产表).B1.Validation .Delete .Add Type:=xlValidateList, Formula1:=$A$1:$A$8 .IgnoreBlank = true .InCellDropdown = true .InputTitle = 提示 .InputMessage = 数据来源是A1:A8 .ShowInput = true .ShowError = true End With End Sub _ 很显然With语句在简化代码中有着不可小觑的贡献。 2. 提升速度 仍然以上面的两段代码为例，稍加修改，进行比较，可以证实With语句对循环语句可以大大提速。 _ Sub 对B1设置数据有效性1() Dim tim As Long tim = Timer For i = 1 To 1000 Worksheets(生产表).B1.Validation.Delete Worksheets(生产表).B1.Validation.Add Type:=xlValidateList, Formula1: =$A$1:$A$8 Worksheets(生产表).B1.Validation.IgnoreBlank = true Worksheets(生产表).B1.Validation.InCellDropdown = true Worksheets(生产表).B1.Validation.InputTitle = 提示 Worksheets(生产表).B1.Validation.InputMessage = 数据来源是A1:A8 Worksheets(生产表).B1.Validation.ShowInput = true Worksheets(生产表).B1.Validation.ShowError = true Next i MsgBox Format(Timer - tim, 0.00) & 秒 End Sub _ Sub 对B1设置数据有效性2() Dim tim As Long tim = Timer For i = 1 To 1000 With Worksheets(生产表).B1.Validation .Delete .Add Type:=xlValidateList, Formula1:=$A$1:$A$8 .IgnoreBlank = true .InCellDropdown = true .InputTitle = 提示 .InputMessage = 数据来源是A1:A8 .ShowInput = true .ShowError = true End With Next i MsgBox Format(Timer - tim, 0.00) & 秒 End Sub _ 两段都是对B1单元格添加有效性设置，为了更容易看出区别，将程序循环执行1000次。分别执行以上两段代码，它们最后所报告的执行时间会差异两倍左右。 3. 减少变量 有一种特殊的对象，无法多次引用。如果第二次调用它，那么将产生不同的对象，而不是第一次引用的对象。这主要在新建一个对象时会涉及。 通常解决这个问题是采用变量来取代它，那么以后调用这个变量即可。但程序中变量太多会对程序的执行效率产生副作用。那么剩下的方案就是使用With语句了。 例如在工作簿中建立一个新工作表，并进行命名、移动处理。使用变量的处理方式是： _ Sub 建立总表且移至最后() Dim sht As Worksheet声明一个工作表对象 Set sht = Worksheets.Add将新表赋予与变量 sht.Name = 总表设置新表的名称 sht.Move after:=Sheets(Sheets.Count)移动新表 End Sub _ 对于“Worksheets.Add”这类新建对象，是不能多次引用的，如果直接对对象赋值，那么每个赋值都会产生一个新的对象。例如下面代码： _ Sub 建立总表且移至最后2() Worksheets.Add.Name = 总表设置新表的名称 Worksheets.Add.Move after:=Sheets(Sheets.Count)移动新表 End Sub _ 该过程中两次“Worksheets.Add”代表了两个对象，会产生两个新工作表。 如果改用With语句则可以完美地处理以上所提到的两个问题。With语句如下： Sub 建立总表且移至最后3() With Worksheets.Add创建一个工作表对象,后续可以多次调用这个对象 .Name = 总表设