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.
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)
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 |
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.
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
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
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)
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