在 Excel 中创建自定义函数

自定义函数（如宏）使用 Visual Basic for Applications (VBA) 编程语言。 它们与宏有两个重要区别。 首先，它们使用 函数 过程而不是 子 过程。 也就是说，它们行 Function 语句而不是 Sub 语句开始，以 End Function 而不是 End Sub 结尾。 其次，它们执行计算而不是操作。 某些类型的语句（例如选择和设置区域格式的语句）从自定义函数中排除。 本文介绍如何创建和使用自定义函数。 若要创建函数和宏，可以使用 Visual Basic 编辑器 (VBE)，该编辑器在独立于 Excel 的新窗口中打开。

假设用户公司在销售产品时，如果订单超过 100 个时，就在数量上给出 10% 的折扣。 以下段落将演示计算此折扣的函数。

以下示例显示了一个订单表格，其中列出了每个商品、数量、价格、折扣（如果有）以及生成的最后价格。

若要在此工作簿中创建自定义 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 函数时，请使用此语法。

自定义函数必须以 Function 语句开始，以 End Function 语句结尾。 除了函数名称之外，Function 语句通常指定一个或多个参数。 但是，可以创建不带参数的函数。 Excel 包括多个不使用参数的内置函数（例如 RAND 和 NOW）。

在 Function 语句后面，函数过程包含一个或多个 VBA 语句，这些语句使用传递给函数的参数做出决策和执行计算。 最后，在函数过程中的某个位置，必须包含一个语句，该语句将值分配给与函数同名的变量。 此值将返回到调用函数的公式。

可以在自定义函数中使用的 VBA 关键字数小于可以在宏中使用的数量。 除了将值返回到工作表中的公式或另一个 VBA 宏或函数中使用的表达式之外，不允许自定义函数执行任何其他操作。 例如，自定义函数无法调整窗口大小、编辑单元格中的公式或更改单元格中文本的字体、颜色或图案选项。 如果在函数过程中包含此类的“action”代码，则该函数将返回#VALUE！ 错误。

函数过程可以执行的操作（除了执行计算以外）是显示一个对话框。 可以使用自定义函数中的 InputBox 语句作为从执行函数的用户获取输入的方式。 可以使用 MsgBox 语句作为向用户传达信息的方式。 也可以使用自定义对话框或 UserForms，但这超出了本介绍的范围。

即使是简单的宏和自定义函数也难以阅读。 可以通过以注释的形式键入说明性文本，使其更易于理解。 通过在说明性文本前面加上撇号来添加注释。 例如，以下示例显示了具有注释的 DISCOUNT 函数。 添加此类注释可让你或其他人在经过一段时间后更轻松地维护 VBA 代码。 如果将来需要更改代码，可以更轻松地了解最初执行哪些操作。

撇号指示 Excel 忽略同一行右侧的所有内容，以便可以在行上或包含 VBA 代码的行右侧创建注释。 你可能会从一段相对较长的代码块开始，其中注释解释了其整体用途，然后使用内联注释来记录各个语句。

另一种记录宏和自定义函数的方法就是为宏和自定义函数提供描述性名称。 例如，可以将其命名为 MonthLabels，而不是命名宏标签，以更具体地描述宏的用途。 创建许多过程时，对宏和自定义函数使用描述性名称尤其有用，尤其是在创建具有类似但目的不相同的过程时。

宏和自定义函数的文档记录方式由个人偏好决定。 重要的是采用某种文档方法，并继续使用。

若要使用自定义函数，必须打开包含在其中创建函数的模块的工作簿。 如果该工作簿未打开，你会收到#NAME? 在尝试使用函数时出错。 如果在不同的工作簿中引用函数，则必须在函数名称前面加上函数所在的工作簿的名称。 例如，如果在名为 Personal.xlsb 的工作簿中创建名为 DISCOUNT 的函数，并且从另一个工作簿调用该函数，则必须键入 =personal.xlsb!discount()，而不只是 =discount()。

可以从“插入函数”对话框中选择自定义函数，这样可以节省一些击键次数（以及可能发生的键入错误）。 自定义函数显示在“用户定义”类别中：

使自定义函数随时可用的更简单方法是将它们存储在单独的工作簿中，然后将该工作簿另存为加载项。 然后，每当运行 Excel 时，都可以使加载项可用。 下面将了解如何执行此操作：

执行这些步骤后，自定义函数将在每次运行 Excel 时可用。 如果要添加到函数库，请返回到Visual Basic 编辑器。 如果在 VBAProject 标题下的 Visual Basic 编辑器项目资源管理器中查看，将看到以加载项文件命名的模块。 加载项将具有扩展名 .xlam。

在项目资源管理器中双击该模块会导致 Visual Basic 编辑器显示函数代码。 若要添加新函数，将插入点定位到终止代码窗口中最后一个函数的 End Function 语句之后，然后开始键入。 你可按照此方式创建所需的任何数量的函数，并且它们始终在“插入函数”对话框中的“用户定义”的类别中可用。

此内容最初由 Mark Dodge 和 Craig Stinson 撰写，属于《Microsoft Office Excel 2007 Inside Out》书中的部分内容。 此后，已更新为也适用于较新版本的 Excel。