Edit: Problem solved.
Links to a resource explaining default margin settings and to a complete list of possible settings in a chart (stuff like
.ChartArea.Width, .PlotArea.Left and .PlotArea.InsideLeft) still very much appreciated!
Here I am with another
thing that bugs me. I am obviously unable to find a good source where I can find information how to correctly set margins in
a graph and how to position objects.
The short version:
Are there any margin settings in a chart area that overrule settings like .AxisTitle.Left, .ChartArea.Width and
Is there any good resource where e.g. differences between .PlotArea.Left and .PlotArea.InsideLeft are explained?
The long version:
Currently, I do position my x-axis labels like this:
gap = 10 'set the gap between axis title and tick labels
caw = ActiveChart.ChartArea.Width
cah = ActiveChart.ChartArea.Heigth
pail = ActiveChart.PlotArea.InsideLeft
paiw = ActiveChart.PlotArea.InsideWidth
.AxisTitle.Left = caw 'pushes axis title to rightmost position possible; since Excel forbids that the axis title
sticks out of the chart area, .AxisTitle.Left actually gets the value of the chart area width minus the length of the axis
.AxisTitle.Left = pail + paiw / 2 - (caw - .AxisTitle.Left) / 2 'centers the position of the axis title in regard
to the x-axis.
.AxisTitle.Top = cah 'push axis title down to lowest possible position
pah_new = .AxisTitle.Top - (gap + pat)
ActiveChart.PlotArea.InsideHeight = pah_new 'set the heigth of the plot area so that the gap between tick labels and
axis title is as set above
Credits for that go partly to this link. So, this seems to work fine as intended. However, setting the value for the variable
"gap" does not match the actual gap between the tick labels and the axis title. Setting gap to zero leads to an
overlap between ticks and title. Even a value of 15 produces the same result. Only when further increasing the value it gets
formatted as I want it.
As a result, I can not really control the outcome of my script the way I want it.
Even worse, what I ultimately want to achieve is that the spacing between the y-axis title and tick labels should match the
space between tick labels and axis title on the x-axis. This of course requires knowing the actual distance between axis
title and ticks.
I tried it with:
.AxisTitle.Left = caw
ath = caw - .AxisTitle.Left 'height of the (rotated) axis title
.AxisTitle.Left = 0
pal_new = ath + gap
ActiveChart.PlotArea.Left = pal_new
I suspect there may be some margins in the chart area that cause problems. This is also because
as in the code above is obviously not the leftmost place for the axis title as
pushes it even more to the left (and it is still not touching the left border of the chart). Why?
So I read the
values of ActiveChart.PageSetup.LeftMargin and ActiveChart.PageSetup.BottomMargin and found that they were different. If I
correct the value of gap by the double the difference of these values, it seems to deliver the correct result. There has to
be a better way to do this. Any ideas?