以编程方式确定命名范围是否作用于工作簿

本文介绍了以编程方式确定命名范围是否作用于工作簿的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试我认为相当简单的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