ExcelBanter - Square Charts in Excel 2007

Hi Simon,

Has the new improved version of Excel got a function for producing square
grapsh (i.e. so the distance between units on the X and Y axes are equal)?
If
not, why not? Seriously though, any suggestions are welcome.

Simon

You need to use a scatter chart and change the axes to the same scale.

You can do this manually, or with a macro....

================================================== =========

Option Explicit

Sub SquareChart()
Dim success As Boolean
Dim oCht As ChartObject

' oCht is the first chart object in the active sheet
' change this to ...ChartObjects("myChart") to use this
' on another chart
Set oCht = ActiveSheet.ChartObjects(1)

' if this is not a scatter chart, tell the user and exit
Select Case oCht.Chart.Type
Case xlXYScatter, xlXYScatterLines, xlXYScatterLinesNoMarkers,
xlXYScatterSmooth, xlXYScatterSmoothNoMarkers
success = SquareScatter(oCht)
Case Else
MsgBox "You need to change the chart type to Scatter"
Exit Sub
End Select

' give helpful message to user if code fails
If Not success Then
MsgBox "The code was willing," & Chr(10) & _
"It considered your request," & Chr(10) & _
"But the chips were weak."
End If

End Sub

Function SquareScatter(oCht As ChartObject) As Boolean
Dim cht As Chart
Dim lngType As Long
Dim oPltArea As PlotArea
Dim oAxisY As Axis
Dim oAxisX As Axis
Dim lngAxisMin As Long
Dim lngAxisMax As Long

' error handling
On Error GoTo fnError
SquareScatter = False

' oCht is a chart object passed to the function
' cht is the chart contained in this chart object
Set cht = oCht.Chart

' set the chart object size
' these values look good to me
' feel free to change
oCht.Height = 250
oCht.Width = 350

' set plot area to be square
Set oPltArea = cht.PlotArea

oPltArea.Height = 220
oPltArea.Width = 220

' get minimum and maximum scale from
' both axes
Set oAxisX = cht.Axes(xlCategory)
Set oAxisY = cht.Axes(xlValue)
lngAxisMin = Application.WorksheetFunction.Min(oAxisX.MinimumSc ale,
oAxisY.MinimumScale)
lngAxisMax = Application.WorksheetFunction.Max(oAxisX.MaximumSc ale,
oAxisY.MaximumScale)

' set both axes to the same scale
oAxisX.MajorUnitIsAuto = False
oAxisX.MinimumScale = lngAxisMin
oAxisX.MaximumScale = lngAxisMax

oAxisY.MajorUnitIsAuto = False
oAxisY.MinimumScale = lngAxisMin
oAxisY.MaximumScale = lngAxisMax

' set function to True (have completed code)
SquareScatter = True
Exit Function
fnError:
End Function

=================================================
Ed Ferrero
www.edferrero.com