Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Voorhees, New Jersey
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel charts ('97, SR-2)

    I think I know the answer but wanted to ask just in case:

    Is there a way to force specific x- and y-axis lengths ?

    I know you can drag and stretch till you get the approximate axis lengths that look right, but what if I want to specify that the x-axis should be 4.5" long and that the y-axis should be 3.3" long (exactly), for instance?

    Some time ago I came to the conclusion that such an option does NOT exist...am I right?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel charts ('97, SR-2)

    I'm curious to hear what others think, but I agree, I don't know of a way to specify the size of chart axes.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Voorhees, New Jersey
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel charts ('97, SR-2)

    A secondary issue:

    Assuming specifying axis lengths isn't doable, is there at least a way to force the x-axis scale to be the same as the y-axis scale, so you don't end up with a "distorted" (stretched) plot?

    [I sometimes use a specialized graphing program ("Grapher" by Golden Software) wherein you can specify axis lengths, but often I just want "quick and dirty" plots inside of Excel. The apparent inability to specify axis lengths in Excel is my #1 complaint.]

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel charts ('97, SR-2)

    We're talking xy (scatter) charts here, aren't we?

    You could set the major unit of the x-axis and y-axis equal to each other, and you could even write a macro to do that for you, but it still wouldn't mean very much, since you can change the width-to-height proportion of the chart as a whole. In the screen shot, I created one chart, set the major unit of both charts to 1, then duplicated the chart and resized the copy.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel charts ('97, SR-2)

    Try this routine. Charts work in pixels 72/inch. InsideHeight and InsideWidth are readonly, so I just continually adjust the height/width until they are within 1% (change the resolution as needed)

    Steve

    <pre>Option Explicit
    Sub SetAxes()
    Dim cht As Chart
    Dim PA As PlotArea
    Dim CA As ChartArea
    Dim xPix
    Dim yPix
    Dim sRes As Single

    sRes = 0.01

    On Error Resume Next
    Set cht = ActiveChart
    On Error GoTo 0
    If cht Is Nothing Then
    MsgBox "Please select a chart"
    Exit Sub
    End If
    Set PA = cht.PlotArea
    Set CA = cht.Shapes.Parent.ChartArea

    xPix = InputBox("How many inches for X-Axes?")
    xPix = Val(xPix) * 72
    If CA.Width < xPix * 1.5 Or xPix = 0 Then
    MsgBox "Your chart is too narrow or invalid entry"
    Exit Sub
    End If

    yPix = InputBox("How many inches for Y-Axes?") * 72
    yPix = Val(yPix) * 72
    If CA.Height < yPix * 1.5 Or yPix = 0 Then
    MsgBox "Your chart is too Short or invalid entry"
    Exit Sub
    End If

    PA.Width = xPix
    PA.Height = yPix

    Do
    PA.Width = PA.Width / PA.InsideWidth * xPix
    PA.Height = PA.Height / PA.InsideHeight * yPix

    Loop While Abs(PA.InsideWidth / xPix - 1) > sRes Or _
    Abs(PA.InsideHeight / yPix - 1) > sRes

    Set CA = Nothing
    Set PA = Nothing
    Set cht = Nothing
    End Sub</pre>


  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel charts ('97, SR-2)

    <P ID="edit" class=small>(Edited by sdckapr on 26-Oct-03 07:06. Edited to fix link. (Thanks, Stuart, for bringing it to my attention))</P>I haven't tried working out code to adjust the axis length directly. I would not say it was "undoable", I would say it was "very impractical". You have to keep track of all sorts of interrelated items

    Regarding making a chart "square" see this link to Jon Peltier's chart site. I think it can be adapted to what you need.

    It might give you some ideas to also adjust the axes sizes directly.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •