Excel VBA开发必须了解的与工作簿Workbook及工作表Worksheet相关的5个对象详解（附实用例程）

目录

0 前言     

1 Workbook及Worksheet相关对象的定义及常用事件和方法

1.1 Workbooks对象

1.2 Workbook对象

1.3 Sheets对象

1.4 Worksheets对象

1.5 Worksheet对象

2  Workbook和Worksheet相关对象的编程示例

2.1遍历所有打开的工作簿，并将符合某些条件的xlsx工作簿关闭。

2.2 利用Activate事件遍历并对比工作簿中的Worksheets和Sheets对象

2.3 利用Worksheet的Selectionchange事件读取选择区域的信息

2.4 利用Workbook的Sheetchange事件禁止修订某个Worksheet

---

0 前言     

        每一个使用过Excel的用户都了解工作簿和工作表这两个概念。工作簿，简单地说就是与一个excel文件对应，一个打开的excel文件就是一个工作簿。而工作表是工作簿的一部分，是一个独立的数据区域。一个工作簿中可以有很多工作表，在较老的office版本中，一个工作簿最多可允许建立255个工作表，但是目前最新的Office，理论上支持在一个工作簿中建立无限个工作表，只受电脑的硬件资源的限制。

        Excel VBA开发中，工作簿集合WorkBooks ，工作簿Workbook，图与表集合Sheets，工作表集合Worksheets，工作表Worksheet，这5个对象是最基础和最常用的对象。几乎每一个自己开发的函数和过程都离不开这5个对象。

        本文首先简要介绍这5个对象的定义，以及他们各自的最常用事件、方法、属性。然后通过几个小程序，来展示他们的用法，以便读者能快速的参考引用。但是随着开发工作的深入，可能读者会希望了解有关这5个对象的更多细节，可以进一步参考Microsoft提供的VBA文档。4个示例程序都是我精心设计的，基本涵盖了与workbook和worksheet相关对象的最常用事件和方法，并且也是我们开发VBA应用时经常需要用到的功能。

遍历所有打开的工作簿，并将符合某些条件的xlsx工作簿关闭。

1. 遍历所有打开的工作簿，并将符合某些条件的xlsx工作簿关闭。
2. 利用Activate事件遍历并对比工作簿中的Worksheets和Sheets对象
3. 利用Worksheet的Selectionchange事件读取选择区域的信息
4. 利用Workbook的Sheetchange事件禁止修订某个Worksheet

1 Workbook及Worksheet相关对象的定义及常用事件和方法

1.1 Workbooks对象

        Workbooks对象是一个集合，指代的是当前Excel程序打开的所有工作簿Workbook的集合。Workbooks对象因为是集合，所以没有事件，只有方法。它常用的属性有Count和Item，常用的方法有Open，Close，Add。

表1 Workbooks对象的常用方法

方法	功能
Add	在当前集合增加一个新的空白工作簿
Open	打开一个已存在的excel文件，并在当前集合中为这个文件创建一个工作簿
Close	关闭当前已经打开的所有工作簿

1.2 Workbook对象

        Workbook对象是Workbooks集合的成员，代表一个Excel 工作簿。我们也可以使用集合对象workbooks(index)来表示当前打开工作簿集合中的第index个工作簿。可以说，workbooks(index)也是一个workbook对象。

        Workbook对象常用的属性有Name,Path,Password，常用的事件有Open，Activate，Deactivate，BeforeClose，AfterSave，BeforeSave

表2 Workbook对象常用的事件

事件	发生的时机
Open	打开工作簿的时候
Activate	工作簿激活的时候
DeActivate	工作簿停用的时候
BeforeClose	工作簿关闭之前
BeforeSave	工作簿保存之前
AfterSave	工作簿保存之后

        Workbook对象常用的方法有Activate，Save， Close,，SaveAs，Protect。

表3 Workbook对象常用的方法

