☰

自定义公式未更新

tag-icon • tag-icon microsoft-excel worksheet-function vba
自定义公式未更新

我跟着本指南创建以下自定义函数，计算电子表格中某种颜色的单元格数量：

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
    If SUM = True Then
       For Each rCell In rRange
        If rCell.Interior.ColorIndex = lCol Then
                vResult = WorksheetFunction.SUM(rCell) + vResult
        End If
       Next rCell
    Else
        For Each rCell In rRange
        If rCell.Interior.ColorIndex = lCol Then
                vResult = 1 + vResult
        End If
       Next rCell
End If
ColorFunction = vResult
End Function

它工作正常，如果我单击公式栏中的绿色勾号，它会给出预期的结果，但在我为另一个单元格着色后不会自动更新。如何将其设置为每次我在电子表格的其他位置手动为单元格着色时自动更新计数？

额外信息：我在 Mac 上使用 Excel，并且已检查设置并已打开自动计算。

答案1

Excel 仅当 UDF 的输入发生变化时才重新计算

您已创建用户定义函数 (UDF)。仅当作为函数输入的单元格发生更改时，Excel 才会运行 UDF 的代码。

例如，假设我有这个 UDF：

Public Function MyFunction(Target As Range)
MsgBox "The target cell's address is " &  Target.Address
End Function

在工作表的 A1 单元格中，我使用以下公式引用该单元格：

=MyFunction(A2)

在正常使用工作簿期间，仅当单元格 A2 或 A2 中公式引用的另一个单元格的内容发生变化时，才会调用我的 UDF 代码。

解决方案

您可以通过多种方式解决此问题：

  1. 强制 Excel 手动重新计算工作簿中的所有公式，即使它们自上次计算以来没有发生改变紧迫 Ctrl在 Windows 上是+ Alt+ F9，在 Mac 上（我假设）是Cmd+ Alt+ F9。如果这不起作用，您还可以尝试添加Shift，在重新计算之前还会重新检查相关公式。

  2. 在 UDF 引用的单元格之一中包含一个易失性函数。通过修改 VBA 函数的定义以接受附加参数来实现此目的：

    Public Function MyFunction(Target As Range, Optional VolatileParameter As Variant)

    然后编辑引用 UDF 的单元格以将易失性函数的结果传递Now()给 UDF：

    =MyFunction(A2,Now())

    最终结果是，每次工作表更改时，Excel 都会认为包含对 UDF 的引用的单元格需要重新计算，因为它引用了一个易失性函数。

  3. 编辑包含 UDF 的单元格。只需进入单元格然后按 Enter 键而不进行任何更改就足以触发更新。

  4. 创建运行以下命令的宏按钮代码行：

    Application.CalculateFull

  5. 将以下代码放入工作簿的 Open 事件中：

    ActiveWorkbook.ForceFullCalculation = True

    这会导致 Excel总是重新计算所有公式，无论它是否认为需要重新计算。此设置在重新启动 Excel 之前一直有效。

为什么 Excel 不总是重新计算我的 UDF？

Excel 执行自动重新计算时，不会重新计算工作簿中的每个公式。相反，它只会更新包含引用最近修改的单元格的公式的单元格。此方法避免了在整个工作簿中不必要地执行许多计算而导致绝大多数计算结果相同的漫长过程。

使用 UDF 时，Excel 计算引擎唯一能识别的工作簿引用是函数输入中标识的引用。Excel 无法检查 UDF 中的 VBA 代码并识别其他单元格可能会影响函数的输出。因此，尽管函数的作者可能会根据工作簿中所做的任何更改来构造函数以返回不同的结果，但 Excel 知道的唯一会更改函数结果的单元格是声明的输入。因此，在决定是否需要重新计算 UDF 时，Excel 只会关注这些单元格的更改。

答案2

用户定义函数本身可以被标记为易失性的，每次工作簿中发生变化时都强制重新计算。

要做到这一点，您必须Application.Volatile从函数内部调用。

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Application.Volatile

Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
    If SUM = True Then
       For Each rCell In rRange
        If rCell.Interior.ColorIndex = lCol Then
                vResult = WorksheetFunction.SUM(rCell) + vResult
        End If
       Next rCell
    Else
        For Each rCell In rRange
        If rCell.Interior.ColorIndex = lCol Then
                vResult = 1 + vResult
        End If
       Next rCell
End If
ColorFunction = vResult
End Function

答案3

我希望在 ActiveX 文本框发生更改时更新所有用户定义函数，即使这些函数不影响用作 UDF 输入的链接单元格，我也会遇到此问题。我偶然发现包括任何函数调用中的间接单元格引用似乎使得它在（似乎）编辑任何其他单元格时运行。这可能是因为当使用间接修饰符时，excel 对于可以信任哪些内容不会被修改的保证较少。这类似于 Twisty Impersonator 的第二个答案，但会减少 vba 代码和公式的繁琐程度（只要函数中使用至少一个范围）。

例如：此函数应始终返回“A2”中的值

Public Function test(some_arg)
test = Sheets("Sheet1").Range("A2").Value
End Function

如果您像这样调用它， =test(B1)它就不会在 A2 发生变化时更新 如果您像这样调用它，=test(INDIRECT(ADDRESS(ROW(B1),COLUMN(B1)))它在功能上是等效的，并且会在 A2 发生变化时更新。

=test(INDIRECT("B1"))也可以使用，并且对于这个例子来说可能更容易理解，但是因为“B1”是一个字符串，所以你不能在新的值上复制公式。

答案4

这些对我都不起作用。我发现唯一能让我的 UDF 进行计算的方法是将下面的代码添加到 worksheet.activate 事件中，并在打开时将焦点指向另一张工作表。然后，当我单击主工作表的选项卡时，它就起作用了。

Application.CalculateFullRebuild

相关内容