Sleeper: Charting Hell

Any of you who have tried to format a chart using VBA will know what a pain in the ass it is.

But do any of you know why
[
CODE]with FetchChart
.PlotArea.Height = 200
End With[/CODE]

sets .PlotArea.Height to whatever Excel feels like,

while

Code:

---

With Sheets(FetchChart.Parent.Parent.Name).ChartObjects(FetchChart.Parent.Name).Chart
.PlotArea.Height = 200
End With

---

sets it to almost the right value (+/- Excel's tweakings)?

ChartObjects are inherently parts of the worksheet. This means you must specify what worksheet they are on just as you would any other worksheet related object. The only difference being that you do not get the luxury as to assume you are dealing with the activesheet that we do with the other objects.

Why? I have no idea. I would think that it was an oversight by MS because they didn't put forth the effort that they did into everything else.

Yeah, it's a little strange. Most chart properties set fine with either method, but PlotArea seems to have issues. I'm having quite a few similar problems now that I'm tidying up my code (in Excel 2000) by replacing objects with references to the objects. Some of these new issues are just lack of updating (such as Chart.CopyAsPicture giving an outdated image) and can be fixed by inserting a Application.CalculateFull. Others, like the PlotArea just don't set correctly.

A related question: Is there a direct way of knowing Chart.ChartTitle.Height and Chart.Axes(xlCategory).AxisTitle.Height? I'm thinking that there isn't. It's a problem when changing font size in the titles and then optimizing the plot size. Right now, my 'fix' is to:

1. Set all the chart text objects to .AutoScaleFont = False
2. Set the ChartObject.Width and .Height to something tiny
3. Set the ChartObject.Width and .Height back to the correct size, so that Excel automatically reformats the whole thing.

It works, but it would be nice to know the actual height values.

Here is some code to determine the chart titles width and heigth.
The technique can be used with any of the chart label items.
There is also code to adjust the plot area. This works as expected unless there is not enough room in the chartarea to achieve the specified height.

Code:

---

Sub X()
Dim sngHeight As Single
    Dim sngTitleLeft As Single
    Dim sngTitleTop As Single
    Dim sngTitleWidth As Single
    Dim sngTitleHeight As Single
sngHeight = 200
    With ActiveChart
        MsgBox "Current plotarea height is " & .PlotArea.Height
        .PlotArea.Height = sngHeight
        MsgBox "and now plotarea height is " & .PlotArea.Height
If .HasTitle Then
            sngTitleLeft = .ChartTitle.Left
            sngTitleTop = .ChartTitle.Top
            .ChartTitle.Left = .ChartArea.Width
            sngTitleWidth = .ChartArea.Width - .ChartTitle.Left
            .ChartTitle.Top = .ChartArea.Height
            sngTitleHeight = .ChartArea.Height - .ChartTitle.Top
            ' re position title
            .ChartTitle.Left = sngTitleLeft
            .ChartTitle.Top = sngTitleTop
            MsgBox "Chart Title Width = " & sngTitleWidth & Chr(10) _
                & "Chart Title Height = " & sngTitleHeight
        End If
    End With
End Sub

---

Thanks, Andy. I'll have to check that out later when I have a bit more time. Cheers!