方法	功能
Activate	激活工作簿
Save	保存工作簿
Close	关闭工作簿，后面跟False时表示不保存并关闭，后面跟True时，表示保存并关闭
SaveAs	将工作簿另存为其他文件
Protect	保护工作簿使其不被修改

 

1.3 Sheets对象

        Sheets对象表示指定的或活动工作簿中所有工作表的集合。跟workbooks对象一样，这个对象也是一个集合，尤其需要引起重视的是，Sheets 集合可以包含 Chart 或 Worksheet 对象。

        作为一个集合类型的对象，Sheets对象也没有事件，只有属性和方法。常用的属性有Count，Item，Visible，常用的方法有Add，Copy，Delete，Select.

表4 Sheets对象常用的方法

方法	功能
Add	创建新的工作表、图表或宏工作表。 新工作表成为活动工作表。
Copy	将工作表复制到工作簿中的另一位置。
Delete	删除工作表、图表或宏工作表
Select	选择工作表、图表或宏工作表

1.4 Worksheets对象

        Worksheets对象特指指定工作簿或活动工作簿中所有 Worksheet 对象的集合。 每个 Worksheet 对象都代表一个工作表。跟Workbooks对象和Sheets对象一样，这个对象也是一个集合，但是这个集合是Sheets集合的子集，只包含Worksheet对象，不包含Chart对象。

        作为一个集合类型的对象，Worksheets对象也没有事件，只有属性和方法。常用的属性有Count，Item，Visible，常用的方法有Add，Copy，Delete，Select

表5 Worksheets对象常用的方法

方法	功能
Add	创建新的工作表,新工作表成为活动工作表。
Copy	将工作表复制到工作簿中的另一位置。
Delete	删除工作表
Select	选择工作表

1.5 Worksheet对象

        Worksheet对象代表一个工作表，它既是Worksheets集合的成员，也是Sheets的成员。可以通过Worksheets(index)或者Sheets(index)来访问。

        常用的属性有Name，UsedRange，Rows，Columns，Visible，常用的事件有Activate，Change，Deactivate，SelectionChange。

表6 Worksheet对象常用的事件

事件	发生的时机
Activate	激活工作表时发生此事件。注意，新建窗口时不发生此事件
Change	当用户更改工作表中的单元格，或外部链接引起单元格的更改时发生此事件。
Deactivate	工作表被停用时发生此事件。
Selectionchange	当工作表上的选定区域发生改变时发生此事件。

        常用的方法有Activate，Calulate，Select，Copy，Move等

表7 Worksheet对象常用的方法

方法	功能
Activate	使当前工作表成为活动工作表。
Calculate	计算某张特定工作表或工作表指定区域中的单元格
Select	选择某个工作表
Copy	将工作表复制到某个工作簿（可以是当前或者其他工作簿）的另一个位置。
Move	移动工作表到某个工作簿中

2  Workbook和Worksheet相关对象的编程示例

2.1遍历所有打开的工作簿，并将符合某些条件的xlsx工作簿关闭。

        我们可以使用workbooks对象的Count属性，遍历当前打开的所有工作簿，然后利用workbook对象的Name属性，找到符合某些特定条件的xlsx工作簿，并利用workbook对象的Close方法，将这些工作簿关闭。

        本例虽然简单，但实际上在VBA开发中经常需要用到，当我们结束某个函数时，如果时意外退出，很多工作簿都可能没有关闭，这时候会增加系统负担，同时也有文件被无意中修改的风险。所以我们在函数的错误处理模块中，可以采用本例程的方法，对本函数打开缺因为意外退出而没有关闭的工作簿关闭。

        采用本例时，注意For循环需要采用递减的方式执行，因为关闭工作簿之后会导致workbooks对象的count属性减小，如果采用递增的方式去关闭文件，做不到遍历所有已打开的工作簿。

For i = Workbooks.Count To 1 Step -1
        If Right(Workbooks(i).Name, 4) = "xlsx" Then
            If Left(Workbooks(i).Name, 1) = "A" Then
                Workbooks(i).Close False
            ElseIf InStr(Workbooks(i).Name, "PO") Then
                Workbooks(i).Close True
            End If
        End If         
    Next

