循环浏览当前活动工作表中excel VBA中的所有命名范围 [英] Looping through all named ranges in excel VBA in current active sheet

问题描述

我想遍历我在activeworksheet中找到的所有命名范围，然后对它们做一些事情.但是，我使用了下面的代码，但似乎什么也没产生.另外，如果我可以遍历包含某些单词的命名范围，那将是很好的.例如，我的命名范围命名为data1，data2，data3，依此类推.如果它们包含单词数据，我只想对其进行处理.

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.

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

推荐答案

如果只想从活动工作表中获取名称，请使用以下方法:

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.Parent将返回与范围关联的工作表.

nm.RefersToRange.Parent will return the worksheet associated with the range.

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

We can then retrieve its name using .Name and compare it to the ActiveWorksheet name.

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

Here you can see I have 2 Named Ranges on Sheet4 and 1 on Sheet3

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

When I run this code, it only returns MyName1 and MyName2 - it does not include MyName3 as it is not on the active sheet.

此宏只会返回我ActiveSheet(Sheet4)

这篇关于循环浏览当前活动工作表中excel VBA中的所有命名范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！