VBA常用代码解析(第四讲）

下面应一位粉丝的要求，开始把我以前的一些学习资料整理了一下，然后分20期发给各位有需要的朋友，您也可以索取源文件，邮箱：[e-mail]！

022 选择工作表的方法

在VBA中需要激活或者选择某个工作表时使用Select方法或Activate方法，如下面的代码所示。

Sub SelectSh()

Worksheets('Sheet2').Select

End Sub

Sub ActivateSh()

Worksheets('Sheet2').Activate

End Sub

代码解析：

SelectSh过程使用Select方法选择“Sheet2”工作表，而ActivateSh过程则使用Activate方法选择“Sheet2”工作表，从表面看两者的作用是相同的，但是如果“Sheet2”工作表是隐藏的，Activate方法可以正常运行，而Select方法将会出现错误。如果需要同时选中工作簿中的所有工作表，则只能使用Select方法而不能使用Activate方法，如下面的代码所示。

Sub SelectShs()

DimShs As Worksheet

ForEach Shs In Worksheets

Shs.Select False

Next

End Sub

Sub SelectSheets()

Worksheets.Select

End Sub

Sub ArraySheets()

Worksheets(Array(1，2，3)).Select

End Sub

代码解析：

SelectShs过程遍历工作表并使用带参数的Select方法选中所有工作表。应用于Worksheet对象的Select方法的语法如下：

Select(Replace)

参数Replace是可选的。如果该值为True，则用指定对象替代当前选定对象。如果该值为False，则延伸当前选定对象以包括任何以前选定的对象。

SelectSheets过程使用Worksheets集合的Select方法选中集合中所有的对象。

ArraySheets过程使用Array 函数返回工作簿中的前三张工作表并使用Worksheets集合的Select方法选中前三张工作表。

▲023 遍历工作表的方法

在Excel应用中经常需要遍历工作簿中所有的工作表，有以下两种方法可以实现。

023-1 使用For...Next 语句

使用For...Next 语句遍历工作簿中所有的工作表，如下面的代码所示。

Sub ShCount1()

Dimc As Integer

Dimi As Integer

Dims As String

c =Worksheets.Count

Fori = 1 To c

s = s & Worksheets(i).Name & Chr(13)

Next

MsgBox'工作簿中含有以下工作表:' & Chr(13) & s

End Sub

代码解析：

ShCount1过程使用For...Next 语句遍历工作簿中所有的工作表，并用消息框显示所有的工作表名称。

第5行代码根据Worksheet对象的Count属性返回工作簿中工作表的数量赋给变量c。应用于Worksheet对象的Count属性返回Worksheets集合中工作表的数量，语法如下：

expression.Count

第6行代码开始For...Next语句循环。For...Next 语句以指定次数来重复执行一组语句，语法如下：

For counter = startTo end [Step step]

[statements]

[Exit For]

[statements]

Next [counter]

参数counter是必需的，用做循环计数器的数值变量。

参数start是必需的，循环计数器的初值。

参数end是必需的，循环计数器的终值。

参数step是可选的，环计数器的步长，缺省值为 1。

参数statements是可选的，放在For和Next之间的一条或多条语句，它们将被执行指定的次数。

第7行代码在For...Next循环中根据工作表的索引号取得所有工作表的名称赋给字符串变量s。

023-2 使用For Each...Next 语句

使用For Each...Next语句遍历工作簿中所有的工作表，如下面的代码所示。

Sub ShCount2()

DimSh As Worksheet

Dims As String

ForEach Sh In Worksheets

s = s & Sh.Name & Chr(13)

Next

MsgBox'工作簿中含有以下工作表:' & Chr(13) & s

End Sub

代码解析：

ShCount2过程使用For Each...Next语句遍历工作簿中所有的工作表，并用消息框显示所有工作表名称。

第4行代码使用For Each...Next语句遍历Worksheets集合中所有元素。For Each...Next语句针对一个数组或集合中的每个元素，重复执行一组语句，语法如下：

For Each element InGroup

[statements]

[Exit For]

[statements]

Next [element]

参数element是必需的，用来遍历集合或数组中所有元素的变量。

参数group是必需的，对象集合或数组的名称。

参数statements是可选的，针对对象集合或数组中的每一项执行的一条或多条语句。

第5行代码将返回的工作表的名称赋给字符串变量s。

▲024 在工作表中上下翻页

如果需要在工作簿的工作表中进行上下翻页，可以使用下面的代码。

Sub DownSheet()

Dimi As Integer

i =Worksheets.Count

If ActiveSheet.Index< i Then

Worksheets(ActiveSheet.Index + 1).Activate

Else

Worksheets(1).Activate

EndIf

End Sub

Sub UpSheet()

Dimi As Integer

i =Worksheets.Count

If ActiveSheet.Index> 1 Then

Worksheets(ActiveSheet.Index - 1).Activate

Else

Worksheets(i).Activate

EndIf

End Sub

代码解析：

DownSheet过程向下翻页，第3、12行代码使用Worksheets对象的Count属性取得工作表的数目，第4行到第7行代码根据Index属性判断活动工作表是否是工作簿中的最后一张工作表。如果活动工作表不是最后一张工作表则激活活动工作表的下一张工作表，否则激活第一张工作表。

UpSheet过程向上翻页，第13行到第16行代码根据Index属性判断活动工作表是否是工作簿中的第一张工作表。如果活动工作表不是第一张工作表则激活活动工作表的上一张工作表，否则激活最后一张工作表。

▲025 工作表的添加与删除

在工作簿中添加工作表使用Add方法，如下面的代码所示。

Sub Addsh()

DimSh As Worksheet

WithWorksheets

Set Sh = .Add(after:=Worksheets(.Count))

Sh.Name = '数据'

EndWith

End Sub

代码解析：

Addsh过程使用Add方法在工作簿中新建“数据”工作表。

第2行代码声明变量Sh为工作表对象。

第4行行代码使用Add方法在工作簿的最后新建“数据”工作表。

Add 方法应用于Sheets和Worksheets对象时新建工作表、图表或宏表，语法如下：

expression.Add(Before，After，Count，Type)

参数Before是可选的，指定工作表对象，新建的工作表将置于此工作表之前。

参数After是可选的，指定工作表对象，新建的工作表将置于此工作表之后。

如果Before和 After两者均省略，则新建的工作表将插入到活动工作表之前。

参数Count可选，要新建的工作表的数目。默认值为 1。

参数Type可选，指定新建的工作表类型。

第5行代码将添加的工作表重命名为“数据”。

如果需要在工作簿中批量添加工作表，可以使用下面的代码。

Sub Addsh_2()

Dimi As Integer

Dimsh As Worksheet

Fori = 1 To 10

Set sh = Sheets.Add(after:=Sheets(Sheets.Count))

sh.Name = i

Next

End Sub

代码解析：

Addsh_2过程使用For...Next 语句和Add方法在工作簿中添加10张工作表并将添加的工作表依次重命名。

在使用以上代码往工作簿中添加工作表时，如果工作簿中已存在相同名称的工作表，运行时会发生错误，代码中断。

为了避免此错误的发生，可以在添加前先删除所有的工作表，如下面的代码所示。

Sub Delsh()

Dimsh As Worksheet

ForEach sh In ThisWorkbook.Sheets

If sh.Name <> '工作表的添加与删除' Then

Application.DisplayAlerts = False

sh.Delete

Application.DisplayAlerts = True

End If

Next

End Sub

代码解析：

Delsh过程使用Delete方法删除工作簿中除了“工作表的添加与删除”工作表以外所有的工作表。

第3行代码使用For Each...Next语句遍历代码所在工作簿中所有的工作表。

第4行到第7行代码判断工作表名称是否为“工作表的添加与删除”，如果不是则使用Delete方法删除。其中第5行代码将Application对象的DisplayAlerts属性设置为False，使删除时不显示系统警告对话框。

第6行代码使用Delete方法删除工作表，应用于工作表对象的Delete方法删除指定的对象，语法如下：

expression.Delete

参数expression是必需的，该表达式返回“应用于”列表中的对象之一。

在运行添加工作表代码前先删除工作簿中的工作表虽然可以避免同名错误，但也可能误删除有用的工作表，因此更为严谨的方法是在添加前先判断工作簿中是否存在相同名称的工作表，然后再进行下一步的操作。对于单张工作表可以使用下面的代码。

Sub Addsh_3()

DimSh As Worksheet

ForEach Sh In Worksheets

If Sh.Name = '数据' Then

MsgBox '工作簿中已有”“数据”“工作表，不能重复添加!'

Exit Sub

End If

Next

WithWorksheets

Set Sh = .Add(after:=Worksheets(.Count))

Sh.Name = '数据'

EndWith

End Sub

代码解析：

Addsh_3过程在使用Add方法在工作簿中新建“数据”工作表时首先判断工作簿中是否存在“数据”工作表，如果已存在“数据”工作表则不运行添加工作表的代码而只显示一个消息框进行提示。还可以使用错误处理语句来绕过错误，如下面的代码所示。

Sub Addsh_4()

Dimsh As Worksheet

On ErrorGoTo line

WithWorksheets

Set sh = .Add(after:=Worksheets(.Count))

sh.Name = '数据'

EndWith

ExitSub

line:

MsgBox'工作簿中已有”“数据”“工作表，不能重复添加!'

Application.DisplayAlerts= False

Worksheets(Worksheets.Count).Delete

Application.DisplayAlerts= True

End Sub

代码解析：

Addsh_4过程是先使用Add方法在工作簿中新建“数据”工作表，如果工作簿中已存在同名的工作表则使用GoTo语句转移到指定的line行处进行提示并删除已添加还没有重命名的工作表，也就是工作簿中最后一张工作表。

如果是批量添加工作表，使用上述方法时，添加工作表和已有工作表重名时，后面即使没有重名的工作表也不能添加，所以应先使用错误处理语句忽略错误，待全部添加好以后再删除多余的工作表，如下面的代码所示。

Sub Addsh_5()

Dimi As Integer，arr

Dimsh As Worksheet

On ErrorResume Next

arr= Array(1，2，3，4，5，6，7，8，9，10)

Fori = 0 To UBound(arr)

With Worksheets

Set sh = .Add(after:=Sheets(.Count))

sh.Name = arr(i)

End With

Next

Application.DisplayAlerts= False

ForEach sh In Worksheets

If sh.Name Like 'Sheet*' Thensh.Delete

Next

Application.DisplayAlerts= True

End Sub

代码解析：

Addsh_5过程使用Add方法在工作簿中添加10张工作表并重新命名为1到10，如果工作簿中已有相同名称的工作表则不添加。

第4行代码错误处理语句，当发生重名错误时忽略错误，继续添加工作表。

第5行到第11行代码在工作簿中添加10张工作表并重新命名为1到10，如果工作簿中已有相同名称的工作表则忽略重命名时发生的错误，此时工作簿中添加的工作表会以系统赋与的名称命名，如“Sheet1”。

第12行到第15行代码使用For Each...Next语句遍历工作簿中所有的工作表，将工作簿中凡是以“Sheet”开头的工作表删除。