How to control aspect ratio of X,Y axes?

[JoeU2004]

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]

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]

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]

To explain the width insidewidth distinction,
The insidewidth and insideheight is the dimensions for the area
displayed by applying the border to the plot area.
The width/height values include the axis labeling. This is the area
within the dotted lines that are visible when manually resizing the
plotarea.

The reason for changing the width property rather than insidewidth is
that the insidewidth property is read-only. So you have to change the
width property but compare the insidewidth property.

The plot area can only be sized within the confines of the chartarea.
So, for example, if the chartarea width is 350 then the width of the
plotarea must be smaller. (Chartareawidth - plotarea.left) is the
maximum width. You can certainly set the value to greater than the chart
width but the maximum allowed will be what is actually set.

If nobody else chips in I will look at aspect size next week.

Cheers
Andy

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]

According to your min/max scale values you will end up with a very width but
short plot area.

Sub X()

Dim lngStep As Long
Dim dblXmin As Double
Dim dblXmax As Double
Dim dblYmin As Double
Dim dblYmax As Double
Dim dblRatio As Double

With ActiveChart
With .Axes(1)
dblXmin = .MinimumScale
dblXmax = .MaximumScale
End With
With .Axes(2)
dblYmin = .MinimumScale
dblYmax = .MaximumScale
End With

dblRatio = (dblYmax - dblYmin) / (dblXmax - dblXmin)

.PlotArea.Height = .PlotArea.Width * dblRatio
lngStep = 1
If .PlotArea.InsideHeight > (.PlotArea.InsideWidth * dblRatio) Then
lngStep = -1
Do While .PlotArea.InsideHeight > (.PlotArea.InsideWidth *
dblRatio)
.PlotArea.Height = .PlotArea.Height + lngStep
Loop
Else
Do While .PlotArea.InsideHeight < (.PlotArea.InsideWidth *
dblRatio)
.PlotArea.Height = .PlotArea.Height + lngStep
Loop
End If
End With
End Sub


Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

To explain the width insidewidth distinction,
The insidewidth and insideheight is the dimensions for the area displayed
by applying the border to the plot area.
The width/height values include the axis labeling. This is the area within
the dotted lines that are visible when manually resizing the plotarea.

The reason for changing the width property rather than insidewidth is that
the insidewidth property is read-only. So you have to change the width
property but compare the insidewidth property.

The plot area can only be sized within the confines of the chartarea.
So, for example, if the chartarea width is 350 then the width of the
plotarea must be smaller. (Chartareawidth - plotarea.left) is the maximum
width. You can certainly set the value to greater than the chart width but
the maximum allowed will be what is actually set.

If nobody else chips in I will look at aspect size next week.

Cheers
Andy

 

[JoeU2004]

According to your min/max scale values you will
end up with a very width but short plot area.

Thanks. Your examples have been extremely helpful to me.

Leveraging your implementation and using Record Macro to learn more, I
applied your ideas to Activesheet.Shapes(name).ScaleWidth, which mimicks
what I actually do manually.

But I think it is working for me (well, sort of) only by coincidence. In
fact, I have had to implement some inexplicable kludges to make it really
work.

Unfortunately, I have to put this on the back-burner for a while. Hopefully
I will be able to resume the discussion in a couple of weeks.

Thanks again for all your assistance.


----- original message -----

According to your min/max scale values you will end up with a very width
but short plot area.

Sub X()

Dim lngStep As Long
Dim dblXmin As Double
Dim dblXmax As Double
Dim dblYmin As Double
Dim dblYmax As Double
Dim dblRatio As Double

With ActiveChart
With .Axes(1)
dblXmin = .MinimumScale
dblXmax = .MaximumScale
End With
With .Axes(2)
dblYmin = .MinimumScale
dblYmax = .MaximumScale
End With

dblRatio = (dblYmax - dblYmin) / (dblXmax - dblXmin)

.PlotArea.Height = .PlotArea.Width * dblRatio
lngStep = 1
If .PlotArea.InsideHeight > (.PlotArea.InsideWidth * dblRatio)
Then
lngStep = -1
Do While .PlotArea.InsideHeight > (.PlotArea.InsideWidth *
dblRatio)
.PlotArea.Height = .PlotArea.Height + lngStep
Loop
Else
Do While .PlotArea.InsideHeight < (.PlotArea.InsideWidth *
dblRatio)
.PlotArea.Height = .PlotArea.Height + lngStep
Loop
End If
End With
End Sub


Cheers
Andy