在 Excel 中建立自訂函數

雖然 Excel 內建了許多工作表功能，但很可能它沒有針對你執行的每種計算類型都有相應的函數。 Excel 的設計者不可能預見每位使用者的計算需求。 Excel 而是提供您建立自訂函數的功能，如本文所述。

建立簡單的自訂函數

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

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

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

要在本工作簿中建立自訂的折扣函數，請遵循以下步驟：

1. 按 Alt+F11 在 Mac 上開啟 Visual Basic Editor (，按 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
   
   

使用自訂函數

現在你準備好使用新的折扣功能了。 關閉 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 關鍵字數目少於可在巨集中使用的數目。 自訂函數除了將值傳回至工作表中的公式，或傳回其他 VBA 巨集或函數中使用的表達式之外，不得執行其他動作。 例如，自訂函數無法調整視窗大小、編輯儲存格中的公式，或變更儲存格中文字的字型、色彩或圖樣選項。 如果你在函式程序中加入這類「action」程式碼，函式會回傳 #VALUE！ 錯誤。

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

記錄巨集和自訂函數

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

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

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

記錄巨集和自訂函數的方式是依據個人喜好。 重要的是採用某種文件化方法，並持續使用。

讓您的自訂函數隨時隨地都能使用

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

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

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

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

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

關於作者

本內容最初由 Mark Dodge 和 Craig Stinson 撰寫，收錄於他們的著作《 Microsoft Office Excel 2007：內幕世界》。此後也更新以適用於較新的 Excel 版本。

需要更多協助嗎？

你隨時可以向 Excel 技術社群 的專家詢問，或在 社群中獲得支援。