在 Excel 中建立自訂函數

自訂函數，例如巨集，會使用 Visual Basic for Applications (VBA) 程式設計語言。 它們與巨集在兩方方面有顯著的不同。 首先，他們使用函數程序，而非 Sub 程序。 也就是說，它們的開頭是函數語句，而不是 Sub 語句，結尾是 End Function，而不是 End Sub。 其次，他們會執行計算，而不是採取動作。 某些類型的語句，例如選取範圍和格式化範圍的語句，會排除在自訂函數之外。 在本文中，您將了解如何建立和使用自訂函數。 若要建立函數和巨集，您可以使用 Visual Basic 編輯器 (VBE)，此編輯器會在獨立於 Excel 的新視窗中開啟。

假設您的公司在產品銷售時提供 10% 的數量折扣，條件是訂購超過 100 份。 在下列段落中，我們將示範計算此折扣的函數。

以下範例顯示訂單表單，其中列出每個項目、數量、價格、折扣 (如果有)，以及產生的總價。

若要在此活頁簿中建立自訂 DISCOUNT 函數，請遵循下列步驟：

1. 按 Alt+F11 以開啟 Visual Basic 編輯器 (在 Mac 上，按 FN+ALT+F11)，然後按一下 [插入] > [模組]。 新的模組視窗會顯示在 Visual Basic 編輯器的右側。

2. 將下列程式碼複製並貼到新模組。

   Function DISCOUNT(quantity, price)
      If quantity >=100 Then
        DISCOUNT = quantity * price * 0.1
      Else
        DISCOUNT = 0
      End If
    
    DISCOUNT = Application.Round(Discount, 2)
   End Function
   

現在您已準備好使用新的 DISCOUNT 函數。 關閉 Visual Basic 編輯器，選取儲存格 G7，然後輸入下列內容：

=DISCOUNT (D7,E7)

Excel 將 200 單位的 10% 折扣計算為每單位 $47.50，並退回 $950.00。

在 VBA 程式碼的第一行中，函數 DISCOUNT (數量、價格)，您指出 DISCOUNT 函數需要兩個引數: 數量和價格。 當您在工作表儲存格中呼叫函數時，必須包含這兩個引數。 在公式 =DISCOUNT (D7，E7) 中，D7 是數量引數，而 E7 是價格引數。 現在您可以將 DISCOUNT 公式複製到 G8:G13，以取得以下所示的結果。

讓我們考慮一下 Excel 如何解譯此函數程序。 當您按 Enter 時，Excel 會尋找目前活頁簿中的 DISCOUNT 名稱，並發現它是 VBA 模組中的自訂函數。 括弧內的引數名稱 (數量和價格)，是計算折扣依據的值的預留位置。

下列程式碼區塊中的 If 語句會檢查數量引數，並判斷售出的項目數量是否大於或等於 100：

If quantity >= 100 Then
 DISCOUNT = quantity * price * 0.1
Else
 DISCOUNT = 0
End If

如果售出的項目數量大於或等於 100，VBA 會執行下列語句，將數量值乘以價格值，然後將結果乘以 0.1:

Discount = quantity * price * 0.1

結果會儲存為變數 Discount。 將值儲存在變數中的 VBA 語句稱為指定陳述式，因為它會評估等號右邊的運算式，並將結果指派給左邊的變數名稱。 由於變數 Discount 與函數程序的名稱相同，因此儲存在變數中的值會回到稱為 DISCOUNT 函數的工作表公式中。

如果數量小於 100，VBA 會執行下列陳述式：

Discount = 0

最後，下列陳述式會將指派給 Discount 變數的值四捨五入至兩位小數字數：

Discount = Application.Round(Discount, 2)

VBA 沒有 ROUND 函數，但 Excel 有。 因此，若要在本陳述式中使用 ROUND，您必須要求 VBA 在 Application 物件 (Excel) 中尋找 Round 方法 (函數)。 您可以在 [Round] 文字前新增 [Application] 這個字。 每當您需要從 VBA 模組存取 Excel 函數時，請使用此語法。

自訂函數必須以函數陳述式開頭，並以 End 函數陳述式結尾。 除了函數名稱之外，函數陳述式通常也會指定一或多個自變數。 不過，您可以建立沒有引數的函數。 Excel 包含數個不使用引數的內建函數，例如 RAND 和 NOW。

