functions not updated automatically

  • Thread starter Thread starter Roland De Bouvere
  • Start date Start date
R

Roland De Bouvere

Hello,

I have a spreadsheet with a lot of functions I wrote myself.
The problem: when a value changes, the function is not recalculated. It's no
use pressing F2. The only way I can invoke an update is to select the cell
with the formula and to press Enter. But that's hardly practical.
Anyone who knows how to make sure all the functions are recalculated when
activating a sheet?

Thanks
 
Hi,

To make sure a UDF calculates include this in its code:

Application.Volatile

But that will force a recalc even if the cells that use
this udf (and their precedent cells) are not changed.

Another way is to make sure the function uses cell
arguments which are passed to the function from the
worksheet:

=YourUDF(A1)

When cell A1 has been changed, Excel will know it has to
go through your udf for the cells that contain the above
formula.

Regards,

Jan Karel Pieterse
Excel TA/MVP
 
It's not the solution I'm looking for.
My function uses cell arguments, but only to pass to the function. The cell
itself doesn't change, but the function uses a cell on another sheet to
calculate the result. The value of the cell on the other sheet changes, but
my function doesn't realize that the cell had changed, even though I
included Application.volatile.

a simple example

= MyUDF(A1)

function MyUDF(A1Value)
MyUDF = A1Value * anothersheet.range("B2")
end function

When I change B2, then nothing happens in the sheet containing MyUDF(A1).

Do you know another solution?

Thanks
 
Hi,
= MyUDF(A1)
function MyUDF(A1Value)
MyUDF = A1Value * anothersheet.range("B2")
end function
When I change B2, then nothing happens in the sheet containing MyUDF(A1).
Do you know another solution?
Click to expand...


Then, follow Jan's last suggestion and include all cell references as
arguments.
Example:
=MyUDF(A1, OtherSheet!B2)

function MyUDF(A1Value, ASecondValue)
MyUDF = A1Value * ASecondValue
end function

Regards,

Daniel M.
 
Back
Top