2.2 利用Activate事件遍历并对比工作簿中的Worksheets和Sheets对象

        本示例利用Worksheet的Activate事件，当激活工作表时，使用Sheets对象的Count属性，遍历Sheets集合，并利用Worksheets对象的Count属性，遍历Worksheets集合，最后在对话框中显示当前工作簿中所有的Sheets集合和Worksheets集合的包含对象。

        读者可以通过这个例程，感受到Activate事件的启动时机，并能够非常清楚的看到Sheets和Worksheets的区别（注：在使用本例程前，需要在工作簿中插入一个Chart工作表，才能看出Worksheets与Sheets的区别）。

Private Sub Worksheet_Activate()
    '定义用于显示的Sheets对象初始字符串
    sheetstr = "本工作簿的Sheets集合包含如下对象：" & Chr(10)
    '遍历Sheets集合
    For i = 1 To ThisWorkbook.Sheets.Count
        sheetstr = sheetstr & ThisWorkbook.Sheets(i).Name & Chr(10)
    Next
    '定义用于显示的Worksheets对象初始字符串
    worksheetstr = "本工作簿中Worksheets集合包含有如下对象：" & Chr(10)
    '遍历Worksheets集合
    For i = 1 To ThisWorkbook.Worksheets.Count
        worksheetstr = worksheetstr & ThisWorkbook.Worksheets(i).Name & Chr(10)
    Next
    '显示遍历结果
    MsgBox sheetstr & worksheetstr
    
End Sub

2.3 利用Worksheet的Selectionchange事件读取选择区域的信息

        这个示例我们来演示一下Worksheet的selectionchange事件的编程。Selectionchange事件在工作表的选择区域发生改变时触发，触发时，会有一个Range类型的Target参数，这个参数就包含了选择区域的全部信息。

        我们这里用到了Target的Row和Column属性，它们代表选择区域的左上角的行和列坐标。同时我们还用到了Target的Rows和Columns属性的Count，它们代表了选择区域的行数和列数。

        运行本示例，会实时的显示选择区域的左上角和右下角的行列坐标，并显示选择区域包含的单元格数量。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    selectionstr = "选择区域为：" & Chr(10)
    
    '利用选择区域的Row和Column属性确定选择区的左上角
    selectionstr = selctionstr & "左上角第" & Target.Row & "行，第" & Target.Column & "列。" & Chr(10)
  
    '利用选择区域的rows.count和columns.count属性确定选择区的右下角
    selectionstr = selectionstr & "右下角第" & Target.Row + Target.Rows.Count - 1 & "行，第" & Target.Column + Target.Columns.Count - 1 & "列。" & Chr(10)
    selectionstr = selectionstr & "共" & Target.Count & "个单元格。"
    '显示选择区域的左上角、右下角以及选择的单元格的数量
    MsgBox selectionstr

End Sub

2.4 利用Workbook的Sheetchange事件禁止修订某个Worksheet

        本示例我们来演示一下如何禁止修订某个工作表。

        这里，我们在Workbook的SheetChange事件中编程，只要用户修订某个工作表就会触发该事件，我们在这个事件中编程，判断修订的是否Sheet1，如果是，我们就显示禁止修订，并恢复到修订前的内容。

        需要注意的是，如果不采取特殊的措施，当我们采用undo恢复时，会再次触发Sheetchange事件，导致无限触发出错。因此我们必须在执行undo之前，先将EnableEvents关闭，然后在恢复，这样就不会再次触发SheetChange事件。当一切恢复完成之后，我们将EnableEvents事件打开。

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = "Sheet1" Then
        MsgBox "禁止修改Sheet1"
        '关闭Enableevents预防在恢复修改时再次触发SheetChange事件
        Application.EnableEvents = False
        '恢复修订的内容
        Application.Undo
        '打开EnableEvent
        Application.EnableEvents = True
    End If
End Sub