fouled file/formula ?

  • Thread starter Thread starter drowetx
  • Start date Start date
D

drowetx

My large Excel 2000 spreadsheet has a new problem in a certain cell
(cell "X") : A trivial formula no longer works unless I reenter it.

FWIW, the cell formula at X is :
=(1+S24)*R156

Results in the referenced cells are as expected. The formula result is
shown to be 0, but should be 8.xxx.

Calculation is automatic; F9 is no help.

If I double-click the cell and then press ENTER, the correct result
appears (but sometimes there remains a similar problem in a referring
cell...). If I enter a constant in a referenced cell, and then UNDO
that entry, the correct result sometimes appears in X.

This is nuts.

Any ideas ?

Thanks,
David
 
Hi David

If this cell is the real problem then, yes, this is nuts. But could it be
that the preceding cells S24 or R156, or one of their precedents, get their
value from a custom function that doesn't work too well ?

Best wishes Harald
 
Hi Harald,

Uhhhhh, indeed there is a custom function called several cells and a
sheet up the road... I don't recall trouble with it, but it was the
first one I wrote in VBA. Oops.

S24 contains a constant. R156 references Q156, which references P156,
.... N156, which references a cell on another sheet calculated by the
custom function. Interestingly, the values in all those cells seem
fine; only in cell X is the problem apparent.

FWIW, the user function invokes Application.Volatile and some present
value methods.

Clearly you are on to the problem. What might I have done, or not done
?

Thanks,
David
 
You can do almost anything in a VBA function. So you can do almost anything
wrong there too <g>. Just make sure they recalculate properly and don't deal
with things that aren't explicitly passed to them -no "Range("A1")" stuff
inside VBA functions.

HTH. Best wishes Harald
 
Hi Harald,

I am satisfied that my function *calculation* is correct, but what must
be done in a user function to ensure correct REcalculation (other than
the application.volatile declaration) ?

And, how can it be that my cell X does not show the right result of its
simple formula applied to the numbers appearing in the referenced
cells, garbage though they may be ?

This smells of a race condition.

Thanks again,
David
 
Hi David

Impossible to say without seeing your function. But this is an illustration
of my previous post. DivideA should be OK at all instances, DivideB is a
pure disaster:

Function DivideA(D1 As Double, D2 As Double) As Double
If D2 = 0 Then
DivideA = 0
Else
DivideA = D1 / D2
End If
End Function

Function DivideB(D1 As Double) As Double
If Range("A2").Value = 0 Then
DivideA = 0
Else
DivideA = D1 / Range("A2").Value
End If
End Function

Note also that there are actions that doesn't start a recalculation. Like
coloring cells, deleting rows, ...

HTH. Best wishes Harald
 
Back
Top