Excel 中的自定義宏簡介

博愛永華 2012-01-01

展開全文

您在 Excel 中執行的許多任務都是一次性的（例如輸入某天的銷售數據或向工作表（工作表:在 Excel 中用于存儲和處理數據的主要文檔。也稱為電子表格。工作表由排列成行或列的單元格組成。工作表總是存儲在工作簿中。）中添加公式）。不過，還有一些任務需要頻繁反復執行但可以使用 Excel 中的工具很快完成（例如更改單元格區域的格式）。除此之外，您可能還會常常碰到一兩個既需要反復執行又步驟繁瑣的任務。

例如，您可能希望在每次向同事演示報告時突出顯示工作表中幾個重要的單元格。要完成這樣的任務，您不必手動執行一系列繁冗的步驟，而是可以通過創建宏或錄制一系列操作來完成這些步驟。或者，如果您熟悉 Microsoft Visual Basic? for Applications (VBA) 編程語言，也可以從頭編寫自定義宏。無論選擇哪種方式，在創建宏后，均可以根據需要運行、編輯或刪除該宏。

本文向您介紹面向對象的編程和 Visual Basic 編輯器，您可以在此環境中編寫自己的 Excel 宏。如果您打算一試身手，編寫自定義的宏，請參閱 Frank Rice 寫的 Microsoft Office Excel 2003 對象模型的超簡易指南（英文），以便對此過程有深入的了解。

使用 Visual Basic 編輯器

Visual Basic 編輯器是一個功能強大的工具，它使您得以擴展宏的功能和多用性，而非局限于通過簡單的錄制就可以完成的任務。要打開該工具，請在“工具”菜單上指向“宏”，然后單擊“Visual Basic 編輯器”。（或按 Alt+F11）。

要打開某個特定的宏，請在“工具”菜單上指向“宏”，然后單擊“宏”（或按 Alt+F8）。在“宏名”對話框中，選擇要編輯或查看的宏，然后單擊“編輯”即可打開 Visual Basic 編輯器。

在 Visual Basic 編輯器中使用面向對象的編程

在 Visual Basic 編輯器中，您可以使用面向對象的編程來創建自己的程序。大體上，程序只是計算機按指定順序運行的一組指令。

在面向對象的編程語言中，計算機代碼的各個特征均基于環境中的某些事物。這些“事物”（毫不奇怪）被表示為對象，所有操作和數據都封裝在這些對象中。在 Excel 中，對象可以是工作簿、工作表、單元格區域和外部文件。

通常，在充實程序的過程中，要用到對象的四個特征：屬性、方法、事件和集合。

屬性

簡單地說，屬性是描述其所屬對象的某個特征的變量。Excel 中常見的對象屬性是名稱，它代表著您或 Excel 指定給工作簿、工作表、單元格區域或其他對象的標識值。例如，如果更改了工作表的名稱（使用 VBA 代碼或右鍵單擊工作表標簽），則會更改名稱屬性中存儲的值。

您可以直接在 VBA 中為某些工作表屬性（如名稱）設置新的值。要更改其他工作表屬性，可能需要在 Excel 中錄制一個操作（例如，保護某個單元格區域時執行的步驟），或使用一個方法（將在下一節中介紹）。

在 VBA 程序中，屬性是使用點標記引用的：首先寫下對象名稱，然后寫下屬性名稱，在兩個元素之間用句點分隔。比方說，若要更改工作表的名稱，則需要使用 Worksheet.Name 屬性。

方法

方法是一種對象“知道”如何執行的操作。比方說，Excel 窗口中顯示的工作表叫做活動工作表。在 Excel VBA 代碼中，可以首先對您要處理的工作表調用 Activate 方法，從而使該工作表成為目標。在 Activate 方法運行后，附帶了該方法的工作表會移動到 Excel 窗口中所有其他工作表的前面，以供編輯。

與屬性一樣，方法也是通過點標記來調用的。例如，若要重新計算工作表中的所有公式，則可以調用 Worksheet.Calculate 方法。

事件

正如屬性是一種可量化的對象特性、方法是一種對象知道如何執行的操作一樣，事件是一種被對象“意識到”已經發生的操作。例如，Excel 2003 可以意識到下面的事件（此外還有許多其他事件）：

打開或關閉一個工作簿。
激活或停用一個工作表。
保存一個工作簿。
單擊一個圖表。
按下一個鍵（或組合鍵）。
在一個單元格中鍵入數據。
重新計算一個工作表上的公式。
訪問一個超鏈接。

Excel 包含若干事件處理程序或代碼例程，用于監視特定的操作。如果某個操作確實發生，并且您已告知 Excel 在發生該事件時要執行的操作，那么 Excel 將運行事件處理程序中的代碼。例如，如果在創建新的工作簿后，希望 Excel 以一組層疊窗口的形式顯示所有打開的工作簿，則可以創建以下事件處理程序：

