Hi,
try this:

Sub Test()
Dim w As Double, h As Double, x As Double

x = Application.CentimetersToPoints(5)

If ActiveChart Is Nothing Then Exit Sub
If ActiveWindow.Type = xlChartInPlace Then ActiveChart.ShowWindow = True

ActiveChart.ChartArea.Select
ActiveChart.PlotArea.Select
ExecuteExcel4Macro "FORMAT.SIZE(" & x & "," & x & ")"
ExecuteExcel4Macro "FORMAT.SIZE(" & x & "," & x & ")"
ExecuteExcel4Macro "FORMAT.SIZE(" & x & "," & x & ")"

w = ExecuteExcel4Macro("GET.CHART.ITEM(1,5)-GET.CHART.ITEM(1,1)")
h = ExecuteExcel4Macro("GET.CHART.ITEM(2,1)-GET.CHART.ITEM(2,5)")
Debug.Print x, w, h, ActiveChart.PlotArea.InsideWidth, _
ActiveChart.PlotArea.InsideHeight

If ActiveWindow.Type = xlChartAsWindow Then ActiveWindow.Visible = False
End Sub

excel adjusts size of object automatically in a chart.
finally, the size might not become the expected.
in an embedded chart on a worksheet, size depends on the cells.
so, the size of object in the printed worksheet may be different than expected.
in a chart sheet, size seems to be correct.

i have experienced some weird results without 'ActiveWindow.ShowWindow = True'.
if no need, you can delete it.

--
HTH,

okaizawa



Janwillem van Dijk wrote:
How to gain control over dimensions in a chart such that the print
exactly as meant, e.g. with both the X and Y axes 5 cm (or 2" if you like)?

There has bean a thread in 1999 on making square charts that make a few
helpful suggestions which can still be found on google:
http://groups-beta.google.com/group/...effe414a793efd


Slightly adapted a VBA routine that makes the inside of the plot area a
square, looks like "SquareGraphArea" added below. Although it seems not
very elegant to set InsideWidth and InsideHeight this way it works! That
is on Excel 97 SR-2 it works but on Excel 2000 the behaviour is somewhat
unexpected (both under Win2k but on different computers).

The code <.Width = .Width + step increases with by something like 9 to
12 irrespective of the value of step. As can be checked by the macro
"XandYsteps" also added below.

The effect is that the inside area is most times almost a square
seldomly exactly. What is happening? Why this behaviour? How to get
better results?

Kind regards,
Janwillem

emailreplace xyz -- jwe


Sub SquareGraphArea()
'After Bill Koran
'http://groups-beta.google.com/group/microsoft.public.excel.charting/browse_frm/thread/3a14742eebec87b/9b9376ec334e803d?lnk=st&q=excel++plotarea+insidewi dth+koran&rnum=1&hl=en#9b9376ec334e803d

Dim eps As Double, step As Double

Application.ScreenUpdating = False
step = 1#
eps = 1#

With ActiveChart.PlotArea
If Abs(.InsideHeight - .InsideWidth) eps Then
.Height = .Width / 2
If .InsideHeight .InsideWidth Then
.Width = .Height / 2
Do While (.InsideHeight - .InsideWidth eps)
.Width = .Width + step
Loop
Else
.Height = .Width / 2
Do While (.InsideWidth - .InsideHeight eps)
.Height = .Height + step
Loop
End If
End If
End With
Application.ScreenUpdating = True

End Sub

Sub XandYsteps()
Dim Xstep As Double, Ystep As Double, step As Double
Dim line As String

step = InputBox("Step size")
Xstep = ActiveChart.PlotArea.Width
ActiveChart.PlotArea.Width = ActiveChart.PlotArea.Width + step
Xstep = ActiveChart.PlotArea.Width - Xstep

Ystep = ActiveChart.PlotArea.Height
ActiveChart.PlotArea.Height = ActiveChart.PlotArea.Height + step
Ystep = ActiveChart.PlotArea.Height - Ystep

line = "Step=" & step & " Xstep=" & Xstep & " Ystep=" & Ystep
MsgBox (line)
End Sub