Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .