☰

Excel Name.RefersToRange：如何检查 Name 对象是否引用某个范围？

tag-icon • tag-icon microsoft-excel vba named-ranges
Excel Name.RefersToRange：如何检查 Name 对象是否引用某个范围？

微软文档提到引用范围：

如果 Name 对象不引用某个范围（例如，如果它引用某个常量或公式），则此属性失败。

我正在尝试遍历工作表中的所有命名范围。

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

在上面的代码中，如果任何 NamedRange 没有引用范围，我的代码就会失败。我该如何检查 Name 对象是否引用范围，以免我的代码失败？

更新

我找到了一种破解方法，但我更喜欢更简洁的方法。我检查命名范围的字符串值是否包含美元符号，这间接地告诉我它是否包含地址值（范围会包含地址值）：

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

答案1

类似这样的测试，测试感兴趣表的使用范围是否与您的范围名称有效相交

警告：这确实假设你的 usedrange 确实涵盖了你的潜在范围名称。我认为这是一个安全的假设

另外，我认为你不能按照ActiveSheet.Names你尝试的方式工作。

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

答案2

编辑：我已经将错误处理更改为省略常量，并不是说我主张用这种方法来解决大多数问题，但你可以使用

on error goto "label"

看起来像

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

当 Referstorange 失败时，错误处理现在将跳过列出名称。

相关内容