Excel错误1004关于使用范围为工作簿级别的范围名称的范围对象

Question

为什么这段代码停止工作？

在几十年中，我使用了范围为VBA中工作簿级别的Excel范围名称，而在大多数情况下，不必对Range()对象与其Sheet()对象父对象进行限定。最近，它停止工作，如果命名范围不在活动工作表上，则现在抛出一个1004错误。

过去起作用的代码

If Range("SNAP_Current").Value = Range("SNAP_Backup").Value Then
    debug.print "True"
else
    debug.print "False"
End If

在这种情况下，"SNAP_Current“在活动工作表上，而"SNAP_Backup”不在活动工作表上。当然，如果Range()被正确的Sheet()对象限定，那么就能工作。

If Range("SNAP_Current").Value = **Sheets("Backup Copy").**Range("SNAP_Backup").Value Then
    debug.print "True"
else
    debug.print "False"
End If

我理解为什么使用sheets()限定符可以使代码工作。例如，如果范围名称被限定为工作表，那么Sheets()限定符是必需的。但是，我不想改变我所有的VBA代码(我有很多)，以便在range_name的作用域到工作簿级别时，用Sheet()对象来限定Range()对象。我希望有一些我无意中改变了，可以逆转，以恢复功能。

有人能告诉我为什么没有Sheet()对象限定符的代码可以工作吗?我是不是缺少了一个库，或者，我是否有两个相互冲突的库？我怎样才能拿回这个功能？

顺便说一下，https://learn.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/refer-to-named-ranges确认Range()应该与范围为工作簿级别的命名范围一起工作。

Answer 1

如果在工作簿级别上定义了命名范围，则不需要使用工作表对其进行限定。如果您用工作表限定它，则它需要是范围指向的工作表。但是，如果不对其进行限定，Excel将在ActiveWorkbook中查找范围，这并不一定是您所期望的工作簿。

我已经创建了一个包含两个工作表的工作簿，其中有三个命名范围：

MyGlobalRange是在工作簿级别上声明的范围，指向工作表1。

MyLocalRange1是在工作表级别上声明的范围，指向工作表1。

MyLocalRange2是在工作表级别上声明的范围，指向工作表2。

下面的代码显示在何种情况下可以访问哪个范围。首先，sheet1是活动的，接下来是sheet2，最后，我创建了一个新的工作簿，它获取活动工作簿。

Sub testSub()
    ThisWorkbook.Activate
    ThisWorkbook.Sheets(1).Activate
    DumpNamedRanges "Sheet 1"
    ThisWorkbook.Sheets(2).Activate
    DumpNamedRanges "Sheet 2"
    Dim newWB As Workbook
    Set newWB = Workbooks.Add
    DumpNamedRanges "new Workbook"
    newWB.Close False
End Sub

Sub DumpNamedRanges(msg As String)
    On Error Resume Next
    Debug.Print msg, "G unqual :", Range("MyGlobalRange").Value
    Debug.Print msg, "G sheet 1:", ThisWorkbook.Sheets(1).Range("MyGlobalRange").Value
    Debug.Print msg, "G sheet 2:", ThisWorkbook.Sheets(2).Range("MyGlobalRange").Value
    Debug.Print msg, "L1 unqual:", Range("MyLocalRange1").Value
    Debug.Print msg, "L1 qual  :", ThisWorkbook.Sheets(1).Range("MyLocalRange1").Value
    Debug.Print msg, "L2 unqual:", Range("MyLocalRange2").Value
    Debug.Print msg, "L2 qual  :", ThisWorkbook.Sheets(2).Range("MyLocalRange2").Value
    On Error Goto 0
End Sub

这就是结果。不工作的行被On Error Resume Next跳过

Sheet 1       G unqual :    I am global
Sheet 1       G sheet 1:    I am global
Sheet 1       L1 unqual:    I am local sheet 1
Sheet 1       L1 qual  :    I am local sheet 1
Sheet 1       L2 qual  :    I am local sheet 2
Sheet 2       G unqual :    I am global
Sheet 2       G sheet 1:    I am global
Sheet 2       L1 qual  :    I am local sheet 1
Sheet 2       L2 unqual:    I am local sheet 2
Sheet 2       L2 qual  :    I am local sheet 2
new Workbook  G sheet 1:    I am global
new Workbook  L1 qual  :    I am local sheet 1
new Workbook  L2 qual  :    I am local sheet 2

只要工作簿处于活动状态，您就可以访问在Workbook级别上声明的不合格范围。合格时，必须使用正确的工作表。

当工作表本身处于活动状态时，可以访问工作表级别上声明的范围。

但是，当不同的工作簿处于活动状态时，所有范围都只能访问限定。

不用说，解决方案不应该是使用Activate-命令来解决这个问题。永远限定你所拥有的一切，永远不要依赖Activesheet或ActiveWorkbook。