试图设置全局命名范围，但局部范围最终被设置? [英] trying to set global named range but local range ends up getting set?

问题描述

我正在尝试更改命名范围引用的地址.工作簿中有两个名称相同的范围，一个范围为工作簿，另一个范围为 SheetA .我正在使用此代码:

I'm trying to change the address that a named range refers to. There are two ranges in the workbook with the same name, one scoped to the workbook and the other scoped to SheetA. I'm using this code:

Sub changeNamedRangeAddress(bk As Workbook, rangeName As String, newRange As Range)
    bk.Names(rangeName).RefersTo = newRange
End Sub

当我在即时"窗口中查看 bk.Names(rangeName)的值时，它似乎在引用该名称的全局版本，因为以下内容返回true:

When I look at the value for bk.Names(rangeName) in the Immediate window, it appears to be referencing the global version of that name, because the following returns true:

?typeof bk.Names(rangeName).Parent is Workbook

但是子程序运行后，本地作用域版本的地址已更改为 newRange.address 的地址，而全局范围版本保持不变.

But after the sub runs, the locally scoped version's address has changed to that of newRange.address and the global one remains the same.

我还能做些什么来确保 .RefersTo 定位到全局命名范围吗?

Is there anything else I can do to make sure that .RefersTo targets the global named range?

运行此脚本时，处于本地作用域的命名范围所引用的工作表处于活动状态.

The sheet that the locally scoped named range refers to is active when this script runs.

推荐答案

这是一个解决方案，如果您在工作表中遇到过几次或几次类似的情况(我已经看到过)，那么它将更实用，但不会1或2的区别很大，除了库中将具有更多功能的过程外！:)

Here is a solution that is more functional if you have a few or several cases like this across your sheets (which I have seen), but won't make much of a difference with 1 or 2, except you'll have a more functional procedure in your library! :)

Option Explicit

Sub changeGlobalNamedRangeAddress(bk As Workbook, rangeName As String, newRange As Range)
'this sub only changes named range scoped to the workbook and ignores any ranges scoped to the worksheet with the same name.

Dim n As Name

For Each n In bk.Names

    If InStr(1, n.Name, rangeName) > 0 And InStr(1, n.NameLocal, "!") = 0 Then

        n.RefersTo = newRange
        Exit For

    End If

Next

End Sub

这篇关于试图设置全局命名范围，但局部范围最终被设置?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！