vba chart xlValue axis title truncated, one series name omitted

[chet]

In Excel2003 under WXPSP2, a sequence of procedures computes frequency
and cumulative distributions and relative frequency and relative
cumulative distributions for each of two treatments for each of ten
variables. Two previously created custom chart types (one line with
markers and one 3D column, each displaying the two series for the two
treatments for the given variable) are invoked in the chart generating
procedure and various properties defined for charting the relative
frequency and relative cumulative distributions. Two problems have
arisen. (1) Either the last half or last 10% of the final letter of
the xlValue axis title is missing. How can I force ExcelVBA to show
the whole title without reducing the font size? There seems to be
plenty of room on the chart to fill out that final letter. (2) The
second series label is missing from all the 3D column charts for which
there are "many" (e. g., 50 or more) categories; but when there are
only a "few" categories (i. e., 11), the columns are big enough that
the depth axis for the series names will show both series names on the
chart. How can I force ExcelVBA to place the second series name (only
5 letters long) next to the first series name (also only 5 letters
long) for all the other 3D column charts? Thank you for any help or
suggestions.

 

[Barb Reinhardt]

The Y axis issue is a well known problem and I've seen microsoft bulletins on
it, but can't find them now. One workaround is to add a couple of spaces
to the end of the Y axis label and format those to the same color as the
background of the chart.

For the 3D column series issue, you can format the series for the 3d part
and change the scale for # of series between tick marks from 2 to 1. I'm
not sure how to do this in VBA, but I usually just record that as I do it and
then clean up the code as necessary.

HTH,
Barb Reinhardt

 

[chet]

Thank you, Barb, for your suggestions. They got me started on the
solution. This response is a little tardy because it took a while to
follow all the procedures. In case anyone searches for discussion of
these same problems, I'll report on my solutions.

(1) For the truncated xlValue axis titles, padding the title with one
or two trailing blank spaces did not work: Excel ignored the blanks
and still cut off part of the final letter. Adding a period at the
end of the title worked for about half the charts, but for the other
half, the period was superimposed on the final letter. The solution
was close to what you suggested: adding a trailing blank space and a
period, then specifying in the VBA code .Characters(<titlelength-2>,
2).Font.Color = vbWhite (where white is the chart background color)
eliminated this problem.

(2) For the formating of 3D column charts, several changes were
necessary. The first step was, as you suggested, changing the number
of series between tick marks to 1. This showed both series names but
placed them too close to or overlapping each other. The spacing of
the columns is controlled by the Format Data Series > Options >
GapDepth, GapWidth, and ChartDepth options, with all three of which I
had to experiment to find the values which worked best for the data in
my case and which were different from the Excel default values.
Finally, between the two data series, I also added a blank series by
setting a column range of blank cells and specifying
.DisplayBlanksAs = xlNotPlotted
.Name = " "
.Legend.LegendEntries(2).Delete
This gave good spacing between the data series columns and the series
axis labels (i. e., the series names) and removed any explicit
indication in the chart, among the series axis labels, and in the
legend of the blank series used for spacing.

I also relearned the lesson of your last suggestion: I rebuilt the
custom chart types while recording a macro, making sure to specify
every feature and option. This gave me the necessary VBA code to
create and manipulate charts in detail.

Thank you for your response to my query.

chet

 

[chet]

Oops. That first line of code for the first problem should read

.Characters(<titlelength-1>, 2).Font.Color = vbWhite

As erroneously specified, it would just recreate my original problem
through a different mechanism.

chet

 

[Jon Peltier]

1. Instead of a period as the last character, use a non-breaking space
(ascii character 0160), which needs no font color adjustment.

2. You should try very hard to find a 2D chart that shows what you want. 3D
charts are more complicated to build, and they present the data in a way
which most readers find more difficult to understand.

- Jon