在函數陳述式之後，函數程式包含一或多個 VBA 陳述式，可使用傳遞至函數的自變數來做出決策並執行計算。 最後，在函數程式的某個位置，您必須包含一個陳述式，將值指派給與函數同名的變數。 此值會傳回呼叫函數的公式。

您可以在自訂函數中使用的 VBA 關鍵字數目少於可在巨集中使用的數目。 自訂函數除了將值傳回至工作表中的公式，或傳回其他 VBA 巨集或函數中使用的表達式之外，不得執行其他動作。 例如，自訂函數無法調整視窗大小、編輯儲存格中的公式，或變更儲存格中文字的字型、色彩或圖樣選項。 如果您在函數程序中包含這類「動作」程式碼，函數會傳回 #VALUE！ 錯誤。

函數程序除了執行計算之外，可以執行的動作是顯示對話方塊。 您可以在自訂函數中使用 InputBox 陳述式，做為從執行函數的使用者取得輸入的一種方式。 您可以使用 MsgBox 陳述式來向使用者傳達資訊。 您也可以使用自訂對話方塊或 UserForm，但這個主題不在本簡介範圍之內。

即使是簡單的巨集和自訂函數也可能很難讀取。 您可以以註解的形式輸入說明文字，讓它們更容易理解。 您可以在說明文字前面加上單引號來新增註解。 例如，下列範例顯示含有註解的 DISCOUNT 函數。 新增這類註解，可讓您或其他人隨時更輕鬆地維護您的 VBA 程式碼。 如果您未來需要變更程式碼，您將能更輕鬆地瞭解您原本所做的內容。

單引號可讓 Excel 忽略同一行右邊的所有內容，因此您可以在包含 VBA 程式碼的行本身或右側建立註解。 您可能會以解釋其整體用途的註解開始一個很長的程式碼區塊，然後使用內嵌註解來記錄個別的陳述式。

另一個記錄巨集和自訂函數的方式是為它們提供描述性的名稱。 例如，您可以將巨集命名為 MonthLabels，以更明確描述巨集的用途，而不是將巨集命名為標籤。 當您建立許多程序時，尤其是當您建立具有相似但不完全相同的程序時，對巨集和自訂函數使用描述性名稱特別有幫助。

記錄巨集和自訂函數的方式是依據個人喜好。 重要的是採用一些記錄方法，並且持續使用。

若要使用自訂函數，必須開啟包含您建立函數之模組的活頁簿。 如果該活頁簿未開啟，您會收到 #NAME? 當您嘗試使用函數時發生錯誤。 如果您在其他活頁簿中參考函數，則必須在函數名稱前面加上函數所在活頁簿的名稱。 例如，如果您在名為 Personal.xlsb 的活頁簿中建立名為 DISCOUNT 的函數，而您從另一個活頁簿呼叫該函數，則必須輸入 =personal.xlsb!discount()，而不只是 =discount ()。

您可以從 [插入函數] 對話方塊中選取您的自訂函數，以節省一些按鍵輸入 (以及可能的輸入錯誤)。 您的自訂函數會顯示在 [使用者定義] 類別中:

將自訂函數儲存在個別的活頁簿中，然後將該活頁簿另存為增益集，是讓您的自訂函數隨時都能使用的簡單方法。 您可以在每次執行 Excel 時提供增益集。 方法如下：

遵循這些步驟之後，每次執行 Excel 時，您都可以使用自訂函數。 如果您想要新增到函數庫，請返回 Visual Basic 編輯器。 如果您在 [Visual Basic 編輯器專案總管] 的 VBAProject 標題底下查看，您會看到以增益集檔案命名的模組。 您的增益集將具有副檔名.xlam。

按兩下專案總管中的該模組，可讓 Visual Basic 編輯器顯示您的功能代碼。 若要新增函數，請將插入點置於終止程式碼視窗中最後一個函數的 End 函數陳述式後面，然後開始輸入。 您可以視需要以此方式建立任意數量的函數，這些函數一律可在 [插入函數] 對話方塊的 [使用者定義] 類別中使用。

此內容原本由 Mark Dodge 和 Craig Stinson 寫在其著作 Microsoft Office Excel 2007 Inside Out 中。 此後已更新並套用至較新版本的 Excel。