Re: Scale of axis in a chart Scale 1:1
This works for XY Scatter Graphs.
Sub ScaleOneToOne()
'Select chart, then run this macro.
'Charts will print 1:1, but 1mm doesn't = 1mm!
Dim theChart
Dim PxPerX As Double
Dim PxPerY As Double
Dim pixelsWide As Integer, pixelsHigh As Integer
Dim xscale As Double, yscale As Double
Dim gpxmn As Double, gpxmx As Double, gpymn As Double, gpymx As Double
Dim xNoAxis As Boolean, yNoAxis As Boolean
Dim xMargin As Double, yMargin As Double
Dim i As Integer
Set theChart = ActiveChart
With theChart
.PlotArea.Left = 0'makes sure it's not too big to start
.PlotArea.Top = 0
.ChartArea.AutoScaleFont = False'otherwise Excel rescales fonts
.PlotArea.Width = .ChartArea.Width
.PlotArea.Height = .ChartArea.Height
pixelsWide = .PlotArea.InsideWidth
pixelsHigh = .PlotArea.InsideHeight
xMargin = .PlotArea.Width - pixelsWide
yMargin = .PlotArea.Height - pixelsHigh
With .Axes(xlCategory)
.MinimumScaleIsAuto = False
.MaximumScaleIsAuto = False
gpxmn = .MinimumScale
gpxmx = .MaximumScale
End With
With .Axes(xlValue)
.MinimumScaleIsAuto = False
.MaximumScaleIsAuto = False
gpymn = .MinimumScale
gpymx = .MaximumScale
End With
PxPerX = pixelsWide / (gpxmx - gpxmn)
PxPerY = pixelsHigh / (gpymx - gpymn)
xscale = Application.Min(PxPerX, PxPerY) / PxPerX
yscale = Application.Min(PxPerX, PxPerY) / PxPerY
With .PlotArea
.Width = pixelsWide * xscale + xMargin
.Height = pixelsHigh * yscale + yMargin
End With
'Scale textbox psn
For i = 1 To .Shapes.count
.Shapes(i).Left = .Shapes(i).Left * xscale
.Shapes(i).Top = .Shapes(i).Top * yscale
Next
End With
End Sub