"Andy Pope" wrote:
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 -----
"Andy Pope" wrote in 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
--
Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Andy Pope" wrote in message
...
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 wrote:
"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
--
Andy Pope, Microsoft MVP - Excel
http://www.andypope.info