vba 非易失性 UDF 总是重新计算

Non-volatile UDF always recalculating

提问by user3682716

I am trying to make a non-volatile UDF but it seems not possible. So here is a my very simple test-UDF:

我正在尝试制作非易失性 UDF，但似乎不可能。所以这是我的一个非常简单的测试-UDF：

Option Explicit
Dim i As Integer

Sub Main()

i = 0
[A1] = "zyy"
MsgBox i

End Sub

Function Test(rng As Range)
Application.Volatile (False)

Test = rng.Value
i = i + 1

End Function

I got a otherwise empty worksheet that uses this function a couple of times, and every time I call Main() and change any cell on the sheet with the UDFs all of them recalculate. How can I make this (any) UDF no-volatile? The Application.Volatile (False) should have that effect but obviously doesn't work.

我得到了一个空的工作表，它多次使用此函数，每次我调用 Main() 并使用 UDF 更改工作表上的任何单元格时，所有这些单元格都会重新计算。我怎样才能使这个（任何）UDF 不挥发？Application.Volatile (False) 应该有这种效果，但显然不起作用。

Edit: If I change a cell manually it works like intended, it only recalculates when I change a cell via VBA. Is this normal behaviour or can I change it?

编辑：如果我手动更改单元格，它会按预期工作，只有在我通过 VBA 更改单元格时才会重新计算。这是正常行为还是我可以改变它？

采纳答案by user3682716

I found the solution, and it is indeed a very simple one but also made this hard to debug: If you make any change to your VBA code all the UDF get flagged for recalculation! I modified the degug code of David:

我找到了解决方案，它确实是一个非常简单的解决方案，但也使调试变得困难：如果对 VBA 代码进行任何更改，所有 UDF 都会被标记为重新计算！我修改了大卫的调试代码：

Sub main()

'nothing depends on the Value in [A13]
[A13] = "" 
[A13] = "hgdg"
[A13] = ""
i = 46

End Sub

Function f_appvol(rng As Range)

Application.Volatile
Debug.Print "f_appvol"

f_appvol = rng.Value
End Function

Function f_appNOTvol(rng As Range)
Application.Volatile (False)
Debug.Print "f_appNOTvol"

f_appNOTvol = rng.Value
End Function

Function f_omit(rng As Range)

Debug.Print "f_omit"
f_omit = rng.Value

End Function

After entering the code and running it for the first time, only f_appvol is recalculated. If you now change i=46 to i=47 and execute it, all the UDF get recalculated. All subsequent runs after that first run after the change give the expected behaviour.

第一次输入代码运行后，只重新计算了f_appvol。如果您现在将 i=46 更改为 i=47 并执行它，则会重新计算所有 UDF。在更改后的第一次运行之后的所有后续运行都给出了预期的行为。

回答by David Zemens

I am posting a new answer instead of trying to salvage my previous answer, even though I think they point to the same thing, it will be better to start fresh.

我发布了一个新答案，而不是试图挽救我以前的答案，即使我认为它们指向同一件事，但最好重新开始。

Background:

背景：

Previously I had tested your code and the results were exactly as I would expect them to be if you simply omit the Falsefrom that statement. I have never seen any reason to explicitly do Application.Volatile (False), because that is equivalent to simply omitting the statement entirely.

以前我已经测试过您的代码，如果您只是False从该语句中省略 ，则结果完全符合我的预期。我从来没有看到有任何理由明确这样做Application.Volatile (False)，因为这相当于简单地完全省略该语句。

