VBA Code

Named ranges can be very useful when using macros.
A specific cell or range of cells referred to in VBA code will become invalid if any rows or columns are inserted or deleted in front of it.
The advantage of referring to named ranges is that this does not happen and the cells are still valid.
Named Ranges can be particularly useful in VBA code when you need to refer to a particular cell or range of cells.
It is also easy to change the definition of a named range using VBA code to allow for any changes that you might make to the cells.

Removing the Equal Sign

This doesn't work you must use:

sValue = ActiveSheet.Names("MyNamedRange").RefersTo 

or remove the equal sign from the front

sValue = ActiveSheet.Names("MyNamedRange").Value 
sValue = Right(sValue,Len(sValue)-1) 

Resizing

Sheets("--").Range("A4").Resize("Range("---").Rows.Count, Range("---").Columns.Count + 1).Name = "-----" 

Changing the Name

It is possible that after a named range has been created, you might want to change the actual name.
This can be done by using the Name property of a Name object.

Dim objName As Excel.Name 
Activeworkbook.Names.Add Name:="MyRange1", _ 
                         RefersTo:=2 
Set objName = Activeworkbook.Names("MyRange1") 
objName.Name = "MyRange2" 
MsgBox ActiveWorkbook.Names("MyRange2").Value 

You can return the cell range of a named range by using string =Sheets("SheetName").Range("NamedRange").Address.
If you reference Range("D4").Value in your VBA code it will be safer to create a names for the range "D4" and refer to that.
If rows or columns get inserted / deleted then your VBA code will still work.

Creating Names

objRange = "Worksheeets("Sheet1").Range("A2:E6") 
objRange.CreateNames Left:=True 

Creates names in the specified range, based on text labels in the sheet.
If you don't specify one of Top, Left, Bottom, or Right, Microsoft Excel guesses the location of the text labels, based on the shape of the specified range.
It is possible to hide a named range after it has been created.

Names.("VisibleName").Visible = False 

If a named range is created with the same name then the previous one will be over-written. This can be prevented by protecting the worksheet.

Hidden Named Ranges

It is possible to create hidden named ranges that do not appear in the Name Manager dialog box.
These can be a useful way to store worksheet and workbook specific information which the user cannot see.

ActiveSheet.Names.Add Name:="HiddenName", _ 
                      RefersTo:="$B$4", _ 
                      Visible:=False 

This technique can be useful for storing worksheet passwords.

Resizing Named Ranges

Range("NamedRange").Resize(3,3).Name = "NamedRange" 

Using the Evaluate Method

It is possible to use the Evaluate method to obtain the value for a specific named range.

Dim sValue As String 
sValue = Application.Evaluate(MyNamedRange1) 
sValue = Application.Evaluate("wbklevel") 
sValue = Application.Evaluate("wshlevel") 
Debug.Print sValue 

How Many Named Ranges ?

Public Sub HowMany() 
    MsgBox ActiveWorkbook.Names.Count 
End Sub 

Updating a Named Range

Private Sub btnUpdate_Click() 
Dim sformula As String 
Dim snewformula As String 
   sformula = ActiveWorkbook.Names("MyFormula").Value 
   snewformula = Replace(sformula, "'05'", "'04'") 
   ActiveWorkbook.Names("MyFormula").RefersTo = snewformula 
End Sub