Why would my Excel 2010 UDF quit working with a #NAME? error?

user_103206 0
Viewed 295

I have Excel 2010. A couple of months ago I wrote some UDF's and got them debugged and working. Today I changed a value in the spreadsheet, and suddenly all the UDF's displayed #NAME?

Ultimately I decided to start from scratch. I created a new workbook with just one UDF. Here it is, in it's entirety:

Function say7() As Integer
    say7 = 7
End Function

I then went to Sheet1 and in box A1 entered "=say7()". I got "#NAME?".

If I type "sa", the dropdown shows "say7". I can press tab or enter and it fills in the function name. And then it says #NAME?.

I understand that Excel 2010 is no longer supported. Did Microsoft do something to break UDFs?

My XLSM was working a few weeks ago. Indeed, the file where I made the change was a copy of the original spreadsheet. I went back to the original, changed a value in one cell that should have forced a recalculation, and it said #NAME?. I didn't change the code, I didn't change any settings, nothing. The only thing I changed was one data value and somehow that makes the function name not be recognized anymore.

Update: Several posters have pointed out that "say7" is not a legal name for a function in Excel and that using this name would produce a #REF? error. True. But my problem was that I was getting a #NAME? error, not a #REF? error. In my attempt to create a simplified scenario -- to make sure that the problem wasn't some error within the function -- I created a new, deliberately very simple function that got the same #NAME? error. Yes, I gave this function an illegal name, which muddles my question. I apologize for the confusion. Explaining why say7 is an invalid name is interesting and useful information, but sadly does not solve my original problem.

vba excel
3 Answers

When you are writing the function then there are conventional rules of naming it. One of them says

You should not use any names that are identical to the functions, statements, and methods in Visual Basic because you may shadow the same keywords in the language.

The name Say7 while it looks innocuous enough, clashes with a column numbered 12895 which is named as SAY. So in principle, below works for me but I do not have Excel 2010 to see and confirm.

Public Function SaySeven() As Integer
    SaySeven = 7
End Function

Edit:

After reading OP's own answer post and other details over, I can think of one more situation where we may encounter #NAME error. This can be reproduced fairly easily.

Step 1: Create a UDF and save the workbook. When you do so, it should not be saved at Excel's trusted locations and the file shall not be identified as trusted. If it is trusted then Excel doesn't prompt for enabling active content. At the point of creation, Excel will produce results without any error. Now close the workbook.

Step 2: Reopen this workbook. You will be prompted with below message. enter image description here

Ignore this message i.e. do not press "Enable Content" button. OP may have missed this message somewhere.

Step 3: If the UDF was implemented in the previous session and it has returned a valid result then it will not return any error upon recalculation. However, if you enter this UDF in a new cell then Excel will return #NAME error. In below snapshot, both green cell and yellow cell contain exactly the same formula i.e.

=dist3()

enter image description here

But the outcome differs.

So in conclusion: UDF name dist3 is valid and it should work as cited by OP in his later postings. However, in above specific case, one may encounter error of this kind. It will, however, apply to all UDFs and not just dist3.

I have tested this in Excel's Office 365 version but I don't think there will be any major differences in this behavior across versions.

user_87599270

I eventually partly figured it out. At least enough to get it working.

Excel normally blocks you from running UDF's unless you explicitly give it permission. When you try to use the first UDF it pops up a question at the top of the window asking for permission, and if you don't give it, then UDFs aren't recognized and you get #NAME? errors for them.

Apparently -- and this is the part I'm not sure about -- the message telling me that support for Excel 2010 had expired was displayed on top of this message so I couldn't see it. Or something like that. I copied the file to another computer that had a newer version of Excel and I got the message. I clicked OK and the UDFs worked. I then saved it and copied it back to the original computer and it worked fine.

As I had previously clicked Ok on this and this answer is apparently saved with the file, I'm not sure why it asked again. Maybe some changes to the file invalidate the previous answer? I'm not sure.

I created a new file and saw the message about "UDFs ok?" on that one. So maybe my theory that the message was covered by the expiration notice is not correct. I guess it's possible that the message was there all along and I was just blind and didn't see it. That seems unlikely as I was searching for some explanation, but, etc.

In my first version of this post I said that the function name "say7" worked. This was incorrect. When I got the spreadsheet to work I had gone back to my original spreadsheet, where the function that was referenced in the spreadsheet was called "dist3", which works. (There were other functions but they wee all called from within dist3.)

In any case, before I created "say7" I was trying to get it to work with a function called "dist3" and that was also failing with the #NAME?. And as I said in the question, it worked a month ago and I didn't change any function names, so I knew that an illegal function name was not the issue. My attempt to create a simple function to test the problem was a wrong turn because I used an illegal function name that created different problems.

user_1032060

Seeing the logic of @shrivallbha redij's reply I changed the function's name to SAYG (with the idea that G defines the 7th column) and still got a #NAME error. However this can be avoided by passing an argument in the function call, even if the argument isn't used by the function or if it's blank.

=SAYG - returns an error

=SAYG(A1) - returns 7

=SAYG() - returns 7

user_76754370
Related