Excel VBA. How do you get a specific name associated with a range? -

i have cell has more 1 named ranges associated it. how return specific name without having loop through names in workbook?

sub test()     activeworkbook.names.add name:="auserdefinedname1", refersto:="='sheet1'!$a$1", visible:=true     activeworkbook.names.add name:="auserdefinedname2", refersto:="='sheet1'!$a$1", visible:=true     activeworkbook.names.add name:="myname", refersto:="='sheet1'!$a$1", visible:=true     dim n name     set n = range("a1").name     debug.print n.name end sub 

if run above, cell a1 return first name. however, how return other names associated cell a1 or return 'myname'?

i loop through names in workbook , see if 'refersto' property of name object matches address cell a1, more direct solution if possible that's faster.

this thread gave partial answer, need if there more 1 name? how range return name?

use workbook.names collection , test address:

for each n in activeworkbook.names     if n.referstorange.address = "$a$1"         debug.print n.name     end if next 

there's whole msdn article named ranges further reading.

there isn't names collection range object way accomplish @ workbook level. unless have thousands of named ranges in workbook there no noticeable speed difference in using method, create udf names need.