vba 以编程方式确定命名范围是否限定于工作簿
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8656793/
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
Programmatically determine if a named range is scoped to a workbook
提问by Hari Seldon
I am attempting what I thought would be a fairly simple vba statement to test whether a named range is scoped to a workbook or a specific sheet.
我正在尝试使用我认为相当简单的 vba 语句来测试命名范围是否限定为工作簿或特定工作表。
As a test, I have created a new Excel document and added in 6 named ranges. Here is how they are layed out in the Name Manager:
作为测试，我创建了一个新的 Excel 文档并添加了 6 个命名范围。以下是它们在名称管理器中的布局：
Name | Refers To | Scope
-------------+----------------------+-----------
rng_Local01 | =Sheet1!$A:$A | Sheet1
rng_Local02 | =Sheet1!$C:$C | Sheet1
rng_Local03 | =Sheet1!$E:$E | Sheet1
rng_Global01 | =Sheet1!$B:$B | Workbook
rng_Global02 | =Sheet1!$D:$D | Workbook
rng_Global03 | =Sheet1!$F:$F | Workbook
I would expect that running:
我希望运行：
For i = 1 To ThisWorkbook.Names.Count
If ThisWorkbook.Names(i).WorkbookParameter Then Debug.Print ThisWorkbook.Names(i).Name
Next i
would result in the three Workbookscoped named ranges to be logged, however, nothing happens. There is no error. The .Names(i).WorkbookParameterevaluates to Falseon ALLof the named ranges and I am not sure why.
将导致Workbook记录三个范围的命名范围，但是，没有任何反应。没有错误。对所有命名范围的.Names(i).WorkbookParameter评估结果为，我不知道为什么。False
Looking through the Nameobject in the VBA help I came across ValidWorkbookParameterwhich looks like the ReadOnlycousin of WorkbookParameter, however using that method does NOT make any difference.
查看NameVBA 帮助中的对象，我发现ValidWorkbookParameter它看起来像 的ReadOnly表亲WorkbookParameter，但是使用该方法没有任何区别。
I have also tried explicitly setting ThisWorkbook.Names(i).WorkbookParameter = True, however this results in an error:
我也试过显式设置ThisWorkbook.Names(i).WorkbookParameter = True，但这会导致错误：
"Invalid procedure call or argument"
“无效的过程调用或参数”
Despite the fact that WorkbookParameteris listed as being Read/Write
尽管WorkbookParameter被列为读/写
Can anyone shed any light onto why this isn't working as I'm expecting it too? Have I misunderstood how Name.WorkbookParameteris supposed to work? Is anyone able to get this to run successfully?
任何人都可以解释为什么这也不能如我所期望的那样工作吗？我是否误解了Name.WorkbookParameter应该如何工作？有没有人能够让它成功运行？
回答by Charles Williams
You can use the Parent property:
您可以使用 Parent 属性：
Sub Global_Local_names()
Dim oNm As Name
For Each oNm In Names
If TypeOf oNm.Parent Is Worksheet Then
Debug.Print oNm.Name & " is local to " & oNm.Parent.Name
Else
Debug.Print oNm.Name & " is global to " & oNm.Parent.Name
End If
Next
End Sub
回答by JMax
I didn't manage to make the WorkbookParameter work but I found a workaround:
我没有设法使 WorkbookParameter 工作，但我找到了一个解决方法：
For i = 1 To ThisWorkbook.Names.Count
If UBound(Split(ThisWorkbook.Names(i).Name, "!")) > 0 Then Debug.Print ThisWorkbook.Names(i).Name
Next i
The name of the localNamed Range (i.e. scope to a sheet only) is formatted this way: Sheet1!NamedRangewhereas the name of the globalNamed Range is formatted: NamedRange.
本地命名范围的名称（即仅限工作表的范围）的格式如下：Sheet1!NamedRange而全局命名范围的名称格式为：NamedRange。
Thus, you can split on the !and check the length of the array.
因此，您可以拆分!并检查数组的长度。
