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

Property	Value
Creator	1480803660 (code for XLCreator)
Index	1327
MacroType	-4142 (code for xlNone)
Name	Sheetlist
NameLocal	Sheetlist
RefersTo	=OFFSET(Scenario!$D$4,0,0,COUNTA(Scenario!$D:$D)-1,1)
RefersToLocal	=OFFSET(Scenario!$D$4,0,0,COUNTA(Scenario!$D:$D)-1,1)
RefersToR1C1	=OFFSET(Scenario!R4C4,0,0,COUNTA(Scenario!C4)-1,1)
RefersToR1C1Local	=OFFSET(Scenario!R4C4,0,0,COUNTA(Scenario!C4)-1,1)
ValidWorkbookParameter	False
Value	=OFFSET(Scenario!$D$4,0,0,COUNTA(Scenario!$D:$D)-1,1)
Visible	True
WorkbookParameter	False

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