Get Name of Range in Excel with Formula

Question

I've named a bunch of columns in Excel.

Now I would like to be able to get the name of a column in a cell using a formula.

So for example, if A:A was named Dates, I want to be able to put a formula in a cell such that =RangeName(A:A) returns the word Dates.

I've found examples of how to do this for a single cell, but not for a range of cells.

This is what I have found for a single cell.

    Public Function CellName(cel As Range) As Variant
Dim nm As Name
    For Each nm In Names
        If nm.RefersTo = "=" & cel.Parent.Name & "!" & cel.Address Then
            CellName = nm.Name
            Exit Function
        End If
    Next
CellName = CVErr(xlErrNA)
End Function

Answer 1

It sounds like a case for Intersect:

Public Function CellName(cel As Range) As Variant
    Dim nm As Name
    For Each nm In Names
        If Not Intersect(cel, nm.RefersToRange) Is Nothing Then
            CellName = nm.Name
            Exit Function
        End If
    Next
    CellName = CVErr(xlErrNA)
End Function