MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How can I make a function calculate automatically?


Posted by GLITZ on December 28, 2000 1:46 PM

I've got a function that refers to a range of data to produce an answer.

If a cell in the refered to range changes, the function's answer doesn't update.

F9 doesn't work.
If I hit F2 and then enter on the cell with the function in it, the cell will then update.....

Am I missing something?
What do I need to change?


Posted by Tim Francis-Wright on December 28, 2000 1:52 PM

Put Application.Volatile in the code
for the function (I put it right before the
Dim statements, but the position may not matter).

Posted by Robert on December 28, 2000 2:25 PM

It does not really answer your question, but it is nice to know if the other answer does not work:

You could include in an event procedure:

Application.CalculateFull

if any cell content changes...

A forced recalc by keybord takes place with "ctl alt F9", this seems to be undocumented...

R.