vba 循环遍历 Excel 工作表中的所有命名范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27234134/
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
Loop through all named ranges in a Excel Sheet
提问by hend
I need to loop through all named ranges in my sheet.
I am currently doing this:
我需要遍历工作表中的所有命名范围。
我目前正在这样做：
For Each nm In ActiveWorkbook.Names
The problem is that this sheet has names with global scope and with sheet scope.
This loop only gets the global scope.
I've tried:
问题是该工作表具有全局范围和工作表范围的名称。
这个循环只获取全局范围。
我试过了：
For Each nm In Activeworkbook.Sheets(1).Names
But couldn't make it work. This loop also only gets those named ranges with global scope. Ideas?
I know the best solution would be to change the scope of the name, but I can't do it.
但无法让它发挥作用。这个循环也只获取那些具有全局作用域的命名范围。想法？
我知道最好的解决方案是更改名称的范围，但我做不到。
回答by hend
Could make it to work today. To post here, I made my problem much more simple, but this wasn't a good idea.
可以让它今天工作。在这里发帖，我让我的问题变得更简单，但这不是一个好主意。
What I really do is to import some values from a sheet (selected with application.getopenfilename and opened with workbooks.open). So I loop through all names in this "imported" sheet and import the values of those ranges to ranges with the same name in my original sheet.
我真正要做的是从工作表中导入一些值（使用 application.getopenfilename 选择并使用 workbooks.open 打开）。因此，我遍历此“导入”工作表中的所有名称，并将这些范围的值导入到原始工作表中具有相同名称的范围内。
for each nm in thisworkbook.names
if left(nm.name, 5) = "campo" then
'here I make my copy
end if
next nm
Turns out that when you have a name with sheet scope, nm.name returns something like this:
事实证明，当您有一个带有工作表范围的名称时， nm.name 会返回如下内容：
nameOfSheet!nameOfField
So I could never get in to that if. To solve my problem I used the following line. Thank you all for trying to help me.
所以我永远无法进入，如果。为了解决我的问题，我使用了以下行。感谢大家试图帮助我。
currentName = Mid(nm.Name, InStr(1, nm.Name, "!") + 1)
回答by bilbo_strikes_back
Dim intCounter As Integer
Dim nmTemp As Name
For intCounter = 1 To ActiveWorkbook.Names.Count
MsgBox ActiveWorkbook.Names(intCounter)
Next intCounter
