Workbook Specific

Workbook Names Collection

Global named ranges belong to the Names collection associated with a workbook
To access named ranges in the active workbook you can use:

Application.ActiveWorkbook.Names() 

Since ActiveWorkbook is the default member of the Application object you can also use:

Application.Names() 

Since Application is the default member you could abbreviate it and use:

ActiveWorkbook.Names() 
Names() 

If you have multiple workbooks open at the same time you can access the named ranges in a specific workbook by using:

Workbooks("Wbk1.xls").Names() 

Creating Workbook Specific Names

You can create workbook specific named ranges in a number of different ways
Most workbook specific named ranges will often refer to a specific worksheet but allow the named range to be used from any worksheet within the workbook.

Application.ActiveWorkbook.Names.Add Name:="MyNamedRange1", _ 
                                     RefersTo:=Range("Sheet1!$A$1:$B$8") 

Since ActiveWorkbook is the default member of the Application object you can also use:

Application.Names.Add Name:="MyNamedRange1", _ 
                      RefersTo:=Range("Sheet1!$A$1:$B$8") 

Since Application is the default member you could abbreviate it and use:

ActiveWorkbook.Names.Add Name:="MyNamedRange1", _ 
                         RefersTo:=Range("Sheet1!$A$1:$B$8") 
Names.Add Name:="MyNamedRange1", _ 
          RefersTo:=Range("Sheet1!$A$1:$B$8") 

When your worksheet names contain spaces then you must surround the worksheet name with single apostrophes.

ActiveWorkbook.Names.Add Name:="MyNamedRange1", _ 
                         RefersTo:=Range("'Sheet 2'!$A$1:$B$8") 
Names.Add Name:="MyNamedRange1", _ 
          RefersTo:=Range("'Sheet 2'!$A$1:$B$8") 

Equal Sign Abbreviation

An alternative to using the Range method is to just use an Equal Sign

Relative References

When you create a named range you should always make the cell references absolute by using the dollar sign ($).
If a named range is created without an absolute cell reference the address will refer to a cell address relative to the cell that was active when the name was created.
Creating the following named range WILL NOT create a named range that refers to cell "A2".

Names.Add Name:="MyNamedRange1", _ 
          RefersTo:=Range("Sheet1!A2") 

The cell which this named range actually refers to will depend on the active cell when the line of code is run.

Referring to the Active Worksheet

Workbook specifc named ranges can be created so that they always refer to the active worksheet.
This can be achieved by not referring to a specific worksheet in the RefersTo parameter.

ActiveWorkbook.Names.Add Name:="MyNamedRange1", _ 
                         RefersTo:=Range("!$A$1:$B$8") 
Names.Add Name:="MyNamedRange1", _ 
                 RefersTo:=Range("$A$1:$B$8") 

Whenever the named range is used it will reference the active worksheet.

Using the Range Object

There is a much quicker way to create workbook specific named range.
It is possible to use the Name property of the Range object.

Range("A2:G6").Name = "Sheet1!MyNamedRange1" 

You can also declare a workbook specific named range to refer to the current selection.

Selection.Name = "'Sheet 2'!MyNamedRange1" 

It is important to remember that using the Add method is the only way to create named ranges that refer to constants and formulas.

Selecting Workbook Specific Names

You can use Range("WorkbookLevelNamedRange").Select

Sheets("MySheet").Select 
Range("MyNamedRange1").Select 

You must have the corresponding worksheet selected first before trying to select a workbook level named range.
If not, you will get the following error message:

You can also use the Application.GoTo method.
Using this method does not require you to have the worksheet selected first.
This method will activate the corresponding worksheet and select the range.

Application.Goto Reference:="MyNamedRange1" 

Obtaining the Range

Dim rgeRange As Range 
Set rgeRange = Names("MyNamedRange1").RefersToRange 

Important

When you use a reference such as Application.Names or just Names you are referring to the Names collection of the active workbook.
The Add method is the only way to create named ranges that refer to numbers, strings or formulas.