Excel Chart is not updated if a formula values chages

Add-in Express™ Support Service
That's what is more important than anything else

Excel Chart is not updated if a formula values chages
UDF from Xll AddIn, that returns random values do not update a chart that references this value. 
Dirk




Posts: 73
 Joined: 2017-10-05
Hello,

we have a Xll AddIn with formulas that are generating new data. If we update the data excel shows new values for that formula result. We update the data by triggering CalculateFull. But if the cell is used in a bar chart (other charts also don't work) the chart is not updated.
Is there any known way to force excel to update the chart data?
We tried already Calculate,CalculateFull and other CalculateFullRebuild.

It seems to be an excel problem, because if we use a VBA Marco like this:

Public Function RollTheDice() As Double
Dim val As Double
val = Rnd()
RollTheDice = 6 * val
End Function

And use the RollTheDice in a Cell on a sheet, we see the same behavior.

There are several ugly workarounds for the Problem on stackoverflow, like change a column width and restore original width.

Best Regards
Dirk
Posted 19 Dec, 2018 03:53:45 Top
Andrei Smolin


Add-in Express team


Posts: 19138
 Joined: 2006-05-11
Hello Dirk,

It works for me if I declare that UDF volatile. With VBA you do this as shown below. Excel recalculates all cells and charts that use a volatile function whenever you change a cell. 

Public Function RollTheDice() As Double
Application.Volatile True
Dim val As Double
val = Rnd()
RollTheDice = 6 * val
End Function


What do you think?


Andrei Smolin
Add-in Express Team Leader
Posted 19 Dec, 2018 04:20:37 Top
Dirk




Posts: 73
 Joined: 2017-10-05
GREAT! Thanks a lot. Works on my computer too. I will try with our UDF from the XLL Add In.
Will this have an impact on performance?
Posted 19 Dec, 2018 05:27:42 Top
Andrei Smolin


Add-in Express team


Posts: 19138
 Joined: 2006-05-11
Glad to be of help!

Well, recalculating all formulas referring to your function will take extra time.


Andrei Smolin
Add-in Express Team Leader
Posted 19 Dec, 2018 05:43:25 Top