[Solved] Basic Macro to Increment a Chart's Data Range

[drjlevi6]

I've written a (LibreOffice) Basic macro to increment a sample spreadsheet's (only) chart's data range by 1:

Code: Select all

Sub AdjustChartRange
Dim Doc As Object
Dim Chart As Object
Dim EndRow As Integer

Doc = ThisComponent
Chart = Doc.Sheets(0).Charts(0)
EndRow = Chart.Ranges(0).EndRow
Chart.Ranges(0).EndRow = EndRow + 1
End Sub

Everything seems to work until the last line: at End Sub, Chart.Ranges(0).EndRow still hasn't been incremented and the chart's data range is unchanged. Any ideas?

TIA, Jonathan

(Using LibreOffice Version 3.6.4.3, Mac OS X 10.8.2)

[FJCC]

It seems you have to replace the entire array of cell ranges, not just increment values in place.

Code: Select all

Doc = ThisComponent
Chart = Doc.Sheets(0).Charts(0)
Ranges = Chart.Ranges
Ranges(0).EndRow = Ranges(0).EndRow + 1
Chart.Ranges = Ranges

[Villeroy]

In all spreadsheet programs, the insertion of new cells expands existing references.
My macro expands everything[*] by insertion of cells: http://forum.openoffice.org/en/forum/vi ... =21&t=2350
In addition, it copies down any formulas and selects the new empty cells for editing.

[*] chart ranges, database ranges, named ranges, list/combo box source ranges, formula references in cells, validations, conditional formattings and UNO objects.

[drjlevi6]

FJCC, thank you very much. Your fix worked.

Villeroy, thank you too, but your solution of importing your Python scripts seems a little more out-of-the-way, even though it's more general. However, I'll bookmark your reply, since importing already-written Python scripts sounds potentially useful, too.

Jonathan