Squaring gridlines in Excel - an adventure

FINALLY! Finally finally finally. This took countless (wo?)manhours to get it working, but I FINALLY did it.

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 / NumTickmarks
NumTickmarks = SpanOfAxis / MajorUnitOfAxis
SpanOfAxis = Max - Min
This can be rearranged to become
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))
yPoints = height * (yMajor / (yMax - yMin))

width * (xMajor / (xMax - xMin)) = height * (yMajor / (yMax - yMin))
Rearranging for xMax and yMax we get these two equations:
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
            .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
                .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

Squaring gridlines in Excel - an adventure

Post a Comment

Copyright 2012 Phile not Found. See About
Powered by Blogger

"Whenever you find that you are on the side of the majority, it is time to pause and reflect."