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?
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