Code Snippets

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

Equal Sign Abbreviation

An alternative to using the Range method is to just include an equal sign.
Workbook Level:

Names.Add Name:="MyNamedRange2", _ 
          RefersTo:=Range("A1") 
Names.Add Name:="MyNamedRange2", _ 
          RefersTo:="=A1"

Worksheet Level:

Names.Add Name:="MyNamedRange3", _ 
          RefersTo:=Range("'" & ActiveSheet.Name & '!" & saddress) 
Names.Add Name:="MyNamedRange3", _ 
          RefersTo:="=" & "'" & ActiveSheet.Name & "'!" & saddress

Constants and Formulas


How to detect a formula or a constant ?

Does the refers to contain an exclamation mark (!) if it does then it must refer to a cell range.
When you use a named range to store a numeric or string value you should not prefix the RefersTo parameter with an equal sign (=).
If you do, it is assumed to be a formula and not a constant.


Workbook Constant

Dim lnumber As Long 
lnumber = 200 
ActiveWorkbook.Names.Add Name:="RandomNo", _ 
                         RefersTo=lnumber

Worksheet Constant

Dim lnumber As Long 
lnumber = 200 
ActiveSheet.Names.Add Name:="RandomNo", _ 
                      RefersTo=lnumber

You can retrieve this value by using the Evaluate method:

lnumber = [RandomNo]

Workbook Formulas

Application.Names.Add Name = "Formula", _ 
                      RefersTo:="=COUNT($D:$D)"

This named range can then be used on any worksheet to return the total number of items in column D


Worksheet Formulas

Worksheets("Sheet1").Names.Add Name = "Formula", _ 
                     RefersTo:="=COUNT($E:$E)"

This named range can only be used on the Sheet1 worksheet to return the total number of items in column E.


Important

You text strings cannot exceed more than 255 characters.


Hidden Named Ranges


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

Application.Names.Add Name:="MyNamedRange1", _ 
                      RefersTo:="Sheet1!$A$1:$B$8" 
                      Visible:=False

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

ActiveWorkbook.Names.Add Name:="MyNamedRange1", _ 
                               RefersTo:="Sheet1!$A$1:$B$8" 
                               Visible:=False 

Names.Add Name:="MyNamedRange1", _ 
               RefersTo:="Sheet1!$A$1:$B$8" 
               Visible:=False

Show Hidden Named Ranges

This subroutine will display any hidden named ranges so they can be viewed in the Name Manager dialog box.

Public Sub HiddenToVisible() 
Dim nameRge As Excel.Name 
Dim lcount As Long 
   For Each nameRge In ActiveWorkbook.Names 
      If (nameRge.Visible = False) Then 
         nameRge.Visible = True 
         Application.StatusBar = "Visible - " & nameRge.Name 
         lcount = lcount + 1 
      End If 
   Next nameRge 
   Application.StatusBar = False 
   Call MsgBox("'" & lcount - 1 & "' hidden named ranges are now visible") 
End Sub

Determining if a range name exists

This function will return true if a named range exists

Private Function BET_RangeNameExists(nname) As Boolean 
Dim n As Name 
    BET_RangeNameExists = False 
    For Each n In ActiveWorkbook.Names 
        If UCase(n.Name) = UCase(nname) Then 
            BET_RangeNameExists = True 
            Exit Function 
        End If 
    Next n 
End Function

Removing Named Ranges


Public Sub DeleteNamedRanges() 
Dim lcount As Long 
Dim sname As Name 
Dim smessage As String 
   For lcount = ActiveWorkbook.Names.Count To 1 Step -1 
      smessage = lcount 
         
      On Error Resume Next 
      Set sname = ActiveWorkbook.Names(lcount) 
      
      If (InStr(sname.RefersTo, "#REF") > 0) Then 
         smessage = sname.Name & " - deleted" 
         ActiveWorkbook.Names(lcount).Delete 
         Debug.Print smessage 
      Else 
         Debug.Print sname.Name 
      End If 

   Next lcount 
End Sub

Workbook Specific

Public Sub DeleteNamedRanges() 
Dim sname as Name 
   For Each sname in ActiveWorkbook.Names 
      ActiveWorkbook.Names(sname.name).Delete 
   Next sname 
End Sub

Identifying worksheet level named ranges

For Each objName In ActiveWorkbook.Names 
   If objName.Name = worksheet!namedrange 
Next objName

Worksheet Specific

Public Sub DeleteNamedRanges() 
Dim inamecount As Integer 
   For inamecount = 1 To ActiveSheet.Names.Count 
      ActiveSheet.Names(1).Delete 
   Next inamecount 
End Sub

© 2026 Better Solutions Limited. All Rights Reserved. © 2026 Better Solutions Limited TopPrevNext