Macro VBA for creating a Chart in LibreCalc, how to position titles, legends, and chart main into chart area.
I have created a Macro by reading many sources as well developers guides here Xshapes and ChartLegends - Automatic position
It says that "boolean - AutomaticPosition - If this property is true the position is calculated by the application automatically. Setting this property to false will have no effect. Instead use the interface ::com::sun::star::drawing::XShape to set a concrete position. "
So I was searching for Xshape but nothing found about legend and title positioning inside a Chart / graph.
What I am doing wrongly? What I must do in order to
Note that I have installed Xray tool, but no help.
Sub CreateCalcWithSimpleChart
Dim oSheet 'Sheet containing the chart
Dim oRect 'How big is the chart
Dim oCharts 'Charts in the sheet
Dim oChart 'Created chart
Dim oAddress 'Address of data to plot
Dim sName$ 'Chart name
Dim oChartDoc 'Embedded chart object
Dim oTitle 'Chart title object
Dim oDiagram 'Inserted diagram (data).
Dim sDataRng$ 'Where is the data
Dim oCalcDoc
oCalcDoc = CreateCalcForChart()
sName = "Example_01"
sDataRng = "$E$8:$G$19"
oSheets = ThisComponent.getSheets()
oSheet = oSheets.getbyName("Normality")
oAddress = oSheet.getCellRangeByName( sDataRng ).getRangeAddress()
oCharts = oSheet.getCharts()
If NOT oCharts.hasByName(sName) Then
oRect = createObject("com.sun.star.awt.Rectangle")
oRect.X = 1
oRect.Y = 1
oRect.width = 20000
oRect.Height= 10000
oCharts.addNewByName(sName, oRect, Array(oAddress), True, True)
End If
oChart = oCharts.getByName( sName )
oChart.setRanges(Array(oAddress))
oChartDoc = oChart.getEmbeddedObject()
' Create a diagram.
oDiagram = oChartDoc.createInstance( "com.sun.star.chart.ChartDiagram" )
oChartDoc.setDiagram( oDiagram )
oDiagram = oChartDoc.getDiagram()
' Min / Max scale values on x-y axis
oAxis = oDiagram.getXAxis()
oAxis.AutoMin = False
oAxis.AutoMax = False
oAxis.Min = 1
oAxis.Max = 5
oAxis.CharColor = RGB(111,0,200)
oAxis.CharFontName = "Liberation San"
oAxis.CharHeight = 8
oAxis.AxisTitle.string ="ccccc"
oAxis.AxisTitle.CharColor = RGB(11,0,200)
oAxis.AxisTitle.CharHeight = 12
oAxis.CharPosture = 0 'Italics
oAxis = oDiagram.getYAxis()
oAxis.AutoMin = False
oAxis.AutoMax = False
oAxis.Min = 1
oAxis.Max = 5
oAxis.CharColor = RGB(111,0,200)
oAxis.CharFontName = "Liberation San"
oAxis.CharHeight = 8
oAxis.AxisTitle.string ="ccccc"
oAxis.AxisTitle.CharColor = RGB(111,0,2)
oAxis.AxisTitle.CharHeight = 12
oAxis.CharPosture = 0 'Italics
oTitle = oChartDoc.getTitle()
oTitle.String = "My title"
oTitle.CharColor = RGB(0,0,200)
oTitle.CharFontName = "Liberation San"
oTitle.CharHeight = 18
oTitle.CharPosture = 1 'Italics
oSubTitle = oChartDoc.getSubtitle()
oSubTitle.String = "My title"
oSubTitle.CharColor = RGB(0,0,200)
oSubTitle.CharFontName = "Liberation San"
oSubTitle.CharHeight = 16
oSubTitle.CharPosture = 0 'Italics
oDiagram.Wall.FillColor = RGB(255,255,255)
oChartDoc.HasLegend = True
oLegend = oChartDoc.getLegend()
oLegend.AutomaticPosition = False
oLegend.Alignment = 3
oLegend.CharHeight = 10
oLegend.CharFontName = "Liberation San"
oLegend.FillColor = RGB(225,255,255)
oLegend.CharColor = RGB(0,0,200)
oLegend.CharPosture = 0 'Italics
oLegend.Position = oLegend.getPosition()
oLegend.Position.X = 125
oLegend.Position.Y =1268
oLegend.Size = oLegend.getSize()
oLegend.Size.Width = 1100
oLegend.Size.Height = 1100
' oArea = oDiagram.getDiagram()
' Xray oChartDoc.getLegend()
' Print oDiagram.getDataRowProperties.Value()
ColumnColor0 = oDiagram.getDataRowProperties(0)
ColumnColor1 = oDiagram.getDataRowProperties(1)
aCOLOR = RGB(131, 221, 300)
bCOLOR = RGB(1, 1, 1)
with ColumnColor0
.FillBackground = False
.FillStyle = 1
.FillColor = aCOLOR
end with
with ColumnColor1
.FillBackground = False
.FillStyle = 1
.FillColor = aCOLOR
end with
end sub
I searched how to set position and set size of objects in VBA.
You must declare the position and size for each object independently instead of eg. oTitle.Size.X = 100 ; oTitle.Size.Y = 200 which "common" logic may think.
' XrayTool that helps to investigate VBA objects.
' Found here: https://berma.pagesperso-orange.fr/index2.html
' Download word document and click the button inside to install
' it.
Sub LoadingLibraries
BasicLibraries.LoadLibrary("XrayTool")
End Sub
Sub CreateCalcWithSimpleChart
' Definitions of variables
Dim oSheet 'Sheet containing the chart
Dim oRect 'How big is the chart
Dim oCharts 'Charts in the sheet
Dim oChart 'Created chart
Dim oAddress 'Address of data to plot
Dim sName$ 'Chart name
Dim oChartDoc 'Embedded chart object
Dim oTitle 'Chart title object
Dim oDiagram 'Inserted diagram (data).
Dim sDataRng$ 'Where is the data
Dim oCalcDoc
' it is needed to define Point and Size in order to
' change Position and Size of Chart Objects
Dim Pos_Chart As New com.sun.star.awt.Point
Dim Pos_Title As New com.sun.star.awt.Point
Dim Pos_SubTitle As New com.sun.star.awt.Point
Dim Pos_xTitle As New com.sun.star.awt.Point
Dim Pos_yTitle As New com.sun.star.awt.Point
Dim Pos_Legend As New com.sun.star.awt.Point
Dim Size_Chart As New com.sun.star.awt.Size
Dim Size_Title As New com.sun.star.awt.Size
Dim Size_SubTitle As New com.sun.star.awt.Size
Dim Size_xTitle As New com.sun.star.awt.Size
Dim Size_yTitle As New com.sun.star.awt.Size
Dim Size_Legend As New com.sun.star.awt.Size
' Creation of objects into variables
oCalcDoc = CreateCalcForChart()
sName = "Example_01"
sDataRng = "$E$8:$G$19"
oSheets = ThisComponent.getSheets()
oSheet = oSheets.getbyName("Normality")
oAddress = oSheet.getCellRangeByName( sDataRng ).getRangeAddress()
oCharts = oSheet.getCharts()
' The size of the whole chart
If NOT oCharts.hasByName(sName) Then
oRect = createObject("com.sun.star.awt.Rectangle")
oRect.X = 1
oRect.Y = 1
oRect.width = 200
oRect.Height= 1000
oCharts.addNewByName(sName, oRect, Array(oAddress), True, True)
End If
' Getting the Chart for manipulations
oChart = oCharts.getByName( sName )
oChart.setRanges(Array(oAddress))
oChartDoc = oChart.getEmbeddedObject()
oDiagram = oChartDoc.createInstance( "com.sun.star.chart.ChartDiagram" )
oChartDoc.setDiagram( oDiagram )
oDiagram = oChartDoc.getDiagram()
' Inside the Chart, Setting Position and Size of the Diagram
oChartDoc.RefreshAddInAllowed = True
Pos_Chart.X = 1000
Pos_Chart.Y = 800
Size_Chart.width = 18000
Size_Chart.height = 8000
oDiagram.setPosition( Pos_Chart )
oDiagram.setSize( Size_Chart )
oDiagram.Wall.FillColor = RGB(255,255,255)
' Min / Max scale values on x-y axis plus Font format
oXaxis = oDiagram.getXAxis()
oXaxis.AutoMin = False
oXaxis.AutoMax = False
oXaxis.Min = 1
oXaxis.Max = 5
oXaxis.CharColor = RGB(111,0,200)
oXaxis.CharFontName = "Liberation San"
oXaxis.CharHeight = 8
oXaxis.CharPosture = 0 'Italics
oYaxis = oDiagram.getYAxis()
oYaxis.AutoMin = False
oYaxis.AutoMax = False
oYaxis.Min = 1
oYaxis.Max = 5
oYaxis.CharColor = RGB(111,0,200)
oYaxis.CharFontName = "Liberation San"
oYaxis.CharHeight = 8
oYaxis.CharPosture = 0 'Italics
'Position and Size of x axis Title plus Font format
oXaxis.AxisTitle.string ="ccccc"
oXaxis.AxisTitle.CharColor = RGB(111,0,2)
oXaxis.AxisTitle.CharFontName = "Liberation San"
oXaxis.AxisTitle.CharHeight = 12
oXaxis.CharPosture = 0 'Italics
Pos_xTitle.X = 1000
Pos_xTitle.Y = 800
Size_xTitle.width = 18000
Size_xTitle.height = 8000
oXaxis.setPosition( Pos_xTitle )
oXaxis.setSize( Size_xTitle )
'Position and Size of y axis Title plus Font Format
oYaxis.AxisTitle.string ="ccccc"
oYaxis.AxisTitle.CharColor = RGB(111,0,2)
oYaxis.AxisTitle.CharFontName = "Liberation San"
oYaxis.AxisTitle.CharHeight = 12
oYaxis.CharPosture = 0 'Italics
Pos_yTitle.X = 1000
Pos_yTitle.Y = 800
Size_yTitle.width = 18000
Size_yTitle.height = 8000
oYaxis.setPosition( Pos_yTitle )
oYaxis.setSize( Size_yTitle )
' Formating Chart Title and Chart Subtitle
oTitle = oChartDoc.getTitle()
oTitle.String = "My title"
oTitle.CharColor = RGB(0,0,200)
oTitle.CharFontName = "Liberation San"
oTitle.CharHeight = 18
oTitle.CharPosture = 1 'Italics
oSubTitle = oChartDoc.getSubtitle()
oSubTitle.String = "My title"
oSubTitle.CharColor = RGB(0,0,200)
oSubTitle.CharFontName = "Liberation San"
oSubTitle.CharHeight = 16
oSubTitle.CharPosture = 0 'Italics
'Position and Size of Chart Title and Chart Subtitle
Pos_Title.X = 5000
Pos_Title.Y = 0
Size_Title.width = 0
Size_Title.height = 0
oTitle.setPosition( Pos_Title )
oTitle.setSize( Size_Title )
Pos_SubTitle.X = 5000
Pos_SubTitle.Y = 800
Size_SubTitle.width = 0
Size_SubTitle.height = 0
oSubTitle.setPosition( Pos_SubTitle )
oSubTitle.setSize( Size_SubTitle )
' Formating Chart Legend
oChartDoc.HasLegend = True
oLegend = oChartDoc.getLegend()
oLegend.AutomaticPosition = False
oLegend.Alignment = 3
oLegend.CharHeight = 10
oLegend.CharFontName = "Liberation San"
oLegend.FillColor = RGB(225,255,255)
oLegend.CharColor = RGB(0,0,200)
oLegend.CharPosture = 0 'Italics
' Position and Size of Chart Legend
Pos_Legend.X = 11000
Pos_Legend.Y = 300
Size_Legend.width = 7800
Size_Legend.height = 1100
oLegend.setPosition( Pos_Legend )
oLegend.setSize( Size_Legend )
' xray oDiagram
' Print oDiagram.getDataRowProperties.Value()
' Setting the Colour of bars pper case
ColumnColor0 = oDiagram.getDataRowProperties(0)
ColumnColor1 = oDiagram.getDataRowProperties(1)
aCOLOR = RGB(131, 221, 300)
bCOLOR = RGB(1, 1, 1)
with ColumnColor0
.FillBackground = False
.FillStyle = 1
.FillColor = aCOLOR
end with
with ColumnColor1
.FillBackground = False
.FillStyle = 1
.FillColor = bCOLOR
end with
end sub