以编程方式确定命名范围是否作用于工作簿 [英] Programmatically determine if a named range is scoped to a workbook

问题描述

我正在尝试我认为相当简单的vba语句来测试命名范围是作用于工作簿还是特定工作表.

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.

作为测试，我创建了一个新的Excel文档，并添加了6个命名范围.以下是它们在名称管理器 中的布局:

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:

 Name         |   Refers To          |   Scope
 -------------+----------------------+-----------
 rng_Local01  |   =Sheet1!$A$2:$A$16 |   Sheet1
 rng_Local02  |   =Sheet1!$C$2:$C$16 |   Sheet1
 rng_Local03  |   =Sheet1!$E$2:$E$16 |   Sheet1
 rng_Global01 |   =Sheet1!$B$2:$B$16 |   Workbook
 rng_Global02 |   =Sheet1!$D$2:$D$16 |   Workbook
 rng_Global03 |   =Sheet1!$F$2:$F$16 |   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

将导致记录三个 Workbook 范围内的命名范围，但是什么也没有发生.没有错误.在指定范围的 ALL 上， .Names(i).WorkbookParameter 的评估结果为 False ，我不确定为什么.

would result in the three Workbook scoped named ranges to be logged, however, nothing happens. There is no error. The .Names(i).WorkbookParameter evaluates to False on ALL of the named ranges and I am not sure why.

在VBA帮助中浏览 Name 对象时，我遇到了 ValidWorkbookParameter ，它看起来像是 WorkbookParameter 的表亲code>，但是使用该方法没有任何区别.

Looking through the Name object in the VBA help I came across ValidWorkbookParameter which looks like the ReadOnly cousin of WorkbookParameter, however using that method does NOT make any difference.

我还尝试过显式设置 ThisWorkbook.Names(i).WorkbookParameter = True ，但这会导致错误:

I have also tried explicitly setting ThisWorkbook.Names(i).WorkbookParameter = True, however this results in an error:

无效的过程调用或参数"

"Invalid procedure call or argument"

尽管 WorkbookParameter 被列为读/写

任何人都可以阐明为什么它不能按我期望的那样工作吗?我是否误解了 Name.WorkbookParameter 应该如何工作?有人能使它成功运行吗?

Can anyone shed any light onto why this isn't working as I'm expecting it too? Have I misunderstood how Name.WorkbookParameter is supposed to work? Is anyone able to get this to run successfully?

推荐答案

您可以使用Parent属性:

You can use the Parent property:

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

这篇关于以编程方式确定命名范围是否作用于工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！