Page 1 of 1
[Solved] Basic Macro to Increment a Chart's Data Range
Posted: Mon Jan 21, 2013 8:31 pm
by 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)
Re: Help: Basic Macro to Increment a Chart's Data Range
Posted: Mon Jan 21, 2013 8:48 pm
by 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
Re: Help: Basic Macro to Increment a Chart's Data Range
Posted: Mon Jan 21, 2013 9:25 pm
by 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.
Re: [Solved] Help: Basic Macro to Increment a Chart's Data R
Posted: Tue Jan 22, 2013 12:52 am
by 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