Make the X and Y axis scales equal on an Excel chart

Question

I'd like the X and Y axes of my Excel charts to have the same scale on the screen, because I'm plotting geographical data. A 1km by 1km square should look like a square, not like a rectangle, i.e. no squishing of the map in one or the other direction. In Matlab, the command that would do this is axis equal.

How do I do this using VBA?

Am I overlooking an even simpler solution directly in Excel?

Solution 1

In addition to guitarthrower's answer you will need to do the following: Select the 'Plot Area' of the chart and then manually set the height and width of the plot area.

Sheets("Chart1").PlotArea.Select
  Selection.Height = 500
  Selection.Width = 500

Just setting the axis min and max values will still allow the chart to be 'squished'.

Solution 2

When you select the plot area and write Selection.Width = something, the Width value you are setting also includes the width of the axis labels/text. This may not be what you want.

Instead, you can set the INSIDE Width/Height value using

Selection.InsideHeight = 250
Selection.InsideWidth = 250

Solution 3

Another method similar to Stewbob's is to set the limits to some ratio of each other (my plots are 4 times as wide as they are tall) and then use the height to set the width.

ActiveChart.PlotArea.Select
Selection.Width = Selection.Height * 4