Excel VBA: Index = ZOrderPosition in Shapes Collection?

Question

Is the shape shape index in the worksheet sheet shape always the same as its ZOrderPosition? (In principle, it is impossible to directly learn about the Index of this Form).

I confirmed that this is true in a few cases (from up to 3000 Shapes), but I did not find any documentation about this.

I went through the entire collection, asking a question about the 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 

The functions used for the request are shown below. Since the warning in MsgBox never slipped, it means that Index = ZOrderPosition, for the evaluated cases.

 ' 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, ' ie, 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? Ie, 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: I adapted the functions for the ChartObjects collection of the worksheet, and also checked the equivalence of Index = ZOrder.

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

Edit : see the section "Editing in Excel VBA: how to get a link to a shape from ChartObject .

Answer 1

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

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

Here is the code to get the previous information:

 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 

Hope this clarifies the question for you!

Regards, Andres

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