I have a workbook with a rather complex set of vba UDFs used to perform thermodynamic flash calculations. Multiple vba routines are called for each udf call. There may be +100 individual UDF calls within a work book across multiple sheets. Many of these will be interlinked with functional dependencies that can extend across sheets. The UDFs typically involve iterative solvers and can take anywhere from .1 to 100 ms to resolve. The udf calls are all written written with array inputs and outputs .
In general the system works well in 2007, 2010, and 365. However, when opening or switching to another workbook, the calculations will get clobbered with #value ! showing in all the udf cells including the most senior dependency. The problem can also occur if a copy and paste is performed as well as other relatively innocuous tasks within the workbook itself. These issues appear to be independent of Excel version albeit 365 seems more problematic.
The errors can be cleared by forcing a recalculate on the workbook or more reliably by executing a vba calculatefullrebuild as F9 is not always sufficient. The vba call always fixes the things.
The problem is mostly an annoyance but clearly degrades implementation appearance. I'm in the process of rewriting the code and would like guidance on options to avoid this problem. Since rebuilding the formulas always fixes the issues I'm thinking Excel's method for managing the dependencies is getting goobered. If so, are there layout strategies that fix such as left to right sheet and formula ordering and top to down as well as limiting cross sheet udf linkages? Or are there other issues driving the instabilities? Thanks!
In general the system works well in 2007, 2010, and 365. However, when opening or switching to another workbook, the calculations will get clobbered with #value ! showing in all the udf cells including the most senior dependency. The problem can also occur if a copy and paste is performed as well as other relatively innocuous tasks within the workbook itself. These issues appear to be independent of Excel version albeit 365 seems more problematic.
The errors can be cleared by forcing a recalculate on the workbook or more reliably by executing a vba calculatefullrebuild as F9 is not always sufficient. The vba call always fixes the things.
The problem is mostly an annoyance but clearly degrades implementation appearance. I'm in the process of rewriting the code and would like guidance on options to avoid this problem. Since rebuilding the formulas always fixes the issues I'm thinking Excel's method for managing the dependencies is getting goobered. If so, are there layout strategies that fix such as left to right sheet and formula ordering and top to down as well as limiting cross sheet udf linkages? Or are there other issues driving the instabilities? Thanks!
