none
Sparklines not updating when underlying data changes (non-volatile) RRS feed

  • Question

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

    Tuesday, August 4, 2015 10:40 PM

All replies

  • 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.
    • Proposed as answer by David_JunFeng Tuesday, August 18, 2015 1:41 AM
    Thursday, August 6, 2015 8:29 AM
  • Yes, that's all fine and good, I'm aware the UDFs are not volatile, and I'd actually prefer to keep my UDF non-volatile as it needs to get data from DB every time it's called.

    The problem I was describing relates to Sparklines and their inability to work with non-volatile UDFs. I would have expected sparklines to always represent the data they consume, but that is not the case when the data changes due to firm recalculation.

    Thursday, August 6, 2015 2:55 PM
  • >>>The problem I was describing relates to Sparklines and  their inability to work with non-volatile UDFs.

    As far as I know this issue is by design, I suggest that you can submit feedback to Office.
    https://support2.microsoft.com/common/survey.aspx?scid=sw;en-us;2222&altStyle=MFE&renderOption=OverrideDefault&showpage=1&fr=1&nofrbrand=1

    Friday, August 7, 2015 9:56 AM