Getting Named range from a function

[SabreWulf]

I have a command button which calls two experimental (I.e. non-working) functions to try to get a named range given an index number. This should be simple but i get four empty cells for my pains. Can someone please spot the non-deliberate mistake. Ideally I'd like a function similar to either of the two that returns a range (object) that is referred to by a given name.

Private Sub CommandButton6_Click()
Dim r As Range

    Set r = GetRangeNameRefersToR(1)
    [i5] = r.Address
    [i6].Value = GetRangeNameRefersTo(2)
    [i7].Value = GetRangeNameRefersTo(3)
    [i8].Value = GetRangeNameRefersTo(4)
    [i5:i8].Select
    
End Sub

and the useless functions are...

Function GetRangeNameRefersTo(TheIndex As Long) As String
    Dim S As String
    On Error Resume Next
    S = Names(TheIndex).RefersToRange.Address    
End Function

Function GetRangeNameRefersToR(TheIndex As Long) As Variant
    Dim r As Variant
    On Error Resume Next
    r = Names(TheIndex).RefersToRange
    Set GetRangeNameRefersToR = r    
End Function

 

[mikerickson]

If you have a named range "myNamedRange", you can access it in a couple of ways.

Range("myNamedRange")
or
ThisWorkbook.Names("myNamedRange").RefersToRange

If you want the third named range in a workbook then
ThisWorkbook.Names(3).RefersToRange

Looking at your code, have you looked at the Insert>Names>Paste>PasteList option that will paste a list of names and their RefesTo onto the worksheet.

 

[Marcelo Branco]

Try this

Function GetAddress(TheIndex As Long) As String
    On Error Resume Next
    GetAddress = ThisWorkbook.Names(TheIndex).RefersToRange.Address
End Function

HTH

M.

 

[mikerickson]

I am really puzzled why the OP is using a numeric index to refer to named ranges rather than their (string) names.

Hmmm...Actualy, I can't think of any way to create a Name object without specifying its .Name property.

 

[SabreWulf]

Thanks for your responses, I'm often away from the web for a day or two. I shall try your suggestions and report back. In the meantime I give a grateful world the sub below which helps with ArrayFormulas.

mikerickson: I don't know what OP means, but I'm working on a sub that will (when called by hotkey) iterate through the Names collection to get those Names that refer to names and then check if the ActiveCell is within (Ie. Intersecting) each of those ranges. Excel will then (I hope) select an intersecting NameRange. Unfortunately I can't know in advance the text strings associated with a name.

The resulting sub will be the sister of the very useful one I managed to write myself (OK so I cheated and used the Macro recorder)


Sub SelectArrayFormulaCells()
'
' If the ActiveCell has an array-Formula applied to it, this sub will select
' the whole block an ArrayFormula has been applied to, to speed up editing.
'
' Keyboard Shortcut: Ctrl+s
'
On Error Resume Next
Selection.CurrentArray.Select
End Sub

 

[mikerickson]

All that selecting is probably not needed. But to find if a cell is in a Named Range you might want to look at this UDF. Note that it returns a Name object, not a Range object.

Function NamedRangeIntersect(Optional homeRange As Range) As Name
    Dim oneName As Name
    
    If homeRange Is Nothing Then Set homeRange = ActiveCell
    On Error Resume Next
    For Each oneName In ThisWorkbook.Names
        With oneName.RefersToRange
            If .Parent.Name = homeRange.Parent.Name Then
                If Not (Application.Intersect(.Cells, homeRange) Is Nothing) Then
                    Set NamedRangeIntersect = oneName
                End If
            End If
        End With
    Next oneName
    On Error GoTo 0
    
End Function

Sub test()
    Dim xVal As Name
    
    Set xVal = NamedRangeIntersect()
    If xVal Is Nothing Then
        MsgBox "not in a range"
    Else
        MsgBox "in the named range " & xVal.Name & ", which is the cells " & xVal.RefersToRange.Address(,,,True)
    End If
End Sub