名称已由现有对象使用_Excel编程周末速成班第3课：Excel对象模型

学习Excel技术，关注微信公众号：

excelperfect

导语：为了帮助想要快速学会Excel VBA的朋友，特以《Excel Programming Weekend Crash Course》这本书为基础，开始整理一系列资料，在完美Excel社群上分享。一共有30课，本文为第3课，目前已在社群上发布4课。有兴趣的朋友可以到社群上学习。

主要内容：

• 使用属性和方法

• 使用集合

• 对象层次模型

• Workbook对象

• Worksheet对象

Excel对象模型是Excel编程的两个支柱之一(另一个是VBA语言)。如你在第1课中所见，Excel应用程序由提供程序功能的对象组成。由于这些对象是“公开的”(即可以从外部进行操作)，因此可以通过VBA代码控制它们以创建自定义程序。Excel公开的对象集合称为Excel对象模型，本节向你讲解该模型及其一些最基本的组件。

理解属性和方法

在讨论对象时，必须了解对象的属性和方法，统称为成员。从某种意义上说，对象就是其属性和方法，因为这些是对象暴露给外界(即程序)的唯一方面。使用对象时，需要使用其属性和方法。

属性是与对象关联的信息。属性可以提供有关对象的某些信息，或指定对象外观的某些方面(对于具有显示组件的对象)或行为。以汽车为例，其属性包括颜色、设置收音机的位置以及油箱中的汽油量。一些对象属性是只读的，意味着你可以确定它们的属性但不能更改它们的值(例如，门数)。另一些则是可读写的，可以读取和更改，例如广播电台。

相比之下，方法是对象可以执行的操作，例如它可以执行的动作。继续使用汽车类比，其方法包括“加速”、“转弯”和“停止”。许多方法都带有参数，这些信息准确地指定了方法的工作方式。例如，“转弯”方法可能具有“方向”参数，该参数可以是“右”或“左”。

属性和方法的表示法遵循标准的对象名.成员名格式。当方法使用参数时，可以通过三种方法进行操作。第一种是在方法名称后的括号中以正确的顺序包含参数：

对象名.成员名(参数1, 参数2, …)

极少数情况下带有参数的属性也必须使用此语法。参数必须精确遵循方法定义的顺序。

在方法调用中包括参数的另一种方法本质上是相同的，但是省略了括号：

对象名.成员名参数1, 参数2, …

同样，参数必须遵循正确的顺序。

在方法调用中包含参数的最后一种方法称为命名参数，也许是最简单且最清晰的方法。它使用方法定义中提供的参数名称和:=运算符，后跟参数值，即：

对象名.成员名参数1名称:=参数1, 参数2名称:=参数2, …

下面是一个实例：

MyWorkbook.SaveAs Filename:=”季度销售.wks”

使用命名参数有两个优点。一个是使代码清晰，每个参数的名称都是其用途的描述，因此可以帮助你或其他人理解之前编写的代码。另一个是简单性，许多方法都有很多可选参数，你可能想在大多数参数保留默认值的情况下调用该方法。如果没有命名参数，则该方法只能根据其在参数列表中的位置来标识一个参数，因此你必须为要忽略的所有可选参数包括占位符(逗号)以使用默认值。换句话说，占位符是一个逗号后跟另一个逗号——如果包含该参数，则省略的参数将在逗号之间。使用命名参数，则没有必要——仅包括要更改其默认值的那些可选参数。

对象引用的重要性

要使用对象，你需要对其进行引用。只是在代码中用于引用对象的名称。有时该对象已经存在，并且你的代码需要做的就是获取对该对象的引用。例如，你的程序打开一个工作簿并需要使用其现有的一个工作表。在其他时候，该对象不存在，并且你的程序必须创建该对象并获得引用(例如，当向工作簿中添加新工作表时)。本书将针对你将要使用的各种Excel对象讲解这两种技术。

使用集合

Excel对象模型经常使用集合。这是一个特殊的类(Collection类)，专门用于跟踪其他对象的多个实例。只要有可能存在一个对象的多个副本，Excel几乎总是使用集合。

下面是一个示例。一个Excel工作簿包含多个工作表，处理方法如下：

• Workbook对象代表该工作簿。

• 该Workbook对象有一个Sheets集合。

• Sheets集合为工作簿中的每个工作表包含一个Sheet对象。

