square chart insidewidth insideheight

[Janwillem van Dijk]

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...quare+chart+vba&rnum=4&hl=en#92effe414a793efd

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...sidewidth+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

 

[keepITcool]

it's one of these annoying things which is a leftover from the old
days..

you CANT print a square square in excel.

all horizontal sizing in Excel( printing routines)
depends on the font in the "normal" style.
and roundings will interfere with perfect results..

Every shape (even a simple rectangle (perfectly square on screen)
will always shift a few pixels...

and will very widely when you change the standard font or
try it on a different printer.

I've tried and tried again..(for acceptgiro positioning....)
Given up. I pump stuff to Word and do my printing there is it needs to
be exact.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Janwillem van Dijk wrote :

 

[okaizawa]

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.

 

[okaizawa]

i have experienced some weird results without 'ActiveWindow.ShowWindow = True'.

sorry for my mistake.
'ActiveChart.ShowWindow = True' is correct.

 

[seisman]

Does this mean that if I specify a chart inside width in points then
when I print it it won't be the specified width? If I set a chart's
inside width and height, for example to 288 x 144 (4" x 2"), when I
print it it will be slightly out. Is that what you are saying?

Seiscons

 

[keepITcool]

sad but that's correct.

easily checked with printing s't to PDF
or Office Document Image Writer in TIFF mode.
Then opening in PhotoShop (or similar)


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


seisman wrote :

 

[Janwillem van Dijk]

Does this mean that if I specify a chart inside width in points then
when I print it it won't be the specified width? If I set a chart's
inside width and height, for example to 288 x 144 (4" x 2"), when I
print it it will be slightly out. Is that what you are saying?

Seiscons

The sad thing is that .InsideWidth and .InsideHeight are readonly. How
easy it would have been to state .InsideWidth=250 and .InsideWidth=250
and get a chart with the graph-area excactly the right size of 88X88 mm.
There will be a Bill Gates employee who had good reasons not to alow us
out there to interfere with what he thought nice graph dimensions.

The happy side is that it is a documented feature!

 

[keepITcool]

be creative

just add the current diff between width and inside width
to whatever you want for insidewidth

with object
.Width = .width-.insidewidth + MyInsideWidth
end with




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Janwillem van Dijk wrote :

 

[seisman]

Realised my error in the post after I had written it. What I am realy
doing is manipulating the max and min X and Y axis values of a scatter
chart and then knowing the values for InsideWidth and InsideHeight
calculating a scale. This works on the screen using the DPI and a
Pixels To Points factor (and a scalling factor for the screen size as
folk seldom change the DPI to anything other than standard 96 or 120
DPI values) but as we all seem to agree it does not work to the
printer; a shame indeed... It looks like another scale factor.
Seisman

 

[Jon Peltier]

The problem is that the plot area is not allowed to be larger than the
chart area, which cannot exceed the size of the chart object. I'm sure
it was much easier to allow the plot area to be resized, given the
constraint of the surrounding chart area, than to try to allow resizing
of the plot inside area, which has a large variable margin around it,
containing all the axis ticks. labels, and titles.

I also wish we could directly modify the inside area, but I think it's
important to try to understand why it's the way it is.

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