Correct aspect ratio for excel chart 1

[navyguysrighthandman]

I want to be able to create a chart that will automatically have the right aspect ratio. Say for example I was trying to plot an ellipse. Excel may plot that ellipse to look like an ellipse, or it could be stretched to look more like a circle, depending on the scale of the x and y 'Major Units' (shutup Beavis). Is there any way to make these axes the correct scale relative to each other, so that on my computer screen a unit of 2 on the x-axis will be the same distance from the origin as a unit of 2 on the y-axis? I can do this for a square chart, where both axes maximum values are equal, but I need to be able to genaralize this to work for a very long shape which would require a rectangular chart. I appreciate the help.

Ben

 

[SkipVought]

Hi,

I would think that the PlotArea Width and Height and the X-Axis and Y-Axis min & max scale and major unit would be the properties that you would manipulate. setting width = height and X & Y max & min scale and major unit ==.

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[navyguysrighthandman]

That would work for shapes that are about as wide as they are tall. What I need is something that would work for shapes that are much taller than they are wide. For example, I have a shape that is 8 units tall and only 1 unit wide. I don't want to plot that on a chart that is 8x8 or 9x9. Maybe I would want to plot it on a chart that is 8 units in the y-axis and 1 unit in the x-direction, and I want the 'major units' on each axis to be equal, so that the x-axis becomes 1/8 the size of the y-axis. It seems like there should be some simple way of doing this but I haven't figured it out.

Ben

 

[SkipVought]

Then you apply the same aspect ratio to the plot area and axes.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[navyguysrighthandman]

You're right Skip. As I was re-reading my message I realized I was almost there. All I need to do is write some code to find the maximum value in the x and y directions and then I have my aspect ratio which I can apply to the chart. Thanks for your help Skip.

Ben

 

[navyguysrighthandman]

I've solved the problem with the plot area but I can't get the chart area to size right. Apparently, Excel treats charts as shapes and therefore will only let you resize by a factor of the original size. (see code below) What I need is to resize the width by a factor of the height. Any suggestions? One requirement is that the chart area be sufficiently larger than the plot area to allow for axis titles and what-not.

Here's the working code for the plot area:

Sub Chart_Aspect_Ratio(aspect_ratio)

Sheet_Profile.ChartObjects("Chart_Profile").Activate
    ActiveChart.PlotArea.Select
    Selection.Height = aspect_ratio * Selection.Width

End Sub

Here's the recorded macro that I need to modify to resize the chart area:

Sub Macro3()
    ActiveSheet.ChartObjects("Chart_Profile").Activate
    ActiveChart.ChartArea.Select
    ActiveSheet.Shapes("Chart_Profile").ScaleWidth 1.14, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.Shapes("Chart_Profile").ScaleHeight 1.04, msoFalse, _
        msoScaleFromTopLeft
End Sub

Thanks for any help!

Ben

 

[SkipVought]

Seems to me tht you'd want to do something like this...

Sub Chart_Aspect_Ratio(aspect_ratio)
    With ActiveSheet.ChartObjects("Chart_Profile")
        .ScaleWidth 1.14, msoFalse, msoScaleFromTopLeft
        .ScaleHeight aspect_ratio * 1.04, msoFalse, msoScaleFromTopLeft
        With .PlotArea
            .Height = aspect_ratio * .Width
        End With
    End With
End Sub

where you would
1. adjust the chart area according to the aspect ration and then
2. adjust the plot area

Finally see my faq regarding NOT Activating or Selecting objects and please rate it's helpfulness.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

My code had some problems. Try this...

Sub Chart_Aspect_Ratio(aspect_ratio)
    With ActiveSheet.ChartObjects(1)
        .ShapeRange.ScaleWidth 1.14, msoFalse, msoScaleFromTopLeft
        .ShapeRange.ScaleHeight aspect_ratio * 1.04, msoFalse, msoScaleFromTopLeft
        With .Chart.PlotArea
            .Height = aspect_ratio * .Width
        End With
    End With
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

The only problem I have with this approch and my code is that the plot width changes and I don't know why.!!!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com