非易失性UDF总是重新计算 [英] Non-volatile UDF always recalculating

问题描述

我正在尝试制作非易失性UDF，但这似乎是不可能的.因此，这是我非常简单的test-UDF:

I am trying to make a non-volatile UDF but it seems not possible. So here is a my very simple test-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

我得到了一个否则为空的工作表，该工作表使用了两次此函数，每次我调用Main()并使用UDF更改工作表上的任何单元格时，它们都会重新计算.如何使这个(任何)UDF非易失性? Application.Volatile(False)应该具有这种效果，但显然不起作用.

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.

如果我手动更改单元格，则其工作原理与预期的一样，仅当我通过VBA更改单元格时才重新计算.这是正常行为还是可以更改?

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代码进行了任何更改，则所有UDF都将被标记为重新计算！ 我修改了David的degug代码:

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:

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

输入代码并首次运行后，仅重新计算f_appvol.如果现在将i = 46更改为i = 47并执行它，则将重新计算所有UDF.更改后的第一次运行之后的所有后续运行都会达到预期的性能.

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.

这篇关于非易失性UDF总是重新计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！