title: Excel VBA: How to obtain a reference to a Shape from the ChartObject
body: <p>I am trying to obtain a reference to a <code>Shape</code> in a <code>Worksheet</code>, corresponding to a <code>ChartObject</code>. 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 <code>ZOrder</code> of a <code>ChartObject</code> is the same as the Index of the corresponding <code>Shape</code>:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>(a slight excess of defined variables is used for debugging purposes).</p>
  
  <p><strong>Is there any more certain way of doing this?</strong></p>
  
  <p>Any identifier used for picking the correct <code>Shape</code> should be unique. The name is not necessarily unique (see <a href="https://stackoverflow.com/questions/19153331/duplicated-excel-chart-has-the-same-name-name-as-the-original-instead-of-increm">https://stackoverflow.com/questions/19153331/duplicated-excel-chart-has-the-same-name-name-as-the-original-instead-of-increm</a>), so it is not guaranteed to work. The <code>Index</code>/<code>ZOrderPosition</code> is just a guess, at least satisfying the requirement of uniqueness.</p>
  
  <p><strong>Edit</strong>: see answer by @Andres in <a href="https://stackoverflow.com/questions/19159093/excel-vba-index-zorderposition-in-a-shapes-collection/19163848?iemail=1&amp;noredirect=1#19163848">Excel VBA: Index = ZOrderPosition in a Shapes collection?</a>. It is clear that the <code>ZOrder</code> of a <code>ChartObject</code> is not equal to the <code>Index</code> of either the <code>ChartObject</code> or the corresponding <code>Shape</code> (and I have verified this).
  But it appears that <code>ZOrder</code> is equal to <code>ZOrderPosition</code> of the corresponding <code>Shape</code>. This was verified with <code>dump_chartobjects</code>:</p>
  
  <pre><code>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 '" &amp; .name &amp; "'" _
            &amp; ", type name: " &amp; TypeName(cho) &amp; ", at: " &amp; .TopLeftCell.Address _
            &amp; ", index: " &amp; ich &amp; ", .Index: " &amp; .Index _
            &amp; ", ZOrder: " &amp; .ZOrder
            '&amp; ", hyperlink: " &amp; .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 &lt;&gt; shn) Or (cox &lt;&gt; shx) Or (coy &lt;&gt; shy) Or (cow &lt;&gt; shw) Or (coh &lt;&gt; shh)) Then
        Dim msg As String
        msg = "ChartObject: '" &amp; con &amp; "', Shape: '" &amp; shn &amp; "'"
        '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
  </code></pre>