Updating Values For Custom Formulas

[FARAZ QURESHI]

Hi,

I have created a custom function to shorten a lengthy sumproduct formula.
However, when the source cells' values are changed the custom function's
results are not automatically updated & I have to press F9, while the
original lengthy formula updates automatically.

What might be the reason? How can the same be done so.

Thanx!

 

[T. Valko]

You can add this line of code to your function:

Application.Volatile

Like this:

Function MyFunct(......)
Application.Volatile

However, this will cause the function to recalculate *every time* a
calculation happens even if the dependent cells didn't change. In which case
your original lengthy formula is probably a better choice.

 

[Niek Otten]

In addition to Biff's advice:

The best way is to include all input cells in the argument list. That is the only way Excel recognizes the dependencies correctly.
Then the function calls will be recalculated when and only when needed.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| You can add this line of code to your function:
|
| Application.Volatile
|
| Like this:
|
| Function MyFunct(......)
| Application.Volatile
|
| However, this will cause the function to recalculate *every time* a
| calculation happens even if the dependent cells didn't change. In which case
| your original lengthy formula is probably a better choice.
|
|
| --
| Biff
| Microsoft Excel MVP
|
|
| | > Hi,
| >
| > I have created a custom function to shorten a lengthy sumproduct formula.
| > However, when the source cells' values are changed the custom function's
| > results are not automatically updated & I have to press F9, while the
| > original lengthy formula updates automatically.
| >
| > What might be the reason? How can the same be done so.
| >
| > Thanx!
|
|