Why doesn't the code create a column stacked chart?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tskogstrom - 25 Oct 2006 15:17 GMT

Hi again...im sorry to ask for help so much, but charts vba is tricky
..

I try to create a column stacked chart of three bars containing each
three different values, much like Andy Pope's
http://andypope.info/charts/StackColTotal.htm

The CHT_21QSBA_S, CHT_21QSBA_B and CHT_21QSBA_A ranges are each three
different cells (name CHT_21QSBA_S = A1,B5,Q3) and I thought this
together with stating charttype=xlcolumnstacked should create a stacked
chart - but it make 9 bars instead...

Why?

...
Set cht = ActiveSheet.ChartObjects("R_Q_" &
Sheet1.Range("RAPP_TILLF").Value & "_21").Chart
With cht
.ChartType = xlColumnStacked
.SetSourceData Union(Sheet2.Range("CHT_21QSBA_S"),
Sheet2.Range("CHT_21QSBA_B"), Sheet2.Range("CHT_21QSBA_A"))
PlotBy = xlRows
.HasTitle = True
.ChartTitle.Characters.Text = chtTitle
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
'.ChartType = xlColumnStacked
End With

cht.SeriesCollection(1).Name = "Name1"
cht.SeriesCollection(2).Name = "Name2"
cht.SeriesCollection(3).Name = "Name3"
...
Please help,
Kind regards
tskogstrom

Reply to this Message

Jon Peltier - 25 Oct 2006 23:23 GMT

You get better results if you overrule the macro recorder and put ChartType
after SetSourceData.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

> Hi again...im sorry to ask for help so much, but charts vba is tricky
> ..
[quoted text clipped - 32 lines]
> Kind regards
> tskogstrom

Reply to this Message

tskogstrom - 26 Oct 2006 05:38 GMT

Hi Jon,
Still same problem after moved ChartType. Has a stacked column chart
one- or three seriescollections? I ask, because when I later in the
code try to name the SeriesCollection(2) I get runtime 1004 error ...

Is it maybe problem to setsourcedata to not only three union named
ranges that also are each defined three different cells? Like "union on
union"? But I thougt that was the way of defind setsourcerange to
stacked columns to tell it to know each bar should contain three
values?

How should you write the code to create a chart lik the on I describe?

Kind regards
tskogstrom

Jon Peltier skrev:

> You get better results if you overrule the macro recorder and put ChartType
> after SetSourceData.
[quoted text clipped - 42 lines]
> > Kind regards
> > tskogstrom

Reply to this Message

Jon Peltier - 26 Oct 2006 17:27 GMT

If the range doesn't contain valid data, some chart types will complain when
you access certain properties of the SeriesCollection object. Your Union may
just be asking for trouble. Also, sometimes Excel parses the range
differently than you'd expect or want when you use SetSourceData. I usually
skip SetSourceData; I add each series separately and define its component
parts independent of other series. See

http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html#addseries

However, I think your problem is more fundamental than that. Looking back on
your original post:

> (name CHT_21QSBA_S = A1,B5,Q3)

You're just begging for trouble. Excel needs nicely shaped ranges for chart
source data. You'd be better off with an unused worksheet range, say
AA1:AA3, with links in these cells to those you tried to associate using the
defined name. Then either assign the name to this range, or simply use the
range as is.

Then combine this nicer data arrangement with my series-by-series chart
building approach above, and explicitly tell Excel which range to use for
the name, values, and xvalues of each series.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

> Hi Jon,
> Still same problem after moved ChartType. Has a stacked column chart
[quoted text clipped - 61 lines]
>> > Kind regards
>> > tskogstrom

Reply to this Message