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

【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.Move after:=Sheets(Sheets.Count)'移动新表
End Sub
______________________________________________________
对于“Worksheets.Add”这类新建对象，是不能多次引用的，如果直接对对象赋值，那么每个赋值都会产生一个新的对象。

例如下面代码：
_____________________________________________
Sub 建立总表且移至最后2()
= "总表"'设置新表的名称
Worksheets.Add.Move after:=Sheets(Sheets.Count)'移动新表
End Sub
______________________________________________
该过程中两次“Worksheets.Add”代表了两个对象，会产生两个新工作表。

如果改用With语句则可以完美地处理以上所提到的两个问题。

With语句如下：
Sub 建立总表且移至最后3()
With Worksheets.Add'创建一个工作表对象,后续可以多次调用这个对象
.Name = "总表"'设置With对象的名称
.Move after:=Sheets(Sheets.Count)'移动With对象到最后
End With
End Sub
________________________________________________
该过程中With语句已经生成了一个新的对象，而后续的代码中可以随意调用这个对象，不再需要变量，也不会产生多个新工作表。

有效性设置.et。