Hi,

I have created a user defined function that is stored in my personal
macro workbook. When I change a value in one of the referenced cells,
the result does not recalc. AutoCalc is set to true in Tools --
Options, but I still need to F9 to see the resulting value. (Excel
2003.)

Any help you can lend on this is appreciated.

Henry

If you post the UDF code we can take a look:
The most likely cause is the UDF referencing directly or indirectly cells
that are in the UDF arguemnt/parameter list.


Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Henry Stockbridge" wrote in message
...
Hi,

I have created a user defined function that is stored in my personal
macro workbook. When I change a value in one of the referenced cells,
the result does not recalc. AutoCalc is set to true in Tools --
Options, but I still need to F9 to see the resulting value. (Excel
2003.)

Any help you can lend on this is appreciated.

Henry

Always post your code

Did you include all input to the UDF in the argument list? Excel is not
aware of any "direct" references from inside the function to worksheet cells
or ranges, so then it doesn't recalculate.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Henry Stockbridge" wrote in message
...
Hi,

I have created a user defined function that is stored in my personal
macro workbook. When I change a value in one of the referenced cells,
the result does not recalc. AutoCalc is set to true in Tools --
Options, but I still need to F9 to see the resulting value. (Excel
2003.)

Any help you can lend on this is appreciated.

Henry

Are you sure that the cell being changed is a parameter to the
function? For example,

Function Times10(D As Double) As Double
Times10 = D*10
End Function

called in a formula as =Times10(A1)

will calculate when A1 is changed. However,

Function Times20() As Double
Times20 = Range("A1") * 10
End Function

will not calculate when A1 is changed because Excel has no knowledge
of what cells might be referenced within the VBA code.

You could add Application.Volatile to the function, which would cause
the function to be calculated any time any calculation is made:

Function Times10(D As Double) As Double
Application.Volatile True
Times10 = D*10
End Function

This can, however, add unnecessary overhead to the calculation cycle
because Times10 would be calculated even if it is not necessary.

And double check the Calculation setting in Tools - Options just to
be doubly sure.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]




On Tue, 15 Dec 2009 14:22:12 -0800 (PST), Henry Stockbridge
wrote:

Hi,

I have created a user defined function that is stored in my personal
macro workbook. When I change a value in one of the referenced cells,
the result does not recalc. AutoCalc is set to true in Tools --
Options, but I still need to F9 to see the resulting value. (Excel
2003.)

Any help you can lend on this is appreciated.

Henry

On Dec 15, 4:54*pm, Chip Pearson wrote:
Are you sure that the cell being changed is a parameter to the
function? *For example,

Function Times10(D As Double) As Double
* * * * Times10 = D*10
End Function

called in a formula as =Times10(A1)

will calculate when A1 is changed. However,

Function Times20() As Double
* * * * Times20 = Range("A1") * 10
End Function

will not calculate when A1 is changed because Excel has no knowledge
of what cells might be referenced within the VBA code.

You could add Application.Volatile to the function, which would cause
the function to be calculated any time any calculation is made:

Function Times10(D As Double) As Double
* * * * Application.Volatile True
* * * * Times10 = D*10
End Function

This can, however, add unnecessary overhead to the calculation cycle
because Times10 would be calculated even if it is not necessary.

And double check the Calculation setting in Tools - Options just to
be doubly sure.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLCwww.cpearson.com
[email on web site]

On Tue, 15 Dec 2009 14:22:12 -0800 (PST), Henry Stockbridge



wrote:
Hi,

I have created a user defined function that is stored in my personal
macro workbook. *When I change a value in one of the referenced cells,
the result does not recalc. *AutoCalc is set to true in Tools --
Options, but I still need to F9 to see the resulting value. *(Excel
2003.)

Any help you can lend on this is appreciated.

Henry- Hide quoted text -

- Show quoted text -

=====================

All set now. It was a parameter value issue, as suspected. Thank you.