excel - 带参数的 Excel 自定义函数

您正在编写一个用户定义函数(UDF)，即Public Function在一个公共的标准模块中，它可以从工作表单元格中调用——并且这种特定类型的函数具有一组特定的约束。例如，不允许有副作用。UDF 接受一些输入，对其进行处理，然后返回一个结果。

因此，UDF 的签名应如下所示：

Public Function {name}({args}) As {type}

当你写一个函数时，首先要考虑的是你需要它返回什么——换句话说，在计算之后，说的单元格=MYFUNCTION(A1,A2)应该包含什么。例如，如果您编写了一个Add将两个Double值相加的函数，您会希望它返回一个Double：

Public Function Add(ByVal value1 As Double, ByVal value2 As Double) As Double

函数体根据给定的参数计算结果：

    Dim result As Double
    result = value1 + value2

在它返回/退出之前，您需要分配函数的返回值。这是通过分配给函数的标识符来完成的：

    Add = result

然后，Excel 的计算引擎会获取该结果，这就是带有公式的单元格的结果，=Add(2, 2)例如4.

您的STATUS函数取决于ActiveCell，这是当前在上选择的任何单元格ActiveSheet：它不是调用该函数的单元格。重新计算选择了一些随机单元格的工作簿可能会产生损坏的结果（如果有的话）。

作为一个 UDF，它不允许.ClearContents在一个单元格上（或以任何方式影响任何其他单元格） - 这就是为什么函数会#NAME?为进入条件块的执行路径返回错误，并且因为没有分配返回值，另一个执行路径产生0，它是Empty变体的数字表示，这是您的函数当前返回的内容。

如果 UDF 需要知道另一个单元格的值，最好的办法是将该单元格的值作为参数：这样函数就可以工作，而无需对工作表的布局进行任何假设。VLOOKUP如果它不带lookup_value参数而是从单元格中获取该值会有多大用处.Offset(0, 1)？会有骚乱！

当您的要求是做某事而不是计算/计算某事时，您需要的不是 UDF，而是宏。

宏是公共标准模块（或模块）中的无参数过程，可以从“宏”窗口调用，或者在用户单击 a 、 ActiveX时执行，或者可以将它们分配给某些属性自定义菜单项 - 让您的船摇摆不定。Public SubWorksheetShapeCommandButtonOnAction

Sub程序做某事，它们就是行动。他们可以访问和更改全局状态，修改任何单元格、工作表或工作簿；他们甚至可以生成一个 PowerPoint 实例并将图表作为图片粘贴到一个新的Slide- 你能想到的任何东西，天空是极限！

由于您在这里需要的是可以做事的东西，所以您需要编写的代码更像是一个宏。不要叫它STATUS；使用动词并描述它在做什么：您正在根据给定的标准将值从一列移动到另一列。编写Sub过程时，首先要考虑如何调用它。

我认为这样的事情会很整洁：

MoveValues Sheet1.Range("$B$2:$B$22"), "System"

所以签名看起来像这样：

Private Sub MoveValues(ByVal Target As Range, ByVal Criteria As String)

并且body现在可以遍历指定的Target范围，评估右侧的单元格是否与匹配Criteria，然后将值相应地向左移动。或者更好 - 我们根本不假设工作表布局如何，并像这样调用它：

With Sheet1
    MoveValues .Range("A2:A22"), .Range("B2:B22"), .Range("C2:C22"), "System"
End With

现在，如果我们需要在 A 和 B 之间或 B 和 C 之间插入一列，我们只需要更改传递给过程的参数，而不是过程本身！

Private Sub MoveValues(ByVal Source As Range, ByVal Target As Range, ByVal Status As Range, ByVal Criteria As String)

但首先，我们需要验证我们的假设，并决定当我们的期望没有得到满足时该怎么做——我们需要具有相同行数的单列范围！

在许多情况下，引发运行时错误是最好的做法。错误 #5 “无效的过程调用或参数”，似乎很合适：

    If Source.Columns.Count <> 1 Or Target.Columns.Count <> 1 Or  Status.Columns.Count <> 1 Or _
       Source.Rows.Count <> Target.Columns.Count Or _
       Status.Rows.Count <> Target.Columns.Count _
    Then
        Err.Raise 5
    End If

我们甚至可以自定义错误消息，以帮助我们稍后调试调用代码，当我们在 6 个月后更改参数并忘记有关该MoveValues过程假设的所有内容时：

    If Source.Columns.Count <> 1 Or Target.Columns.Count <> 1 Or  Status.Columns.Count <> 1 Or _
       Source.Rows.Count <> Target.Columns.Count Or _
       Status.Rows.Count <> Target.Columns.Count _
    Then
        Err.Raise 5, "MoveValues", _
            "Source, Target, and Status ranges must be 1 column and the same number of rows."
    End If

我们还需要验证我们Criteria的不是空的，或者只是空格！

    If Trim$(Criteria) = vbNullString Then
        Err.Raise 5, "MoveValues", "Criteria string cannot be empty or whitespace."
    End If

现在我们已经验证了我们的输入，接下来的过程可以安全地假设Source和范围都是单列范围，它们的大小都相同，并且我们有一个有效的标准可以使用Target。Status所以我们可以继续迭代单元格并做我们的事情：

    Dim current As Long
    For current = 1 To Target.Rows.Count
        If Status.Cells(current).Value = Criteria Then
            Target.Cells(current).Value = Source.Cells(current).Value
            Source.Cells(current).ClearContents 
        End If
    Next

现在剩下要做的就是编写一个调用它的宏：

Public Sub MoveSystemValues()
    With Sheet1
        MoveValues .Range("A2:A22"), .Range("B2:B22"), .Range("C2:C22"), "System"
    End With
End Sub

现在我们可以MoveSystemValues从 Excel 的“宏”窗口运行该宏，或者分配MoveSystemValues给一些Shape或按钮......然后意识到它对于少量行工作得很好，但在较大范围内相当慢 - 但我们有足够的时间咀嚼暂时开启。

excel - 带参数的 Excel 自定义函数

问题描述

解决方案

推荐阅读