# Thread: Excel charts ('97, SR-2)

1. ## 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. ## 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. ## 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. ## 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. ## 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
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. ## 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
•