Use macro to create chart dimensions based on cells, not values


i have many new charts to create on many worksheets (some sheets contain several charts) of different sizes every day.

i want to use a macro for that.

is it possible to create chart using a macro where the chart ‘size’ is specified:

not in values (ex: 1000 1000)

but in cells (b2 h12)? ex: where the chart is positioned in cells b2 (upper left) through h12 (bottom right)




Not going to present creation of chart using macro. That can be extensive and depends upon chart type and various options. You can get a basic macro for this in the Andrew Pitonyak book on Open Office Macros Explained → PDF here. Look in section 15.13 Charts about page #541.

The chart is still based upon x & y values but you can get these from the cell. The upper left point of a cell is in Position and width & height in Size:

oSheet = oSheets.getByName("YOUR_SHEET_NAME")
oCellRangeByName = oSheet.getCellRangeByName("B2:H12")
aPosition = oCellRangeByName.Position
nX = aPosition.X
nY = aPosition.Y
aSize = oCellRangeByName.Size
nHeight = aSize.Height
nWidth = aSize.Width  

So in creating the chart, you need the x & y position (nX & nY) and the Width & Height of the chart (nHeight & nWidth).

that did it. many thanks!


You closed question as right answer was accepted but no answer was marked as accepted. Re-opening question.

Thank you. FYI, there is no real need to actually close a question.