How to get the name "cell name" of an Excel cell?

Question

I have a cell in Excel for which I assigned a variable name to a this_cells_namecell D2, using the Excel name field.

Here is an example of what an Excel name field is:

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

I know how to do the following:

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

I want to do the following:

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

How can i do this? A VBA solution would be nice.

Answer 1

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"

,

Answer 2

:

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

: