当基础数据发生变化时，Sparklines不会更新（非易失性） [英] Sparklines not updating when underlying data changes (non-volatile)

问题描述

我有一个非易失性UDF，无论何时调用它都可以返回不同的值，比如类似于随机数生成器。

问题似乎是如果我使用我的UDF激活了几个单元格，当基础数据发生变化时，迷你图似乎没有更新。

对我的udf调用计算（F9）什么都不做，因为它不是易变的，但  调用CalculateFullRebuild（或按Ctrl + Alt + F9 / Ctrl + Alt + Shift + F9）将触发UDF更新其
输出值。那时  sparkline  不会更新。

迷你图更新的唯一方法是在其数据单元格中的任何位置一些不稳定的函数。

是否故意构建了这样的迷你图？或者这是对他们行为的疏忽。

有关此信息的任何信息将不胜感激。谢谢

（这与Excel 2010有关，我没有用其他版本的Excel测试过这个）

解决方案

默认情况下，Excel VBA中的UDF（用户定义函数）不易变。只有当函数的任何参数发生变化时，才会重新计算它们。只要在工作表上的任何单元格中进行计算，就会重新计算volatile函数。

所以我建议您可以定义一个易失性UDF，它可以在调用时返回不同的值，类似于随机数生成器

I have a non-volatile UDF that can return different values whenever it is called, say, something similar to Random number generator.

The problem seems to be that if I sparkline a few cells with my UDF, the sparkline doesn't seem to update when the underlying data changes.

Calling calculate (F9) on my udf does nothing, as it is not volatile, but calling CalculateFullRebuild (or pressing Ctrl+Alt+F9/Ctrl+Alt+Shift+F9) will trigger the UDFs to update their output value. At that time the sparkline will not update.

The only way the sparkline will update is if anywhere within its data cells there's some volatile function.

Were sparklines built like that intentionally? or is this some oversight on their behaviour.

Any info about this will be appreciated.Thanks

(this pertains to Excel 2010, I haven't tested this with other version of Excel)

解决方案

By default, UDF's (User Defined Functions) in Excel VBA are not volatile. They are only recalculated when any of the function's arguments change. A volatile function will be recalculated whenever calculation occurs in any cells on the worksheet.
So I suggest that you could define a volatile UDF that can return different values whenever it is called, something similar to Random number generator.

这篇关于当基础数据发生变化时，Sparklines不会更新（非易失性）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！