CODEWITHSUNDEEP
excelvba
Chewjunnie
Chewjunnie

Reputation: 137

Confusion over activechart and series collection

i wanted to plot a chart. I have researched from the internet and saw different methods to plot a graph Some declare myChart as ChartObject, Shape, Chart. What is the difference between them?

Some uses codes such as

With myChart
        .Chart.SetSourceData Source:=Range("$E$3:$E$40")
End With

ActiveSheet.ChartObjects(1).Activate
With ActiveSheet.ChartObjects(1).Chart

With .SeriesCollection(1)
           .XValues = ARng
           .Values = BRng
End With

Whats the difference between these 3 methods? I wanted to clarify my doubts as i keep receiving errors while doing vba codes for creating a graph.

Upvotes: 0

Views: 340

Answers (1)

Tim Williams
Tim Williams

Reputation: 166790

A ChartObject is a type of Shape which serves as a "container" for a Chart when the chart is sitting on a worksheet (and not on a Chart sheet). A ChartObject has a Chart property which returns the actual chart itself.

Conversely Chart.Parent is the ChartObject.

It's worth noting that it's not necessary to Activate a chart before working with it using VBA.

Set srsNew = .SeriesCollection.NewSeries
        With srsNew
            .XValues = ColumnARngData
            .Values = ColumnBRngData
            Set xAxes = .Axes(xlCategory, xlPrimary)  ' [error 438]

Your With block scope is the Series, not the Chart. A series has no .Axes property - you want the Chart for that.

If you want to loop over chart objects in different sheets then:

Dim s, Cht As Chart

For Each s in array("Sheet1", "Sheet2", "Sheet3")
    Set Cht = Sheets(s).ChartObjects(1).Chart
    With Cht
         '...
         'configure Cht
         '...
    End with 
Next s

Upvotes: 1

Related Questions