[Solved] Adding Trendlines and Markers on a Chart OO Basic

[MacroSaveTheQueen]

How can I add a trendline and markers on a chart. Here is how I added trendline in MS VBA:

Code: Select all

Sub TrendLineAdd()
With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Trendlines
    If Not .Count > 0 Then
    With .Add
        .Type = xlPolynomial
        .Order = 5
        .Forward = 5
        .Backward = 5
        .Border.ColorIndex = 1
        .Border.Weight = 4
        .Border.LineStyle = xlContinuous
    End With
    End If
End With
End Sub

And this is how I added markers:

Code: Select all

Sub MarkerUnHide()
    With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
    .MarkerStyle = xlMarkerStyleCircle
    .MarkerSize = 6
    .MarkerBackgroundColor = RGB(255, 0, 0)
    .MarkerForegroundColor = RGB(0, 0, 0)
    End With
End Sub

So is there a method for chart object to access SeriesColleciton to add/delete, count trendlines and add markers with needed formatting? Please help!

[Zizi64]

The Starbasic and API is not same, nor compatible with the MS VBA.
It is possible to achieve what you want to do with macro in AOO, but you need study and use the API functions of the Apache OpenOffice or LibreOffice. (API: Application Programming Interface.) Then you will able to rewrite your macros in the StarBasic, or in other programming language related to open source office suites.

See Andrew Pitonyak's books, and the API descriptions on the internet. There are many example codes for starting in this forum too...

[MacroSaveTheQueen]

Thanks for your reply!
I know that StarBasic is not compatible with MS VBA. I just need a direction in witch to dig further. At least which API function controlls trendlines and markers on a chart.
Here is a sub (in StarBasic) in which I can edit chart's title and legend:

Code: Select all

Sub Chart
Dim Sheet As Object
Dim Charts As Object
Dim Chart as Object

Sheet = ThisComponent.CurrentController.ActiveSheet
Charts = Sheet.Charts
Chart = Charts(0).EmbeddedObject
Chart.HasMainTitle = True
Chart.Title.String = "Title"
Chart.HasSubTitle = True
Chart.Subtitle.String = "Subtitle"
Chart.HasLegend = True
Chart.Legend.Alignment = com.sun.star.chart.ChartLegendPosition.BOTTOM
Chart.Legend.FillStyle = com.sun.star.drawing.FillStyle.SOLID
Chart.Legend.FillColor = RGB(255, 255, 0)
Chart.Legend.CharHeight = 7
End Sub

Here I can address a title of a certain chart. But I just need to address a series on a chart, so I can format each series separatly, and add trendline plus markers.
Don't get me wrong, but If I had time to read all the books about APIs, I wouldn't have started a topic on forum.

[Zizi64]

https://www.openoffice.org/api/docs/com ... eries.html

https://www.openoffice.org/api/docs/com ... ainer.html


viewtopic.php?f=9&t=72723

[FJCC]

Adding symbols to an xy chart:

Code: Select all

  oSheets = ThisComponent.getSheets()
  oSheet1 = oSheets.getByName("Sheet1")
  oCharts = oSheet1.getCharts()
  
  oChart = oCharts.getByName("Object 1")
  oEmbeddedObject = oChart.getEmbeddedObject()
  oDiagram = oEmbeddedObject.getDiagram()
  
  oDataRowProperties = oDiagram.getDataRowProperties(0)
  oDataRowProperties.SymbolType = 2
  Size = oDataRowProperties.SymbolSize
  Size.Height= 350
  Size.Width=350
  oDataRowProperties.SymbolSize = Size

[MacroSaveTheQueen]

Thank you for help!

It was a tough journey, but I've reached my finall destination! Off Topic but, I discovered a strange thing: first you hate that OpenOffice doesn't utilize MS VBA, and after a few days with your head deep in StarBasic, you begin to love it even more than VBA (^-^). In my project I have a XYDiagram chart embeeded in the sheet. Here's the code.

Adding Trenlines:

Code: Select all

oSheet = ThisComponent.CurrentController.ActiveSheet
oDiagram = oSheet.Charts(0).EmbeddedObject.getDiagram()
oDataRowProperties = oDiagram.getDataRowProperties(1)
oDataRowProperties.RegressionCurves = com.sun.star.chart.ChartRegressionCurveType.LINEAR
oDataRowProperties.DataRegressionProperties.LineColor = RGB(0,0,0)
oDataRowProperties.DataRegressionProperties.LineWidth = 100

To delete a line:

Code: Select all

oDataRowProperties.RegressionCurves = com.sun.star.chart.ChartRegressionCurveType.NONE

Adding markers:

Code: Select all

oSheet = ThisComponent.CurrentController.ActiveSheet
oDiagram = oSheet.Charts(0).EmbeddedObject.getDiagram()
oForm = oSheet.DrawPage.Forms.GetByIndex(0)
oDataRowProperties = oDiagram.getDataRowProperties(1)
oDataRowProperties.SymbolType = 1
oDataRowProperties.LineColor = RGB(0, 255, 255)
Size = oDataRowProperties.SymbolSize
	Size.Height = 250
	Size.Width = 250
oDataRowProperties.SymbolSize = Size

VERY IMPORTANT NOTE: In XYDiagrams, the first series has an index 1, because the first array of values contains the x-values of the diagram that is not visualized. This behavior exists for historical reasons.

To delete markers (or beter say, hide them):

Code: Select all

oDataRowProperties.SymbolType = -3