|
I would like to thank my forum
buddy shg for
providing the material for this page.
Scatter plots in Excel show data that has no
intrinsic spatial component; e.g., time series of cash flows for
financial applications, waveforms for engineering applications, etc.
Left to its own devices, Excel sets chart scales automatically,
adjusting them generally to maximize the area of the plot that contains
the data within the overall plot area -- and generally does it pretty
well.
Sometimes, though, we use scatter plots where the x and y axes represent
true linear dimensions, and in those cases, Excel's default behavior
doesn't help at all. We'd like equal spans (the size of max - min on
each axis) to be the same, so that squares, for example, are actually
square. A sub in the attached workbook does that, and shows a simple
example that compares what the sub does to what Excel does by default.
In your own application, you (the user) first create a chart that has
equal height and width for the plot area. Then as data changes, you pass
the min and max x and y values to the sub and it adjusts the scales to
have equal spans and the major unit to be common to both axes. In the
example, the major unit is a fixed size, but you could compute it based
on the data if you wish.
There's another (and more practical) example at
http://www.excelforum.com/excel-general/672617-calculating-the-radius-of-a-curve.html
Function SetSquareAxes(cht As Chart, dBuf As Double, dInc As Double, _ ByVal xMin As Double, ByVal xMax As Double, _ ByVal yMin As Double, ByVal yMax As Double) As Boolean ' shg 2009-0220
' Sets the chart scales to ' o be of equal span ' o start and end on a multiple of dInc, and have dInc as the major unit ' o contain all points with a minimum buffer distance of dBuf to the edges ' o center the points in the plot area within the constraints above
' E.g.,
' SetSquareAxes Sheet1.ChartObjects(1).Chart, 100, 500, _ ' WorksheetFunction.Min(rngX.Value), _ ' WorksheetFunction.Max(rngX.Value), _ ' WorksheetFunction.Min(rngY.Value), _ ' WorksheetFunction.Max(rngY.Value) ' Returns True if successful
Static WF As WorksheetFunction Dim xCtr As Double Dim yCtr As Double Dim dRad As Double ' half-dimension of bounding box Dim dDelta As Double ' common span of x and y scales
' verify cht is a scatterchart Select Case cht.SeriesCollection(1).ChartType Case xlXYScatter, xlXYScatterLines, xlXYScatterSmooth, _ xlXYScatterLinesNoMarkers, xlXYScatterSmoothNoMarkers ' all good ... Case Else MsgBox "Chart type must be XY (Scatter)", vbOKOnly, "SetSquareAxes" Exit Function End Select
If WF Is Nothing Then Set WF = WorksheetFunction
' compute center and bounding box radius xCtr = (xMax + xMin) / 2# yCtr = (yMax + yMin) / 2# dRad = WF.Max(xMax - xCtr, yMax - yCtr) + dBuf
' compute the scale minima xMin = Int((xCtr - dRad) / dInc) * dInc yMin = Int((yCtr - dRad) / dInc) * dInc
' compute the common span and the scale maxima dDelta = WF.Ceiling(WF.Max(xMax - xMin, yMax - yMin) + dBuf, dInc) xMax = xMin + dDelta yMax = yMin + dDelta
With cht.Axes(xlCategory) .MinimumScale = xMin .MaximumScale = xMax .MinorUnitIsAuto = True .MajorUnitIsAuto = False .MajorUnit = dInc End With
With cht.Axes(xlValue) .MinimumScale = yMin .MaximumScale = yMax .MinorUnitIsAuto = True .MajorUnitIsAuto = False .MajorUnit = dInc End With SetSquareAxes = True End Function
The example file,
setsquareaxis contains charts and VBA code.
|