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

[calcboy]

hi.

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)

thanks

thanks

[Ratslinger]

Hello,

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).

[calcboy]

that did it. many thanks!

[Ratslinger]

@calcboy,

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

[Ratslinger]

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