excel - 选择范围内的形状。奇怪的看似随机的结果？

Question

我正在尝试选择范围内的形状，但代码的结果并不完全符合我的预期。它随机选择比预期更多的形状(不在范围内)。

Public Sub ShapeSelection()
Dim Sh As Shape
Dim selectedOne As Boolean
On Error Resume Next

With ActiveSheet
   For Each Sh In .Shapes
       If Not Application.Intersect(Sh.TopLeftCell, .Range(Selection.Address)) Is Nothing Then
          If selectedOne = False Then
              Sh.Select
              selectedOne = True
           Else
              Sh.Select (False)
           End If
       End If
    Next Sh
End With
End Sub

Answer 1

奇怪的行为是由“Selection.Address”引起的

在循环中，当找到第一个形状时，您可以将当前选择从范围 C3 更改为第一个形状

下一次循环时，它会尝试将 TopLeftCell 的地址与形状对象的地址进行比较(相交):形状对象本身没有地址(其 TopLeftCell 有一个地址)

但是你走了很长的路:你不需要使用相交。下面的代码按照您的预期工作:

Option Explicit

Public Sub ShapeSelection()

    Dim Sh As Shape
    Dim sRng As Range

    With ActiveSheet
        Set sRng = Selection
        For Each Sh In .Shapes
            If Sh.TopLeftCell.Address = sRng.Address Then
                Sh.Select
                Exit For
            End If
        Next Sh
    End With
End Sub

<小时/>

编辑:我刚刚注意到您之前的问题:How to select multiple shapes based on range?

需要交集来完成该要求，但您仍然需要保留对所选单元格的引用:

Option Explicit

Public Sub ShapeSelection()

    Dim Sh As Shape
    Dim sRng As Range

    With ActiveSheet
        If TypeName(Selection) = "Range" Then
            Set sRng = Selection
            If sRng.CountLarge = 1 Then
                For Each Sh In .Shapes
                    Sh.Select False
                Next Sh
            Else
                For Each Sh In .Shapes
                    If Not Application.Intersect(Sh.TopLeftCell, .Range(sRng.Address)) Is Nothing Then
                        Sh.Select False
                    End If
                Next Sh
            End If
        End If
    End With
End Sub