如何在 Microsoft Excel 中创建用户定义函数

Microsoft Excel 用户定义函数简介

众所周知，Microsoft Excel 提供了丰富的内置函数，使我们的数据分析工作变得非常轻松。然而，这并非全部，因为很多时候我们可能会遇到所有提供的公式都不足以或不适合我们正在进行的任务的情况。

在这种情况下，我们可以借助 VBA（Visual Basic for Applications）在 Microsoft Excel 中高效地创建满足我们需求的自定义函数，这些函数被称为 **用户定义函数**（UDFs）。

此外，用户定义函数（UDF）可以像我们使用普通的 Excel 函数一样在工作表中使用，包括以下函数：

1. SUM。
2. AVERAGE 等。

这些函数可以根据用户的需求，利用 Visual Basic for Application (VBA) 来创建，并将有助于简化用户在 Microsoft Excel 中的任务。

除此之外，任何用户定义函数（UDF）都有一个基本规则；用户必须在 **FUNCTION** 过程中定义它，而不是在子过程（subroutine procedure）中，并且借助 **FUNCTION** 过程，我们可以有效地创建 UDF，并在 Microsoft Excel 中像通用的 Excel 函数一样使用它们，例如：

• VLOOKUP。
• SUM。
• AVERAGE 等。

如何在 Microsoft Excel 中创建自定义函数或用户定义函数？

首先，您需要打开 Visual Basic 编辑器（VBE）。请记住，VBE 会在一个新窗口中打开，不会关闭您的 Excel 电子表格。

打开（Visual Basic 编辑器）最简单、最直接的方法就是使用键盘上的快捷键。

打开 VBE 后，我们需要添加一个新的模块，我们将在其中编写我们的函数。

在 VBA 项目窗格中右键单击，选择 *插入 -> 模块*。单击“模块”后，屏幕上会出现一个空白模块窗口，您需要在其中指定您的自定义函数。

创建用户定义函数时需要遵循的规则

1. 用户定义函数必须始终以“Function”开头，并通常以“End Function”有效地结束。
2. “Function”后面必须跟函数的名称。这是一个您创建并用于您函数的标题，以便您可以轻松地识别和以后使用它。
3. 请记住，您不应将 UDF 名称与标准的 Excel 函数命名相同，否则将始终执行标准函数。
4. 用户定义函数的名称不能与工作表中使用的特定单元格的地址匹配。
5. 函数参数主要列在括号内，这是函数将要处理的数据，您可以有多个参数。如果有多个参数，则应以逗号分隔。
6. 如果 UDF 中的函数不使用任何参数，则需要创建一个不带参数的函数。
7. 您还需要确定用户定义函数将使用的变量。
8. 之后，您将编写几个 VBA 语句，通过传递给函数的参数有效地执行计算。
9. 最后，您需要编写一个语句，将最终值赋给一个名称与函数相同的变量。

示例

示例 #1 - 用于在 Excel 中查找三角形面积的用户定义函数

众所周知，数学专业的学生经常会遇到需要计算三角形面积的数学问题。虽然我们知道有一个非常简单的公式可以解决这个问题，但如果有一个函数可以计算三角形的面积，学生们会很高兴。

因此，我们将按照下面提到的步骤创建一个可以有效地计算三角形面积的函数。

第 1 步：第一步，我们需要插入一个新模块，该模块位于 Visual Basic 编辑器（VBE）下方。打开 VBE > 单击“插入”选项卡 > 然后从中选择“模块”。

第 2 步：在此步骤中，我们将定义一个新的 FUNCTION 过程，并将其命名为 TriangleArea。

由于这是一个 FUNCTION 过程，因此在定义函数时，我们还需要定义用于计算三角形面积的参数。

第 3 步：之后，我们将为 TriangleArea 函数分配两个参数：height（高度）和 base（底）。这些参数位于括号内，被认为是必需参数，这意味着您需要提供高度和底的值才能计算三角形的面积。

第 4 步：现在，在此步骤中，我们将编写负责在提供高度和底值时返回三角形面积的代码。

第 5 步：现在，在此步骤中，在 C2 单元格中，我们将开始键入公式 **=TriangleArea (A2, B2)** 来计算三角形的面积，然后我们将高度和底作为参数提供给同一个公式。

第 6 步：之后，我们将关闭括号以完成公式，然后按键盘上的 Enter 键，即可得到高度为 12、底为 7.5 的三角形面积。

然后，我们将拖动该公式以有效地获得第二个和第三个高度和底值的面积。

# 示例 2：用于查找任何数字平方的用户定义函数

让我们假设我们想要获取任何特定数字的平方值，无论是整数还是浮点数。目前没有这样的函数可以为我们提供给定数字的平方值。因此，让我们创建一个可以在 Microsoft Excel 中有效地完成此任务的函数。

第 1 步：在第一步中，在同一个模块中，我们将开始定义一个新的 FUNCTION 过程来计算数字的平方，名为 **SquareNum**。

第 2 步：然后，我们需要为该函数添加一个参数，并在括号中使用 `num` 作为该函数的一个参数。一旦您在函数中提供了一个参数，您就会看到 End Function 语句。

第 3 步：在此步骤中，在函数内部，我们可以添加代码来获取特定数字的平方。由于 `num` 是 **SquareNum 函数** 的参数，我们有两种方法可以获得所需的输出：

