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