Refreshing Formulas

[Alan Kinney]

I've created a user-defined function in a module and used it in a
spreadsheet with a nested standard function.

For some of the spreadsheets where I've used this formula, the formula
requires refreshing in order to display the correct values each time the
spreadsheet is opened.

These spreadsheets were created for others to use and I don't them to have
to refresh these formulas. How can these formulas be automatically refreshed
when the spreadsheet is opened? I've already checked the automatic
calculation box on the "Calculation" tab under options.

 

[hgrove]

Alan Kinney wrote...

I've created a user-defined function in a module and used it in spreadsheet
with a nested standard function.

For some of the spreadsheets where I've used this formula, the formul requires
refreshing in order to display the correct values each time th spreadsheet is
opened.

...

By 'refreshing' I assume you mean recalculating by pressing [F9]. I
so, you should read about Application.Volatile in Excel VBA online hel
in the Visual Basic Editor

 

[Gord Dibben]

Alan

Posting by Charles Williams.............

Excel only recalculates functions when:
- their input arguments change
- they are volatile

so either put the range(s) you are looking up as an argument to the
function (best solution) or put application.volatile in the function.

Be warned that using application.volatile will make the function always
calculate even when the data has not changed, and so will slow down
recalculation.

For more on UDF calculation from Charles see.......

http://www.decisionmodels.com/calcsecretsj.htm

Gord Dibben Excel MVP