Specifying chart width and height Excel 2007+

[Adam Feldman]

I'm rather new to Excel VBA and to be totally honest, the object model throws me a bit. So any help that you are able to give would be greatly appreciated.

My environment: Win7 and Win8, Excel 2007, Excel 2010
I use a program called Advent Axys that embeds a chart within a frame in their application, and it allows for the execution of macros to style the chart. I would like to, using VBA, specify the maximum size of the chart (the dimensions of the frame in this application) in point. It should be noted, however, that my problems with my VBA exist both in standalone worksheets as well as the embedded in Axys.
The sheet has 1-5 items A1:A5 which are datatypes and values in B1:B5. This is used to generate a pie chart which resides in its sheet, Chart1. I have successfully done various manipulations on the chart after having used Charts(1).Activate, and then referencing ActiveChart from there on.

I have seen

Charts(1).Activate
ActiveChart.Parent.Width = 200
ActiveChart.Parent.Height = 200

referenced in various locations online as the way to do that, however, when I attempt to do that, I receive:

Run-time error '438': Object doesn't support this property or method.

Thanks in advance for your time and help.

 

[Adam Feldman]

You write that the result chart is in a frame in the Advent Axys application and also that it is in a chartsheet.

Which is it?

Also, it is possible to embed a chart in a chartsheet just as in a worksheet. So, depending on how the chart is put in a chartsheet, the chart's parent may have a height and width that you can adjust.

Axys embeds a chart from excel into a frame inside of one of its documents. It allows for the calling of an excel macro, which I used to dump the xls to file. The xls consists of data in Sheet1 and a pie chart in Chart1. So my goal is to use the macro, to act on Chart1, so that the chart embedded into an Axys document looks the way that I want it to. In the case of this thread, I am looking for information regarding how to set the size of the "chart." So Glenn suggested that I want to look at the plot area. That's sort of an internal area, so if you can recommend how to change the size of the external area, chartarea or chartobject size, or something of the sort, that would be much appreciated.

 

[tusharm]

I'm sure you have a grasp on what you want to do but I remain confused as to what you want to do to what object. So, maybe, you can check if Glenn's suggestion works for you.

Axys embeds a chart from excel into a frame inside of one of its documents. It allows for the calling of an excel macro, which I used to dump the xls to file. The xls consists of data in Sheet1 and a pie chart in Chart1. So my goal is to use the macro, to act on Chart1, so that the chart embedded into an Axys document looks the way that I want it to. In the case of this thread, I am looking for information regarding how to set the size of the "chart." So Glenn suggested that I want to look at the plot area. That's sort of an internal area, so if you can recommend how to change the size of the external area, chartarea or chartobject size, or something of the sort, that would be much appreciated.

 

[Adam Feldman]

So just a sum up from my testing, plotarea.width and plotarea.height were very useful. I should note that ultimately I couldn't figure out how Axys was choosing the area to include, so my widths and heights did not end up in point, rather, I had to empirically find them. Thanks for your patience and help.