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.

 

[GlennUK]

You say that the chart resides in a chart sheet, and yet you want to change the size?!?!? But surely sizing only applies to charts embedded within a worksheet?

 

[Adam Feldman]

Is your implication that charts are only resizable if in a normal worksheet, and not in a separate chart sheet? Seems to me like someone would want to be able to resize the chart in both scenarios. https://www.dropbox.com/s/qxkf3hv2devcmdy/Book1.xls Here is a sample of the workbook, in the event it helps to clarify my goal. Also, from messing around after reading your statement, it appears that one can copy the object to a regular sheet, resize it, then copy it back to a chart sheet to keep the size change. I'll attempt to see if that accomplishes my goal when I get back into the office on Monday, but any response with clarification about the problem at hand in the meantime would be appreciated. Thanks again for the help thusfar.

 

[GlennUK]

What I mean is that the chart in a Chart sheet is the sheet, and resizing wouldn't make sense. Maybe you mean that you want to alter the Plot Area within the chart?

    ActiveChart.PlotArea.Width = 200
    ActiveChart.PlotArea.Height = 200

 

[Adam Feldman]

From my understanding, I guess I want to be able to alter both the Plot Area and the Chart Area sizes. ChartArea appears to be locked when the chart is in it's own sheet, however that size appears to be set when the chart is moved to a Chart sheet. So, moving the chart to a regular sheet and setting the chartarea, before copying back to a Chart sheet seems to be a workaround. Does this sound correct to you?

 

[GlennUK]

No, that doesn't sound correct to me ... to reiterate:

What I mean is that the chart in a Chart sheet is the sheet, and resizing wouldn't make sense.

Change the zoom on the Chart sheet ... is that what you are actually aiming for?

 

[Adam Feldman]

When the chart is embedded in this document in Axys, I've found that depending on the labels/label positions, the chart appears scaled differently, so as to fit within a particular space. So my intention is to set a size, such that all charts to embedded end up the same size. Setting the plot size may do that. I'm mostly trying to understand the way that Excel has the chart so that I can attempt to act upon it in some way that accomplishes that goal. It's my belief that the chart area is what is constraining the chart in the embedding, and that the resize of the chart is due to the fact that the chart area is square, and if it had larger height, it would not attempt to resize the chart to be smaller when a label is above or below the chart. The program that the embedding is in aside, it is if to say the default width in the chart sheet chart area is okay, but having a little more space in the height section to allow for labels to go there, would accomplish my goal.

 

[GlennUK]

Change the Plot Area.

 

[Adam Feldman]

Change the Plot Area.

Alright, I'll implement it when I reach the office on Monday. I'll mark the thread as solved. Thanks again for all of your help, Glenn.

 

[tusharm]

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.

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.