Excel VBA Error 1004 on range objects using range names scoped to the Workbook level

Question

Why did this code stop working?

I've used Excel range names scoped to the Workbook level in VBA for decades without, in most situations, having to qualify Range() objects with their Sheets() object parent. Recently, this stopped working and now throws a 1004 error if the named range is not on the active worksheet.

Code that used to work

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

In this case, "SNAP_Current" is on the active worksheet and "SNAP_Backup" is Not on the active worksheet. Of course, the code works if Range() is qualified with the correct Sheet() object.

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

I understand why using sheets() qualifiers makes the code work. For example, if the range name is scoped to the worksheet, then Sheets() qualifier is required. However, I don't want to change all of my VBA code (I have a lot of it) to qualify Range() objects with the Sheet() object when the range_name is scoped to the workbook level. I hope there is something I inadvertently changed that can be reversed to restore functionality.

Can anyone tell me why the code used to work without the Sheet() object qualifier? Am I missing a Library or, perhaps, do I have two Libraries that are conflicting? How can I get this functionality back?

BTW, https://learn.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/refer-to-named-ranges confirms that Range() should work with named ranges scoped to the workbook level.

From my experience, most VBA-ers here would recommend that you fully qualify any `Range` call with the `Workbook`/`Worksheet`. — BigBen, May 18 '20 at 15:55
`Range()` works with named ranges on workbook level, but for the current active workbook. Is this your issue? I would never ever use unqualified Excel objects (worksheets, Range, Cell, Shape...) — FunThomas, May 18 '20 at 15:57
I believe that with no sheet qualifier, excel will try to create the range reference using the `Activesheet`. As you mentioned, the two NamedRanges are on different sheets and therefore can't both be on the activesheet. — ArcherBird, May 18 '20 at 15:57
Can you verify that your named ranges are indeed scoped to the workbook level? Did one get changed to sheet scope? — ArcherBird, May 18 '20 at 16:10
I can access a workbook-scoped named range regardless of the active sheet. — Tim Williams, May 18 '20 at 16:20

score 1 · Answer 1 · answered May 18 '20 at 17:14

If a named Range is defined on Workbook level, you don't need to qualify it with a sheet. If you qualify it with a sheet, it needs to be the sheet where the range points to. However, if you don't qualify it, Excel will look for the range in the ActiveWorkbook, which is not necessarily the workbook you are expecting.

I have created a workbook with 2 sheets, having three named ranges:
MyGlobalRange is a Range declared on Workbook level, pointing to sheet 1.
MyLocalRange1 is a Range declared on Worksheet level, pointing to sheet 1.
MyLocalRange2 is a Range declared on Worksheet level, pointing to sheet 2.

The following code shows under which circumstances you can access which range. First, sheet1 is active, next sheet2, and last I create a new Workbook which gets the Active Workbook.

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

This is the outcome. Lines that doesn't work are simply skipped by the 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

As long as the Workbook is Active, you can access the range declared on Workbook level unqualified. When qualified, the correct sheet must be used.
The range declared on Worksheet level can be accessed unqualified when the sheet itself is active.
But when a different Workbook gets active, all ranges are only accessible qualified.

Needless to say that the solution should not be to use the Activate- command to solve that issue. Always qualify all you have, never rely on the Activesheet or ActiveWorkbook.

thanks for all the comments. Yes, the range names are all scoped to the Workbook level. I am using w — Paradox, May 19 '20 at 13:56

Excel VBA Error 1004 on range objects using range names scoped to the Workbook level

1 Answers1

Linked