Microsoft Office Tutorials and References
In Depth Information
ADJUST XY CHART SCALING FOR CORRECT ASPECT RATIO
The sample chart in Figure 89 is a plot of a circle of radius 4 units, with a center
at (5,5), and a square of side 8 units, with the top-left corner at (4.5,12).
Breaking It Down: This problem crops up in situations where the x and y
data are of similar orders of magnitude—for example, when you are plotting
a shape rather than an algebraic function. In the general case, when such a
chart is created, the scaling of the x and y axes are not the same. The height
and width of the plot area also contribute to the degree of distortion of the
plotted series. The idea is to determine which of the two axes needs to be
set to a larger range of Min/Max scale values so the series appears with the
correct aspect ratio, so the required Min/Max scale values are calculated, and
so the axis scale is set accordingly.
The following section of code calculates the Min/Max x and y across all series
in the chart:
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
The following section maximizes the plot area to the chart boundaries and gets
the inside dimensions of the plot area (these dimensions are required for the
scaling exercise):
With .PlotArea
.Top = 0
.Left = 0
.Width = Cht.ChartArea.Width
.Height = Cht.ChartArea.Height
PWd = .Width
 
Search JabSto ::




Custom Search