Hi,
I have a complex sheet with long formulae and functions. This
normally works well but occasionally the cells with formulae or
functions display only #VALUE!

I have Application.Volatile at the start of each function and a full
recalculation does not cure the problem.

Only editing each cell and pressing enter gives the correct value.

I could use a workaround but would appreciate any suggestions of why
this should be happening so I can correct it.
Many thanks,
Rod

It sounds like the sheet is not really recalculated; watch the status bar to ensure "Calculate" does not show. However I have had complex workbooks such as you, and found that "Calculate" can sometimes not be shown, even though recalculation IS needed! I'll give you what I use as a lame workaround; it's absolutely an incomplete solution since I simply can't tell if the workbook is "dirty" thus I don't know if I'm overdue to do it:

It is to hit F2 and hit enter or the tab key. Once the focus arrives at the next cell (e.g., the one to the right), I can tell that the recalc has indeed been complete for that sheet.

Sorry to have nothing better than a clumsy workaround. It's no true solution. :(

On Jan 3, 5:53 am, REM wrote:
Hi,
I have a complex sheet with long formulae and functions. This
normally works well but occasionally the cells with formulae or
functions display only #VALUE!

I have Application.Volatile at the start of each function and a full
recalculation does not cure the problem.

Only editing each cell and pressing enter gives the correct value.

I could use a workaround but would appreciate any suggestions of why
this should be happening so I can correct it.
Many thanks,
Rod

"REM" wrote:
I have a complex sheet with long formulae and functions.
This normally works well but occasionally the cells with
formulae or functions display only #VALUE!

I have Application.Volatile at the start of each function
and a full recalculation does not cure the problem.

Only editing each cell and pressing enter gives the correct
value.

When a function returns a #VALUE error, it is usually either because there
is syntax error in the function call, or because there is an error in the
function itself.

The fact that you work around it by recalculating selectively (i.e.
re-editing) suggests to me that either the function has a dependency on
other cells, perhaps implicitly depending on a particular order of
evaluation, or an error in some other cell is aborting the recalculation
cycle.

(Although I think the latter results in a #NAME error, not a #VALUE error.)

Note that we cannot control the order of evalulation. I have found that
even when I try to control the order by judicious use of cell references,
Excel sometimes recalculates formulas out of order. (It actually
recalculates them twice so that the final order dependency is met.)

If you are not using On Error in your function already, I suggest that you
add the following:

On Error GoTo oops
.....your code....
Exit Function
oops:
Stop
End Function

That might help you isolate the problem if it is in your function.