Page 1 of 1
[Solved] MeanValue & ErrorBarStyle from ChartStatistics
Posted: Fri Jun 18, 2021 7:43 pm
by flyer48
I am trying to use the MeanValue and ErrorBarStyle properties of ChartStatistics, and with my current formatting
Code: Select all
Dim Doc As Object
Dim Charts As Object
Dim Chart as Object
Dim Rect As New com.sun.star.awt.Rectangle
Dim RangeAddress(0) As New com.sun.star.table.CellRangeAddress
Doc = StarDesktop.CurrentComponent
Charts = Doc.Sheets(0).Charts
Rect.X = 8000
Rect.Y = 1000
Rect.Width = 10000
Rect.Height = 7000
RangeAddress(0).Sheet = 0
RangeAddress(0).StartColumn = 0
RangeAddress(0).StartRow = 0
RangeAddress(0).EndColumn = 8
RangeAddress(0).EndRow = 1
Charts.addNewByName("MyChart", Rect, RangeAddress(), True, True)
Chart = Charts.getByName("MyChart").embeddedObject
Chart.Diagram = Chart.createInstance("com.sun.star.chart.LineDiagram")
Chart.Diagram.Wall.FillColor = RGB(255,255,255)
Chart.Diagram.HasXAxisGrid = True
Chart.Diagram.HasYAxisGrid = True
Chart.Diagram.YAxis.Min = 0
Chart.Diagram.YAxis.Max = 100
Chart.Diagram.DataRowSource = com.sun.star.chart.ChartDataRowSource.ROWS
Chart.HasLegend = False
Chart.Diagram.HasXAxisTitle = True
Chart.diagram.XAxisTitle.String = "Tests"
Chart.Diagram.HasYAxisTitle = True
Chart.Diagram.YAxisTitle.String = "Result Value"
Chart.Title.String = "From Row 2"
Chart.ChartStatistics.MeanValue = True
Chart.ChartStatistics.ErrorBarStyle = 2
I get the error message "Property or Method not found: ChartStatistics".
When formatted as
Code: Select all
com.sun.star.chart.ChartStatistics.MeanValue = True
I get "Property or Method not found: MeanValue".
From reading the API and looking at the other properties that I have used that works I thought that one of these two would work. How do you format this?
Thanks in advance.
Re: Using MeanValue and ErrorBarStyle from ChartStatistics
Posted: Fri Jun 18, 2021 8:44 pm
by FJCC
Re: Using MeanValue and ErrorBarStyle from ChartStatistics
Posted: Fri Jun 18, 2021 9:02 pm
by flyer48
The error messages have resolved and the MeanValue line is on the chart, however the error bars are not present on the chart. I have it currently formatted as
because the API indicates that ErrorBarStyle uses a Long Integer, with 2 being the indicator for Standard Deviation. How can I format this so that the standard deviation bars ae visible?
Thanks in advance.
Re: Using MeanValue and ErrorBarStyle from ChartStatistics
Posted: Fri Jun 18, 2021 10:39 pm
by FJCC
Here is a macro that I recorded with the MRI extension that adds error bars around the mean to an xy chart.
Code: Select all
oSheets = ThisComponent.getSheets()
oObj1 = oSheets.getByName("Sheet3")
oCharts = oObj1.getCharts()
oObj2 = oCharts.getByIndex(0)
oEmbeddedObject = oObj2.getEmbeddedObject()
oDiagram = oEmbeddedObject.getDiagram()
oDataErrorProperties = oDiagram.DataErrorProperties
oDataErrorProperties.ErrorBarStyle = 2
The key is getting access to the DataErrorProperties property.
Re: Using MeanValue and ErrorBarStyle from ChartStatistics
Posted: Mon Jun 21, 2021 4:52 pm
by flyer48
When I attempt to run your code it throws "runtime error Object Variable not set" on the last line. I then defined all of the elements as such
Code: Select all
Dim Sheets As Object
Dim Obj1 As Object
Dim Charts As Object
Dim Obj2 As Object
Dim EmbeddedObject As Object
Dim Diagram As Object
Dim DataErrorProperties As Object
Sheets = ThisComponent.getSheets()
Obj1 = Sheets.getByName("CombinedSynthetics")
Charts = Obj1.getCharts()
Obj2 = Charts.getByName("Alpha")
EmbeddedObject = Obj2.getEmbeddedObject()
Diagram = EmbeddedObject.getDiagram()
DataErrorProperties = Diagram.DataErrorProperties
DataErrorProperties.ErrorBarStyle = 2
and I get the same error on
Code: Select all
Obj1 = Sheets.getByName("CombinedSynthetics")
.
Re: Using MeanValue and ErrorBarStyle from ChartStatistics
Posted: Mon Jun 21, 2021 5:14 pm
by FJCC
That error suggests that there is no sheet named CombinedSynthetics. Can you post an example document?
Re: Using MeanValue and ErrorBarStyle from ChartStatistics
Posted: Mon Jun 21, 2021 5:37 pm
by flyer48
Unfortunately, I cannot post an example document. I switched the code to attempt to call by number
Code: Select all
Dim Sheets As Object
Dim Obj1 As Object
Dim Charts As Object
Dim Obj2 As Object
Dim EmbeddedObject As Object
Dim Diagram As Object
Dim DataErrorProperties As Object
Sheets = ThisComponent.getSheets()
Obj1 = Doc.Sheets(0)
Charts = Obj1.getCharts()
Obj2 = Charts.getByName("Alpha")
EmbeddedObject = Obj2.getEmbeddedObject()
Diagram = EmbeddedObject.getDiagram()
DataErrorProperties = Diagram.DataErrorProperties
DataErrorProperties.ErrorBarStyle = 2
and I got the same error.
Re: Using MeanValue and ErrorBarStyle from ChartStatistics
Posted: Mon Jun 21, 2021 5:47 pm
by FJCC
The variable Doc is not defined. Try
If you document contains confidential information, delete everything you do not need for the chart and change the chart data to different numbers. Without an example document, it will be very difficult to provide help.
Re: Using MeanValue and ErrorBarStyle from ChartStatistics
Posted: Mon Jun 21, 2021 6:02 pm
by flyer48
I have attached the example document. When I ran the macro with the updated code, it gave the same error on
Code: Select all
DataErrorProperties.ErrorBarStyle = 2
Re: Using MeanValue and ErrorBarStyle from ChartStatistics
Posted: Mon Jun 21, 2021 6:45 pm
by FJCC
This code works for me with your example file.
Code: Select all
Dim Sheets As Object
Dim Obj1 As Object
Dim Charts As Object
Dim Obj2 As Object
Dim EmbeddedObject As Object
Dim Diagram As Object
Dim DataErrorProperties As Object
Sheets = ThisComponent.getSheets()
Obj1 = Sheets.getByIndex(0)
Charts = Obj1.getCharts()
Obj2 = Charts.getByName("Alpha")
EmbeddedObject = Obj2.getEmbeddedObject()
Diagram = EmbeddedObject.getDiagram()
DataErrorProperties = Diagram.DataErrorProperties
DataErrorProperties.ErrorBarStyle = 2
DataErrorProperties.ShowNegativeError = TRUE
DataErrorProperties.ShowPositiveError = TRUE
If you still get an error, try starting the macro from the spreadsheet using the menu Tools -> Macros -> Run Macro
Re: Using MeanValue and ErrorBarStyle from ChartStatistics
Posted: Mon Jun 21, 2021 6:54 pm
by flyer48
On both the actual and example dataset, I get the "Object Variable not set" on
Code: Select all
DataErrorProperties.ErrorBarStyle = 2
I do not have a Java Runtime Environment to try the Tools -> Macros -> Run Macro process. Do you know where I can get a JRE to attempt this?
Re: Using MeanValue and ErrorBarStyle from ChartStatistics
Posted: Mon Jun 21, 2021 7:23 pm
by FJCC
The link on
this page labeled Windows Offline should get you the 32 bit version of Java.
The attached file has the macro embedded in it. Does that work?
Re: Using MeanValue and ErrorBarStyle from ChartStatistics
Posted: Mon Jun 21, 2021 7:48 pm
by flyer48
It worked after installing Java and going through Tools -> Macros -> Run Macro. It also worked before installing Java on the file with the macro embedded.
Thank you for your help!
Re: [Solved] MeanValue & ErrorBarStyle from ChartStatistics
Posted: Wed Jun 23, 2021 5:27 pm
by flyer48
When attempting to make this run as a looping function to create standard deviation bars for all of the graphs in the sheet
Code: Select all
Dim Sheets As Object
Dim i As Long
Dim Obj1 As Object
Dim Charts As Object
Dim Obj2 As Object
Dim EmbeddedObject As Object
Dim Diagram As Object
Dim DataErrorProperties As Object
For i = 0 To 9
Sheets = ThisComponent.getSheets()
Obj1 = Sheets.getByIndex(0)
Charts = Obj1.getCharts()
Obj2 = Charts.getByName(i)
EmbeddedObject = Obj2.getEmbeddedObject()
Diagram = EmbeddedObject.getDiagram()
DataErrorProperties = Diagram.DataErrorProperties
DataErrorProperties.ErrorBarStyle = 2
DataErrorProperties.ShowNegativeError = TRUE
DataErrorProperties.ShowPositiveError = TRUE
Next i
an "Object variable not set" is thrown on
Code: Select all
DataErrorProperties.ErrorBarStyle = 2
.
The original works in the example sheet posted in this thread, but as soon as the any change is made to either the dataset or the code to get the right chart name, that error is thrown.
Here is my graph creating macro for reference
Code: Select all
Dim Doc As Object
Dim Charts As Object
Dim Chart as Object
Dim Sheets As Object
Dim Obj1 As Object
Dim Charts2 As Object
Dim Obj2 As Object
Dim Rect As New com.sun.star.awt.Rectangle
Dim RangeAddress(0) As New com.sun.star.table.CellRangeAddress
Dim CellRange As Object
Dim Sheet As Object
Dim i As Long
Doc = StarDesktop.CurrentComponent
Sheet = Doc.Sheets.getByIndex(0)
CellRange = Sheet.getCellRangeByName("A1:GZ1")
Charts = Doc.Sheets(0).Charts
For i = 0 To 5
Rect.X = 8000
Rect.Y = 1000 + 1000 * i
Rect.Width = 10000
Rect.Height = 7000
RangeAddress(0).Sheet = 1
RangeAddress(0).StartColumn = 0
RangeAddress(0).StartRow = 2 * i
RangeAddress(0).EndColumn = CellRange.computeFunction(com.sun.star.sheet.GeneralFunction.COUNT) - 1
RangeAddress(0).EndRow = 2 * i + 1
Charts.addNewByName(i, Rect, RangeAddress(), True, True)
Chart = Charts.getByName(i).embeddedObject
Chart.Diagram = Chart.createInstance("com.sun.star.chart.LineDiagram")
Chart.Diagram.Wall.FillColor = RGB(255,255,255)
Chart.Diagram.HasXAxisGrid = True
Chart.Diagram.HasYAxisGrid = True
Chart.Diagram.YAxis.Min = 0
Chart.Diagram.YAxis.Max = 100
Chart.Diagram.DataRowSource = com.sun.star.chart.ChartDataRowSource.ROWS
Chart.HasLegend = False
Chart.Diagram.SymbolType = 0
Chart.Diagram.HasXAxisTitle = True
Chart.Diagram.XAxisTitle.String = "Tests"
Chart.Diagram.HasYAxisTitle = True
Chart.Diagram.YAxisTitle.String = "Result Value"
Chart.Title.String = "From Row" & i+2
Chart.Diagram.MeanValue = True
Next i
Re: MeanValue & ErrorBarStyle from ChartStatistics
Posted: Wed Jun 23, 2021 7:12 pm
by FJCC
I doubt you want to do
Since you are using a numeric index, I would expect
Re: MeanValue & ErrorBarStyle from ChartStatistics
Posted: Wed Jun 23, 2021 7:38 pm
by flyer48
Even with that change it is throwing the same error.
Re: MeanValue & ErrorBarStyle from ChartStatistics
Posted: Wed Jun 23, 2021 8:39 pm
by FJCC
I made a file with two line charts on the first sheet and this macro added error bars to both charts.
Code: Select all
Dim Sheets As Object
Dim i As Long
Dim Obj1 As Object
Dim Charts As Object
Dim Obj2 As Object
Dim EmbeddedObject As Object
Dim Diagram As Object
Dim DataErrorProperties As Object
For i = 0 To 1
Sheets = ThisComponent.getSheets() 'Could be outside of the loop
Obj1 = Sheets.getByIndex(0) 'Could be outside of the loop
Charts = Obj1.getCharts() 'Could be outside of the loop
Obj2 = Charts.getByIndex(i)
EmbeddedObject = Obj2.getEmbeddedObject()
Diagram = EmbeddedObject.getDiagram()
Diagram.ErrorBarStyle = 2
DataErrorProperties = Diagram.DataErrorProperties
DataErrorProperties.ShowNegativeError = TRUE
DataErrorProperties.ShowPositiveError = TRUE
Next i