chartgroups,chartobjects etc-confusion to me

R

R.VENKATARAMAN

I am fairly familiar with excel charting and also vba. But I am still
confused with the terms like

chartgroups
chartgroup
chartobjects
chartobject
charts
chart,
seires.

Let me see how I have understood or misunderstood the various terms

1.SERIES denote the various charts in a chartsheet or worksheet(embedded)

2.if in a "chartsheet" I have three line charts and four column charts that
means I have two CHARTGROUPS one group of linecharts and second group of
column charts

3. now how to go one step down is it chart or series or chartgroups

4. CHARTOBJECTS are embedded charts in a worksheet.
If I have two embedded charts in the active worksheet one line chart and one
column chart (or even if I have two same type chars)

?activesheet.chartobjects.count(in the immediate window) gives 2. but if I
write
?activeshet.chartgroups.count gives error message
but the code
MsgBox Worksheets(1).ChartObjects(1).Chart.ChartGroups.Count(as in help
file)
gives 1 even though I have one line chart and one column chart. Besides why
does that term"chart" comes in.
my perhaps faulty logic says that in the active worksheet
there are 2 CHARTOBJECTS
there are two CHARTS
there are two CHARTGROUPS one linechart and one column chart
so the above msgbox should give 2 and not 1.
?activesheet.Charts.count also gives error

do you understand my confusion.

The help in excel is of not much help atleast to me- not even their
hierarchical diagrams . will anybody please direct me to any webpage so
that my mind is clarified about how and when these terms can be used.
 
J

Jon Peltier

The object model is daunting at first, but does make sense.

SERIES: Do not think of a single series within a chart as a "chart". A
series is simply a set of data points plotted as a unit: a string of
markers with or without connecting lines, or a set of columns or bars
with the same format (unless you've changed that).

Instead of this:
there are two CHARTGROUPS one linechart and one column chart think in terms of this:
there are two CHARTGROUPS one line SERIES and one column SERIES

CHART: This is the object with the series, the axes, the labels, and all
that. It can be a Chart Sheet, which is also called a chart. It can be
embedded in another sheet (a chart sheet or worksheet), in which case,
there is a ChartObject that contains it.

I think this distinction is evolutionary in nature. When I first started
using Excel (the papyrus version), there were several different Excel
file types: MySeries.xls was a worksheet, MyChart.xlc was a chart, and
MyMacro.xlm was a macro sheet. You could save a workspace (maybe with
the extension .xlw?), which included xls, xlc, and xlm files. In a newer
version, all of these were included within the xls files, and the
chartobject was the mechanism they invented to embed the chart file into
the worksheet.

CHARTOBJECT: This is the container that holds an embedded chart in a
sheet. To refer to its chart from VBA, you need to refer to the chart
object, then the chart:

Worksheets(1).ChartObjects(1).Chart.SeriesCollection(1).Name

"activesheet.charts.count" gives an error, because the active sheet has
chartobjects: "activesheet.chartobjects.count" gives the answer you are
seeking.

CHARTGROUPS: These are sets of series within a chart. There is a unique
chartgroup for each type of chart, for each axisgroup. I don't know why
your Column-Line chart had 1 chartgroup: it should have had 2 (were you
referring to the right chartobject, and the right worksheet?). If you
have a line chart with series on both the primary and secondary axes,
you have two chartgroups.

"activesheet.chartgroups.count" is not recognized by VBA, because
chartgroups is not a member of a worksheet. You need to reference the
chart and chartobject within which there are chartgroups:
"activesheet.chartobjects(1).chart.chartgroups.count"

If the active sheet is a chart sheet (a chart),
"activesheet.chartgroups.count" makes sense.

If you think this is confusing, just try to identify the active slide in
a PowerPoint presentation. The VBA object model diagram looks like a map
of downtown Boston.

- Jon
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top