Excel VBA入门(6) - Worksheet对象常用方法事件

1. 激活工作表 Active

    Dim ws As Worksheet    Set ws = Application.WorkBooks(1).Worksheets(2)    ws.Activate

激活了第一个工作簿的Sheet2

2. 复制工作表 Copy([before], [after])

将当前工作表复制一份, 名字为"当前工作表名字(2)"

    Dim ws As Worksheet    Set ws = Application.ActiveWorkbook.Worksheets(1)    ws.Copy after:=Worksheets(1)

该代码将Sheet1的内容拷贝到新表Sheet1(2)

如果不指定before或after, 同样会生成一个新表, 注意, before和after不能同时使用

另外, 工作表的复制可以跨工作簿之间进行

    Dim wbSrc As Workbook    Dim wbDes As Workbook    Dim ws As Worksheet    Set wbSrc = Application.WorkBooks(1)    Set wbDes = Application.WorkBooks.Add    Set ws = wbSrc.Worksheets(1)    ws.Copy after:=wbDes.Worksheets(1)

将当前工作表的内容复制到新工作簿的第2个工作表

3. 将剪贴板的内容粘贴到工作表Paste([destination], [link])

    Dim ws As Worksheet    Set ws = Application.ActiveWorkbook.ActiveSheet    ws.range("A1:A3").Copy    ws.Paste destination:=ws.range("F1:F3")

先将A1:A3的内容复制到剪贴板, 然后利用Paste方法, 粘贴到F1:F3区域, 亦或者直接:

    ws.range("A1:A3").Copy    ws.Paste destination:=ws.range("F1")

Worksheet事件:

和Workbook的事件类似, 在"工程资源管理器"中, 双击一个工作表, 在右边代码区上面选择Worksheet, 然后再选择相应的事件

选择一个事件会自动列出事件代码

常用的操作工作表的方法

1. 访问工作表

两种方式: a. 根据索引号(从1开始) b.根据工作表名称

    Dim wb As Workbook    Dim ws As Worksheet    Dim wsCount As Integer    Dim i As Integer    Dim sheetnames() As String    Set wb = Application.WorkBooks(1)    wb.Activate    wsCount = wb.Worksheets.Count    ReDim sheetnames(1 To wsCount)        PrintInfo "当前工作簿共包含" & CStr(wsCount) & "个工作表"        For i = 1 To wsCount        Set ws = wb.Worksheets(i)        Debug.Print Space(5) & ws.name        sheetnames(i) = ws.name    Next        Debug.Print    Debug.Print "使用Sheets集合按名称访问工作表"    For i = 1 To wsCount        Set ws = wb.Worksheets(sheetnames(i))        Debug.Print Space(5) & ws.name    Next        Set ws = Nothing    Set wb = Nothing

例子比较简单, 说明一下Space(5)的意思是五个空格, CStr()是把参数转换为字符串

核心就是 Worksheets(1) 和Worksheets("Sheet1") 是等效的 (默认没有改工作表名字而且没有移动工作表顺序的情况下)

另外在遍历工作表的时候使用的是Worksheets属性, 如果使用Sheets属性则需要判断工作表的类型是普通工作表还是图表工作表

根据Type属性判断:   If ws.Type = xlWorksheet Then 

2. 判断工作表是否存在

判断工作表是否存在就是用指定的名称遍历所有工作表, 没什么难点

    Dim wb As Workbook    Dim ws As Worksheet    Dim i As Integer    Dim count As Integer    Dim flag As Boolean    Dim findName As String    findName = "Sheet7"    Set wb = Application.ActiveWorkbook    count = wb.Worksheets.count    flag = False    For i = 1 To count        If wb.Worksheets(i).name = findName Then            flag = True            Exit For        End If    Next    If flag Then        MsgBox "存在" & findName    Else        MsgBox "不存在" & findName    End If        Set ws = Nothing    Set wb = Nothing

这段代码不用解释了

3.新建工作表 Application.ActiveWorkbook.Worksheets.Add([Before], [After], [Count], [Type]) As Object

新建工作表和之前的新建工作簿类似

    Dim ws As Worksheet    Set ws = Worksheets.Add    Debug.Print ws.name

完整写法Set ws = Application.ActiveWorkbook.Worksheets.Add(before:=Worksheets(8), count:=2, Type:=xlWorksheet)

意思是在第8个表前加入两个工作表

同样的Before和After不能同时使用

4. 重命名工作表 

直接设置工作表的name属性即可, 但是要先判断该名称是否已经存在, 否则会报错

为了简单说明, 这里就不作判断了

    Dim ws As Worksheet    Set ws = Application.WorkBooks(1).Worksheets(1)    ws.name = "SheeT1"

将"Sheet1"重命名为了"SheeT1"

5. 移动工作表

    Dim wb As Workbook    Dim ws As Worksheet    Set wb = Application.WorkBooks(1)    wb.Activate    Set ws = wb.Worksheets(1)    ws.Move after:=ws.Next        Set wb = Nothing    Set ws = Nothing

道理和复制一样, ws.Move after:=ws.Next 将第一个工作表向后移动一次

同理, 移动也可以跨工作簿进行

    Dim wbSrc As Workbook    Dim wbDes As Workbook    Dim ws As Worksheet    Set wbSrc = Application.WorkBooks(1)    Set wbDes = Application.WorkBooks.Add    Set ws = wbSrc.Worksheets(1)    ws.Move after:=wbDes.Worksheets(1)

将当前工作簿的Sheet1 移动到新的工作簿的Sheet1后

6. 删除工作表

注意: 删除前请保存重要数据

    Dim wb As Workbook    Dim ws As Worksheet    Dim sheetName As String    Dim count As Integer    Set wb = Application.WorkBooks(1)    Set ws = wb.Worksheets(1)    sheetName = ws.name    count = wb.Worksheets.count        If count > 1 Then        Application.DisplayAlerts = False        ws.Delete        MsgBox "成功删除" & sheetName, vbOKOnly, "删除工作表"        Application.DisplayAlerts = True    Else        MsgBox "工作表" & sheetName & "是工作簿的最后一张表, 无法删除", vbCritical, "删除工作表"    End If    Set wb = Nothing    Set ws = Nothing