"Andy Pope" wrote:
VBA code is the only way to do this preciesly.
Something like this, which is only quick and nasty code.
Thanks for the response. However, that is not what I wanted. Your VBA
routine makes the aspect ratio of the plot area square. What I want,
instead, is for the distance between units on the X-axis (e.g. 1 and 2) to
be the same as the distance between units on the Y-axis (e.g. 118 and 117).
(Hmm, all that might be complicated by the aspect ratio of the display. I
would be content with a solution that works only for a display with a 4:3
aspect ratio.)
Regardless, I do not quite understand the logic of your routine.
The initial IF statement sets PlotArea.Width to PlotArea.Height. The WHILE
loop then adjusts PlotArea.Width stepwise by 1 or -1 until
PlotArea.InsideWidth equals PlotArea.InsideHeight.
First, what is the distinction between PlotArea.Width and
PlotArea.InsideWidth?
Second, why does PlotArea.Width need to be changed stepwise?
Hmm, let me guess: InsideWidth is affected by the display aspect ratio, so
we cannot know a priori how much of a change in Width will cause InsideWidth
to equal InsideHeight. Right?
Despite my lack of understanding, I tried to tweak your routine to do what I
wanted, to no avail.
As I mentioned previously, the X-axis has 597 units (1 to 597), whereas the
Y-axis has 37 units (118 to 155). So I thought that perhaps I want to set
PlotArea.Width = PlotArea.Height*597/37.
(Obviously, I would like to generalize those limits. I don't know how.)
Below is your macro with my hacks. It does not work; no surprise, probably.
But it fails to behave as expected in strange ways.
First, initially, PlotArea.Width is 346 and PlotArea.Height is 179. When I
try to set Width = Height*597/37, PlotArea.Width becomes only 353, not 5582
or 5583. How can I fix that?
Second, after setting PlotArea.Width to 353 in that way,
PlotArea.InsideWidth is 320 and PlotArea.InsideHeight is 148. So we fall
into my second loop, which attempts to increase Width until InsideWidth =
InsideHeight*597/37.
(Note: That is the opposite of what your original macro did when
InsideWidth InsideHeight.)
But the loop aborts after the first iteration because PlotArea.Width is not
changed after trying to add one. Why not?
Well, the explanation is probably the same as the answer to my first
question: why is PlotArea.Width limited to 353 in the first place?
Other than that, are my hacks a step in the right direction for what I want
to accomplish? Can you or someone do better?
Sub fixAspectRatio()
With ActiveChart
If .PlotArea.Width .PlotArea.Height Then
.PlotArea.Width = .PlotArea.Height * 597 / 37
Else
.PlotArea.Height = .PlotArea.Width * 37 / 597
End If
lastWidth = .PlotArea.Width
If .PlotArea.InsideWidth .PlotArea.InsideHeight * 597 / 37 Then
Do While .PlotArea.InsideWidth .PlotArea.InsideHeight * 597 /
37
.PlotArea.Width = .PlotArea.Width - 1
If .PlotArea.Width = lastWidth Then Exit Do
lastWidth = .PlotArea.Width
Loop
Else
Do While .PlotArea.InsideWidth < .PlotArea.InsideHeight * 597 /
37
.PlotArea.Width = .PlotArea.Width + 1
If .PlotArea.Width = lastWidth Then Exit Do
lastWidth = .PlotArea.Width
Loop
End If
End With
End Sub
----- original message -----
"Andy Pope" wrote in message
...
Hi,
VBA code is the only way to do this preciesly.
Something like this, which is only quick and nasty code.
'----------------
Sub X()
Dim lngStep As Long
With ActiveChart
If .PlotArea.Width .PlotArea.Height Then
.PlotArea.Width = .PlotArea.Height
Else
.PlotArea.Height = .PlotArea.Width
End If
lngStep = 1
If .PlotArea.InsideWidth .PlotArea.InsideHeight Then _
lngStep = -1
Do While .PlotArea.InsideWidth < .PlotArea.InsideHeight
.PlotArea.Width = .PlotArea.Width + lngStep
Loop
End With
End Sub
'----------------
Cheers
Andy
JoeU2004 wrote:
Is there a straight-forward way to control the aspect ratio of the X and
Y axes?
I managed to do it by manually dragging the length and width of the chart
area until the aspect ratio looked like what I wanted.
But I'd rather do it a more methodical way, if possible. For example, I
thought under the Format Axis Scale, I thought there might be something
for Units per Inch. Nope.
(I am using Excel 2003.)
Since my original X coordinate ranged from 1 to 597 and Y ranged from 118
to 155, the aspect ratio was way off. I wanted 1-to-1; that is, the
visual distance between 118 and 123 on Y is the same as the distance
between 1 and 6 on X.
--
Andy Pope, Microsoft MVP - Excel
http://www.andypope.info