说明：为什么包含工作表的集合称为Sheets而不是Worksheets？这是因为Excel具有两类工作表，其中包含数据的行和列以及嵌入式图表的工作表和图表工作表(仅包含一个图表)。因此，Sheets集合包含Sheet对象，有两种类型：工作表和图表工作表。如果你只想获取工作表或图表工作表，请使用Worksheets或Charts属性(如本课后面所述)。

此示例说明了用于将集合命名为包含的多个对象的约定——Sheets集合包含Sheet对象，Windows集合包含Window对象，依此类推。这条规则几乎没有例外，整本书都是这样。

Collection对象具有Count属性，Count属性给出集合中对象的数量。这是一个只读属性，你可以读取但不能更改其值。

有两种方法可以引用集合中的对象。一种是通过其在集合中的数字位置；另一种是通过对象的唯一键。集合的键取决于它包含的对象，通常是标识对象的信息。例如，在Sheets集合中，唯一键是工作表的名称，因此：

Sheets(1)

引用当前工作簿中的第一个工作表，而：

Sheets(“销售数据”)

引用名为“销售数据”的工作表。后面的方法更有用，因为你通常不知道对象在集合中的位置。

当引用集合成员时，可以通过两种方式使用引用。一种是直接的，如下面的代码行所示，它使指定的工作表重新计算其所有公式：

Sheets(“销售数据”).Calculate

你也可以将引用赋值给变量，然后使用该变量来引用对象，例如：

Set MyWorksheet = Sheets(“销售数据”)

假设已将变量MyWorksheet创建为可以容纳工作表引用的变量类型(在第4课中将学习这个知识点)，注意使用Set关键字，这在赋值对象引用时是必需的。结果是MyWorksheet引用工作表“销售数据”，并且可以按如下方式使用：

MyWorksheet.Calculate

说明：即使有两个或多个对一个对象的引用，仍然只有一个对象。

大多数集合提供了将新对象添加到集合以及删除对象的方法。添加对象使用Add方法，语法为：

集合名.Add(参数)

参数提供了有关如何创建对象的详细信息，具体取决于所使用的特定集合。Add方法返回对新创建对象的引用，如果不需要则可以忽略该引用。若要删除对象，使用Delete方法。

使用集合时一个特别有用的工具是ForEach … In语句，此语句设置一个代码循环，该循环对集合中的每个项目重复一次，语法如下：

For Each Item In Collection

    …

Next

Item是一个变量，已被声明为引用集合内容的正确数据类型。Collection是对集合的引用。将Item设置为引用集合中的第一个元素，并执行循环中的代码(由...表示)。当到达Next语句时，执行将循环回到ForEach语句，将Item设置为引用集合中的下一个元素，然后重复该过程，直到处理完集合中的所有元素为止。如果开始时该集合为空，则执行仅跳过循环。本课后面的Workbook对象部分提供了使用此循环的示例。

以上只是对集合的快速介绍。你将在本书中学会更多的特定集合的详细内容。

对象层次模型

Excel对象模型按层次结构组织。该层次结构的顶部是代表Excel应用程序本身的Application对象。Excel对象模型中的所有其他对象都是Application的附属对象。Application对象始终对你的代码可用。

说明：如果要在另一个Office程序(如Word)中编写VBA代码，并且需要对Excel进行编程，则需要显式创建ExcelApplication对象。但是这超出了本书的范围。在Excel中编程时，Application对象始终可用作隐式引用。

Workbook对象有几个直接的子对象，其中最重要的是Workbooks集合。此集合为每个打开的工作簿包含一个Workbook对象。每个Workbook对象都有一个Sheets集合，其中为该工作簿包含的每个工作表或图表工作表包含一个Sheet对象。图3-1说明了到目前为止所描述的对象模型的结构。

图3-1：Application、Workbook和Sheet对象之间的关系

不管怎么说，Excel对象模型比此图可能表示的复杂得多。在以后的课程中将介绍此处未显示的许多其他对象和集合。

Workbook对象

Excel中每个打开的工作簿都由一个Workbook对象表示，该对象保存在Application对象的Workbooks集合中。本节说明如何使用Workbook对象执行各种重要任务。

创建和打开工作簿

使用Workbooks集合的Add方法创建新工作簿，语法为：

Workbooks.Add(Template)

说明：由于Workbooks集合是Application对象的子对象，因此其完整语法为：

Application.Workbooks.Add(Template)

然而，在Excel中进行编程时，始终可以将Application用作隐式引用，因此尽管可以指定Application，但不必指定它。

Template是一个可选参数，用于指定现有工作簿文件的名称。如果包含该参数，则基于现有工作簿创建一个新的工作簿；如果省略该参数，则创建一个包含默认数量空工作表的新工作簿。该方法返回对新创建的工作簿的引用，该引用可以使用或忽略：

Dim MyWB As Workbook

Set MyWB = Workbooks.Add

与前面一样，使用Set关键字是因为该语句赋值一个对象引用。变量MyWB引用新的工作簿，并可用于对其进行操作。Workbooks集合中也存在对该工作簿的引用。

要从磁盘中打开现有工作簿，使用Open方法：

Workbooks.Open(Filename)

Filename参数是要打开的工作簿文件的名称，必要时包括完整路径。此方法将打开指定的工作簿，将其添加到Workbooks集合，然后返回对该工作簿的引用。例如：

Dim MyWB As Workbook

Set MyWB = Workbooks.Open(“C:\数据\销售.xlsx”)

如果指定的文件不存在或由于其他任何原因(例如共享冲突)而无法打开，则会发生运行时错误。

提示：你的代码应该总是考虑可能的错误，尤其是在处理文件时。为了清楚起见，本课程中的示例代码通常会省略错误处理代码，但这并不意味着你可以这样做！

保存和关闭工作簿

若要以现有名称保存工作簿，则使用Save方法。工作簿的名称要么是你使用SaveAs方法赋给的名称(在本课程的后面部分介绍)，要么是Excel在创建新工作簿时赋给的默认名称(工作簿1、工作簿2，等)。Save方法不带任何参数。

要以新名称保存工作簿，使用SaveAs方法：

WB.SaveAs(Filename)

WB是对工作簿的引用，filename是保存工作簿的名称。该名称可以包含.xlsx扩展名，也可以不包含。如果省略，则Excel会自动添加它。如果你不希望工作簿保存在Excel的默认数据文件夹中，则名称还可以包括驱动器和/或路径信息。例如：

MyWB.SaveAs(“g:\数据\销售\核查.xlsx”)

SaveAs方法具有一些附加的可选参数，这些参数用于为工作簿设置密码、指定备份模式以及设置其他保存选项。有关详细信息，请参考VBA的在线帮助。

另一种方法SaveCopyAs使你可以使用新名称保存工作簿的副本，而无需更改打开的工作簿的名称。语法为：

WB.SaveCopyAs(Filename)

若要关闭打开的工作簿，使用Close方法，如以下语法所示：

WB.Close(SaveChanges, Filename,RouteWorkbook)

这些可选参数的作用如下：

• 参数SaveChanges，将此参数设置为True以保存自上次保存工作簿以来所做的更改，或设置为False放弃此类更改。如果省略，则提示用户保存更改。

• 参数Filename，此参数指定保存工作簿的名称。如果省略，则使用当前工作簿名称。如果尚未为工作簿赋予名称(它仍使用默认名称工作簿1、工作簿2，等)，并且省略了此参数，则会提示用户输入名称。

• 参数RouteWorkbook，仅当将路由清单附加到工作簿且尚未路由时，此参数才有意义。设置为True可以路由工作簿，设置为False可以不路由工作簿。如果省略此参数并附加了路由清单，则会提示用户。

下面的代码示例演示如何使用ForNext … In循环关闭所有打开的工作簿，并保存所有更改。

Dim WB As Workbook

For Each WB in Workbooks

    WB.CloseSaveChanges:=True

Next

引用工作簿

若要在代码中操作工作簿，则必须具有对它的引用。有多种方法可以执行此操作，而你使用的方法则取决于你的具体情况。

一种方法是在分别使用Add或Open方法创建或打开工作簿时赋值引用，因此：

Dim MyNewWB As Workbook

Dim MyOpenedWB As Workbook

Set MyNewWB = Workbooks.Add

Set MyOpenedWB = Workbooks.Open(“消费.xlsx”)

注意，用于对象引用的变量必须使用正确的类型创建。

如果已经创建或打开了工作簿(换句话说，如果它已经存在于Workbooks集合中)，则可以直接从集合中引用它。因此，代码

Workbooks(Name)

返回对具有指定名称的工作簿的引用。注意，该名称必须包含扩展名：销售.xlsx，而不仅仅是“销售”。如果指定的工作簿不存在，则会发生错误。

最后，VBA提供了一些特殊的关键字，可用于引用工作簿：

• ActiveWorkbook，引用当前活动工作簿。

