Names Object (Excel)
A collection of all the Name objects in the application or workbook.
Each Name object represents a defined name for a range of cells. Names can be either built-in names—such as Database, Print_Area, and Auto_Open—or custom names.
The RefersTo argument must be specified in A1-style notation, including dollar signs ($) where appropriate. For example, if cell A10 is selected on Sheet1 and you define a name by using the RefersTo argument "=sheet1!A1:B1", the new name actually refers to cells A10:B10 (because you specified a relative reference). To specify an absolute reference, use "=sheet1!$A$1:$B$1".
Use the Names property to return the Names collection. The following example creates a list of all the names in the active workbook, plus the addresses they refer to.
Set nms = ActiveWorkbook.Names Set wks = Worksheets(1) For r = 1 To nms.Count wks.Cells(r, 2).Value = nms(r).Name wks.Cells(r, 3).Value = nms(r).RefersToRange.Address Next
Use the Add method to create a name and add it to the collection.The following example creates a new name that refers to cells A1:C20 on the worksheet named "Sheet1."
Names.Add Name:="test", RefersTo:="=sheet1!$a$1:$c$20"
Use Names(index), where index is the name index number or defined name, to return a single Name object. The following example deletes the name "mySortRange" from the active workbook.
This example uses a named range as the formula for data validation. This example requires the validation data to be on Sheet 2 in the range A2:A100. This validation data is used to validate data entered on Sheet 1 in the range D2:D10.
Sub Add_Data_Validation_From_Other_Worksheet() 'The current Excel workbook and worksheet, a range to define the data to be validated, and the target range 'to place the data in. Dim wbBook As Workbook Dim wsTarget As Worksheet Dim wsSource As Worksheet Dim rnTarget As Range Dim rnSource As Range 'Initialize the Excel objects and delete any artifacts from the last time the macro was run. Set wbBook = ThisWorkbook With wbBook Set wsSource = .Worksheets("Sheet2") Set wsTarget = .Worksheets("Sheet1") On Error Resume Next .Names("Source").Delete On Error GoTo 0 End With 'On the source worksheet, create a range in column A of up to 98 cells long, and name it "Source". With wsSource .Range(.Range("A2"), .Range("A100").End(xlUp)).Name = "Source" End With 'On the target worksheet, create a range 8 cells long in column D. Set rnTarget = wsTarget.Range("D2:D10") 'Clear out any artifacts from previous macro runs, then set up the target range with the validation data. With rnTarget .ClearContents With .Validation .Delete .Add Type:=xlValidateList, _ AlertStyle:=xlValidAlertStop, _ Formula1:="=Source" 'Set up the Error dialog with the appropriate title and message .ErrorTitle = "Value Error" .ErrorMessage = "You can only choose from the list." End With End With End Sub
About the Contributor
Dennis Wallentin is the author of VSTO & .NET & Excel, a blog that focuses on .NET Framework solutions for Excel and Excel Services. Dennis has been developing Excel solutions for over 20 years and is also the co-author of “Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft Excel, VBA and .NET (2nd Edition)."