Bar Chart Label Sizes

[bobschroeder]

I am dynamically populating multiple Horizontal Bar Charts with Data. I
want the Labels (y axes) and chart area (Bar Charts) to be the same on
each chart. When I dynamically set the data the labels and charts
resize themselves based on the width of the data.

Is there a way through code VBA, C#, etc that I can set the width of
the Graphic (the bar charts --no labels) independently of the width of
the labels (the y axis). I haved tried xlChart.Chart.PlotArea.Width but
it appears to be the width of both of these areas together? Any other
thoughts.

Thanks In Advance

 

[Jon Peltier]

Bob -

You've touched on one of the frustrating aspects of Excel charting. You cannot get
these to exactly match up, but you can get close. First let's clear up some terminology.

The Chart Area is the outermost rectangle of a chart. It is the part that is
included in a chart object which is embedded in a worksheet, or that includes the
chart in a chart sheet. The Plot Area includes the charting rectangle and all of the
axis features (ticks and tick labels). The Plot Inside Area comprises the rectangle
which is defined by the axes and includes all of the plotted points.

In VBA (or C#, whatever), the Plot Area dimensions are read-write, the Plot Inside
Area dimensions are read-only. But you want to control the Plot Inside Area, so you
have to tweak the Plot Area until the Plot Inside Area comes up right (or within a
pixel).

The trick is to make the Plot Area smaller than you need (make its .Width and
..Height like half the size you need), set the Plot Area's .Left and .Top so the Plot
Inside Area is where you want it, then set the Plot Area size so the Inside Area is
what you need. Make sure there is room for the Plot Area to expand to include longer
labels, or Excel will change the Inside Area to fit.

This zipped workbook contains code to standardize the Plot Inside Areas of charts
embedded in a worksheet. Sometimes it is necessary to run the code twice.

http://peltiertech.com/Excel/Zips/AlignChartDimensions.zip

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

 

[bobschroeder]

Thanks Jon!!