• ThisWorkbook，引用正在运行的代码所在的工作簿。

活动工作簿的概念很容易理解：它表示活动的并且在屏幕上的工作簿。ActiveWorkbook关键字可能非常有用。

然而，对ThisWorkbook的需求可能并不明确，在许多情况下，正在执行的VBA代码位于该代码所操纵的同一工作簿中，因此不需要ThisWorkbook。但是，有时一个工作簿中的代码正在处理另一工作簿中的数据，当此类代码需要引用它所在的工作簿而不是要操纵的工作簿时，使用ThisWorkbook关键字。在对加载宏进行编程时，这种情况最经常发生。然后使用ThisWorkbook关键字确保引用适用于代码所在的工作簿，而不适用于活动的工作簿。

说明：ThisWorkbook关键字与工程资源浏览器中列出的ThisWorkbook元素不同。

打印工作簿

使用PrintOut方法来打印全部或部分工作簿，该方法的语法为：

WB.PrintOut(From, To, Copies,Preview, ActivePrinter, PrintToFile, Collate, PrToFileName)

此方法的所有参数都是可选的：

• 参数From、To，要打印的工作簿的第一页和最后一页，默认为打印整个工作簿。

• 参数Copies，要打印的份数，默认值为1。

• 参数Preview，如果设置为True，则将打开“Excel打印预览”窗口供用户查看。默认值为False。

• 参数ActivePrinter，要使用的打印机的名称，默认为Windows默认打印机。

• 参数PrintToFile，如果为True，则输出将发送到磁盘文件而不是打印机。默认值为False。

• 参数Collate，如果为True且正在打印多份副本，则对输出进行整理，默认值为False。

• 参数PrToFileName，输出文件的名称(仅在PrintToFile为True时相关)。

提示：PrintOut方法可用于其他几个Excel对象，包括图表、单元格区域、窗口和工作表。

通过电子邮件发送工作簿

使用Workbook对象的SendMail方法，你可以通过电子邮件将工作簿发送给一个或多个收件人。使用系统上安装的任何电子邮件系统，将工作簿作为电子邮件的附件发送。语法为：

WB.SendMail(Recipients, Subject, ReturnReceipt)

这些参数的作用如下：

• 参数Recipients，必需的参数，列出消息的一个或多个收件人。

• 参数Subject，指定消息主题的可选参数。如果省略，则将工作簿的名称用作消息主题。

• 参数ReturnReceipt，如果为True，则要求返回收件人。可选，默认值为False。

可以将邮件收件人指定为显式电子邮件地址，也可以指定为通讯簿中条目的名称。如果只有一个邮件收件人，将收件人的姓名或地址本身作为参数传递：

WB.SendMail Recipients:=”excelperfect”

对于一个以上的收件人，创建一个包含每个收件人一个条目的数组；然后将该数组作为方法参数传递：

Dim recip As Variant

Recip = Array(“完美Excel”, “excelperfect@163.com”,“excelperfect”)

WB.SendMail Recipients:=recip

Workbook的其他方法和属性

Workbook对象具有大量的属性和方法——本书中没有介绍的内容。这些成员中的许多很少使用，但是在任何情况下，你都可以使用在线文档查找详细信息。表3-1中对此进行了汇总。有关如何使用这些功能的更多详细信息，请参阅联机帮助。

表3-1：Workbook对象的其他成员

Worksheet对象

如本课程前面所述，工作表是工作簿可以包含的两种工作表中的一种(另一种是图表工作表)。本节提供有关使用Worksheet对象的重要信息。

添加和删除工作表

要将新的空白工作表添加到工作簿，使用Worksheets集合的Add方法。语法为：

WB.Add(Before, After, Count)

参数都是可选的：

• 参数Before，这是对现有工作表的引用，在该工作表之前添加新工作表。

• 参数After，这是对现有工作表的引用，在该工作表之后将添加新工作表。

• 参数Count，此参数指定要添加的新工作表的数量，默认值为1。

显然，你不会同时使用参数Before和参数After，如果这些参数都不包括在内，则新的工作表将插入到当前活动的工作表之前。注意，Add方法返回对新添加的工作表的引用。如果参安徽Count大于1，则引用最后添加的工作表。

提示：你也可以通过调用Sheets集合的Add方法来添加新工作表，但这没有任何好处。

创建新工作表后，最好将其Name属性设置为Excel赋给的默认Sheet1和Sheet2名称以外的名称。此名称显示在Excel屏幕上的工作表标签上，也可以用于从Worksheets或Sheets集合中获取引用。

