Refreshing user defined functions

[kMan]

Hello,

I am having this annnnnoying problem with Excel (2007 and 2003). I have a
user defined function in my workbook. The function works fine (well unless
it's not working fine, but I'm getting the results I want from the function).
Except once a while, especially if close and open file, the cell containing
the user defined function just displays "#REF!"..... Now if I double click
the cell, and then press enter, it's back to working as usual.... and the
cycle goes on...... Is there a way to somehow refresh automatically? Shift-F9
not working...

Thanks in advance

 

[Niek Otten]

Maybe you did not include all input for the function in the argument list?
If you access cells directly from within the function, Excel is not aware of that and does not include the cells in the dependency
tree, so recalculations may skip the function call.
You can include Application.Volatile in the function, but really the most reliable way is to include all input in the argument
list.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hello,
|
| I am having this annnnnoying problem with Excel (2007 and 2003). I have a
| user defined function in my workbook. The function works fine (well unless
| it's not working fine, but I'm getting the results I want from the function).
| Except once a while, especially if close and open file, the cell containing
| the user defined function just displays "#REF!"..... Now if I double click
| the cell, and then press enter, it's back to working as usual.... and the
| cycle goes on...... Is there a way to somehow refresh automatically? Shift-F9
| not working...
|
| Thanks in advance

 

[Bob Phillips]

I did suggest to you in the other thread that it was poor UDF design!

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[kMan]

Hehehe... I knew you'd say that.... It's all OK now (well that's a worry
actually), Excel seems to have fixed it self... I remember having this
problem with recursive calculations (using built-in functions), Excel would
display #REF! until I "touched" the cell and then it does it's recursive
thing.... looon ago it was