When does a Chart object become a ChartObject object

[Mister_joe]

Guys,
I am having problem differentiating between Chart object and ChartObject object. Please looking into the code shown below and oblige me with explanation.



Sub Macro1()
ActiveWorkbook.Worksheets("Raw Data").Activate
With ActiveSheet.Shapes.AddChart.Select
With ActiveChart
.ChartType = xlXYScatterSmoothNoMarkers
.HasAxis(xlValue, xlPrimary) = True
.HasAxis(xlCategory, xlPrimary) = True
.SeriesCollection.NewSeries
.SeriesCollection(ActiveChart.SeriesCollection.Count).Name = "=""Whatever"""
.SeriesCollection(ActiveChart.SeriesCollection.Count).XValues = "='Sheet1'!$D$2:$D$133" 'x-axis
.SeriesCollection(ActiveChart.SeriesCollection.Count).Values = "='Sheet1'!$E$2:$E$133" 'y-axis

ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Select
With Selection.Format.Line
.Style = msoLineSingle
.Weight = 4
.Visible = msoTrue
.Transparency = 0
.ForeColor.RGB = RGB(255, 0, 0)
.DashStyle = msoLineSolid
End With


ActiveSheet.ChartObjects(ActiveSheet.ChartObjects.Count).Activate
With ActiveChart
.Width = 300 'The following four lines give error message that says "Method or Data Member Not Found"
.Left = 300
.Height = 300
.RoundedCorners = True

.HasTitle = True
.ChartTitle.Text = "Blah blah blah"
.ChartTitle.Select
With Selection
.Orientation = xlHorizontal
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
End With
End with
End Sub


I thought the command "ActiveSheet.ChartObjects(ActiveSheet.ChartObjects.Count).Activate" would return a ChartObject object. That doesn't seem to be the case. My questions are:-
1. Does the ChartObjects collection contain objects that are not necessarily ChartObject objects?
2. When does a Chart object become a ChartObject object?


Thanks.

[Kenneth Hobs]

Why do you think that is not the case? Intellisense would tell you there is no Width property. Recording a macro will give the syntax if needed. There is a difference between chart and plot area.


ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart 1").ScaleWidth 1.0760456274, msoFalse, _
msoScaleFromTopLeft
ActiveSheet.Shapes("Chart 1").ScaleHeight 1.1590214067, msoFalse, _
msoScaleFromTopLeft
ActiveChart.PlotArea.Select
Selection.Width = 356.428
Selection.Height = 227.532

Try selecting the chart manually and then in the Immediate Window of the VBE, type: ActiveChart.
The period would open the intellisense options.

[Mister_joe]

Thank you Sir. What does the ChartObjects collection contain - Chart objects or ChartObject objects? In other words, what does a command such as ActiveSheet.ChartObjects(ActiveSheet.ChartObjects.Count) return? I thought it would return a ChartObject object and a ChartObject object ought to have the .Width, .Height, .Left property. I am really confused about this.

[Kenneth Hobs]

ActiveSheet.ChartObjects(ActiveSheet.ChartObjects.Count) would return one chart object from the collection of ChartObjects. Always use Option Explicit as the first line of code in a Module, Sheet object, etc. Be sure that Auto List Members is selected in your VBE menus Tools > Options.

In the VBE, you can use F2 to browse commands. Press F1 for a selected command or in the code editor, with cursor in or next to a command word or object, press F1 for detailed help. But as I said, the macro editor is your friend if you are not familiar with a command. Of course you can always use Microsoft to view references like chartobject and chartobjects. http://msdn.microsoft.com/en-us/library/office/ff846392%28v=office.14%29.aspx

[Mister_joe]

Again, thank you. How does one get a ChartObject object? That is, which collection would return a ChartObject object?

Talking of recording macros, I have tried to record the steps of formatting a ChartArea or a PlotArea; like changing the background color, forecolor, pattern etc. The macro did not record any of the formatting steps. All I got was ActiveChart.Activate. Has anybody experienced this behavior or am I missing something?

[Aflatoon]

The ChartObjects collection contains ChartObjects and your code above returns a ChartObject, but you then refer to ActiveChart.

A ChartObject is the container for a Chart that is embedded in worksheets.

[Mister_joe]

Thank you Sir. The confusion is a bit clearer now. The "ActiveSheet.ChartObjects(ActiveSheet.ChartObjects.Count).Select" returns a ChartObject object, but the ActiveSheet.ChartObjects(ActiveSheet.ChartObjects.Count).Activate returns a Chart object. I am only wondering now why there is a ChartObject object instead of just having to deal with a Chart object.

Macro recording does not record the steps in formatting ChartArea or PlotArea. I am using Office 2007. Had anybody had this experience or is there another way to record the steps?

[Aflatoon]

Select and Activate don't return anything at all.

The ChartObject is essentially a containing shape so you use that to change the dimensions/location of the chart within the sheet. It would not make sense for a Chart to have these properties when it is located as a chart sheet.

The macro recorder is broken in 2007 and fails to record most steps taken with the new (at the time) chart model.

[Mister_joe]

Apology for using the word "returns". It is just the way I see it in my mind's eyes. Your explanation makes the issue even clearer.

Regards.