• If omitted, the function is non-volatile and the UDF evaluates onlywhen a reference changes (i.e., notwhen other, non-referent cells change)
• If included as Application.Volatile(or Application.Volatile (True), the UDF becomes volatile and any change to the worksheet will force re-evaluation.

• 如果省略，则该函数是非易失性的，并且 UDF仅在引用更改时（即，其他非引用单元格更改时不求值）
• 如果包含为Application.Volatile(或Application.Volatile (True)，UDF 将变得不稳定，对工作表的任何更改都将强制重新评估。

Continuing investigation

继续调查

You commented that you still observed otherwise. So I made some changes to my code and tested again. All of a sudden weird stuff was happening. No matter what I did with the Application.Volatilefunction, anychange to the worksheet was re-evaluating the UDF.

您评论说您仍然观察到其他情况。所以我对我的代码做了一些更改并再次测试。突然间发生了一些奇怪的事情。无论我对Application.Volatile函数做了什么，对工作表的任何更改都会重新评估 UDF。

This didn't make sense, so I started googling and doing a little more testing.

这没有意义，所以我开始使用谷歌搜索并进行更多测试。

In my tests I created three functions.

在我的测试中，我创建了三个函数。

1. The first one is explicitly Volatile:
2. The second one is explicitly not volatile.
3. The third omits any statement of volatility.

1. 第一个是明确的易失性：
2. 第二个明确不是 volatile。
3. 第三个省略了任何波动性陈述。

I put one instance of each formula on a worksheet. Each referenced a different range.

我将每个公式的一个实例放在工作表上。每个都引用了不同的范围。

I tested each of these by making changes to the worksheet (manually), and through a named subroutine. I used a Printstatement and monitored the Immediate window in the VBE to confirm that in all cases, the functions evaluated (or not) only as expected. The first one alwaysevaluates, while 2 and 3 only evaluate if reference range changed.

我通过对工作表进行更改（手动）并通过命名子例程来测试其中的每一个。我使用了一条Print语句并监视 VBE 中的“立即”窗口，以确认在所有情况下，函数仅按预期评估（或不评估）。第一个总是评估，而 2 和 3 只评估参考范围是否改变。

Function f_appvol(rng As Range)

Application.Volatile
Debug.Print "f_appvol"

f_appvol = rng.Value
End Function

Function f_appNOTvol(rng As Range)
Application.Volatile (False)
Debug.Print "f_appNOTvol"

f_appNOTvol = rng.Value
End Function

Function f_omit(rng As Range)

Debug.Print "f_omit"
f_omit = rng.Value

End Function

Then it got weird...

然后就奇怪了...

I started making changes within these functions and they start to behave wonky.

我开始在这些函数中进行更改，但它们的行为开始变得不稳定。

Specifically I got lucky and noticed that if I changed my non-volatile function to a volatile one, then all functions started acting as if they were volatile -- even the f_omit. I believe this may be the condition you are experiencing.

具体来说，我很幸运，并注意到如果我将非易失性函数更改为易失性函数，那么所有函数都开始表现得好像它们是易失性的——甚至f_omit. 我相信这可能是您正在经历的情况。

Somehow, we have managed to "confuse" Excel

不知何故，我们设法“混淆”了 Excel

I saved the workbook and tried again... back to normal!

我保存了工作簿并再次尝试...恢复正常！

Then I changed the argument in the volatile statement, and the strange behavior happened again.

然后我改变了 volatile 语句中的参数，奇怪的行为又发生了。

This appears to be a bug

这似乎是一个错误

I don't see anything in the documentationthat suggests this is normal/expected behavior, and it sure as hell is not desirablebehavior from a debugging standpoint. This is the sort of thing that makes you pull out your hair in frustration!

我在文档中没有看到任何表明这是正常/预期行为的内容，而且从调试的角度来看，这肯定不是理想的行为。这是一种让你沮丧地拔头发的事情！

I am using Excel 2010, Win 7 64b.

我使用的是 Excel 2010，Win 7 64b。

Resolution

解析度

The cause of the error seems to be making change to the volatility of a UDF.

错误的原因似乎是改变了 UDF 的波动性。

In order to restore expected behavior, it seems necessary to save the workbook. Again, I don't think this is normal but it seems to solve your problem (or at least a very similar problem that I was able to replicate while troubleshooting yours).

为了恢复预期的行为，似乎有必要保存工作簿。同样，我认为这不正常，但它似乎解决了您的问题（或者至少是我在对您的问题进行故障排除时能够复制的非常相似的问题）。

On a possibly related note

在一个可能相关的注释上

There appears to be at least one bug related to volatility, as mentioned here. I link to it mainly because this writer echos my own sentiment: there is no reason to do Application.Volatile (False)because that is (or should be) the "normal" state of a UDF.

似乎有至少一个错误有关的波动，提到这里。我链接到它主要是因为这位作者回应了我自己的观点：没有理由这样做，Application.Volatile (False)因为这是（或应该是）UDF 的“正常”状态。

I have to admit that I had never seen the point of using Application.Volatile False since thats supposed to be what you get if you omit the Application.Volatilestatement altogether.

我不得不承认，我从来没有看到使用 Application.Volatile False 的意义，因为如果Application.Volatile完全省略该语句，那应该就是你得到的。