Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

The Microsoft documentation says about RefersToRange:

If the Name object doesn't refer to a range (for example, if it refers to a constant or a formula), this property fails.

I'm trying to iterate over all the named ranges in a worksheet.

Private Sub Something()

    Dim namedRanges As names
    Set namedRanges = ActiveSheet.names

    Dim targetSheet As Worksheet
    Set targetSheet = Sheet1
    targetSheet.Cells.Clear

    Dim i As Integer
    For i = 1 To namedRanges.count
        targetSheet.Cells(i, 2).Value = namedRanges(i).Name
        targetSheet.Cells(i, 3).Value = namedRanges(i).RefersToRange.Address
    Next


End Sub

In the above my code will fail if any NamedRange doesn't refer to a range. How can I check if the Name object refers to a range so my code doesn't fail?

UPDATE

I found a hack to do this but I'd prefer a cleaner way. I check if the string value of the named range contains a dollar sign which indirectly tells me if it contains an address value (which a range would):

For i = 1 To namedRanges.count
    targetSheet.Cells(i, 2).Value = namedRanges(i).Name
    If InStr(namedRanges(i).Value, "$") > 0 Then
        targetSheet.Cells(i, 3).Value = namedRanges(i).RefersToRange.Address
    End If
Next
share|improve this question
    
That won't always work. Valid ranges don't have to have "$" signes, ie =OFFSET(NameRange,0,0) may have a valid range, and a relative range name '=A1' also is valid without a $. –  brettdj Nov 5 '11 at 7:01
add comment

2 Answers

Something like this which tests if there is a valid intersect in the usedrange of the sheet of interest with your range name

Caveat: This does assume that your usedrange does cover your potential range names. Which I think is a safe assumption

Also you I don't think you can work with ActiveSheet.Names in the way you tried.

Private Sub Something()
    Dim nmRng As Name
    Dim rng1 As Range
    Dim rng2 As Range
    Set rng1 = Sheets(1).UsedRange
    On Error Resume Next
    For Each nmRng In ActiveWorkbook.Names
        Set rng2 = Nothing
        Set rng2 = Intersect(rng1, Range(nmRng))
        If Not rng2 Is Nothing Then Debug.Print nmRng & " " & nmRng.RefersTo.Address
    Next
    On Error GoTo 0
End Sub
share|improve this answer
    
You can use ActiveSheet.Names but you may be right that it doesn't restrict names to the current sheet. –  User Nov 5 '11 at 19:01
    
Is there a reason you have the on error resume next in your code? –  User Nov 5 '11 at 20:41
    
Yes, it means that invalid Intersect tests wont cause the code to fail. –  brettdj Nov 5 '11 at 22:19
add comment

EDIT: I've changed the error handling to omit constants Not that I advocate this approach for most problems but you could use

on error goto "label"

which would look something like

Private Sub Something()

Dim namedRanges As names
Set namedRanges = ActiveSheet.names

Dim targetSheet As Worksheet
Set targetSheet = Sheet1
targetSheet.Cells.Clear

Dim i As Integer

'skip the errors
on error goto skipName
'set start of data range
Row = 2
For i = 1 To namedRanges.count

    targetSheet.Cells(Row, 3).Value = namedRanges(i).RefersToRange.Address
    targetSheet.Cells(Row, 2).Value = namedRanges(i).Name
Row = Row + 1
skipName:
Next

'reinstate normal error trapping
on error goto 0

End Sub

The error handling will now skip listing the names when Referstorange fails.

share|improve this answer
    
Thanks but yes I do not want to list constant or formula names. –  User Nov 4 '11 at 7:10
    
I've changed the code above so that it will no longer print constant names. –  Pynner Nov 4 '11 at 23:27
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.