Excel Name.RefersToRange: como verificar se o objeto Name se refere a um intervalo?

Question

A documentação da Microsoft diz sobre 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.

Estou tentando iterar todos os intervalos nomeados em uma planilha.

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

No código acima, meu código falhará se qualquer NamedRange não se referir a um intervalo. Como posso verificar se o objeto Name se refere a um intervalo para que meu código não falhe?

UPDATE

Eu encontrei um hack para fazer isso, mas eu prefiro um jeito mais limpo. Eu verifico se o valor da string do intervalo nomeado contém um sinal de dólar que indiretamente me diz se ele contém um valor de endereço (que seria um intervalo):

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

    

Answer 1

Algo parecido com isso, que testa se há uma intersecção válida na faixa usada da planilha de interesse com o nome do intervalo

Ressalva: Isso supõe que a sua área usada cobre seus nomes de alcance em potencial. O que eu acho que é uma suposição segura

Além disso, você não acha que pode trabalhar com ActiveSheet.Names da maneira como tentou.

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

    

Answer 2

EDIT: Eu mudei o tratamento de erros para omitir constantes Não que eu defenda essa abordagem para a maioria dos problemas, mas você poderia usar

on error goto "label"

que seria algo como

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

O tratamento de erros agora ignorará a listagem dos nomes quando o Referstorange falhar.