Private Sub App_NewWorkbook(ByVal Wb As Workbook)
   Application.Windows.Arrange xlArrangeStyleCascade
End Sub

您可能不知道這個事件處理程序例程的每個元素都是用來做什么的，不過不必擔心。現在您只需要注意代碼中間的一行，該行告訴 Excel 使用層疊樣式排列窗口。您可以使用 Excel 中的菜單命令（“窗口”菜單，“重排窗口”命令，“層疊”子命令）獲得相同的結果。不過，如果您希望每當發生某個特定事件時即執行一個操作，則可以使用 VBA 讓其自動執行，免得您勞神費力。

集合

在面向對象的編程中，您要了解的最后一個元素是集合。顧名思義，集合是包含在另一個對象中的一組類型相同的對象。舉例說來，一個工作簿包含由一個或多個工作表組成的集合。如果要對工作簿中的每個工作表進行同樣的更改，則可以用編程方式依次遍歷集合中的每個工作表并進行更改。

如果您以前創建過程序，那么可能運行過 For...Next 循環，該循環允許您使用與下面類似的順序，多次重復一組指令。（本例將活動工作簿的目錄路徑添加到前三個工作表頁腳的右側。）

For i = 1 to 3
   Worksheets(i).PageSetup.RightFooter = Path
Next i

對此 For...Next 循環的上限進行硬編碼（即分配設置值）的問題是，在每次添加或刪除工作表時，都必須更改代碼。如果是一兩次，也不算什么，但如果您管理著許多代碼，難免會在某些位置忘記更改這些代碼。這將導致一些錯誤，事后您不得不去糾正它們。更嚴重的情況是，人們可能一直沒有注意到這些錯誤，直到年度報告的前 500 份副本已經全部打印出來，編審人員才發現格式是錯誤的，而這時候您已經關掉手機，正在度假海灘上欣賞落日。

您可以使用一段代碼來得出工作簿中的工作表數，不過還有更簡單的方法：即改用 For Each...Next 循環。For Each...Next 循環可以查找集合中的對象數目（例如工作簿中的工作表數），并遍歷出現的每個對象。使用該技術，可以按如下方式編寫上述代碼：

For Each Wksht in Worksheets
    Wksht.PageSetup.RightFooter = Path
Next Wksht

在標準的 For...Next 循環中，值是不斷遞增的；與之不同的是，For Each...Next 循環只搜索 Worksheets 集合的下一個成員，在找不到時會停止搜索。

Visual Basic 編輯器及窗口

Visual Basic 編輯器可在不同的窗口中顯示不同的信息。開始編寫宏時，需要重點了解以下幾個窗口：“項目資源管理器”窗口、“屬性”窗口和“代碼”窗口。

“項目資源管理器”窗口

在直接打開 Visual Basic 編輯器時，可以使用“項目資源管理器”窗口來選擇要處理的宏。“項目資源管理器”將在一個樹狀視圖（類似于 Windows? 資源管理器中的樹狀視圖）中顯示所有項目（代碼組）和宏。該樹狀結構中的根對象或基本對象是您所處理的當前工作簿以及其他一些打開的工作簿和外接程序。

VBA 項目使用的主要元素分別存儲在每個項目的獨立文件夾中。這些元素包括：

對象（如上文所述）
模塊，其中包含與工作表相關的宏代碼
類模塊，它們是您為工作簿創建的用戶定義對象的定義
用戶窗體，提供了一個可視界面，您可以在其上放置圖形控件，例如按鈕、圖像和文本區域

在下面的示例中，當前工作簿被標識為 VBAProject。該工作簿包含的三張工作表（Sheet1、Sheet2 和 Sheet3）和整個工作簿 (ThisWorkbook) 均顯示在 Microsoft Office Excel Objects 文件夾中。所有在 VBA 中編寫的宏或錄制宏都顯示在“模塊”文件夾中。所有類模塊或用戶窗體分別顯示在“類模塊”文件夾和“窗體”文件夾中。

“項目資源管理器”窗口、“屬性”窗口和“代碼”窗口

“屬性”窗口

“項目資源管理器”窗口的正下方是“屬性”窗口，用于查看和更改與選定對象相關的各種屬性。對于模塊而言，通常只有名稱屬性是可用的。工作表有其他一些可更改的屬性（如 StandardWidth 和 DisplayPageBreaks）。對“屬性”窗口中的屬性的更改通常在處理用戶窗體時進行。

注釋 “屬性”窗口的名稱屬性框中顯示的對象名稱可能有別于 Excel 中相應的對象名稱。兩個名稱可能稍有不同，造成這種情況的原因是，Visual Basic 編輯器為對象使用默認的命名規則（例如，名稱不能包含空格、必須以字母開頭、不能是保留字且長度不能超過 31 個字符）。不過，即使名稱不同，它們內部也依然聯系在一起，以確保引用適當的對象。