View Full Version : Charting Hell

04-19-2005, 10:58 AM
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
with FetchChart
.PlotArea.Height = 200
End With
sets .PlotArea.Height to whatever Excel feels like,

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)?

Zack Barresse
04-19-2005, 11:37 AM
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.

04-19-2005, 12:39 PM
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.

Andy Pope
04-19-2005, 01:55 PM
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.
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

04-20-2005, 07:31 AM
Thanks, Andy. I'll have to check that out later when I have a bit more time. Cheers!