Issue with UDF's showing #NAME? error

[mcomp72]

I have an Excel file with several user defined functions (UDF). When I open the file, sometimes (but not always) the cells that contain a UDF shows this error: #NAME?

If I type into a cell that is a parameter for the UDF, sometimes that will make the errors go away -- but not always.

I have tried pressing F9 (recalculate workbook) but that has no effect.

I have tried setting calculation method to manual and then back to automatic, but that also has no effect.

If I save & close the file and then reopen it, the error is gone. (Simply closing it doesn't always work... I have to save & close.)

Why do I sometimes see this error, and what can I do so it does not appear? Obviously there is nothing wrong with the UDF's themselves, or they would always show the #NAME? error. It appears Excel sometimes has a problem with them when opening a file, but I don't understand why.

 

[MARK858]

Try putting

Application.CalculationFullRebuild

in a workbook open event to see if it stops it happening.

If you want to try it manually when it happens use the shortcut CTRL+ALT+SHIFT+F9.

You'll also get the error if macro's aren't enabled but I'm assuming that isn't the case

 

[mcomp72]

Thanks for that suggestion. Just tried CTRL+ALT+SHIFT+F9, but it had no effect. I'll try putting in the FullRebuild line in Workbook_Open to see if that makes any difference, but since CTRL+ALT+SHIFT+F9 did not, I'm guessing it probably won't. Any other thoughts?

 

[MARK858]

Macro's are enabled I take it?

 

[mcomp72]

When the file is first opened, sometimes (but not always) I have to click "Enable Content" -- which will then enable the macros.

 

[MARK858]

You'll get that error if macro's are disabled when it calculates

 

[mcomp72]

Something else I forgot to mention in my original post. If the problem happens when I open the file, sometimes I can click the X in the top right of the Excel window, as if I am going to close the file. I get prompted if I want to save the file before close. When that message appears, sometimes the #NAME? errors go away. Then I can click Cancel on the save window and go back to working on the file. But this doesn't always happen, so I have to then proceed with saving & closing the file.

In terms of your last comment: once I click the "Enable Content" button, since that gives permission for the macros to run, if I have Application.CalculationFullRebuild in the Workbook_Open sub, should that always solve the issue?

 

[MARK858]

if I have Application.CalculationFullRebuild in the Workbook_Open sub, should that always solve the issue?

Doubtful if macro's are disabled when the workbook opens as it is past the trigger for the Workbook open event. You would need to run it separately but you also stated the manual shortcut didn't work, and so I doubt if rebuilding the dependencies is your issue.