一种方法是将 `num` 乘以自身，或者另一种方法是利用幂运算符“^”来计算 `num` 的平方。在此示例中，我们将选择后一种方法，因为它使代码更通用且更简单。

第 4 步：之后，我们将单击“保存”按钮来保存代码，该按钮位于 Visual Basic 编辑器（VBE）下方，以便我们可以在 Microsoft Excel 工作表中使用名为 **SquareNum** 的函数。

假设我们有一组不同的数字，我们需要计算它们的平方值。在这种情况下，我们可以使用上面定义的 SquareNum 函数来有效地获得结果。

第 5 步：在 B2 单元格中，我们将开始输入 Square，然后就可以看到我们刚刚定义的用于计算数字平方的函数，之后双击它以选择它。

第 6 步：之后，我们将 A2 作为参数提供给此函数，以便它可以计算 A2 单元格中的数字的平方。

第 7 步：执行完上述步骤后，我们将关闭括号以完成公式，然后按键盘上的 Enter 键，即可得到数字 11 的平方值。

第 8 步：在此步骤中，我们将拖动公式到其余单元格以获取剩余数字的平方值。

Microsoft Excel 中有哪些用户定义函数类型？

Microsoft Excel 中可用的用户定义函数类型如下：

1. 无参数。
2. 带有一个参数。
3. 使用数组作为参数。
4. 使用多个参数。

无参数

众所周知，Microsoft Excel 包含许多不需要参数的标准函数（NOW、RAND、TODAY）。

• 例如，TODAY 函数返回当前日期，而 RAND 函数返回 0 到 1 之间的随机数，我们不需要为它们指定任何值。

最重要的一点是，我们也可以在 Visual Basic for Application (VBA) 中创建这样的函数。以下代码将在单元格中相应地写入我们的工作表名称。

代码

我们应该注意到，函数名后面的括号中没有参数，这是因为需要返回的结果不依赖于我们工作文件中的任何值，因此不需要任何参数。

除此之外，上述代码会将函数的输出定义为字符串数据。如果您不指定数据类型，Excel 会自动确定。

带有一个参数

现在我们将继续创建一个简单的函数，该函数可以有效地处理一个特定单元格中的单个参数。

我们的主要任务是从给定的文本字符串中提取最后一个单词，为此我们将使用以下代码：

代码

***The_Text*** 是选定单元格的值，我们可以将其指定为文本值。

***StrReverse*** 函数以相反的字符顺序返回文本，而 ***InStr*** 函数用于确定第一个空格的位置。通过 ***Left*** 函数，我们可以获取所有以第一个空格结尾的字符，我们还可以通过使用 ***Trim*** 来删除所有空格。StrReverse 有助于改变字符顺序；最后，我们将获得给定文本的最后一个单词。

由于此函数通常接收单元格值，因此在此情况下我们不需要使用 ***Application.Volatile***。

使用数组作为参数

我们都知道大多数 Microsoft Excel 函数都使用数组作为参数。

以下代码将帮助创建这样一个函数，它将对指定单元格范围内的所有偶数求和。

代码

在上面的代码中，***NumRange*** 参数主要被视为 ***rangeRange***，这意味着该函数将使用原始数据数组。它看起来如下：

此外，***Variant*** 类型提供了一个“非维度”容器来存储数据。因此，这种变量可以用于存储 VBA 中允许的任何类型的数据，包括数值、文本、日期等。

代码中有一个 ***For Each*** 参数，它会遍历每个单元格并检查它是否包含数字。如果它不包含数字，则什么也不发生，并继续到下一个单元格。如果找到数字，它会通过 **MOD** 函数检查该数字是否为偶数。

所有偶数都加到 ***Result*** 变量中，当循环结束时，***Result*** 值将被有效地赋给 ***SumEven*** 变量，然后传递给函数。

Excel 中的多个参数

许多 Microsoft Excel 函数都有多个参数。因此，能够有效地创建带有多参数的 UDF 非常重要。

代码

上面提到的代码主要有 3 个参数：一个值范围，一个数字字段的下限，以及一个范围的上限。

第一个是 ***the rngCells*** *As range*。它是用于在其中搜索最大值的单元格范围 Range。相比之下，第二个和第三个参数（*MinNum, MaxNum*）通常在没有类型声明的情况下指定，这意味着它们将自动应用 ***Variant 数据*** 类型。

此外，Visual Basic for Application (VBA) 使用 6 种不同的数字数据类型。仅指定其中一种意味着限制了函数的使用。因此，最好让 Microsoft Excel 来确定数字数据类型。

***For Each*** 循环顺序使用选定范围中的所有值。通过标准的 **MAX FUNCTION**，范围 Range 中的数字（从最大到最小值）被写入一个特殊数组 - ***arrNums***，以有效地找到该数组中的最大数字。

在 Microsoft Excel 中，用户需要记住哪些要点？

在使用 Excel 中的用户定义函数时，用户需要记住的重要事项如下：

1. 用户应该记住，通过 VBA 创建用户定义函数（UDF）可以创建自定义公式，从而减少执行任何任务所需的时间并简化任务。
2. 用户定义函数（UDF）仅限于为其创建的那个工作表。但是，您可以将代码复制并粘贴到您想要使用的其他工作表中。