Change data area of a chart

[N4rk0]

I can't find the way to change the data area of a chart...
I have a macro that creat a series of numbers and i want to display these numbers on the chart , but number of numbers is variable so i have to adjust the chart everytime by hand ....i would like to do it in the macro.

[FJCC]

The following has worked for me. I have the macro create the chart and assign it to the variable Chart of type Object

Code: Select all

CellRange = Sheet.getCellRangeByPosition(1,0,2,20)            'This covers all of the cells where the new data could be
Cells = CellRange.queryContentCells(1)                     'the 1 causes a query for cells with numeric Values and returns a CellRanges object. You can also search for Formulas etc.
oData = Chart.Data            'Chart has a property called Data that contains information about the chart data
oData.Data = Cells.Data        'Assigns the data array from Cells to the data array of the object oData

This will not change the labels assigned to the axis (if this is a line or bar chart), so you have to be careful to not create a mislabeled chart. I have a rather clumsy way to do that if you need it.

[wagafo]

I'd like to know how you change the categories for a bar chart with a macro, can you post your way even if clumsy?

[FJCC]

Ok, here it is.

Code: Select all

CellRange3 = Sheet.getCellRangeByPosition(0,16,0,20)  'Defines a cell range holding the labels

For x = 0 to CellRange3.Rows.Count-1
    Cell = CellRange3.getCellByPosition(0,x)
    LabelArry(x) = Cell.String
Next x
Chart.Data.RowDescriptions = LabelArry

I created this just fooling around with macros, so I haven't used it in any real situation. I hope it helps.

[Villeroy]

Your macro could also resize the new range by insertion or deletion of rows, based on the difference between new area and the current data data area.

My list keeping macro [Calc, Python] expand/shrink list ranges solves this "resizing-data-problem" for filters, sort ranges, data pilots, and all kinds of references (A2:A99 => A2:A100) in formulas, conditional formatting, validation and charts if they are edited manually. It gets the current list area from the current region of adjacent non-blanks around the user's selection.

A few generic helper snippets in plain, old Basic:

Code: Select all

'calls: none
Function getUsedRange(oSheet)
Dim oRg
	oRg = oSheet.createCursor()
	oRg.gotoStartOfUsedArea(False)
	oRg.gotoEndOfUsedArea(True)
	getUsedRange = oRg
End Function

Function getCurrentRegion(oRange)
Dim oCursor
	oCursor = oRange.getSpreadSheet.createCursorByRange(oRange)
	oCursor.collapseToCurrentRegion
	getCurrentRegion = oCursor
End Function

'pass a spreadsheet-document, sheet or range.
'return empty if oAddr out of bounds or wrong obj
Function getRangeByAddress(obj, oAddr as com.sun.star.table.CellRangeAddress)
on error goto nullErr:
Dim oSheet
	If obj.supportsService("com.sun.star.sheet.SpreadsheetDocument") then
		REM use the sheet specified by given address
		oSheet = obj.getSheets.getByIndex(oAddr.Sheet)
	else
		REM use given object (range/sheet) as parent range
		oSheet = obj
	endif
	getRangeByAddress = oSheet.getCellRangeByPosition(oAddr.StartColumn,oAddr.StartRow,oAddr.EndColumn,oAddr.EndRow)
exit function
nullErr:
	getRangeByAddress = Null
End Function

Function isEqualRangeAddress(a1, a2) as Boolean
	isEqualRangeAddress = (a1.Sheet = a2.Sheet)AND(a1.StartColumn = a2.StartColumn)AND _
	(a1.StartRow = a2.StartRow)AND(a1.EndColumn= a2.EndColumn)AND(a1.EndRow = a2.EndRow)
end function

REM Analogue to the spreadsheet's OFFSET function)
Function getOffsetRange(oRg, nSheetOffset&, nRowOffset&, nColOffset&, nRowResize&, nColResize&)
Dim addr
'calls: getRangeByAddress
	addr = oRg.getRangeAddress()
	addr.Sheet = addr.Sheet + nSheetOffset
	addr.StartRow = addr.StartRow + nRowOffset
	addr.EndRow = addr.EndRow + nRowOffset
	addr.StartColumn = addr.StartColumn + nColOffset
	addr.EndColumn = addr.EndColumn + nColOffset
	if nRowResize > 0 then addr.EndRow = addr.StartRow + nRowResize
	if nColResize > 0 then addr.EndColumn = addr.StartColumn + nColResize
	getOffsetRange = getRangeByAddress(oRg.getSpreadsheet(), addr)
End Function