Page 1 of 1
Change data area of a chart
Posted: Wed Jan 21, 2009 7:00 am
by 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.
Re: Change data area of a chart
Posted: Wed Jan 21, 2009 7:17 pm
by 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.
Re: Change data area of a chart
Posted: Mon Jan 26, 2009 1:44 pm
by 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?
Re: Change data area of a chart
Posted: Tue Jan 27, 2009 6:16 am
by 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.
Re: Change data area of a chart
Posted: Tue Jan 27, 2009 12:11 pm
by 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