After a lot of trial and error I came up with a solution for
reformatting a chart such that the inside dimensions are exactly the
ones you defined, e.g. a square of 70 x 70 mm, and no unecessary white
space around it.

For the cases I needed (only XY-scatter) it worked fine but do not be
surprised if miraculous things happen when you try it on your own
charts; a lot of surprising phenomena, or hidden added intelligence if
you like, in the charting world!

I end up with some 530 lines of VBA; too long for this message. To get
an impression of what you may expect I added the comments of the module
below. Please mail me for getting an example sheet containing the code.
Replace xyz with jwe in the published address.

Question:
What am I to do to make it a tool that is available also to other
workbooks like AddIns for functions? (I have only experience (quite a
lot actually) with writing AddIns in PASCAL into an xll library.)

Thank you for the suggestions in this news group.
Janwillem

P.S. I am from the ALGOL-60 days and have ever since made an effort of
not using BASIC. I would therefore not be surprised when some of you can
improve considerably on the coding.

'Macros to resize a chart such that the
'1) "PlotArea proper" has exactly defined dimensions
'2) The chart area tightly fits the plot and text elements of the plot

'Janwillem van Dijk
'August 2005
'email: jwe dot van dot dijk at hccnet dot nl

'Defines:
'Sub SetInsidePlotArea()
'Sub MoveToBottomLeft()
'Sub FitChartAreaToPlot()
'Sub ShowPlotDimentions()
'Sub SaveAsTiff()

'Suggested way of doing things:
'1) Create the plot as an embedded chart as usual
'2) Make the ChartArea sufficiently large
'3) Add all optional elements to the plot
'4) Do all the formatting that affect sizes such as the placing of legends
' and choosing fonts and font sizes
'5) Call SetInsidePlotArea entering the width and height in cm as an array
' e.g. {7,7} for a plot with a square inside plot area of 7 cm (198 X
198 pt)
'6) Place all additional element at the correct position
' text boxes might sometimes need resizing
'7) Call MoveToBottomLeft to move all plot elements to the bottom left
corner of the chart area
' and check layout
'8) Call FitChartAreaToPlot to reduce the size of the ChartArea such that it
' nicely fits the plot.
'The plot is now shaped such that it can be "Edit/Copy" and
' "Edit/Paste Special/As Picture (Enhanced Metafile)" into Word or
Powerpoint
' having the intended dimensions and without unnecessary "white"
around the figure

'Charts with TextBoxes that link to a formula instead of containing
literal text
' tend to pose problems which can be dealt with by afterwards slightly
enlarging
' the chart area and fine tuning the positions of the TextBoxes and than
' re-applying SetInsidePlotArea() without FitChartAreaToPlot()

'Printing from Excel results in sligtly wrong dimensions and aspect ratio's
'Copy and paste as EMF into PowerPoint or Word gives almost exact results,
' as GDI metafile in OpenOffice gives a larger picture but aspect
ratio is preserved

'Partly based on suggestions made on microsoft.public.excel.charting
' on my question "square chart insidewidth insideheight" 30 July 2005
'This in particular the suggestion to use
' ExecuteExcel4Macro("FORMAT.SIZE(" & w & "," & h & ")")
' to set the dimensions of the inside PlotArea