Aug 24 2018 01:19 AM
Hi,
I have a Bubble Chart where I am plotting a value (Z) against X/Y co-ordinates. I need to make the X & Y axis the same scale, i.e. I need to size the Plot Area so that the co-ordinates map onto grid squares.
I see there is an option to set the Chart Area size, but that size also includes the size of the titles, axis labels, etc. How do I specify the exact size for the plot area itself? I can't see a way to do this.
e.g. If the min/max for X & Y are 0-40, I want to set the plot area size to 16x16cm so X & Y are on the same scale.
If I try setting the chart area to 16x16cm then the X/Y plot area is a rectangle, not a square, because Excel makes room for the chart title!
Aug 24 2018 03:41 AM
SolutionSub plotplot()
Dim w As Variant
Dim h As Variant
Dim crt_name As Variant
w = InputBox("Width in cm")
h = InputBox("Height in cm")
crt_name = InputBox("Chart name")
ActiveSheet.ChartObjects(crt_name).Activate
ActiveChart.PlotArea.Select
Selection.Width = Application.CentimetersToPoints(w)
Selection.Height = Application.CentimetersToPoints(h)
End Sub
This should do what you want.. if you add it as module and run it you should be asked for 3 things.. width in cm, height in cm and the chart name. You should be able to find the chart name by clicking it and the going to the Chart tools->layout ribbon. Default is Chart 1, Chart 2 etc.
Normal warnings, make sure you have a saved copy of your workbook before trying this, you cant undo actions taken by VBA..
Hope it does what you want.
Aug 24 2018 04:27 AM
Philip,
thanks for the quick response.
Yes, it does work. This has to be done as the very last step in setting up the chart. If any other changes to the chart are made which affect the layout, then the plot area size is auto-adjusted!
You would think that after so many years there would be some way to achieve the same thing in the UI, along with an option to keep the plot area size fixed!
Aug 24 2018 04:33 AM
Haha, yes I know.. I was thinking the same thing when I was checking it worked..
Glad it did what you needed though.
I think I might add this one to my Personal.xlsb, seems like the sort of thing that might come in useful again one day.
Nov 26 2021 03:22 PM
Hello Philip, not sure if you're still around but I've encountered a problem with this.
I am making several charts, the Y and X axis need to be the same scale on each chart, and also the different charts need to be the same scale.
However, if the charts are not the same absolute size, the scales are not the same.
See the attached image for an example. Using your method, I set the height of both plots to 4cm. The width of Example 1 I set to 15 cm, and the width of Example 2 I set to double at 30 cm. Since the values of the X axis of Example 2 were also doubled, the scales of the X axis should be the same. However, as you can see they do not line up. Any solutions?
Aug 24 2018 03:41 AM
SolutionSub plotplot()
Dim w As Variant
Dim h As Variant
Dim crt_name As Variant
w = InputBox("Width in cm")
h = InputBox("Height in cm")
crt_name = InputBox("Chart name")
ActiveSheet.ChartObjects(crt_name).Activate
ActiveChart.PlotArea.Select
Selection.Width = Application.CentimetersToPoints(w)
Selection.Height = Application.CentimetersToPoints(h)
End Sub
This should do what you want.. if you add it as module and run it you should be asked for 3 things.. width in cm, height in cm and the chart name. You should be able to find the chart name by clicking it and the going to the Chart tools->layout ribbon. Default is Chart 1, Chart 2 etc.
Normal warnings, make sure you have a saved copy of your workbook before trying this, you cant undo actions taken by VBA..
Hope it does what you want.