If required you can bypass the problem as demonstrated in in the download, or by ensuring that there are no instances of the UDF where the multi-area range refers to the sheet with the UDF formula.
The bug exists in Excel 97, Excel 2000, Excel 2002 and partially in Excel 2003. If your UDF references cell properties other than .value or .formula (ie .
Un-trapped errors can halt the calculation process before it is complete.
Excel depends on analysis of the input arguments of a Function to determine when a Function needs to be evaluated by a recalculation.
What this means is that the function will only be flagged as needing evaluation when one or more of the input arguments change (unless the function is volatile).
See Areas Bug Bypass2for a download containing two workbooks that illustrates the problem and a way of bypassing it..
The problem occurs when: In these circumstances Excel/VBA incorrectly treats the multi-area range as referring to the active sheet.
If your UDF gets input values from any cells that are not in its argument list then it may not be recalculated, and give the wrong answer.
Mostly you can bypass this problem by doing a Full Calculation (Ctrl-Alt-F9), rather than a recalculation (F9), or by making your UDF volatile, but I strongly recommend that you put all the input cells in the argument list.Note that putting all the references in the argument list does not control the initial sequence which Excel uses to calculate the UDF, and it may well be calculated more than once per recalculation cycle.See UDFs evaluated more than once per workbook calculation.This means that the UDF may give incorrect answers without warning.I recommend that you do not attempt to program a UDF to handle multi-area ranges as arguments: use multiple (optional if there are a varying number) range arguments instead.To distinguish between uncalculated cells and cells without any data check that the length of the formula is greater than 0 (suggested by David Cuin) or use Cell. UDFs written using the C API can check for xl Coerce returning xlret Uncalced.