Custom function incorrectly returns “#VALUE!” in Excel 2007

Question

I have a custom formula (“FindNumber(..)” e.g. “FindNumber(I236)”) that is returning “#VALUE!”.

Calculate Options is set to Automatic.

Clicking “Calculate Now” or “Calculate Sheet” does nothing.

Setting a break point in the custom function shows that the function isn’t actually called.

However, if I edit the formula, and press enter, my function is called and the result is shown.

If I alter the value of cell I236, then the formula updates.

It's like Excel is getting to the point where it thinks that the valaue has been calculated and doesn't need recalculating.

Answer 1

You will get a #VALUE error if you pass an invalid parameter (e.g., passing an Integer where the function requires a String). In this case, no code in the function is executed. Excel just blows it out of the water. Ensure that your parameters are correct.

Answer 2

Regarding your questions:

1. No.
2. No circular references.
3. No.
4. Yes, it does reference other cells in the function (entire sheets), but those sheets are either just data values, or if they do contain calculated values, then the references for them are self contained on that sheet.

The spreadsheet is quite large, perhaps the calculations on low spec'd machines are just taking too long and timing out? I don't know, just guessing really as to what causes it to get into this state periodically.

Not so much of an issue now we have a solution / work around.

I had exact same problem.  Calculatefull works but slows things down too much.  Adding xxxx=now() to the udf worked for me.

Answer 3

Consider changing the list separation symbol. For example, in Venezuela we use "," for decimal separation, so I had to set ";" as list separator. I was working with a UDF, and I worked perfectly on vba, but when I tried it on a spreadsheet it returns "#VALUE".. It results that in VBA you separte each argument with a comma, but in my case, for the spreadsheet I have to use a semicolon between each argument. 

It is a small detail, but it changes everything.

Answer 4

Andy wrote:

It's just every few weeks we get the files into a state where the functions just aren't getting called.

I'm fairly sure this is a bug in Excel 2007's dependency checking, as I've written a C# command line utility to fix it that basically does the following:

[....]

The call to CalculateFullRebuild fixes it (which further confirms that there's nothing wrong with the actual functions):

You may be correct.  I have had similar experiences.

However, at least in some cases, the root cause of the pervasive and bogus "errors" from UDFs is that Excel seems to abort the recalculation process due to some unrelated error.

It is as if the recalculation engine initially marks all UDFs as returning a #VALUE (or #NAME) error by default.  Normally, that is "fixed" when the UDF is eventually called during the recalculation cycle.  But if Excel aborts the recalculation process before that happens, the #VALUE (or #NAME) error sticks.

In that case, CalculateFullRebuild might remedy the problem simply because it reorders the recalculation.

So, some questions come to mind.

1. After you use CalculateFullRebuild, are there Excel errors in some other cells, perhaps errors that you tolerate by design?
2. Do you have any unintended circular references?
3. Do you enable Iterative calculation, and do you have purposeful any purposeful circular references, particularly systems of non-self-referential CRs?
4. Does the UDF reference other cells, thereby implicitly relying on a particular order of recalculation?

#4 is significant because Excel (2003 at least) recalculates some formulas multiple times, causing UDFs in those formulas to be be called multiple times, sometimes before implicit dependencies are met.  This can cause some UDFs to misbehave if their design does not tolerate these premature calls.

#3 is significant because I have seen instances where systems of non-self-referential circular references confuses the recalculation engine.

Answer 5

I have a custom formula (“FindNumber(..)” e.g. “FindNumber(I236)”) that is returning “#VALUE!”.

Calculate Options is set to Automatic.

Clicking “Calculate Now” or “Calculate Sheet” does nothing.

Setting a break point in the custom function shows that the function isn’t actually called.

However, if I edit the formula, and press enter, my function is called and the result is shown.

If I alter the value of cell I236, then the formula updates.

It's like Excel is getting to the point where it thinks that the valaue has been calculated and doesn't need recalculating.

Hi,

Post the function code.