Dim MyNewWorksheet As Worksheet

Set MyNewWorksheet = ActiveWorkbook.Add

MyNewWorksheet.Name = “销售总量”

说明：默认情况下，你在代码中添加的工作表是隐藏的。如果需要使其可见以便用户可以使用它，将其Visible属性设置为True。

要删除一个工作表，调用Worksheet对象的Delete方法：

某工作表.Delete

记住，你也可以从Worksheets集合中获取对某工作表的引用：

某工作表.Worksheets(工作表名).Delete

当代码尝试删除工作表时，Excel通常会向用户显示提示，要求他或她确认删除操作。如果要删除工作表而不显示此提示，则必须将Application对象的DisplayAlerts属性设置为False：

DisplayAlerts = False

某工作表.Delete

DisplayAlerts = True

提示：如上例所示，确保在调用Delete方法后将DisplayAlerts属性设置回True。

引用工作表

你已经看到了使用创建工作表时返回的引用或从Worksheets集合中获取引用来引用工作簿中特定工作表的几种方法。获取引用的另一种有用方法是使用Workbook对象的ActiveSheet属性。此属性返回对指定工作簿中位于顶部或处于活动状态的活动工作表(工作表或图表工作表)的引用。如果没有活动工作表，则该属性将返回特殊值Nothing(请参见“ Nothing的实用性”)。

Nothing的实用性

在某些情况下，对象变量不引用任何对象：它不引用任何内容，并且VBA具有特殊关键字Nothing来表示此对象。一个已声明但尚未初始化的对象变量不包含任何内容(尚未分配对象引用)，因此：

Dim MyWB AsWorkbook

‘此时MyWB包含Nothing

Set MyWB =Workbooks.Add

‘现在它被初始化,MyWB不再包含Nothing

在某些情况下，某些对象属性也会返回Nothing值，例如没有活动工作表时的ActiveSheet属性。你可以使用If ... Then语句中的Is Nothing子句测试包含Nothing的对象引用：

If 某对象引用 Is Nothing Then

…

End If

最后，完成使用对象后，你可以(并且应该)将对象引用显式设置为Nothing：

Set 某对象引用 = Nothing

当以此方式销毁对对象的最后一个引用时，该对象所占用的内存将被释放以用于其他目的。

复制和移动工作表

可以将整个工作表复制或移动到原始工作簿中的新位置或其他工作簿中，其语法如下(使用Move方法移动工作表；语法与Copy相同)：

工作表.Copy(Before,After)

工作表是对要复制的工作表的引用。如果要复制到原始工作簿中的某个位置，使用参数Before或参数After(但不能两个都使用)指定要在其之前或之后放置复制的工作表的现有工作表。例如，此代码复制Sheet1并将其放置在Sheet3之后。

Worksheets(“Sheet1”).CopyAfter:=Worksheets(“Sheet3”)

在工作簿中进行复制时，将为副本指定带有索引的原始工作表的名称，例如“ Sheet1(2)”。

要将工作表复制到另一个工作簿，省略After和Before参数。Excel创建一个新的工作簿，然后将工作表复制到其中。

提示：无法将工作表直接复制或移动到现有工作簿。为此，必须使用Range对象将数据复制到Windows剪贴板，然后将数据粘贴到新位置。有关详细信息，请参见本书后续内容。

要点回顾

你编写的任何VBA程序都将取决于Excel对象模型。本课程向你介绍了此对象模型，并介绍了一些重要的背景材料。你在本课程上学到的东西包括：

• 你可以通过对象的属性和方法来使用它们。

• Excel使用集合来跟踪多个对象副本。

• 对象模型被组织为层次结构，其中Application对象位于顶部。

• Excel工作簿由Workbooks集合中的Workbook对象表示。提供了用于创建新工作簿和打开现有工作簿，保存和关闭工作簿以及其他必需任务的方法。

• 工作表由Sheets集合中的Sheet对象表示。你可以根据需要添加、删除、复制和移动工作表。

自我测评

1.属性和方法之间的主要区别是什么？

2.如何将新对象添加到集合中？

3.命名集合的一般规则是什么？

4.Excel对象层次结构的顶部是哪个对象？

5.如何为新创建的工作簿赋予名称？

6.如何引用当前活动的工作表？

欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。

欢迎到知识星球：完美Excel社群，进行技术交流和提问，获取更多电子资料。