Dynamic Charting (StackedBar)

[Dale Fye]

I've been working on this for a couple of days now, so bear with me. I've
got some data (linked from an Access query) that looks like:

Mission Day Sorties
A 1 9
A 2 7
A 3 5
B 1 10
B 2 11
B 3 19
C 1 5
C 2 16
C 3 12

The types of missions vary, as do the number of day (although for a
particular query, the number of day will be constant across each of the
missions). What I want to do is dynamically format the data series of a
stacked bar chart so that each of the Missions is a different data series,
the days are the X-Axis Values, and the Sorties reflect the height of the
bars in the stacked bar chart.

I'm relatively new to Excel VBA, but have been using Access for about 10
years. I'm just not familiar with the Excel object model. The first
problem I am having is actually selecting the chart so that I actually have
an "ActiveChart" object. Can anyone help me out with that step?

Once I have selected a chart, I want to clear out it's SeriesCollection;
figured out a way to do that one series at a time but would be interested in
knowing if that can be done in a single step.

Next, I assume I am going to have to do some looping to identify the start
and end point of each series (mission), but should probably start out by
clearing out the Series collections. Is there an easy way within Excel to
identify the row that contains the next value. For example, Mission type
"A" starts on Row 2, and mission type "B" starts on row 5. Is there a quick
way to identify Row 5 without looping through each row and testing the value
of the first cell in each row against some preset value? In most cases, my
dataset will contain around a thousand rows (5-10 mission types)

I assume that once I define the start and end positions of each series, I'll
be able to use code similiar to the following to build the series.

ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R2C2:R4C2"
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R2C3:R4C3"
ActiveChart.SeriesCollection(1).Name = "=Sheet1!R2C1"

Any help would be greatly appreciated

 

[Jon Peltier]

Do you need to do it in VBA? You could make a pivot table in Excel which
will produce a table with Days 1, 2, 3 down the first column and Missions A,
B, and C across the top row. This is ideally suited to creating a chart,
either a pivot chart or a regular chart:

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=553

If you do want to use VBA, don't be afraid of looping. To clear out unwanted
series:

Sub RemoveUnwantedSeries()
With ActiveChart
Do Until .SeriesCollection.Count = 0
.SeriesCollection(1).Delete
Loop
End With
End Sub
(excerpted from http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html,
which has a lot of other hints too).
To find the series, you start at the first row of mission data (row i1),
read what the mission is, and then read each row after that until you get to
a different value. This is row i2, so your series is defined as going from
i1 to i2-1. Build a string in R1C1 notation and use the code you posted.
Then set the new value of i1 equal to i2, and continue down your data.

While the looping may seem tedious, it's pretty fast. On a recent project I
used this system to create about 30 charts from a 1200 row worksheet.
Including adding a worksheet for each chart, putting some labels on the
sheet, creating the chart, and formatting it, the program build 30 charts in
under 5 seconds.

- Jon

 

[Dave Patrick]

When I've needed to do this I rotate the table (crosstab query) in Access
before using the TransferSpreadsheet Method.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect