Share via

#NAME? error in Excel formulas that use User Defined Functions

Anonymous
2023-01-08T20:18:49+00:00

I created a macro-enabled workbook in Excel that is used by around 100 people, mostly on Mac.

In my first version of the file, I used regular formulas, meaning with functions that were built into Excel. However, in my second version, which is what people are using now, I changed most formulas to use User Defined Functions, as it gave me a little more flexibility with what I could do with the formulas.

Several people are reporting that some cells with those formulas are changing to #NAME? at random times, instead of showing the result of the formula. This actually happened to me several time as I was creating it, and for that I was using Excel for Windows.

I've researched the issue, and found one forum post on Stack Overflow that said "Excel sometimes has an issue 'remembering' the dependency chains for its calculations. A symptom of this is when a known functioning file has strange errors in cells that you know should have values."

When this happened to me, I would save the file, close it, and reopen it. Every time I did this, the #NAME? error would be gone. So far, this has also worked for my users. However, it is very annoying for them to have to keep doing this. Does anyone have any idea how I could stop this from happening? Or is this just a bug in Excel that will continue to happen at random times?

UPDATE: I was able to reproduce the issue on my M1 Mac running Excel 16.68. I opened two copies of the workbook. After opening the second one, when I switched back to the first one and changed the value in a cell, I got the #NAME? error in a cell on the same row. (The cell had a formula that used a User Defined Function, as mentioned above.) I repeated this a few times, and it did it each time, so it does not appear to be a fluke. Here is a video showing the behavior: https://youtu.be/6pwoBwjW9n8

I tried the same thing in Windows a few times and Excel never gave me the #NAME? error. So this may be an issue specific to Excel for Mac, though I cannot be sure, since I did receive the error at random times when I was creating the file in Windows. However, I have never seen the issue reappear in Windows since I "finalized" the file, meaning since I password protected the VBE and sent it to my users.

Does anyone have any idea why this is happening, and more importantly, how I can get it to stop happening?

Microsoft 365 and Office | Excel | For home | MacOS

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2023-01-09T09:36:23+00:00

    I tested it form Excel for mac 16.67 and 16.68.

    In 16.67, it works without any problem.

    In 16.68, it gets #Name as you.

    As workaround, roll back the office update temporally and submit it to Microsoft to resolve it.

    Click the Helpmenu and choose Give Feedback.

    In the dialog box that opens, select the feedback to Microsoft you want to give.

    1 person found this answer helpful.
    0 comments

7 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-01-23T22:40:22+00:00

    Does this happen with specific user-defined functions, or is it with all functions? It would be very helpful if you could provide a snippet of code or a simplified version of your workbook with the VBA project unlocked so as not to give away any of your intellectual property.

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2023-01-09T19:04:04+00:00

    I just downgraded to Excel 16.67, and I can confirm that the error no longer happens for me. I will report this to Microsoft.

    Thank you for the help!

    0 comments
  3. Anonymous
    2023-01-09T08:32:55+00:00

    The file can be downloaded here: https://www.dropbox.com/s/xm7usdo9ggbl3uh/TrueBudget-v2.1.1.xlsb?dl=0

    I was unable to get the issue to happen on Windows, so if you try it, please try it on Excel for Mac v16.68. That is the version I was using in the video I recorded.

    0 comments
  4. Anonymous
    2023-01-09T08:07:30+00:00

    Hi mcomp72,

    Greetings! Thank you for posting to Microsoft Community.

    I wrote a simple custom function and do the same steps in your video. But I can't reproduce your issue. It should be some name range or other variable in your custom function which caused the #Name.

    Could you share your test file or share the custom function which you can reproduce the issue?

    Here is another post about the issue.

    Custom function returns #NAME? - Microsoft Community

    Best Regards,
    Snow Lu

    0 comments