Excel Produces #VALUE When Multiplying Two Numbers

Question

More Excel bugs:

Followed by this:

Restarting Excel did not fix this problem, but recalculating the entire spreadsheet does. So this is not reproducible. This is very similar to another problem I encountered, documented here: https://superuser.com/questions/1692017/excel-round-function-producing-value-error-when-given-valid-numeric-input

Answer 1

So does error checking produce any result?

Answer 2

The cell contains a UDF with a call to another UDF as one of its parameters. Error checking on this formula reports circular references but identifies none. There are no circular references. Stepping through Evaluate gets correct numbers all the way to the simple multiplication you see above. And the results are not consistent. If I change/replace all occurrences of '=' to '=', the sheet recalculates correctly. But a few more edits later, even on another sheet, and the errors are back.

Answer 3

For a dispositive explanation, you should upload an example Excel file that demonstrates the problem(s) to a file-sharing website, and post the public shared URL in a response here. I like box.net/files; others like dropbox.com.

I suspect that your UDF tries to modify the Excel "state". For example, it might try to modify a cell value or format directly. Or it might try to change an Excel option; e.g. Application.Calculation. Or it might directly or indirectly reference the cell that calls the UDF. (But in my simplistic example, my version of Excel has no problem identifying the circular reference culprit.)

Such changes are not permitted in UDFs or in Sub(routine)s that are called directly or indirectly from UDFs.

My guess is: Evaluate Formula and Find-and-Replace might work up to a point because they operate in a different context.