CODEWITHSUNDEEP
excelvbacharts
peter
peter

Reputation: 439

How to set width and height of a chart in pixels?

Setting width and height of the ChartObject or its Shape doesnt do it. They both seem to be just some inner part of that white rectangle which is the embedded chart. Also the ChartArea doesn't seem to be the object I'm interested in.

I want the exported file in the following example to have dimensions 800x600 (and I don't 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. 

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

update:

It turns out the ChartObject and the Shape belonging to the same Chart already have the Worksheet as parent but width and height are not specified in pixels but in points, where 1 point = 1/72 inches, and most of the time, Windows seems to assume 96 pixels per inch.

Thanks to Steve's comment I am now using the following, which is quite reliable.

A ChartObject that is not activated at the moment of export seems to produce a file where width and height is 1 higher than it should be.

It remains to find out how to determine the factors px2ptH and px2ptV automatically.

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

Upvotes: 6

Views: 42645

Answers (1)

Steve Rindsberg
Steve Rindsberg

Reputation: 14809

The dimensions are in points, not pixels. 72 points to the inch.

800 pixels / 72 = 11.11111.... The size of the exported image will be the size in inches/the dpi of your computer's display, usually 96 (as it is in your case, but you can't always count on it ... use WIN API calls to find the current value)

Randy Birch has published code you can use to access the WinAPI calls to get screen resolution and more.

Go to http://vbnet.mvps.org/index.html and use the search feature on the left to look up GETDEVICECAPS

Upvotes: 10

Related Questions