vba 用户定义的函数不重新计算
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5719650/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
User Defined Functions NOT recalculating
提问by tpascale
I recently took a large, stable XLSM file, and split it apart into an XLAM and XLSX. Thousands of cells in the XLSX call (udfs) functions in the XLAM, and each such udf begins with the statement "Application.Volatile" (overkill, to force recalc).
我最近采用了一个大的、稳定的 XLSM 文件，并将其拆分为 XLAM 和 XLSX。XLSX 调用 (udfs) 中的数千个单元格在 XLAM 中起作用，每个这样的 udf 都以语句“Application.Volatile”开头（矫枉过正，强制重新计算）。
The XLSX will NOT recalc with F9 thru Ctrl-Alt-Shift F9, nor with Cell.Calculate thru Application.CalculateFull. The XLSX cells are simply "dead" ... but ... I can reawaken them one by one if I hit F2 to edit the formula and then hit ENTER. Cells reawakened this way seem to stay awake, and recalc normally thereafter.
XLSX 不会使用 F9 到 Ctrl-Alt-Shift F9 重新计算，也不会使用 Cell.Calculate 到 Application.CalculateFull。XLSX 单元格只是“死了”……但是……如果我按 F2 编辑公式然后按 ENTER，我可以将它们一个一个地重新唤醒。以这种方式重新唤醒的细胞似乎保持清醒，然后正常重新计算。
Has anyone encountered this strange behavior and are there any additional ways to force Excel to reconstruct the calc graph from scratch that I should try ?
有没有人遇到过这种奇怪的行为，是否有任何其他方法可以强制 Excel 从头开始重建我应该尝试的计算图？
One additional note in case it matters: I opened the XLAM and the XLSX via File Open, and have not installed the XLAM using the File ... Options ... Addins route - because in the past when I have done so, the minute you "uncheck" and installed XLAM then all the UDF references get replaced by full pathname links - pretty ugly. Alternatively if someone can outline a workaround for installing XLAM addins that doesn't create broken links everywhere I'll go with that.
一个额外的注意事项，以防万一：我通过 File Open 打开了 XLAM 和 XLSX，并且没有使用 File ... Options ... Addins 路线安装 XLAM - 因为在过去我这样做的时候，一分钟您“取消选中”并安装了 XLAM，然后所有 UDF 引用都被完整路径名链接替换 - 非常难看。或者，如果有人可以概述安装 XLAM 插件的解决方法，该方法不会在任何地方创建断开的链接，我都会这样做。
采纳答案by tpascale
Figured it out - not sure why Microsoft has this "feature":
想通了 - 不知道为什么微软有这个“功能”：
The condition arises when a virgin XLSX that uses an XLAM function is opened / created prior to opening the XLAM. In this case no amount of cajoling will cause the XLSX formulas to bind to and execute those XLAM functions, UNLESS you go into each cell & touch the formula bar & hit ENTER (or, as I discovered, do so en masse via a global replace - in my case all the funcs began w a "k", so globally replacing "k" with "k" fixed the error). The problem does not occur if the XLAM is opened first.
当在打开 XLAM 之前打开/创建使用 XLAM 函数的原始 XLSX 时，就会出现这种情况。在这种情况下，没有多少哄骗将导致 XLSX 公式绑定到并执行这些 XLAM 函数，除非您进入每个单元格并触摸公式栏并按 ENTER（或者，正如我发现的那样，通过全局替换来批量执行） - 在我的例子中，所有的函数都以“k”开头，所以用“k”全局替换“k”修复了错误）。如果首先打开 XLAM，则不会出现此问题。
回答by Greg Glynn
This works:
这有效：
Sub Force_Recalc()
Cells.Replace What:="=", Replacement:="=", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub
回答by Jamie Bull
You can force recalculation in this situation by search and replace on the =which is at the start of all formulae. You can also make this into a macro and map it to a key combination.
在这种情况下，您可以通过搜索和替换=所有公式开头的来强制重新计算。您还可以将其制作为宏并将其映射到组合键。
Edited to add
编辑添加
See the macro Greg Glynn has in his answer.
请参阅 Greg Glynn 在他的回答中的宏。
回答by DenisKolodin
回答by guest2
回答by Evan Winograd
One possible solution: Set calculation mode to manual, then back to automatic
一种可能的解决方案：将计算模式设置为手动，然后返回自动
Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic
回答by shmolf
Here is what I found. I haven't tested it but I believe there may be a work-around.
这是我发现的。我还没有测试过，但我相信可能有解决方法。
This is a direct quote: "Excel depends on analysis of the input arguments of a Function to determine when a Function needs to be evaluated by a recalculation. "
这是直接引用：“Excel 依赖于对函数输入参数的分析来确定何时需要通过重新计算来评估函数。”
from http://www.decisionmodels.com/calcsecretsj.htm
来自http://www.decisionmodels.com/calcsecretsj.htm
Here's what I'm going to try later today. I am going to generate a specific address of a table, dynamically within my function. Based on why we're here, I should not get an update should the value at the calculated address change.
这就是我今天稍后要尝试的内容。我将在我的函数中动态生成表的特定地址。基于我们为什么在这里，如果计算出的地址处的值发生变化，我不应该得到更新。
By including the whole table as a parameter, even without using the parameter, the function should update if anything in the table changes.
通过将整个表作为参数包含在内，即使不使用该参数，如果表中的任何内容发生变化，函数也应该更新。
In this way, your function hits the dependency tree regardless if you actually process the whole table.
这样，无论您是否实际处理整个表，您的函数都会命中依赖关系树。
回答by Ali Hussain Al Khawaher
Excel will monitor the range mentioned in the formula for any change, I faced this today and I was thinking and realized that. So to fix it, make a dummy argument in your function which takes the range you want to monitor or create a dummy function. In my case I called it monitorRange which returns nothing
Excel 将监控公式中提到的范围是否有任何变化，我今天遇到了这个问题，我正在思考并意识到这一点。因此，要修复它，请在您的函数中创建一个虚拟参数，该参数采用您想要监视的范围或创建一个虚拟函数。在我的情况下，我称它为 monitorRange，它不返回任何内容
Function monitorRange(rng As Range)
End Function
and I mentioned it in my formula example
我在我的公式示例中提到了它
=myfunction(a,b) & monitorRange(RANGE_TO_MONITOR)
This worked very well and it should work with any other function
这工作得很好，它应该与任何其他功能一起使用
回答by Paul Kinney
Ice ages after the original question, but ran into a similar problem just today where I had created a UDF to determine which values of a pivot table filter were selected. The UDF would work fine in when running in macro editor as well as when directly updating the field where the UDF was used, but would throw a "#VALUE!" when updating the sheet or pivot table.
在最初的问题之后冰河时代，但就在今天我遇到了一个类似的问题，我创建了一个 UDF 来确定选择了哪些数据透视表过滤器的值。在宏编辑器中运行以及直接更新使用 UDF 的字段时，UDF 可以正常工作，但#VALUE!在更新工作表或数据透视表时会抛出“ ”。
It was killing me until I incrementally added content from my original UDF to Ali's simple monitorRange function. I then found that my UDF had a pivot table refresh statement that when removed eliminated the "#VALUE!" error. Below is the specific offending lines from my UDF, but I the general rule is that the UDF cannot have any code in its call chain that updates other workbook content. That is, the UDF must only GET values, NOT SET values.
直到我将原始 UDF 中的内容逐渐添加到 Ali 的简单 monitorRange 函数之前，这让我很痛苦。然后我发现我的 UDF 有一个数据透视表刷新语句，删除时消除了“ #VALUE!”错误。下面是来自我的 UDF 的特定违规行，但我的一般规则是 UDF 的调用链中不能包含任何更新其他工作簿内容的代码。也就是说，UDF 只能获取 GET 值，而不是 SET 值。
I verified this with the following two scenarios, which both cause this error:
我通过以下两种情况验证了这一点，这两种情况都会导致此错误：
- Refresh a pivot, e.g.:
pt.PivotCache.Refresh - Change a value of another cell, e.g.:
Range("AA1").Value = "Test"
- 刷新一个枢轴，例如：
pt.PivotCache.Refresh - 更改另一个单元格的值，例如：
Range("AA1").Value = "Test"
Strangely, I tested and found that it is absolutely okay (kinda cool, actually) that the UDF can include a MsgBox call to display a dialog without problem. This would allow a UDF to monitor a range, then popup a msgbox dialog for any condition you wish to include in the UDF. I will keep that in mind for other situations.
奇怪的是，我测试并发现 UDF 可以包含 MsgBox 调用以毫无问题地显示对话框绝对没问题（实际上有点酷）。这将允许 UDF 监视范围，然后针对您希望包含在 UDF 中的任何条件弹出一个 msgbox 对话框。对于其他情况，我会记住这一点。
Hope this helps others running afoul of this nasty litter issue.
希望这可以帮助其他人与这个讨厌的垃圾问题发生冲突。
回答by Pham Vu Hong Linh
My screen shot:
我的屏幕截图：
I had the same problem. Find and Replace works, but not very nice. My solution is:
我有同样的问题。查找和替换工作，但不是很好。我的解决办法是：
go to Data tab > Edit links > Click Open Source will resolve this
转到数据选项卡> 编辑链接> 单击开源将解决此问题
