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