Excel VBA Named Range Scope and/or Permanence of a Named Range

The Sub shown below is in the workbook's Module1 (in the Modules folder)

The workbook/file is named "240122 Range Name Scope v01.xlsm" It has 2 worksheets S1 and S2. Each worksheet has One Named Range.

Running this macro places in the cell Worksheets("S1").Cells(1,2) (the above being cell B1 in worksheet S1) the following text: =SUM(r_IN_Wksht_S2)

HOWEVER THE RETURNED VALUE IS #NAME? which I assume means Excel doesn't know the Named Range r_IN_Wksht_S2

Can someone please help me understand why the value isn't 10? The value of cell A1 in worksheet S2 is 10.

THE SUBROUTINE'S CODE:

Sub Scope_Rng_Name_In_2_Wkshts_Insert_Formula()

Dim r_IN_Wksht_S1 As Range 'Named Range in Worksheet S1
Dim r_IN_Wksht_S2 As Range 'Named Range in Worksheet S2

Set r_IN_Wksht_S1 = Worksheets("S1").Range("$A$1")
Set r_IN_Wksht_S2 = Worksheets("S2").Range("$A$1")

r_IN_Wksht_S1.Value = 5
r_IN_Wksht_S2.Value = 10

Worksheets("S1").Cells(1, 2).FormulaR1C1 = "=sum(r_IN_Wksht_S2)"

End Sub

WHAT I HAVE TRIED:
I have looked for the named ranges r_IN_Wksht_S1 and r_IN_Wksht_S2 in Excel in the Name Manager function/area of the Formlas tab and those named ranges don't appear.

If I create the named ranges r_IN_Wksht_S1 and r_IN_Wksht_S2 with Workbook scope with the Name Manager function in the Formulas tab rather than creating the named ranges in Excel VBA the when I write the formula in Excel VBA:

Worksheets("S1").Cells(1, 2).FormulaR1C1 = "=sum(r_IN_Wksht_S2)"

It works. But I really need to be able to define/set the Named Ranges in VBA.

Solution

Here is an example of adding the statements to create a named range with workbook scope (the names must be unique within the workbook):

Sub Scope_Rng_Name_In_2_Wkshts_Insert_Formula()

Dim r_IN_Wksht_S1 As Range 'Named Range in Worksheet S1
Dim r_IN_Wksht_S2 As Range 'Named Range in Worksheet S2

Set r_IN_Wksht_S1 = Worksheets("S1").Range("$A$1")
Set r_IN_Wksht_S2 = Worksheets("S2").Range("$A$1")

r_IN_Wksht_S1.Value = 5
r_IN_Wksht_S2.Value = 10

'// Create Named Ranges
ThisWorkbook.Names.Add Name:="r_IN_Wksht_S1", RefersTo:=r_IN_Wksht_S1
ThisWorkbook.Names.Add Name:="r_IN_Wksht_S2", RefersTo:=r_IN_Wksht_S2

'//Or Alternative way to create named ranges
'r_IN_Wksht_S1.Name = "r_IN_Wksht_S1"
'r_IN_Wksht_S2.Name = "r_IN_Wksht_S2"

Worksheets("S1").Cells(1, 2).FormulaR1C1 = "=sum(r_IN_Wksht_S2)"

End Sub

For further reference I have often used this site for details: http://cpearson.com/Excel/DefinedNames.aspx

Also just as a tip, avoid very short one, two, or three letter names followed by numbers! For instance, DCA1001 is not a good name for a named range because it is already the name of a cell in an excel spreadsheet - all the cells from A1 to XFD1048576 are already named cells in xlsx workbooks.