StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POvba: how to set width and height of an excel chart in pixels?
text
Body
copied!<p>a seemingly simple question. but setting width and height of the <code>ChartObject</code> or its <code>Shape</code> doesnt do it, they both seem to be just some inner part of that white rectangle which is the embeded chart. also the <code>ChartArea</code> doesnt seem to be the object i'm interested in.</p> <p>what i want is the exported file in the following example to have dimensions 800x600 (and i dont mean rescaling the exported image or trial and error until the size accidentally fits). there must be some object around the chart which i have overlooked. </p> <pre><code>Sub mwe() Dim filepath As String Dim sheet As Worksheet Dim cObj As ChartObject Dim c As Chart Dim cShape As Shape Dim cArea As chartArea filepath = Left(ThisWorkbook.FullName, InStrRev(ThisWorkbook.FullName, "\")) Set sheet = ActiveSheet Set cObj = sheet.ChartObjects(1) Set c = cObj.chart Set cShape = sheet.Shapes(cObj.Name) Set cArea = c.chartArea cObj.Width = 800 cObj.Height = 400 MsgBox cArea.Width & " x " & cArea.Height '793x393 c.Export filepath & "test1.png" '1067x534, this is also the size on screen cShape.Width = 800 cShape.Height = 400 MsgBox cArea.Width & " x " & cArea.Height '794x393 c.Export filepath & "test2.png" '1068x534, this is also the size on screen End Sub </code></pre> <p>update:</p> <p>it turns out the <code>ChartObject</code> and the <code>Shape</code> belonging to the same <code>Chart</code> already have the <code>Worksheet</code> as parent. but width and height are not specified in pixels as i assumed but in points, where 1 point = 1/72 inches.</p> <p>and most of the time, windows seems to assume 96 pixels per inch.</p> <p>thanks to steve's comment i am using the following now, which is quite reliable. a <code>ChartObject</code> that is not activated at the moment of its export seems to produce a file where width and height is 1 higher than it should be.</p> <p>it remains to find out how to determine the factors <code>px2ptH</code> and <code>px2ptV</code> automatically.</p> <pre><code>Sub mwe() Dim filepath As String Dim sheet As Worksheet Dim cObj As ChartObject Dim c As Chart Dim px2ptH As Double: px2ptH = 72 / 96 Dim px2ptV As Double: px2ptV = 72 / 96 Dim w As Double: w = 800 * px2ptH Dim h As Double: h = 400 * px2ptV filepath = Left(ThisWorkbook.FullName, InStrRev(ThisWorkbook.FullName, "\")) Set sheet = ActiveSheet Set cObj = sheet.ChartObjects(1) Set c = cObj.Chart 'otherwise image size may deviate by 1x1 cObj.Activate cObj.Width = w cObj.Height = h c.Export filepath & "test.png" End Sub </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload