|
Sub ScalePlot()
Dim Cht As Chart, Ser As Series, AxX As Axis, AxY As Axis
Dim XVals, YVals, MinX, MinY, MaxX, MaxY
Dim i
Dim PWd, PHt, PWd1, PHt1
Dim XDiff, YDiff, XDiff1, YDiff1
Dim Buffer
Dim WdScale, HtScale
Set Cht = ActiveChart
With Cht
For i = 1 To Cht.SeriesCollection.Count
Set Ser = Cht.SeriesCollection(i)
XVals = Ser.XValues
YVals = Ser.Values
If i = 1 Then
MinX = WorksheetFunction.Min(XVals)
MaxX =WorksheetFunction.Max(XVals)
MinY =WorksheetFunction.Min(YVals)
MaxY =WorksheetFunction.Max(YVals)
Else
MinX =WorksheetFunction.Min(MinX, XVals)
MaxX =WorksheetFunction.Max(MaxX, XVals)
MinY =WorksheetFunction.Min(MinY, YVals)
MaxY =WorksheetFunction.Max(MaxY, YVals)
End If
Next
With .PlotArea
.Top = 0
.Left = 0
.Width = Cht.ChartArea.Width
.Height = Cht.ChartArea.Height
PWd = .Width
PHt = .Height
PWd1 = .InsideWidth
PHt1 = .InsideHeight
End With
Set AxX = .Axes(xlCategory)
Set AxY = .Axes(xlValue)
XDiff = MaxX - MinX
YDiff = MaxY - MinY
Buffer = 0.1
MaxX = MaxX + Buffer * XDiff
MinX = MinX - Buffer * XDiff
MaxY = MaxY + Buffer * YDiff
MinY = MinY - Buffer * YDiff
XDiff = MaxX - MinX
YDiff = MaxY - MinY
With AxX
.MaximumScale = MaxX
.MinimumScale = MinX
End With
With AxY
.MaximumScale = MaxY
.MinimumScale = MinY
End With
WdScale = PWd1 / XDiff
HtScale = PHt1 / YDiff
If WdScale > HtScale Then
XDiff1 = (XDiff * WdScale / HtScale- XDiff) / 2
AxX.MinimumScale = MinX - XDiff1
AxX.MaximumScale = MaxX + XDiff1
Else
YDiff1 = (YDiff * HtScale / WdScale- YDiff) / 2
AxY.MinimumScale = MinY - YDiff1
AxY.MaximumScale = MaxY + YDiff1
End If
End With
End Sub
抄来的代码,我不懂
可以自动修正图表比例为1:1
调整后
|
|