遍历命名范围列表 [英] Loop through named range list

问题描述

我找到了很多例子，但就我而言，它不起作用，我也不知道为什么. 非常基本的代码:

I found a lot of examples but it's not working in my case and I don't know why. Very basic code:

Sub Test()
Dim namCur As Name

For Each namCur In ActiveSheet.Names
MsgBox "Name: " & namCur.Name & ", Refers To: " & namCur.RefersTo
Next namCur
End Sub

当我使用Worksheets("Assumptions").Names

当我观看ActiveSheet.Name时，这是正确的，我得到了假设"，您可以在图片上看到命名范围列表的下方.但是我从来没有得到MsgBox，For循环直接结束了. 非常重要，我只需要循环此工作表的命名范围，而不是整个工作簿 有什么主意吗?

When I watch ActiveSheet.Name, this is correct I get "Assumptions", you can see on the picture below the list of named ranges. But I never get the MsgBox and the For loop goes directly to the end. Very important, I need to loop only this sheet's named ranges, not the whole workbook Any idea?

我使用Excel 2016

I use Excel 2016

推荐答案

您的解决方案将仅列出范围设置为仅ActiveSheet的名称.

Your solution will only list Names that have a scope set to only the ActiveSheet.

更改此

For Each namCur In ActiveSheet.Names

对此

For Each namCur In ThisWorkBook.Names

列出工作簿中的所有名称.然后，您可以检查RefersTo地址以检查它是否适用于ActiveSheet.

to list all names in the Workbook. You can then check the RefersTo address to check if it applies to the ActiveSheet.

Sub Test()

Dim namCur As Name
Dim TargetSheetName As String

TargetSheetName = "Assumptions"

For Each namCur In ThisWorkbook.Names

    If Range(namCur.RefersTo).Parent.Name = TargetSheetName Then MsgBox "Name: " & namCur.Name & ", Refers To: " & namCur.RefersTo

Next namCur

End Sub

这篇关于遍历命名范围列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！