User Defined Function Not Does Not AutoCalc

[Henry Stockbridge]

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

 

[Charles Williams]

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

 

[Niek Otten]

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.

 

[Chip Pearson]

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]

 

[Henry Stockbridge]

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]

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.