Excel VBA：Index = ZOrderPosition在Shapes集合中？ [英] Excel VBA: Index = ZOrderPosition in a Shapes collection?

问题描述

工作表的形状集合中的形状索引是否与ZOrderPosition始终相同？ （原则上不能直接查询给定形状的索引）。

Is the Index of a Shape in a Shapes collection of a Worksheet always the same as its ZOrderPosition? (One cannot in principle inquire directly about the Index of a given Shape).

我已经验证了这种情况在几种情况下（最多3000个形状），但是我没有发现任何文件。

I have verified this is true in a few cases (with up to 3000 Shapes), but I found no documentation on this.

我已经遍历了整个集合，询问Index和ZOrderPosition之间可能的区别：

I have traversed the whole collection, inquiring about possible differences between Index and ZOrderPosition:

Sub dump_shapes()
' Dump information on all shapes in a Shapes collection
    Dim shc As Shapes
    Set shc = ActiveSheet.Shapes
    Dim shp As Shape
    For Each shp In shc
      Dim sh2 As Shape
      Set sh2 = sh2idxzosh_shc(shp)
      Dim zoidx As Long
      ' The second argument is not actually the Index, but since we are traversing the 
      ' whole collection, and Index and ZOrderPosition are at most permutations, we are
      ' covering all of the possible Indexes.
      zoidx = idx2zo_shc(shc, shp.ZOrderPosition)
    Next shp
End Sub

用于查询的功能如下所示。由于MsgBox的警告从未弹出，也就是说，Index = ZOrderPosition，用于评估的情况。

The functions used for inquiring are shown below. Since the warning in the MsgBox'es never popped, that means that Index=ZOrderPosition, for the cases evaluated.

' Functions between the set of shapes S and the set of natural numbers N.
' O=ZOrderPosition : S -> N  (function exists)
' D=From Index : N -> S  (function exists)
' D^-1=Index : S -> N  (function does not exist)
' f=OoD : N -> N  (can be constructed; this is expected to be only a permutation, 
'  i.e., bijective)
' g=DoO : S -> S  (can be constructed)

Function sh2idxzosh_shc(ByRef sh As Shape) As Shape
    Dim shc As Shapes
    Set shc = sh.Parent.Shapes
    Dim zo As Long
    zo = sh.ZOrderPosition
    Dim sh2 As Shape
    Set sh2 = shc(zo)
    ' g=DoO : S -> S
    ' Test Shape : g(S)=S for all s? If so, g=DoO=I ; D=O^-1 ; D^-1=O. Thus, the Index 
    '  is equal to the ZOrderPosition.
    ' Use ZOrderPosition to test Shape : O(g(s))=O(s) for all s? I.e., OoDoO=O? If so, 
    '  given that O is bijective, OoD=I ; D=O^-1 ; D^-1=O. Thus, the index is equal to 
    '  the ZOrderPosition.
    Dim zo2 As Long
    zo2 = sh2.ZOrderPosition
    If (zo2 <> zo) Then
      MsgBox ("Compound ZOrderPosition: " & zo2 & ", ZOrderPosition: " & zo)
    End If
    Set sh2idxzosh_shc = sh2
    'Set sh2 = Nothing
End Function

Function idx2zo_shc(ByRef shc As Shapes, idx As Long) As Integer
    Dim sh As Shape
    Set sh = shc(idx)
    Dim zo As Long
    zo = sh.ZOrderPosition
    ' f=OoD : N -> N
    ' Test index : f(i)=i for all i? If so, f=OoD=I ; D=O^-1 ; D^-1=O. Thus, the Index is 
    '  equal to the ZOrderPosition.
    If (zo <> idx) Then
      MsgBox ("Index: " & idx & ", ZOrderPosition: " & zo)
    End If
    idx2zo_shc = zo
End Function

PS：我已经调整了工作表的ChartObjects集合的函数，并且等价指数= ZOrder也被验证。

PS: I have adapted the functions for the ChartObjects collection of a Worksheet, and the equivalence Index=ZOrder was also verified.

PS2：可以询问这是否是任何集合的典型（甚至保证）。在 Excel VBA：在Shapes集合中的ZOrderPosition的非连续编号我报告了一个情况，不仅这不是真的，而且Index和ZOrderPosition并不是排列（注意它是与ChartObject关联的Shape的Shapes集合，与上面提到的不同）。

PS2: One may ask if this is typical of (or even guaranteed for) any collection. In Excel VBA: non sequential numbering of ZOrderPosition in Shapes collection I reported a case where, not only this is not true, but Index and ZOrderPosition are not even permutations (note that it was the Shapes collection of the Shape associated with a ChartObject, a case different from that mentioned above).

编辑：请参阅 Excel VBA：如何从ChartObject获取对Shape的引用。

推荐答案

对于Excel表格的形状，规则ZOrderPosition = index是** NOT **如果您创建一组形状，则为true 。例如，如果您在Zorder 1至6中具有A，B，C，D，E，F的形状，然后将B，C，D一起，你会打破形状E和F的ZOrderPosition =索引规则。如果列出形状（i），您将得到以下内容：

The rule "ZOrderPosition = index" for the shapes of an excel sheet is ** NOT ** true if you make a group of shapes. For example if you have the shapes "A","B","C","D,"E","F" in Zorder 1 to 6 and then you group shapes "B", "C", "D" together, you will break the "ZOrderPosition = index" rule for the shapes "E" and "F". If you list the shapes(i) you will get the following:

index  ZOrder  Name 
 1       1      "A"        
 2       2      "Group 1"   
 3      *6*     "E"        
 4      *7*     "F"        

信息：

Sub DebugListInfoOfShapes()
    Dim i As Long
    Debug.Print "Index   ZOrder   Name"
    For i = 1 To ActiveSheet.Shapes.Count
        Debug.Print i & "         " _
                      & ActiveSheet.Shapes(i).ZOrderPosition _
                      & "      " _
                      & ActiveSheet.Shapes(i).Name
    Next i
End Sub

希望澄清你的问题！

问候，Andres

PD：如果你想看形状B，C，D，您必须使用例如： ActiveSheet.Shapes（2）.GroupItems（i）

PD: If you want to see shapes "B", "C", "D" you have to loop inside the group using for example: ActiveSheet.Shapes(2).GroupItems(i) for the case above.

这篇关于Excel VBA：Index = ZOrderPosition在Shapes集合中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！