4

I have a cell in Excel for which I have assigned a variable name, this_cells_name, to a cell, D2, by using the Excel Name Box.

Here is an example of what the Excel Name Box is:

The Excel Name Box

I want to be able to point to that cell and get the variable name as a return value.

I know how to do the following:

  • use =CELL("address",D2) and get "$D$2" as a return value,
  • use =CELL("address",this_cells_name) and get "$D$2" as a return value.

I want to do the following:

  • use =some_function(D2) and get "this_cells_name" as a return value.

How can I do this? A VBA solution would be fine.

Simon Streicher
  • 2,638
  • 1
  • 26
  • 30

2 Answers2

3

You can also use

Dim var as variant
on error Resume Next
var=Range("D2").Name.Name
on error goto 0
if IsEmpty(var) then msgbox "Cell has no name"

The error trapping is to handle the case where the cell has no name

Charles Williams
  • 23,121
  • 5
  • 38
  • 38
  • I don't think its recent: since my hard disk crashed I can only go back to Excel 2003 but that has Range.Name (IIRC it goes back to at least Excel97). The trick is to use Name.Name since the default property of a Name object is its refersto formula. – Charles Williams Jan 25 '15 at 21:42
2

Consider:

Public Function WhatsInAName(r As Range) As String
    WhatsInAName = ""
    For Each n In ThisWorkbook.Names
        If Range(n).Address(0, 0) = r.Address(0, 0) Then
            WhatsInAName = n.Name
        End If
    Next n
End Function

For example:

enter image description here

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • What do you think of using `r.Name.Name`? Actually, I see that as an accepted answer here: http://stackoverflow.com/a/3630587/293078 – Doug Glancy Jan 25 '15 at 19:35