創建索引名稱已由現有對象使用_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

??? …

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

引用工作簿

若要在代碼中操作工作簿，則必須具有對它的引用。有多種方法可以執行此操作，而你使用的方法則取決于你的具體情況。

一種方法是在分別使用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