Так попробуйте:  
 
Sub RangeName()  
If Not Intersect(ActiveCell, [qwer]) Is Nothing Then
  MsgBox "Ячейка " & ActiveCell.Address & vbCrLf & _  
       "находится в диапазоне " & [qwer].Address & ", " & vbCrLf & _
       "имя которого - " & Names(1).Name  
Else  
   MsgBox "Ячейка " & ActiveCell.Address & vbCrLf & _  
       "не принадлежит диапазону " & [qwer].Address
End If  
End Sub

Function arnm(cell As Range) As String  
   Dim x  
   On Error Resume Next  
   For Each x In Application.Names  
      If Intersect(x.RefersToRange, cell) Is Nothing Then  
      Else  
           arnm = arnm & x.Name & " "  
      End If  
   Next  
   arnm = Trim(arnm)  
End Function  
Sub t()  
   Dim s  
   s = arnm(Cells(9, 4))  
End Sub  
 
ибо может быть несколько именованных диапазонов, содержащих данную ячейку

тут несколько раньше надо начать:  
On Error Resume Next' необходимая строка, так как не все имена ссылаются на диапазон  
For Each x In Application.Names ' перечисление всех имен  
If Intersect(x.RefersToRange, cell) Is Nothing Then ' проверка на пересечение заданного диапазона с диапазоном, на кот ссылается имя : x.RefersToRange  
Else   ' если возникает ошибка(имя не ссылается на диапазон), то эта строка обеспечивает правильную работу макроса(ну и сравнение короче - не надо not)    ( not Intersect(x.RefersToRange, cell) Is Nothing не будет работать)  
arnm = arnm & x.Name & " " 'перечисление через пробел  
End If  
Next  
arnm = Trim(arnm) ' удаление ненужного пробела в конце

это свойство объекта name  
 
у меня объекты name перечисляются в переменной x, поэтому x.referstorange  
 
 
если хотите непосредственно по имени обратиться, то будет:  
 
names("qwer").referstorange