According to this you must know the index in the collection.
MsgBox Names(2).Name
Name Object
See Also Properties Methods Events Specifics
Application
Workbooks (Workbook)
Names (Name)
Worksheets (Worksheet)
Range
Name
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.
Application, Workbook, and Worksheet Objects
The Name object is a member of the Names collection for the
Application,Workbook, and Worksheet objects. Use Names(index), where index
is the name index number or defined name, to return a single Name object.
The index number indicates the position of the name within the collection.
Names are placed in alphabetic order, from a to z, and are not
case-sensitive (this is the same order as is displayed in the Define Name
and Apply Names dialog boxes, returned by clicking the Name command on the
Insert menu). The following example displays the cell reference for the
first name in the application collection.
MsgBox Names(1).RefersToThe following example deletes the name "mySortRange"
from the active workbook.
ActiveWorkbook.Names("mySortRange").DeleteUse the Name property to return or
set the text of the name itself. The following example changes the name of
the first Name object in the active workbook.
Names(1).Name = "stock_values"Range Objects
Although a Range object can have more than one name, there's no Names
collection for the Range object. Use Name with a Range object to return the
first name from the list of names (sorted alphabetically) assigned to the
range. The following example sets the Visible property for the first name
assigned to cells A1:B1 on worksheet one.
Worksheets(1).Range("a1:b1").Name.Visible = False
