Managing range names with VBA

If you start using range names extensively, and you find
yourself needing to add or remove names from workbooks, knowing how
to do it in code will save you a lot of time. When you manipulate or
loop through range names in VBA you will need to use the Names
collection. The code samples on this page should help you to become
proficient with using names in VBA.

Creating range names in VBA

Range names have a number of properties in VBA. The two that must
be defined when you create a name in code are the Name
and the RefersTo properties.

Note: If you have a
particular reason for hiding a name from general view and making it
accessible only from VBA, you can also use the Visible
property and set it to False. The Visible property
hides the name from the Name box drop-down and the Insert Names
dialog. It will not display if you list the names using F3 > Paste
list. However, you can refer to the name in VBA and use its value in
VBA routines.

As an example, the following code creates a name referring to
Sheet2!$A$1:$F$50

Sub MakeName()
  ActiveWorkbook.Names.Add Name:="PTable", RefersTo:="Sheet2!$A$1:$F$50"
End Sub

This will create the name, using the current selection as
the reference

Sub MakeName_Selection()
  Sheets("Sheet2").Activate
  Range("A1").CurrentRegion.Select
  ActiveWorkbook.Names.Add Name:="PTable", RefersTo:=Selection
End Sub

Let’s assume that you need to find a specific cell first, then
create a range 60 columns wide, offset from the found cell. You
could use code like this:

  'find row for Lot Profile
  Range("A1").Select
    Cells.Find(What:="Lot Profile", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False).Activate
  ActiveCell.Offset(1,5).Resize(1,60).Select
  ActiveWorkbook.Names.Add Name:="Lots", RefersTo:=Selection

  'you can also create names based on an offset of the current selection:
  ActiveWorkbook.Names.Add Name:="Deposits", RefersTo:=Selection.Offset(1,0)

Listing properties of names

The following code attempts to list all of the possible
properties of a given name. As you can see in the output table below
the code, not all properties can be retrieved for all names.

Sub NameProperties()
  Dim nm As Name
  Set nm = Names("Sheetlist")

  On Error Resume Next
  With nm
    Debug.Print "Category: " & nm.Category 'valid only with XLM macros
    Debug.Print "CategoryLocal: " & nm.CategoryLocal
    Debug.Print "Creator: " & nm.Creator
    Debug.Print "Comment: " & nm.Comment 'new in Excel 2007
    Debug.Print "Index: " & nm.Index
    Debug.Print "MacroType: " & nm.MacroType 'valid only with XLM macros. Returns xlNone otherwise
    Debug.Print "Name: " & nm.Name
    Debug.Print "NameLocal: " & nm.NameLocal
    Debug.Print "Parent: " & nm.Parent
    Debug.Print "RefersTo: " & nm.RefersTo
    Debug.Print "RefersToLocal: " & nm.RefersToLocal
    Debug.Print "RefersToR1C1: " & nm.RefersToR1C1
    Debug.Print "RefersToR1C1Local: " & nm.RefersToR1C1Local
    Debug.Print "RefersToRange: " & nm.RefersToRange
    Debug.Print "ShortcutKey: " & nm.ShortcutKey 'valid only with XLM macros
    Debug.Print "ValidWorkbookParameter: " & nm.ValidWorkbookParameter 'new in Excel 2007
    Debug.Print "Value: " & nm.Value
    Debug.Print "Visible: " & nm.Visible
    Debug.Print "WorkbookParameter: " & nm.WorkbookParameter 'new in Excel 2007
  End With
End Sub

Comments on the properties

Some properties (Category, MacroType and ShortcutKey) are only
valid if the name is used in an XLM macro. Others (Comment,
WorkbookParameter and ValidWorkbookParameter) are new to Excel 2007.

The properties ending in Local return the same
result as their counterparts without Local. The difference is that,
for example, RefersTo will use the language currently being used in
the VBA code; RefersToLocal will use the language in the user’s
regional settings.

Creating workbook-level names

To create a workbook-level name, you add the name to the
ActiveWorkbook.

Sub MakeName_Workbook()
  ActiveWorkbook.Names.Add Name:="Stages", RefersTo:=Selection
End Sub

Creating sheet-level names

To create a sheet-level name, you add it to the ActiveSheet.
Otherwise, you create sheet-level names exactly as you would for
workbook-level names.

Sub MakeName_Worksheet()
  ActiveSheet.Names.Add Name:="Stages", RefersTo:=Selection
End Sub

Creating dynamic named ranges

If you need to create a large number of dynamic ranges for
charting, it can be tedious to do it by hand. The following code
will create dynamic ranges from a table with headings in Row 1. The
headings will be used as the range names.

Sub DynamicNames()

  Dim LastCol As Long, _
      LabelRow As Long, _
      Col As Long
  Dim sName As String
  Dim c As Range
  Dim Sht As String

  'assign row and column parameters
  '**adjust for the row containing your headings
  LabelRow = 1
  LastCol = Range("IV1").End(xlToLeft).Column

  'grab sheet name
  Sht = "'" & ActiveSheet.Name & "'"

  For Each c In Range(Cells(LabelRow, 1), Cells(LabelRow, LastCol))
    Col = c.Column
    sName = c.Value
    If Len(sName) > 1 Then
      'replace spaces with underscores
      sName = Replace(sName, " ", "_", 1) 
      'create the name
      ActiveWorkbook.Names.Add Name:=sName, RefersToR1C1:= _
        "=OFFSET(" & Sht & "!R2C" & Col & ",0,0,COUNTA(" & Sht & "!C" & Col & ")-1,1)"
    End If
  Next c
End Sub

Assuming that you ran this on a sheet called Chart Data, with NSW
Sales in Column D, the corresponding range name would be called
NSW_Sales. Its reference as created by the above
code would be

=OFFSET('Chart Data'!$D$2,0,0,COUNTA('Chart Data'!$D:$D)-1,1)

Deleting names in VBA

Deleting names with invalid references

Over time, as you delete worksheets and ranges, you will end up
with names that have broken references. To clean them out, use this
code:

Sub DeleteBadRefs()
  Dim nm As Name
  
  For Each nm In ActiveWorkbook.Names
    If Instr(1, nm.RefersTo, "#REF!")>0 Then
      'List the name before deleting
      Debug.Print nm.Name & ": deleted"
      nm.Delete
    End If
  Next nm
End Sub

 
Home | About |
Links |

Contact | Excel |
Access