用户定义的函数不重新计算 [英] User Defined Functions NOT recalculating

问题描述

我最近采取了一个大型，稳定的XLSM文件，并将其拆分成XLAM和XLSX。 XLSX调用（udfs）中的数千个单元格在XLAM中起作用，每个这样的udf从语句Application.Volatile开始（overkill，强制重新计算）。

XLSX不会用F9通过Ctrl-Alt-Shift F9，也不能通过Application.CalculateFull来计算Cell.Calculate。 XLSX单元格是简单的死...但是，如果我按F2编辑公式，然后按ENTER键，我可以逐个重新唤醒它们。以这种方式重新唤醒的细胞似乎保持清醒，并在之后恢复正常。

有没有人遇到这种奇怪的行为，还有其他方法来强制Excel重建计算图我应该尝试一下吗？

一个额外的注意事项，如果重要：我通过文件打开打开了XLAM和XLSX，并没有使用文件安装XLAM。 ..选项... Addins路由 - 因为在过去，当我这样做，一分钟你取消选中并安装XLAM然后所有的UDF引用被替换为完整的路径名链接 - 很丑陋。或者，如果有人可以勾画出安装XLAM加载项的解决方法，这些插件不会在任何地方创建破坏的链接，那么我会去。

解决方案

想出来 - 不知道为什么Microsoft有这个功能：

当使用XLAM功能的处女XLSX打开/创建之前，会出现这种情况XLAM。在这种情况下，没有数量的cajoling会导致XLSX公式绑定并执行这些XLAM函数，除非你进入每个单元格&触摸公式栏& （或者，正如我所发现的那样，通过全局替换，大体上这样做 - 在我的情况下，所有的功能开始都是k，所以用k替换k来修正错误）。如果首先打开XLAM，则不会出现此问题。

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).

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.

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 ?

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.

解决方案

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.

这篇关于用户定义的函数不重新计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！