excel: get index of current chart

Question

I create a new chart with

Sheets("DatenFilledChart").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlArea
ActiveChart.SetSourceData Source:=Range("DatenFilledChart!$B$4:$B$1004")
ActiveChart.SeriesCollection(1).XValues = "=DatenFilledChart!$A$4:$A$1004"
ActiveChart.SeriesCollection(1).Name = "=DatenFilledChart!$B$1"

In order to work with the current chart I want to save its index with

indexOfChart = ActiveChart.Index

That however fails with

The method Index is invalid for a object _Chart

What am I doing wrong?

Solution 1

Try to use this:

indexOfChart = Shapes.Count

instead of

indexOfChart = ActiveChart.Index

Index property is not applicable for Charts collection:

Index Property

The position of a Tab object within a Tabs collection or a Page object in a Pages collection.

Read more: http://msdn.microsoft.com/en-us/library/office/ff194426.aspx

Solution 2

Index is a property of the ChartObject class.

Chart is a member of the ChartObject class.

Therefore, when you use a Chart object, you have to go up a level in the heirarchy like so:

indexOfChart = ActiveChart.Parent.Index

Solution 3

Since AddChart returns a reference to the added object, the easiest way is just to work with that reference...

Sub tester()
    Dim co As Shape

    Set co = Sheet1.Shapes.AddChart()
    With co.Chart
        .ChartType = xlArea
        .SetSourceData Source:=Range("Sheet1!$A$1:$B$5")
        .SeriesCollection(1).Name = "Testing"
        'etc etc
    End With
End Sub

Comments

[Matthias Pospiech]

I tried your code with the enhancement of setting Sheet1 to a variable: ` Dim ws As Worksheet Set ws = Sheets("DatenFilledChart") ShapeRef = ws.Shapes.AddChart()` but that fails with error 91: variable not defined.

[Tim Williams]

Make sure you have declared ShapeRef, and use Set when you assign it.