Issue with UDF's showing #NAME? error

mcomp72

Active Member
Joined
Aug 14, 2016
Messages
305
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Try putting
VBA Code:
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
 
Upvote 0
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?
 
Upvote 0
When the file is first opened, sometimes (but not always) I have to click "Enable Content" -- which will then enable the macros.
 
Upvote 0
You'll get that error if macro's are disabled when it calculates
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,884
Messages
6,175,174
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top