Here is a more general solution to the problem of equal scaling of both axes in xy-charts.
Excel usually autoscales the two axes and does not provide an option to keep x and y-scales equal.
The following VBA-Module accurately sets the scaling to best fit the plotArea of the diagram in the diagramArea (the bounding box).
It first blows up the plotArea to the maximum and then looks, which axis is to be reduced for equal scales.
Option Explicit
''
' Default borders between plot area and chart area
'
Const BORDER_LEFT = 4
Const BORDER_RIGHT = 4
Const BORDER_TOP = 29
Const BORDER_BOTTOM = 4
''
' test procedure for ScaleChart
Sub test1()
Dim ws As Worksheet
Dim oChart As ChartObject
Set ws = ActiveSheet
Set oChart = ws.ChartObjects("Diagramm 4")
If ScaleChart(oChart) Then
MsgBox "The axes are now equally scaled", vbInformation, "Success"
Else
MsgBox "An error occured", vbCritical, "Failed"
End If
End Sub
''
' ScaleChart - set equal scaling to both axes of a xy-chart
'
' oChart a chartObject
'
' borderLeft (optional) left border between plot area and chart ares
' borderRight (optional) right border between plot area and chart ares
' borderTop (optional) top border between plot area and chart ares
' borderBottom (optional) bottom border between plot area and chart ares
'
' returns true on success, false on any error
'
Function ScaleChart(oChart As ChartObject, _
Optional borderLeft As Double = BORDER_LEFT, Optional borderRight As Double = BORDER_RIGHT, _
Optional borderTop As Double = BORDER_TOP, Optional borderBottom As Double = BORDER_BOTTOM) As Boolean
'
' Variables
'
Dim xMin As Double
Dim xMax As Double
Dim yMin As Double
Dim yMax As Double
Dim xWidth As Double
Dim yHeight As Double
Dim scaleX As Double
Dim scaleY As Double
On Error GoTo mark_err
With oChart.Chart
' ''
' ' display currend border settings
' Debug.Print "Const BORDER_LEFT = "; .PlotArea.Left
' Debug.Print "Const BORDER_RIGHT = "; .ChartArea.Width - .PlotArea.Width - .PlotArea.Left
'
' Debug.Print "Const BORDER_TOP = "; .PlotArea.Top
' Debug.Print "Const BORDER_BOTTOM = "; .ChartArea.Height - .PlotArea.Height - .PlotArea.Top
'
' ''
''
' reset plot area to full size
'
.PlotArea.Left = BORDER_LEFT
.PlotArea.Width = .ChartArea.Width - .PlotArea.Left - BORDER_RIGHT
.PlotArea.Top = BORDER_TOP
.PlotArea.Height = .ChartArea.Height - .PlotArea.Top - BORDER_BOTTOM
'
''
''
' get axis min/max values and current display sizes
'
xMin = .axes(xlCategory).MinimumScale
xMax = .axes(xlCategory).MaximumScale
xWidth = .PlotArea.Width
yMin = .axes(xlValue).MinimumScale
yMax = .axes(xlValue).MaximumScale
yHeight = .PlotArea.Height
End With
scaleX = (xMax - xMin) / xWidth
scaleY = (yMax - yMin) / yHeight
' '
' ' scales information
' '
' Debug.Print "x-axis: M 1:"; 1 / scaleX, "y-axis: M 1:"; 1 / scaleY
If 1 / scaleX > 1 / scaleY Then
'
' the diagram has to be reduced in x-direction
'
oChart.Chart.PlotArea.Width = oChart.Chart.PlotArea.Width * (1 / scaleY) / (1 / scaleX)
Else
'
' the diagram has to be reduced in y-direction
'
oChart.Chart.PlotArea.Height = oChart.Chart.PlotArea.Height * (1 / scaleX) / (1 / scaleY)
End If
'
' no error - return true
'
ScaleChart = True
On Error GoTo 0
Exit Function
mark_err:
'
' error - return false
'
ScaleChart = False
On Error GoTo 0
End Function
