c# - Excel:在 Excel 中获取周围的命名范围

Question

我有一个包含许多命名范围的 Excel 工作表，这些范围具有层次结构(一个范围包含其他范围)。这些范围没有交叉点，也没有多像素区域。我如何估计特定命名范围范围的周围命名范围(换句话说，我想获得类似父子关系的东西)。例如:

这里我有范围 C。现在我想估计它的“父亲”。在这种情况下是 B。

Answer 1

如果“父级”始终完全包含子级并且是这样做的最小范围(就单元格计数而言)，那么这可能会有所帮助。我确实意识到它是 VBA 并且您的标签暗示 VSTO 但该方法可能仍然适用......

请注意，有很多情况未检查:命名公式就是其中之一。

Public Function ParentName(rng As Range) As String

Dim intersection As Range
Dim possibleParent As Range
Dim nm As Name

Dim rngCellCount As Long
Dim intersectCellCount As Long
Dim possParentCellCount As Long
Dim rangeParentCellCount As Long

    rngCellCount = rng.Cells.Count

    For Each nm In Names
        Set possibleParent = nm.RefersToRange
        Set intersection = Application.Intersect(rng, possibleParent) ' which cells are common between the target and possible parent?
        If Not intersection Is Nothing Then ' Nothing means no cells in common
            intersectCellCount = intersection.Cells.Count
            possParentCellCount = possibleParent.Cells.Count
            ' if intersection is same size as child then child is completely contained
            If intersectCellCount = rngCellCount And possParentCellCount > intersectCellCount Then
                If rangeParentCellCount > possParentCellCount Or rangeParentCellCount = 0 Then
                    ' record name of parent if smaller than best so far (or if we haven't already found a parent at all)
                    parentName = nm.Name
                    rangeParentCellCount = possParentCellCount
                End If
            End If
        End If
    Next

End Function