＃姓名？Excel 中 VBA 函数的错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18830181/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
#NAME? error in Excel for VBA Function
提问by Teofrostus
I am making my first VBA program and trying to run the following function. The function checks a specific named range for the first row which does not have a value greater than it's leading value, but less than 1.
我正在制作我的第一个 VBA 程序并尝试运行以下函数。该函数检查第一行的特定命名范围，该范围的值不大于其前导值，但小于 1。
Public Function findPurchase()
Dim CRT As Range
Set CRT = Range("CostRateTable")
Dim existsBetter As Boolean
existsBetter = True
Dim r As Integer
r = 2
Dim c As Integer
c = 4
While existsBetter
Dim Found As Boolean
FoundBetter = False
While Not FoundBetter And c <= CRT.Columns.Count
If CRT(r, c) > CRT(r, 2) And CRT(r, c) < 1 Then
FoundBetter = True
Else
c = c + 1
End If
Wend
existsBetter = FoundBetter
If existsBetter Then
r = r + 1
End If
Wend
findPurchase = CRT(r, 3)
'MsgBox(findPurchase)
End Function
I know the function does what it is supposed to because I have both manually checked the table of values, removed the comment ' from the MsgBox, and used the debug tools to step in and out of each of the functions steps as it went through the table. However, when I reference the function in Excel with =findPurchase()I'm given a #NAME?error. The function even shows up in the function auto-complete box when I begin to type its name. When I write other functions, both with and without parameters, I can reference them just fine, for example:
我知道该函数做了它应该做的事情，因为我已经手动检查了值表，从 MsgBox 中删除了注释 '，并使用调试工具在每个函数步骤通过桌子。但是，当我在 Excel 中引用该函数时=findPurchase()出现#NAME?错误。当我开始键入其名称时，该函数甚至会显示在函数自动完成框中。当我编写其他函数时，无论有没有参数，我都可以很好地引用它们，例如：
Function addtwo()
addtwo = 1 + 2
End Function
What am I doing wrong with my function which causes it not to work?
我的功能做错了什么导致它不起作用？
回答by Siddharth Rout
You are getting that error because you have a module with the same name as the function.
您收到该错误是因为您有一个与函数同名的模块。
Change that name to say find_Purchaseand everything will be fine :) See the image below...
更改该名称find_Purchase，一切都会好起来的 :) 见下图...
回答by Erik G
I had the same issue myself. It turned out that I "Saved As..." another file and macros were not enabled for that file. No banner on the top appeared, but a #NAME?error was generated.
I reopened the file, enabled macros, and the problem was resolved.
我自己也有同样的问题。原来我“另存为...”另一个文件和宏没有为该文件启用。顶部没有出现横幅，但#NAME?产生了错误。我重新打开了文件，启用了宏，问题就解决了。
回答by Gary's Student
Make sure you have placed the function in a Standard Module. The error message means Excel can't find the function.
确保您已将该函数放置在标准模块中。错误消息意味着 Excel 找不到该函数。
回答by DrMarbuse
When Excel opens an unkown workbook containing VBA-Code, it usually asks for macros to be enabled by the user (depending on the application settings).
当 Excel 打开包含 VBA 代码的未知工作簿时，它通常会要求用户启用宏（取决于应用程序设置）。
If the user then enables the macros, all event-driven procedures will be started, such as auto_openor others.
如果用户随后启用宏，则将启动所有事件驱动的过程，例如auto_open或其他。
Custom VBA Functions however require for a full recalculation of the workbook. Otherwise the functions return-value still is #NAME, as the calculation is only done directly after opening the workbook.
但是，自定义 VBA 函数需要完全重新计算工作簿。否则函数返回值仍然是#NAME，因为计算仅在打开工作簿后直接完成。
In order to work directly at the first time opening, one has to add the following line to the workbook_openevent
为了在第一次打开时直接工作，必须在workbook_open事件中添加以下行
'
' Workbook open event
Private Sub Workbook_Open()
Application.CalculateFullRebuild
End Sub
回答by sf321
In addition to checking some of the above mentioned items, you might need to specify the filename where the custom function is actually defined, e.g. cell content =XLstart.xlsm!myCustomFunc(Arg1,Arg2) where myCustomFunc is defined in the startup file XLstart.xlsm.
除了检查上面提到的一些项目，您可能需要指定自定义函数实际定义的文件名，例如单元格内容 =XLstart.xlsm!myCustomFunc(Arg1,Arg2) 其中 myCustomFunc 在启动文件 XLstart 中定义。 xlsm。
Following the Excel help for "Correct a #NAME? error":
按照“更正#NAME？错误”的 Excel 帮助：
In the formula bar, select the [suspect] function name. In the Name Box (to the left of the formula bar), click the arrow and then select a [user-defined] function from the list that Excel suggests.
在公式栏中，选择 [可疑] 函数名称。在名称框（在编辑栏的左侧）中，单击箭头，然后从 Excel 建议的列表中选择一个 [用户定义] 函数。
This will add the filename per the above format.
这将按照上述格式添加文件名。
MS 2010, Windows 10.
微软 2010，Windows 10。
回答by Theodor Paulus
Check "Trust access to the VBA project object model" in Macro settings from Macros security
从宏安全中的宏设置中选中“信任对 VBA 项目对象模型的访问”
回答by Karen
I had a similar persistent problem with one of my functions when everything else seemed fine. Open the worksheet & go to the Developer Tab. Open VBA, and back on the Developer ribbon select "View Code". See if it opens any similar Code (apart from your Module) specific to that worksheet (eg. Sheet2 (Code). I found that I had duplicated the code on the worksheet in addition to the Module. Delete the "worksheet" code. (You may need to save the workbook & re-open at this stage). When I deleted the worksheet code, the module function then worked.
当其他一切看起来都很好时，我的一个功能也遇到了类似的持续问题。打开工作表并转到“开发人员”选项卡。打开 VBA，然后返回开发人员功能区，选择“查看代码”。看看它是否打开了特定于该工作表的任何类似代码（除了您的模块）（例如 Sheet2（代码）。我发现除了模块之外，我还复制了工作表上的代码。删除“工作表”代码。（在此阶段您可能需要保存工作簿并重新打开）。当我删除工作表代码时，模块功能就可以工作了。
回答by Peter Rosenberg
True, I had the same (in Excel 2010) and when I migrated to Excel 2016 , the function prototype was shown, but when I completed the function, the #NAME error was shown with a pop-up... so the code was never triggered.
确实，我有相同的（在 Excel 2010 中），当我迁移到 Excel 2016 时，显示了函数原型，但是当我完成该函数时，#NAME 错误显示为弹出窗口......所以代码是从未触发。
It turned out I had a Macro of the same name as a Sub or UDF function ! I renamed the Macro, and then it worked
原来我有一个与 Sub 或 UDF 函数同名的宏！我重命名了宏，然后它起作用了
Cheers
干杯
回答by Ross Bailey
Another cause I found for the #NAME? error is that the macro workbook with the custom function has a range name the same as the function name. I changed the function name and solved the problem.
我为#NAME 找到的另一个原因？错误是带有自定义函数的宏工作簿具有与函数名称相同的范围名称。我更改了函数名称并解决了问题。
回答by Pooyan Tavakoli
One reason for this problem is security restrictions.. I had this problem and I activate "Enable all macros" from security center, and the problem solved
这个问题的一个原因是安全限制..我遇到了这个问题，我从安全中心激活了“启用所有宏”，问题解决了
