问题描述
我想删除工作簿中的所有全局命名范围.我有以下代码:
I want to delete all global named ranges in a workbook.I have this code:
Dim xName As Name
For Each xName In Application.ActiveWorkbook.Names
If InStr(xName.Name, "!") = 0 Then xName.Delete
Next
即使更改代码以删除所有命名范围，我仍然会遇到相同的错误.
Even when I change the code to delete ALL named ranges, I still get the same error.
Dim xName As Name
For Each xName In Application.ActiveWorkbook.Names
xName.Delete
Next
当我运行它时，仅删除第一个命名范围，然后引发400错误.它不会继续前进到下一个.
When I run it, only the first Named Range is deleted and then it throws a 400 error. It doesn't move on to the next one.
我缺少某种附加组件或设置吗?这段代码看起来很简单，但是我无法正常工作.
Is there some sort of add-on or setting that I'm missing? This code seems pretty straight-forward, but I can't get it to work.
推荐答案
好，经过长时间的聊天和反复试验，我发现了问题所在.实际上是两倍.
Ok, after a long chat and some trial and error I've found the problem.It's actually two-fold.
此代码不起作用的原因似乎是因为它找到的第一个命名范围是Excel内置范围，显然无法删除.这两个代码段都是这种情况
The reason this code didn't work, seems to be because the first Named Range it found was an Excel built-in range which obviously couldn't be deleted.This is the case with both code snippets
Dim xName As Name
For Each xName In Application.ActiveWorkbook.Names
xName.Delete
Next
AND
Dim xName As Name
For Each xName In Application.ActiveWorkbook.Names
If InStr(xName.Name, "!") = 0 Then xName.Delete
Next
因为这两个原因都找到了内置的命名范围"_xlfn.CONCAT"，并试图将其删除.
Because both of these find that built-in Named Range "_xlfn.CONCAT" and tries to delete it.
我终于听了@QHarr的建议，并使用了另一个限定词.幸运的是，我在工作簿合并范围"中的所有命名范围的名称都带有"Master"，因此很容易.
I finally heeded @QHarr's advice and used a different qualifier. Luckily all my Named Ranges in the Workbook Scope has "Master" in the name, so it was easy enough.
那么，最终的解决方案:
Dim xName As Name
For Each xName In Application.ActiveWorkbook.Names
If InStr(xName.Name, "Master") > 0 Then xName.Delete
Next
感谢大家！
这篇关于尝试删除命名范围时出现VBA 400错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！