Villeroy wrote:You must not try to record macros. This is a dead end.
+1 (and then some)
I should probably scold you further since we've gone as far as c++ add-ins, but I'll let it slide for now.
saleem145 wrote:Hello,
I have written ("recorded") a simple OO Basic Macro to insert a chart. .... I would like to make the following enhancements --
1. Set the chart type
2. Set the main and sub title
3. Set the x and y axis
4. Insert some text at the bottom
5. Instead of specifying a range to plot, I would like to create a 2D array of data and pass in the data.
Are these possible?? (Record macro does not provide any insights)
Thanks,
Saleem
Everything you ask is possible, using the API. I'll concentrate on the trickiest thing, which is #5. I'll do an xy chart.
The only way I know to add a chart via the API, is with
addNewByName, which requires, among other things, a CellRangeAddress, to get around this, just pass an empty one, with all the elements = 0, then you can remove all the dataseries with a subroutine:
Code: Select all
Sub ClearChart(Chart As Object)
Dim Doc As Object
Dim Sheet As Object
Dim ChartSheet As Object
Dim SetupSheet As Object
Dim oChart
Dim oFirstDiagram
Dim oDataSeries
Dim oCoords
Dim oCoord
Dim oChartTypes
Dim oChartType
Dim i As Long
Doc = ThisComponent
oChart = Chart.getEmbeddedObject()
oFirstDiagram = oChart.getFirstDiagram()
' get coordinates from the diagram
oCoords = oFirstDiagram.getCoordinateSystems()
oCoord = oCoords(0)
' get chart type
oChartTypes = oCoord.getChartTypes()
oChartType = oChartTypes(0)
oDataSeries = oChartType.getDataSeries()
i = UBound(oDataSeries)
do while i >= 0
oChartType.removeDataSeries(oDataSeries(i))
i = i - 1
loop
End Sub
Then you need a dataarray, say
Code: Select all
Dim oDataArray( 0 To 1000,0 To 1)
You can do multiple series by making the second dimension larger (0 to x), but I'll stick with one for now.
I believe this is limited to 32,767 rows, which I hope isn't a problem.
oDataArray(i, 0) are the x values, and oDataArray(i, 1) the y values, so you can do something like
Code: Select all
Dim x As Double
Dim i As Long
for i = 0 to 1000
if i <> 0 then
x = 2 * pi * i / 1000
else
x = 0
endif
oDataArray( i, 0) = x
oDataArray( i, 1) = Sin(x)
next i
Then oDataArray is assigned to the chart by
Code: Select all
oData = oChartDoc.getData()
' call setData() method of XChartDataArray to apply the data
oData.setData(oDataArray())
It is not at all obvious how to give a title to the series, and I'll refer you to the attached document for that and other details.
Run Main to regenerate the chart.
I'm not sure what you want for text at the bottom. If you want more than the x axis title, that's something else.
