在 stackoverflow 上重新发布，因为这个问题可能与编程更加相关
我正在一个工作簿中的两个工作表上使用两个结构化表。
我在 SheetA 中使用我的 UDF，它接受 3 个参数作为字符串。
第一个参数是ID列，第二个参数是其列标题单元格上的前8个字符，第三个参数是其列标题单元格上的后8个字符。
UDF 应用于许多其他类似的单元格。列将随着时间的推移而扩大。ID 和标题单元格都是静态值。它们不是从其他单元格派生而来的。
UDF 将通过“Application.Match”搜索 TableB，并从匹配的行和列中提取值。如果所有内容有效，它将返回适当的结果。
工作表 A：表 A
| ID | 04/07/21 - 10/07/21 | 21/07/11 - 21/07/17 |
|---|---|---|
| 123456 | =UDF(表A:[@[ID]:[ID]],LEFT(表A[[#标题],[04/07/21 - 10/07/21]],8),Right(表A[[#标题],[04/07/21 - 10/07/21]],8)) | =UDF(表A:[@[ID]:[ID]],LEFT(表A[[#标题],[11/07/21 - 17/07/21,8),Right(表A[[#标题],[11/07/21 - 17/07/21]],8)) |
| 美国大都会牙科协会 | =UDF(表A:[@[ID]:[ID]],LEFT(表A[[#标题],[04/07/21 - 10/07/21]],8),Right(表A[[#标题],[04/07/21 - 10/07/21]],8)) | =UDF(表A:[@[ID]:[ID]],LEFT(表A[[#标题],[11/07/21 - 17/07/21,8),Right(表A[[#标题],[11/07/21 - 17/07/21]],8)) |
工作表 B：表格 B
| 日期 | 123456 | 美国大都会牙科协会 |
|---|---|---|
| 04/07/21 | 5.0 | -- |
| 05/07/21 | -- | 7.5 |
| 06/07/21 | -- | 7.5 |
| 07/07/21 | 8 | -- |
| 08/07/21 | -- | -- |
| 09/07/21 | -- | -- |
| 10/07/21 | -- | -- |
| 11/07/21 | -- | -- |
我的问题是，每当我编辑表格（即易变的）时，即使我没有编辑这三个参数/相关单元格，我的 UDF 也会导致耗时的计算。此外，每当我折叠/展开分组表格列时，UDF 都会重新计算。我希望仅让 UDF 重新计算（当其中一个参数发生变化时）或（如果“SumRange”中的值发生变化）。当匹配范围更新时，结果应该表现得有点像索引/匹配公式。
这里显示了 UDF：
Function UDF(ID As String, Date1 As String, Date2 As String) As Variant
TargetTable = "TableB"
Set WS1 = ThisWorkbook.Worksheets("SheetB")
With WS1
matchCol = Application.Match(ID, .ListObjects(TargetTable).HeaderRowRange, 0)
If IsError(matchCol) Then
UDF = "ID not found"
Exit Function
End If
matchRow1 = Application.Match(CLng(CDate(Date1)), .ListObjects(TargetTable).ListColumns("Date").Range, 0)
matchRow2 = Application.Match(CLng(CDate(Date2)), .ListObjects(TargetTable).ListColumns("Date").Range, 0)
Set SumRange = .Range(.Cells(matchRow1, matchCol), .Cells(matchRow2, matchCol))
Arr = SumRange.Value
For Each cel In Arr
If Len(Trim(cel)) > 0 Then
UDF = Application.Sum(SumRange)
Exit Function
End If
Next cel
End With
UDF = ""
End Function
答案1
每当任何单元格的值发生改变或任何其他事件触发重新计算时，都会重新计算易失性 UDF。
因此，向单元格添加数据会导致 UDF 重新计算。
当您添加、编辑或删除任何单元格（无论是否为表格单元格）的条目时，您对 UDF 的所有使用都将重新计算。
根据您对 UDF 的使用次数（以及每次计算所需的时间，尽管在您描述的情况下这似乎不太可能持续很长时间），即使没有任何问题，您也很容易看到明显的滞后。
这是使 UDF 可变的基本方法，也是“我为什么不让每个 UDF 都可变？”这个问题的答案。请注意它们与 Excel 内置函数之间的区别，Excel 会正确地将其构建到计算树中，并且只在需要时重新计算。不幸的是，没有更多的 VBA 工作就没有中间立场。
您可以做的一件事可能是使其非易失性，并为您正在编辑的行中的单元格添加 ON KEY 重新计算命令。这样，一行中的编辑将强制重新计算该行的 UDF（但不会重新计算其他 UDF），虽然您可能有 50 列的单元格执行此操作，但这只是对一个 UDF 使用的 50 次重新计算，而不是对所有 UDF 的 50 次重新计算。在 1,000 或 20,000 行的表中，这可能会有所不同。
对您的 UDF 来说唯一重要的直接更改是其所在行的 ID 列单元格。如果使用 ON KEY 重新计算行中的 UDF 单元格，就可以了。可能。如果发生任何间接更改，例如 ID 列单元格填充了公式，而该公式的依赖项会看到一些值更改，则 ON KEY 方法将无法捕捉到该更改。
所以有优点也有缺点。你可以决定它们加起来如何。
UDF 波动率设置的文档可在以下位置找到：
https://docs.microsoft.com/en-us/office/vba/api/excel.application.volatile
答案2
这不是您的 UDF 的问题，而是您如何调用它的问题。
=UDF(TableA[@[ID]:[ID]],LEFT(TableA[[#Headers],[04/07/21 - 10/07/21]],8),RIGHT(TableA[[#Headers],[04/07/21 - 10/07/21]],8))
这[@[ID]:[ID]]是不正确的语法。它计算结果为，[@[ID]]但它看起来像一个数组。当我将其更改为时[@[ID]]，您的 UDF 不再表现为易失性。