在当前激活的Excel VBA工作表中遍历所有命名区域

Question

我希望能够循环遍历activeworksheet中所有的命名区域，并对其进行操作。我已经使用了下面的代码，但似乎没有产生任何结果。此外，如果我可以循环遍历包含特定单词的命名区域，那就更好了。例如，我的命名区域被命名为data1、data2、data3等等。如果它们包含单词"data"，我只想在它们上面工作。

For Each nm In Activesheets.Names
    MsgBox "nm.name"
Next nm

- user7729135

---

尝试使用 msgbox nm.Name。你现在的消息框上显示的是“nm.name”，对吧？ - BruceWayne

你可能需要使用 'ActiveSheet' 而不是 'ActiveSheets'。 - Brandon Barney

你需要使用 ActiveWorkbook.Names，并像其他人所说的那样去掉引号。 - user1274820

你的编辑出现了运行时错误 '1004'。 - user7729135

@user1274820 我尝试将整个内容复制并粘贴，但仍然出现错误。我正在使用 Windows 上的 Excel 2016，并在 Mac 上尝试过。 - user7729135

Answer 1

如果你只想从活动工作表中获取名称，请使用以下代码：

Sub Test()
For Each nm In ActiveWorkbook.Names
    If nm.RefersToRange.Parent.Name = ActiveSheet.Name Then MsgBox nm.Name
Next nm
End Sub

这段代码仅返回引用活动工作表上的范围的已命名区域。

nm.RefersToRange.Parent 将返回与该范围关联的工作表。

我们可以使用 .Name 检索它的名称，并将其与 ActiveWorksheet 名称进行比较。

在这里，您可以看到我在 Sheet4 上有 2 个命名范围和 1 个在 Sheet3 上。

当我运行此代码时，它仅返回 MyName1 和 MyName2 - 它不包括 MyName3，因为它不在活动工作表上。

此宏仅返回我的 ActiveSheet（Sheet4）上的命名区域。

- user1274820

---

如果您已启用自动筛选功能，您可能需要在 If 语句中添加类似于 And Right(nm.name, 15) <> "_FilterDatabase" 的内容，因为 Excel 是通过这种方式定义被筛选的列的。（有什么更好的方法来区分您定义的名称和可能在“幕后”使用的其他像这样的东西吗？） - Ben Philipp

Answer 2

这个宏将循环遍历工作簿中的所有命名区域。 考虑到上面的注释，它展示了如何在特定范围内执行操作。

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

注意，使用InStr()将在名称中的任何位置查找“data”。因此，如果您有data1、datas1和myData1，它将为所有这些运行“do stuff here”代码。如果您只想要以data开头的范围，则将InStr()行更改为：If Left(nm.Name,4) = "data" Then 编辑：您可以将其与@user1274820建议的If nm.RefersToRange.Parent.Name = ActiveSheet.Name 一起使用。 （只需使If语句包括一个And运算符）。

- BruceWayne

Answer 3

只是对Ben Philipp评论的一个小补充：_FilterDatabase名称的.Visible属性设置为False。

如果OP以前没有通过VBA添加过不可见名称，他们可以检查.Visible属性而不是比较字符串。

- Guy Bukzi Montag