vba 在 Excel 2003 中删除具有无效名称的隐藏名称定义
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3516542/
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
Deleting hidden name definitions with invalid names in Excel 2003
提问by Code Commander
I am using this macro:
我正在使用这个宏：
http://support.microsoft.com/kb/119826
http://support.microsoft.com/kb/119826
to try and clean up hidden names in my excel file. It works for most of the hidden names, but not for a few _123Graph names. I'm not sure where these names came from, but when I try to delete them I get a 1004 automation error.
尝试清理我的 excel 文件中的隐藏名称。它适用于大多数隐藏名称，但不适用于少数 _123Graph 名称。我不确定这些名称的来源，但是当我尝试删除它们时，我收到了 1004 自动化错误。
The knowledge base mentions that names with spaces may cause an error. Is there a way to delete these?
知识库提到带空格的名称可能会导致错误。有没有办法删除这些？
采纳答案by user430877
Excel 2007 and above resolves this issue... but a quick fix for Excel 2003 is:
Excel 2007 及更高版本解决了这个问题......但 Excel 2003 的快速修复是：
Go to the Tools Menu - Options - General tab - Check R1C1 Reference Style Then hit "Ok".
转到工具菜单 - 选项 - 常规选项卡 - 检查 R1C1 参考样式然后点击“确定”。
You will then be prompted for a new name for each of the corrupted names.
然后，系统会提示您为每个损坏的名称输入一个新名称。
...then you can go back and uncheck the R1C1 check box.
...然后您可以返回并取消选中 R1C1 复选框。
回答by Lunatik
I use the excellent Name Manager add-into, erm, manage the named ranges in my workbooks, including all those pesky ones like the example you give that are automatically created by Excel when autofiltering etc. which aren't normally exposed.
我使用出色的名称管理器加载项来管理我的工作簿中的命名范围，包括所有那些讨厌的范围，例如您提供的示例，这些范围是在自动过滤等时由 Excel 自动创建的，通常不会公开。
It allows filtering of names by type, location, scope etc. and generally knocks the awful built-in dialog into next week.
它允许按类型、位置、范围等过滤名称，并且通常将可怕的内置对话框敲入下周。
Edit:If installing an add-in is out of the question then adding the following code in a standard module will allow you to loop through the names in the workbook and delete the offending items.
编辑：如果安装加载项是不可能的，那么在标准模块中添加以下代码将允许您遍历工作簿中的名称并删除有问题的项目。
Sub deleteNamedRanges()
Dim n As Name
Dim a As Variant
For Each n In ThisWorkbook.Names
a = MsgBox("Do you want to delete the following name?:" & vbCrLf & vbCrLf & n.Name & " (" & n.RefersTo & ")", vbYesNo, "Delete ranges")
If a = vbYes Then
n.Delete
End If
Next n
End Sub
If there are a great many names then you should be able to modify this to suit your needs.
如果有很多名称，那么您应该能够修改它以满足您的需要。
