在形状中循环以获得尺寸

Question

目前，我有一大堆工作簿，每个工作簿都包含数百个形状的工作表，我需要一个代码来选择它们的最宽的形状，并得到其宽度的值。谢谢

    Sub GetWidestShape()
        for each shape in activesheet.shapes
        ' if shape is widest then
        ' shape.copy
        ' range("a1").value=the width of the widest shape
    end sub

Answer 1

您也可以使用一个函数返回最宽的形状并设置其宽度。

Function GetWidestShape(widestShpWidth As Long) As Shape
    Dim shp As Shape

    For Each shp In ActiveSheet.Shapes
      If shp.Width > widestShpWidth Then
           widestShpWidth = shp.Width
           Set GetWidestShape = shp
      End If
    Next
End Function

您可以在主代码中按以下方式利用该漏洞：

Sub Main()
    Dim widestShp As Shape
    Dim widestShpWidth As Long

    Set widestShp = GetWidestShape(widestShpWidth) '<--| get the widest shape along with ist width 
    With widestShp
        ' ...
        ' your code to act on referenced shape
        '...
    End With
End Sub

当然，采取双重办法也是可能的：

Function GetWidestShapeWidth(widestShp As Shape) As Long
    Dim shp As Shape
    Dim widestShpWidth As Long

    For Each shp In ActiveSheet.Shapes
      If shp.Width > widestShpWidth Then
           widestShpWidth = shp.Width
           Set widestShp = shp
      End If
    Next
End Function


Sub Main()
    Dim widestShp As Shape
    Dim widestShpWidth As Long

    widestShpWidth = GetWidestShapeWidth(widestShp) '<--| get the width of the widest shape along with the widest shape        
    With widestShp
        ' ...
        ' your code to act on referenced shape
        '...
    End With
End Sub

Answer 2

据猜测，这将是一个类似this....Note的东西，我现在还没有权限来测试它。

Sub GetWidestShape()
        dim widest
        dim i as int;
        i=0

        for each shape in activesheet.shapes
          if(shape.width > widest.width or i =0) then
               widest = shape
          end if
          i++
        next
        'Do whatever you want with the shape.
        'you should be able to refrence the shape with the variable/object name "widest"
        'E.g range("a1").value=widest.width
end sub