在当前活动工作表中循环遍历 excel VBA 中的所有命名范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43238985/
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
Looping through all named ranges in excel VBA in current active sheet
提问by user7729135
Hi I am looking to loop through all named ranges found in my activeworksheet and then do something to them. However I've used the code below but it does not seem to produce anything. Also, it will be good if I can loop through named ranges that contain certain words. Example, my named ranges are named data1, data2, data3 and so on. I would only like to work on them if they contain the word data.
嗨，我希望遍历在我的活动工作表中找到的所有命名范围，然后对它们做一些事情。但是我使用了下面的代码，但它似乎没有产生任何结果。此外，如果我可以遍历包含某些单词的命名范围，那就太好了。例如，我的命名范围被命名为 data1、data2、data3 等。如果它们包含单词数据，我只想对它们进行处理。
For Each nm In Activesheets.Names
MsgBox "nm.name"
Next nm
回答by user1274820
If you only want to get names from the active worksheet, use this:
如果您只想从活动工作表中获取名称，请使用以下命令：
Sub Test()
For Each nm In ActiveWorkbook.Names
If nm.RefersToRange.Parent.Name = ActiveSheet.Name Then MsgBox nm.Name
Next nm
End Sub
^ This code will only return named ranges that refer to ranges on the active worksheet.
^ 此代码将仅返回引用活动工作表上的范围的命名范围。
nm.RefersToRange.Parentwill return the worksheet associated with the range.
nm.RefersToRange.Parent将返回与范围关联的工作表。
We can then retrieve its name using .Nameand compare it to the ActiveWorksheet name.
然后，我们可以使用检索其名称.Name并将其与 ActiveWorksheet 名称进行比较。
Here you can see I have 2 Named Ranges on Sheet4and 1 on Sheet3
在这里你可以看到我有 2 个命名范围Sheet4和 1 个Sheet3
When I run this code, it only returns MyName1and MyName2- it does not include MyName3as it is not on the active sheet.
当我运行此代码时，它只返回MyName1并且MyName2- 它不包括，MyName3因为它不在活动工作表上。
This macro will only return the ones on my ActiveSheet(Sheet4)
这个宏只会返回我的ActiveSheet( Sheet4)
回答by BruceWayne
This macro will loop through all named ranges in your workbook. It takes the comments above into consideration and shows how you can do things with specific ranges.
此宏将遍历工作簿中的所有命名范围。它考虑了上面的评论，并展示了如何在特定范围内做事。
Sub nameLoop()
Dim nm
Dim wb As Workbook
Set wb = ActiveWorkbook
For Each nm In wb.Names
If InStr(1, nm.Name, "data") Then
' Do stuff with the named range.
Debug.Print nm.Name
End If
Next nm
End Sub
Note, using InStr()will find "data" anywherein the name. So if you have data1, datas1, and myData1, it'll run the "do stuff here" code for all of those. If you just want ranges startingwith data, then change the InStr()line to: If Left(nm.Name,4) = "data" Then
请注意，使用InStr()将在名称中的任何位置找到“数据” 。因此，如果您有data1, datas1, 和myData1，它将为所有这些运行“在此处执行操作”代码。如果你只是想范围开始用data，然后换InStr()行：If Left(nm.Name,4) = "data" Then
Edit: You can couple this with the If nm.RefersToRange.Parent.Name = ActiveSheet.Nameline suggested by @user1274820 below. (Just make the Ifstatement include an Andoperator).
编辑：您可以将此与If nm.RefersToRange.Parent.Name = ActiveSheet.Name下面@user1274820 建议的行结合起来。（只需使If语句包含一个And运算符）。
