Chart Area

[M H]

Hi all,
How could I retrieve the width and height of the plotarea of activechart
sheet (i.e.ActiveChart.PlotArea.Width (or .height))? I need these
numbers in order to plot several charts with identical dimensions
together in one single chart sheet, and they have to equally placed
along the width and height of the plot area.
Much thanks.

 

[Robin Hammond]

Maurice,

I've never managed to do this absolutely perfectly thanks to the lack of
exactitude in the excel chart engine, but this gets very close. It assumes
you have a chart sheet set up with an existing chart on it, and have just
added another chart to the chart sheet.

If anyone can improve on this, please let me know directly. Thanks.

Sub AlignSubChartOnParent(chParent As Variant, chSubChart As Variant, _
Optional dSubHeight As Double)

'chParent is the chartsheet
'chSubChart is the sub chart
'aligns the sub chart underneath all the other charts on the page
'assumes no x-axis labels on the pre-existing charts
'kludges various items to make things work

Dim dPTotalWidth As Double
Dim dPCatHeight As Double
Dim dPCatWidth As Double
Dim dPValueLeft As Double
Dim lCounter As Long
Dim dAdjust As Double
Dim lVersion As Long
Dim shStart As Worksheet

Set shStart = ActiveSheet
lVersion = Val(Application.Version)

dAdjust = 0

If lVersion < 9 Then

chParent.Select
chSubChart.Parent.Select
chSubChart.Parent.Activate
chSubChart.PlotArea.Select

End If

'get parent dimensions
With chParent

dPTotalWidth = .ChartArea.Width
dPCatHeight = .Axes(xlValue).Top + .Axes(xlValue).Height
dPCatWidth = .Axes(xlCategory).Width
dPValueLeft = .Axes(xlValue).Left

End With

'get total height of all pre-existing charts on the sheet
For lCounter = 1 To chParent.ChartObjects.Count - 1
With chParent.ChartObjects(lCounter).Chart
dPCatHeight = dPCatHeight + .Axes(xlValue).Height
End With
Next lCounter

With chSubChart
.Parent.Left = 0
.Parent.Width = dPTotalWidth
If dSubHeight <> 0 Then .Parent.Height = dSubHeight
dSubHeight = .Parent.Height

'set the plot area narrower than necessary then expand it later
With .PlotArea
.Top = 0
.Width = dPTotalWidth - 150
.Height = dSubHeight
End With

'try and get the left edges to line up
.PlotArea.Left = .PlotArea.Left + dPValueLeft - _
.Axes(xlValue).Left - .ChartArea.Left - dAdjust
'readjust the width
.PlotArea.Width = .PlotArea.Width + dPCatWidth - .Axes(xlCategory).Width
+ dAdjust

'readjust the left edge
.PlotArea.Left = .PlotArea.Left + dPValueLeft - .Axes(xlValue).Left -
..ChartArea.Left
.Parent.Top = dPCatHeight - .Axes(xlValue).Top - .ChartArea.Top - 0.5
End With
shStart.Select
End Sub

Robin Hammond
www.enhanceddatasystems.com

 

[M H]

Hi Robin,
Thanks for the code, but I couldn't run it when I pasted them into VBE.
It simply didn't show up in the macro list, even after corrected a
couple of typing errors.
Nice to meet you. BTW, are you also in HK?
Maurice

 

[Robin Hammond]

Maurice,

try checking for messed up line breaks. Other than removing my error
handlers this is pasted as is from working code.
you too.
yes.

Robin Hammond
www.enhanceddatasystems.com

 

[Jon Peltier]

It won't show up in the macro list, because it requires parameters to be
passed, this stuff:

(chParent As Variant, chSubChart As Variant, _
Optional dSubHeight As Double)

Call it from within another sub or from the Immediate window, providing
values for the parameters.

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

 

[M H]

Thanks Jon. Does it mean something like:

Sub Test()
Call AlignSubChartOnParent("Chart1","Chart2",[55])
End Sub

I must have to read more from your website! It looks marvellous!

rgds,
Maurice

 

[Jon Peltier]

Maurice -

Actually, the first entry must be a sheet object and the second a
chartobject object. It would be something like this (sorry, I can't wade
through the proposed code, it would take too many minutes):

Sub Test()
Call AlignSubChartOnParent(Sheets("Chart1"), _
Sheets("Chart1").chartobjects("Chart2"), 55)
End Sub

or

Sub Test()
AlignSubChartOnParent Sheets("Chart1"), _
Sheets("Chart1").chartobjects("Chart2"), 55
End Sub

It slipped my mind earlier, but I have a file you could download from my
website that handles alignment of charts:

http://peltiertech.com/Excel/Charts/chartvba.html#AlignChtDims

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