and yet another (more elaborate?) way:
:Uses NameLocal
:Gets dynamic ranges too...
Option Explicit
Function GetRangeName(oRange As Range, Optional bExpand As Boolean) As
String
Dim rg As Range, nm As Name
On Error Resume Next
Set nm = oRange.Name
If Not nm Is Nothing Then
GetRangeName = nm.NameLocal
End If
If bExpand Then
'part of larger named range?
'(also dynamic names with index of offset formulas)
For Each nm In oRange.Parent.Parent.Names
Set rg = Range(nm.Name)
If Not rg Is Nothing Then
If Not Intersect(oRange, rg) Is Nothing Then
GetRangeName = GetRangeName & ";" & nm.NameLocal
End If
End If
Next
If Left$(GetRangeName, 1) = ";" Then
GetRangeName = Mid$(GetRangeName, 2)
End If
End If
End Function
Sub Demo()
MsgBox "Activecell (not expanded)" & vbTab & _
GetRangeName(ActiveCell, False) & vbLf & _
"Activecell (expanded) " & vbTab & _
GetRangeName(ActiveCell, True) & vbLf & _
"Selection (not expanded)" & vbTab & _
GetRangeName(ActiveWindow.RangeSelection, False) & vbLf & _
"Selection (expanded) " & vbTab & _
GetRangeName(ActiveWindow.RangeSelection, True)
End Sub
--
keepITcool
|
www.XLsupport.com | keepITcool chello nl | amsterdam
Tetsuya Oguma wrote :