Stability issues with complex array UDF calls


New Member
Dec 7, 2018
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!

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...