I can now automatically turn this chart

into this:

Looks simple, eh? Well, it's NOT.

I wanted the display to be squared off so that it showed the circles properly. Of course the values are right, but the user would have to actually check the values and couldn't simply glance at the chart and get the picture. In addition to that, the angle of the sets of circles is actually relevant. You can't display a proper angle on a skewed chart! I also didn't want to modify the chart's own shape or the size of the plot itself. So I went searching for techniques to fix the skewed-ness.

I started off with Jon Peltier's code provided here. It looks like it would work well; in practice it was somewhat lacking for my application. (1. It has to run in a loop and I figured there had to be a way to only run the code once and 2. under some circumstances the loop would run infinity, and while I could modify the loop constraints to fix this, see point 1) The idea was solid though, so I pursued it.

His approach is as follows: measure the space between tickmarks on each axis. A squared chart will have tickmarks spaced the same distance on both axes; a skewed chart will have different spacing on each axis.

Find the axis that has a bigger space between tickmarks. If you somehow squish this axis, the tickmarks will become closer together, forcing the points to be drawn closer together and remedying the skew.

You can squish an axis by increasing the maximum value of that axis. If the axis stays the same length, it will have to draw the tickmarks closer together in order to fit more values in.

Excel uses "points" as the smallest unit of measure for most things. Chart.PlotArea.InsideHeight and Chart.PlotArea.InsideWidth will return the length of both axes in points. If you divide that value by the number of ticks on the axis, you get the spacing (in points) between tickmarks:

NumPointsBetweenTickmarks = LengthOfAxisInPoints / NumTickmarksThis can be rearranged to become

NumTickmarks = SpanOfAxis / MajorUnitOfAxis

SpanOfAxis = Max - Min

NumPoints = LengthOfAxis * (MajorUnit / (Max - Min))(Note: I use the Major Unit of my axes throughout this; some people may want to use the Minor Unit)

Remember to set yMajor and xMajor equal to each other, otherwise the points between each tickmarks won't be comparable and you might try to scale the wrong axis.

Since we ultimately want the spacing between tickmarks to be the same, we can set the equations for the X axis and the Y axis equal to each other:

xPoints = width * (xMajor / (xMax - xMin))Rearranging for xMax and yMax we get these two equations:

yPoints = height * (yMajor / (yMax - yMin))

width * (xMajor / (xMax - xMin)) = height * (yMajor / (yMax - yMin))

xMax = (yMax - yMin) * (width / height) * (xMajor / yMajor) + xMin

yMax = (xMax - xMin) * (height / width) * (yMajor / xMajor) + yMin

A quirk I ran into is how Excel labels the axes. It appears that Excel begins labeling ticks from the minimum. This means that if your minimum is -53 and your major unit is 20 you can get an axis like this:

See how, even though the major unit is a round 20, the labeled ticks on the x-axis are weird? Well, that annoyed me so I came up with a way to fix it. If you find the next lowest number after your current minimum that your major unit will divide evenly into, it will make sure the ticks originate at 0 on your axis. If you round the minimum off nicely, it will prevent Excel from placing ticks on fractional numbers, too - keeping your chart axes looking nice and clean. (Note: Mod is an integer operation, so if you have a fractional number as an Major Unit or as your oldMinimum it will round them. If your Major Unit gets rounded to 0 you'll get a divide by 0 error. Excel's worksheet function MOD doesn't round, but it is not one of the functions exposed to VBA. If you really had to you could calculate the value in a cell and read that back.)

newMinimum = oldMinimum - (majorUnit - (oldMinimum Mod MajorUnit))

.MinimumScale = Round(minX, 0) - (xMajor - (Abs(minX) Mod xMajor))

You'll also notice my use of a "magic Ratio". I calculated this number back when I thought points were nonsymmetric (IE the width of a point was different from the height - a common trait for display units) but it turned out I had been confusing myself with the difference between PlotArea.Height, PlotArea.Width and PlotArea.InsideHeight, PlotArea.InsideWidth. However, any time I removed the magic ratio the plot didn't come out square. I have no idea what magicRatio represents but I never get correct results without it.

So here's everything put together:

Public Sub makePlotSquare(chartObject As Chart) Const magicRatio = 0.91316 'Exactly what it looks like. '---Rescaling variables Dim xPoints As Double Dim yPoints As Double Dim xMin As Double Dim xMax As Double Dim yMin As Double Dim yMax As Double Dim xMajor As Double Dim yMajor As Double Dim xDiff As Double Dim yDiff As Double With chartObject '---The following simply grabs the graph's axis stats If .Axes(xlValue).MajorUnit > .Axes(xlCategory).MajorUnit Then .Axes(xlValue).MajorUnit = .Axes(xlCategory).MajorUnit Else .Axes(xlCategory).MajorUnit = .Axes(xlValue).MajorUnit End If yMajor = .Axes(xlValue).MajorUnit xMajor = .Axes(xlCategory).MajorUnit With .Axes(xlCategory) .MaximumScaleIsAuto = False 'keep Excel from trying to be "helpful" .MinimumScaleIsAuto = False .MajorUnitIsAuto = False If not .MinimumScale = 0 then .MinimumScale = Round(.MinimumScale, 0) - (xMajor - (Abs(Round(.MinimumScale, 0)) Mod xMajor)) End If xMin = .MinimumScale xMax = .MaximumScale End With With .Axes(xlValue) .MaximumScaleIsAuto = False 'keep Excel form being "helpful" .MinimumScaleIsAuto = False .MajorUnitIsAuto = False If not .MinimumScale = 0 Then .MinimumScale = Round(.MinimumScale, 0) - (yMajor - (Abs(Round(.MinimumScale, 0)) Mod yMajor)) End If yMin = .MinimumScale yMax = .MaximumScale End With xDiff = xMax - xMin yDiff = yMax - yMin '---This is where we actually fix the display xPoints = .PlotArea.InsideWidth * (xMajor / xDiff) yPoints = .PlotArea.InsideHeight * (yMajor / yDiff) If xPoints > yPoints Then .Axes(xlCategory).MaximumScale = (yDiff * (.PlotArea.InsideWidth / .PlotArea.InsideHeight) * magicRatio) + xMin Else .Axes(xlValue).MaximumScale = (xDiff * (.PlotArea.InsideHeight / .PlotArea.InsideWidth) * (1 / magicRatio) + yMin) End If End With End Sub

The first set of before-and-afters were in Excel '03. Here's a set from Excel '07:

Now, most of my time was working on the other half that I haven't posted which is "squaring" a chart that has longitude degrees on the X-axis and Latitude degrees on the Y-axis. There is an extra scale value there because 1 degree Latitude (usually) doesn't equal 1 degree Longitude, so the grid itself will actually not be square when circles are displayed as circles and not ellipses.

It should also be noted that when changing the axis gridline values, if their text is longer or shorter than the previous labels', the plotarea will resize and will skew the chart again! In my specific application I actually made a fix for this (that doesn't run in a loop) and will post it later.

## 0 things about

## Post a Comment