四时宝库

程序员的知识宝库

Excel VBA 常见技巧21-工作表的添加与删除

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

#001 Sub Addsh()

#002 Dim Sh As Worksheet

#003 With Worksheets

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

#005 Sh.Name = "数据"

#006 End With

#007 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行代码将添加的工作表重命名为“数据”。

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

#001 Sub Addsh_2()

#002 Dim i As Integer

#003 Dim sh As Worksheet

#004 For i = 1 To 10

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

#006 sh.Name = i

#007 Next

#008 End Sub

代码解析:

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

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

图 25?1 运行错误提示

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

#001 Sub Delsh()

#002 Dim sh As Worksheet

#003 For Each sh In ThisWorkbook.Sheets

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

#005 Application.DisplayAlerts = False

#006 sh.Delete

#007 Application.DisplayAlerts = True

#008 End If

#009 Next

#010 End Sub

代码解析:

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

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

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

图 25?2 系统警告对话框

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

expression.Delete

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

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

对于单张工作表可以使用下面的代码。

#001 Sub Addsh_3()

#002 Dim Sh As Worksheet

#003 For Each Sh In Worksheets

#004 If Sh.Name = "数据" Then

#005 MsgBox "工作簿中已有""数据""工作表,不能重复添加!"

#006 Exit Sub

#007 End If

#008 Next

#009 With Worksheets

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

#011 Sh.Name = "数据"

#012 End With

#013 End Sub

代码解析:

Addsh_3过程在使用Add方法在工作簿中新建“数据”工作表时首先判断工作簿中是否存在“数据”工作表,如果已存在“数据”工作表则不运行添加工作表的代码而只显示一个消息框进行提示,如图 25?3所示。

图 25?3 工作表同名提示

还可以使用错误处理语句来绕过错误,如下面的代码所示。

#001 Sub Addsh_4()

#002 Dim sh As Worksheet

#003 On Error GoTo line

#004 With Worksheets

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

#006 sh.Name = "数据"

#007 End With

#008 Exit Sub

#009 line:

#010 MsgBox "工作簿中已有""数据""工作表,不能重复添加!"

#011 Application.DisplayAlerts = False

#012 Worksheets(Worksheets.Count).Delete

#013 Application.DisplayAlerts = True

#014 End Sub

代码解析:

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

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

#001 Sub Addsh_5()

#002 Dim i As Integer, arr

#003 Dim sh As Worksheet

#004 On Error Resume Next

#005 arr = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

#006 For i = 0 To UBound(arr)

#007 With Worksheets

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

#009 sh.Name = arr(i)

#010 End With

#011 Next

#012 Application.DisplayAlerts = False

#013 For Each sh In Worksheets

#014 If sh.Name Like "Sheet*" Then sh.Delete

#015 Next

#016 Application.DisplayAlerts = True

#017 End Sub

代码解析:

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

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

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

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

发表评论:

控制面板
您好,欢迎到访网站!
  查看权限
网站分类
最新留言
    友情链接