Excel VBA: How to obtain a reference to a Shape from the ChartObject

Question

I am trying to obtain a reference to a Shape in a Worksheet, corresponding to a ChartObject. I found no certain way of doing this. The only approximation, by trial-and-error and simply tested in a few cases, is assuming that the ZOrder of a ChartObject is the same as the Index of the corresponding Shape:

Function chobj2shape(ByRef cho As ChartObject) As Shape
' It appears that the ZOrder of a ChartObject is the same as the Index of
' the corresponding Shape, which in turn appears to be the same as its ZOrderPosition
    Dim zo As Long
    Dim ws As Worksheet
    Dim shc As Shapes
    Dim sh As Shape
    zo = cho.ZOrder
    Set ws = cho.Parent
    Set shc = ws.Shapes
    Set sh = shc.Item(zo)
    Set chobj2shape = sh
    'Set sh = Nothing
End Function

(a slight excess of defined variables is used for debugging purposes).

Is there any more certain way of doing this?

Any identifier used for picking the correct Shape should be unique. The name is not necessarily unique (see https://stackoverflow.com/questions/19153331/duplicated-excel-chart-has-the-same-name-name-as-the-original-instead-of-increm), so it is not guaranteed to work. The Index/ZOrderPosition is just a guess, at least satisfying the requirement of uniqueness.

Edit: see answer by @Andres in Excel VBA: Index = ZOrderPosition in a Shapes collection?. It is clear that the ZOrder of a ChartObject is not equal to the Index of either the ChartObject or the corresponding Shape (and I have verified this). But it appears that ZOrder is equal to ZOrderPosition of the corresponding Shape. This was verified with dump_chartobjects:

Sub dump_chartobjects()
' Dump information on all ChartObjects in a Worksheet.
    Dim coc As ChartObjects
    Set coc = ActiveSheet.ChartObjects
    Dim cho As ChartObject
    Dim ich As Long
    For ich = 1 To coc.Count
      Dim msg As String
      Set cho = coc(ich)
      With cho
        msg = "ChartObject '" & .name & "'" _
          & ", type name: " & TypeName(cho) & ", at: " & .TopLeftCell.Address _
          & ", index: " & ich & ", .Index: " & .Index _
          & ", ZOrder: " & .ZOrder
          '& ", hyperlink: " & .Hyperlink
      End With
      Debug.Print msg
      Dim ish As Long
      ish = choidx2shpidx(ich, coc.Parent)
    Next ich
End Sub

Function choidx2shpidx(coidx As Long, ws As Worksheet) As Long
    Dim cozo As Long
    Dim coc As ChartObjects
    Dim co As ChartObject
    Set coc = ws.ChartObjects
    Set co = coc(coidx)
    cozo = co.ZOrder
    choidx2shpidx = zo2idx_shp(cozo, ws)

    Dim con As String, shn As String
    Dim sh As Shape
    Set sh = ws.Shapes(choidx2shpidx)
    con = co.name
    shn = sh.name
    Dim cox As Double, coy As Double
    Dim cow As Double, coh As Double
    Dim shx As Double, shy As Double
    Dim shw As Double, shh As Double
    cox = co.Left
    coy = co.top
    cow = co.Width
    coh = co.Height
    shx = sh.Left
    shy = sh.top
    shw = sh.Width
    shh = sh.Height
    If ((con <> shn) Or (cox <> shx) Or (coy <> shy) Or (cow <> shw) Or (coh <> shh)) Then
      Dim msg As String
      msg = "ChartObject: '" & con & "', Shape: '" & shn & "'"
      'Debug.Print msg
      MsgBox msg
      choidx2shpidx = -1
    End If
End Function

Function zo2idx_shp(zo As Long, ws As Worksheet) As Long
    Dim ish As Long
    Dim shc As Shapes
    Dim sh As Shape
    Set shc = ws.Shapes
    For ish = 1 To shc.Count
      Set sh = shc(ish)
      If (sh.ZOrderPosition = zo) Then
        zo2idx_shp = ish
        Exit Function
      End If
    Next ish
    zo2idx_shp = -1
End Function

Answer 1

After losing hours in a similar issue, I found a couple of concepts related to referencing shapes in excel, but none satisfies me 100%. For accessing a shape you have 4 pure methods:

1. Shape.Name : Is FAST, but NOT RELIABLE. The name of the shape could be used to get a reference of a shape but provided you don't have duplicated names. Code: ActiveSheet.Shapes("Shape1")

2. Shape.ZOrderPosition : Very FAST, but NOT RELIABLE. The ZOrder of the shape could be used to get a reference of a shape, because is the same as the index of the shape in the shapes collection. But provided you don't have group of shapes that breaks previous rule (See: https://stackoverflow.com/a/19163848/2843348). Code: ActiveSheet.Shapes(ZOrderFromOneShape)

3. Set shpRef=Shape: FAST, RELIABLE, but NOT PERSISTENT. I try to use this always I can, specially when I create a new shape. Moreover, if I have to iterate on the new shapes later one I try to keep the object reference inside a collection. However not Persistent, that means if you stop and run you VBA code again to will loose all the references and collection. Code: Set shp = NewShape, or you can add it to a collection: coll.add NewShape for loop it later on.

4. Shape.ID : RELIABLE, PERSISTENT, but not directly supported! The ID of the shape is very reliable (don't change and cannot be duplicates IDs in a Sheet). However, there is no direct VBA function to get a shape back knowing its ID. The only way is to loop thorough all shapes until the ID match the ID you was looking for, but this can be very SLOW!.

Code:

Function FindShapeByID(ws as excel.worksheet, ID as long) as Excel.Shape
    dim i as long
    set FindShapeByID = nothing 'Not found...
    for i = 1 to ws.shapes.count
        if ws.shapes(i).ID = ID then
             set FindShapeByID = ws.shapes(i) 'Return the shape object
             exit function
        end if 
    next i
End Function

Note 1: If you want to access this function several times, you can improve it by using a cache of Shape IDs. That way you will make the loop only one time.
Note 2: If you move a shape from one sheet to other, the ID of the shape will change!

---

By mixing and using above knowledge, I have concluded in two main approaches:

FIRST APPROACH

• FASTEST BUT VOLATILE: (same as point#3) Try to keep the reference in a object as longer you can. When I have to iterate trough a bunch of shapes later on, I save the references inside a collection and I avoid to use other secondary reference like the name, ZOrder or ID.

For example:

dim col as new Collection
dim shp as Excel.Shape
'' <- Insert the code here, where you create your shape or chart
col.add shp1
'' <- Make other stuffs
for each shp in col
    '' <- make something with the shape in this loop!
next shp

The problem of course is that the collection and reference are not permanent. You will loose them when you stop and restart the vba code!

SECOND APPROACH

• PERSISTENT: My solution is to save the name and the ID of the shape for later reference. Why? Having the name I can access the shape very fast most of the time. Just in case I found a duplicated name I make the slow loop searching the ID. How can I know if there is a name duplicated? Very simple, just check the ID of the first name search, and if they don't match you have to suppose is duplicated.

Here the code:

Function findShapeByNameAndID(ws As Excel.Worksheet, name As String, ID As Long) As Shape
    Dim sh As Excel.Shape
    Set findShapeByNameAndID = Nothing 'Means not found
    On Error GoTo fastexit
    Set sh = ws.Shapes(name)
    'Now check if the ID matches
    If sh.ID = ID Then
        'Found! This should be the usual case!
        Set findShapeByNameAndID = sh
    Else
        'Ups, not the right shape. We ha to make a loop!
        Dim i As Long
        For i = 1 To ws.Shapes.Count
            If ws.Shapes(i).ID = ID Then
                'Found! This should be the usual case!
                Set findShapeByNameAndID = ws.Shapes(i)
            End If
        Next i
    End If
fastexit:
    Set sh = Nothing
End Function

Hope this helps you!

---

Note 1: Is you want to search shapes that maybe inside groups, then the function is more complicated.

Note 2: The ZOrder looks nice, but cannot find it useful. When I tried to take advantage of it, there was always a missing part...