在Excel中创建自定义函数

在Excel中创建自定义函数

在Excel中，自定义函数（UDF）是实现复杂计算和个性化需求的强大工具。本文将详细介绍如何创建和使用自定义函数，包括具体的操作步骤、规则限制以及如何使自定义函数在任何工作簿中可用。

尽管Excel包含大量内置工作表函数，但它很可能没有用于执行的每种计算类型的函数。Excel的设计人员无法预测每个用户的计算需求。相反，Excel提供了创建自定义函数的功能，本文对此进行说明。

创建简单的自定义函数

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

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

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

若要在此工作簿中创建自定义DISCOUNT函数，请执行以下步骤：

2. 按Alt+F11打开Visual Basic编辑器（在Mac上，按FN+ALT+F11），然后单击“插入”>“模块”。新模块窗口显示在Visual Basic编辑器的右侧。

3. 将以下代码复制并粘贴到新模块。

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

注意: 若要使代码更具可读性，可以使用Tab键缩进行。缩进仅用适用于你，并且是可选项，因为代码使用或不使用它都会运行。键入缩进行后，Visual Basic编辑器会假定下一行将采用类似的缩进方式。 若要移出（即向左）一个制表符，请按Shift+Tab。

使用自定义函数

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

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

记录宏和自定义函数

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

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

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

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

使自定义函数可在任何位置使用

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

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

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

WindowsmacOS
2. 创建所需的函数后，单击“文件”>“另存为”。
4. 在“另存为”对话框中，打开“另存为类型”下拉列表，然后选择“Excel加载项”。将工作簿保存在AddIns文件夹中可识别名称（例如MyFunctions）下。“另存为”对话框将建议该文件夹，因此只需接受默认位置。
6. 保存工作簿后，单击“文件”>“Excel选项”。
8. 在“Excel选项”对话框中，单击“加载项”类别。
10. 在“管理”下拉列表中，选择“Excel加载项”。然后单击“转到”按钮。
12. 在“加载项”对话框中，选中用于保存工作簿的名称旁边的复选框，如下所示。
2. 创建所需的函数后，单击“文件”>“另存为”。
4. 在“另存为”对话框中，打开“另存为类型”下拉列表，然后选择“Excel加载项”。将工作簿保存在可识别的名称（例如MyFunctions）下。
6. 保存工作簿后，单击“工具”>“Excel加载项”。
8. 在“加载项”对话框中，选择“浏览”按钮以查找加载项，单击“打开”，然后在“加载项可用”框中选中加载项旁边的复选框。

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

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

本文原文来自Microsoft官方支持页面