How to get cell name?

  • Thread starter Thread starter Tros Alba
  • Start date Start date
T

Tros Alba

How I can get the name of the cell ?
I want something like this = NAME(Sheet2:B5)

Thank you
Alba Tros
 
Hi there,

Not sure if I am understanding you correctly here.. I'll give it a go.

Option Explicit
Option Compare Text
Public Function NAMEDRANGE(celRef As String) As String
Dim iName As Name, wbCall As Workbook
Set wbCall = Workbooks(Application.Caller.Parent.Parent.Name)
For Each iName In wbCall.Names
If iName.RefersTo = "=" & celRef Then
NAMEDRANGE = iName.Name
Set wbCall = Nothing
Exit Function
End If
Next
NAMEDRANGE = "Not Found"
Set wbCall = Nothing
End Function

Called like ..

=NAMEDRANGE("Sheet1!$A$1")

... will return the named range if that is the exact Refers To of the named
range. Is this what you are looking for?

HTH
 
A couple of suggested changes.

Allow for a string or a cell.
Test for intersect.

Public Function NAMEDRANGE(celRef) As String
Dim iName As Name, wbCall As Workbook
If TypeOf celRef Is Range Then
celRef = celRef.Address
End If
If Not celRef Like "*!$" Then
celRef = Application.Caller.Parent.Name & "!" & celRef
End If
Set wbCall = Workbooks(Application.Caller.Parent.Parent.Name)
For Each iName In wbCall.Names
If Not Intersect(Range(celRef), Range(iName.RefersTo)) Is Nothing
Then
NAMEDRANGE = iName.Name
Set wbCall = Nothing
Exit Function
End If
Next
NAMEDRANGE = "Not Found"
Set wbCall = Nothing
End Function



--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Thanks Bob! Very nice! Thought about doing the Variant way.. too lazy I
guess. LOL! I adapted it slightly to test for 'near intersects' ...


Option Explicit
Option Compare Text
Public Function NAMEDRANGE(celRef As Variant) As String
'Declare variables
Dim iName As Name, wbCall As Workbook, blnApprox As Boolean
On Error Resume Next
'Check if celRef is a Range
If TypeOf celRef Is Range Then
celRef = celRef.Address
End If
If Not celRef Like "*!$" Then
celRef = Application.Caller.Parent.Name & "!" & celRef
If Err = 424 Then celRef = Range(celRef).Parent.Name & "!" & celRef
Err.Clear
End If
'Set function caller (dynamic)
Set wbCall = Workbooks(Application.Caller.Parent.Parent.Name)
'If called from VBA, then ..
If Err = 424 Then Set wbCall = ActiveWorkbook
On Error GoTo 0
'Loop through all names in workbook
For Each iName In wbCall.Names
'If name matches specified parameter
If iName.RefersTo = "=" & celRef Then
NAMEDRANGE = iName.Name
Set wbCall = Nothing
Exit Function
ElseIf Not Intersect(Range(celRef), Range(iName.RefersTo)) Is
Nothing Then
NAMEDRANGE = "Intersects '" & iName.Name & "'"
blnApprox = True
End If
Next
Err_Exit:
'If not found, mark it and release variable.
If Not blnApprox Then NAMEDRANGE = "Not Found"
Set wbCall = Nothing
End Function
 
Back
Top