formating excel charts

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Does anyone know how to scale the chart area in excel??
Here is a synopsis of my problem. I created a chart and
pasted it into word. I changed the page setup in word to
landscape and made the chart as big as possible to fit on
one page. I understand if I mess with the size, I can
make it scale corectly. It appears that the scale is like
2 in the x direction and 1 in the y. I would like to know
if there is a way to define the x and y scale so that the
grid lines are a 1 to 1 scale. Any comments or
suggestions would be appreciated.
 
This trick works okay for resizing. However, now the grid
shows goes way beyond the realm of useful data.

If I have the following
x y
..5 .5
1 1
1.5 1.5........thru
4 4
The chart after using the macro will now show a scale in
the x direction of almost 16. This is not what I am
looking for. How do you now manipulate the maximum value
to be 4 or 5 without screwing up the scale? If you go
into format axis and then change the maximum value to 4
or 5 it goofs up the scale If anyone knows the answer to
this please help.

Thanks
SS
 
What you need to do is shorten the plot area. I'm not sure what
algorithm I have on my web site anymore (I have several on my hard
drive). Try this:

'==================================================
Sub ActiveChartSquareGridlines()
ChartSquareGridLines ActiveChart
End Sub

Sub AllChartsSquareGridlines()
Dim myChartObj As ChartObject
For Each myChartObj In ActiveSheet.ChartObjects
ChartSquareGridLines myChartObj.Chart
Next
End Sub

Sub ChartSquareGridLines(myChart As Chart)
' declarations
' probably overdid it, but you never know what you will want later
Dim Ymax As Double, Ymin As Double, Ytic As Double
Dim Xmax As Double, Xmin As Double, Xtic As Double
Dim Ytics As Integer, Xtics As Integer
Dim Yticspace As Single, Xticspace As Single
Dim Ytics2 As Integer, Xtics2 As Integer
Dim PltAreaHt As Long, PltAreaWd As Long, PltAreaLeft As Long, _
PltAreaTop As Long
Dim PltArInHt As Long, PltArInWd As Long, PltArInLeft As Long, _
PltArInTop As Long

With myChart
' get dimensions
PltAreaHt = .PlotArea.Height
PltAreaWd = .PlotArea.Width
PltAreaLeft = .PlotArea.Left
PltAreaTop = .PlotArea.Top
PltArInHt = .PlotArea.InsideHeight
PltArInWd = .PlotArea.InsideWidth
PltArInLeft = .PlotArea.InsideLeft
PltArInTop = .PlotArea.InsideTop
' get axis scales
With .Axes(xlCategory, xlPrimary)
Xtic = .MajorUnit
Xmin = .MinimumScale
Xmax = .MaximumScale
End With
With .Axes(xlValue, xlPrimary)
Ytic = .MajorUnit
Ymin = .MinimumScale
Ymax = .MaximumScale
End With
Xtics = (Xmax - Xmin) / Xtic
Ytics = (Ymax - Ymin) / Ytic
Xticspace = PltArInWd / Xtics
Yticspace = PltArInHt / Ytics
' decide what to change
' ignore if ratio is between 0.98 and 1.02
If Xticspace / Yticspace > 1.02 Then
' fine tune by decreasing width
.PlotArea.Width = PltAreaWd * Yticspace / Xticspace
ElseIf Xticspace / Yticspace < 0.98 Then
' fine tune by decreasing height
.PlotArea.Height = PltAreaHt * Xticspace / Yticspace
End If
End With

End Sub
'==================================================

- Jon
 
Back
Top