Formatting a Chart with a Macro

Stargazer2050 · August 14, 2026, 11:09am

LO 26.2.5.2
Charting with a Macro that includes all of the formatting
Address the chart formats by NAME only
suggested snippets are welcome

Need example code for the formatting:
add 2nd Y axis
assign a range to the 2nd Y axis
color the line (of the range)
adjust the X axis
label text orientation 90deg
naming Series
plot options continue line

Now Includes Inserting Title

Sub CreateProjChart()
	'GlobalScope.BasicLibraries.loadLibrary("MRILib")
	Dim Sheet As Object, Data_Range As Object, Chart As Object
	Dim Charts
	Dim rangos(2)
    Dim oAxis As Variant
    'Dim oXDataSeries.Color as Variant
    'Dim oXDataSeries as object
	Dim pos_size As New com.sun.star.awt.Rectangle
	On Error GoTo Erro
	Sheet = ThisComponent.CurrentController.ActiveSheet
	rangos(0) = Sheet.getCellRangeByName("D9747:D9897").RangeAddress   'Date Range
			'DataSeries =rangos(0)
			'oXDataSeries.Color = RGB(192, 192, 192)
			'oXDataSeries = RGB(192, 192, 192)
			'rangos(0).Color = RGB(192, 192, 192)
		'oXDataSeries.Color = "red"

	rangos(1) = Sheet.getCellRangeByName("G9747:G9897").RangeAddress
	rangos(2) = Sheet.getCellRangeByName("N9747:N9897").RangeAddress
	
	'rangos(0) = Sheet.getCellRangeByName("D130:D200").RangeAddress
	'rangos(1) = Sheet.getCellRangeByName("G130:G200").RangeAddress	
	'rangos(2) = Sheet.getCellRangeByName("N130:N200").RangeAddress

	Charts = Sheet.Charts
	If Charts.hasByName("ProjChart") Then
        MsgBox "Chart exists"
        Exit Sub
    End If	
	With pos_size
    	.X = 90000
        .Y = 3000         
        .Width = 25000
        .Height = 15000 
    End With

    Charts.addNewByName("ProjChart", pos_size, rangos, True, True)
    'Chart = Charts(0).EmbeddedObject
	chart = charts.getByName("ProjChart").EmbeddedObject    
    Chart.setDiagram(Chart.createInstance("com.sun.star.chart.LineDiagram")) 
    'Chart.setDiagram(Chart.createInstance("com.sun.star.chart.XYDiagram"))
   
    Chart.HasLegend = True
 
    Chart.HasMainTitle = True
    Chart.Title.String = "S&P & Mer88"
    Chart.Title.CharColor = cBlack
    Chart.Title.CharFontName = "Liberation Sans"
    Chart.Title.CharHeight = 16    ' Font Size
    Chart.Title.CharWeight = 0     ' Bold in %
	Chart.Title.CharPosture = 0    ' Italics = 1

closeProjChartDialog    
	Exit Sub
	Erro: MsgBox "ERRO " & Error & Chr(10) & "na linha " & Erl
End Sub

ms777 · August 16, 2026, 12:07pm

Hi,

see the below code to get some hint …

Good luck ms777

    oCharts.addNewByName("ProjChart", pos_size, rangos, True, True)
    oChart1 = oCharts.getByName("ProjChart")
    oEmbed	= oChart1.EmbeddedObject
    oDiagram = oEmbed.createInstance("com.sun.star.chart.LineDiagram")
    with oDiagram
    	.HasSecondaryYAxis = True   
		.HasSecondaryYAxisDescription = True   
		.HasSecondaryYAxisTitle = True   
		.getDataRowProperties(1).Axis = com.sun.star.chart.ChartAxisAssign.SECONDARY_Y 
		.SecondaryYAxis.Max = 200
		.SecondYAxisTitle.String = "secondary axis"
    end with
    oEmbed.setDiagram(oDiagram)

chart.ods (24.1 KB)

Stargazer2050 · August 16, 2026, 5:46pm

Ok, I’ve pasted this thing in there, I’m getting an “object variable not set”
I’ve looked at your example but can’t tell what variable i need to set (or how to do it)
could you run this and maybe fix that ?
(the 2nd Y axis will be the rangos(2) ) so I changed that to a 2, hope that’s ok

Sub CreateProjChart()
	Dim Sheet As Object, Data_Range As Object, Chart As Object
	Dim Charts as object
	Dim rangos(2)
    Dim pos_size As New com.sun.star.awt.Rectangle
	On Error GoTo Erro
	
	'Sheet = ThisComponent.CurrentController.ActiveSheet
	 Sheet = ThisComponent.Sheets.getByName("Projection")

	rangos(0) = Sheet.getCellRangeByName("D9747:D9897").RangeAddress   	'Label Range, Date Range
	rangos(1) = Sheet.getCellRangeByName("G9747:G9897").RangeAddress		
	rangos(2) = Sheet.getCellRangeByName("N9747:N9897").RangeAddress	
	'rangos(3) = Sheet.getCellRangeByName("N9747:N9897").RangeAddress
		
	Charts = Sheet.Charts
	If Charts.hasByName("ProjChart") Then
        MsgBox "Chart exists"
        Exit Sub
    End If	
	With pos_size
    	.X = 90000
        .Y = 3000         
        .Width = 25000
        .Height = 15000 
    End With
    
    Charts.addNewByName("ProjChart", pos_size, rangos, True, True)
	chart = charts.getByName("ProjChart").EmbeddedObject    
    Chart.setDiagram(Chart.createInstance("com.sun.star.chart.LineDiagram")) 
    'Chart.setDiagram(Chart.createInstance("com.sun.star.chart.XYDiagram"))
   
    Chart.HasLegend = True
 
    Chart.HasMainTitle = True
    Chart.Title.String = "S&P & Mer88"
    Chart.Title.CharColor = cBlack
    Chart.Title.CharFontName = "Liberation Sans"
    Chart.Title.CharHeight = 16    ' Font Size
    Chart.Title.CharWeight = 0     ' Bold in %
	Chart.Title.CharPosture = 0    ' Italics = 1
	
'''''''  add in the 2nd Y axis	
	oCharts.addNewByName("ProjChart", pos_size, rangos, True, True)
    oChart1 = oCharts.getByName("ProjChart")
    oEmbed	= oChart1.EmbeddedObject
    oDiagram = oEmbed.createInstance("com.sun.star.chart.LineDiagram")
    with oDiagram
    	.HasSecondaryYAxis = True   
		.HasSecondaryYAxisDescription = True   
		.HasSecondaryYAxisTitle = True   
		.getDataRowProperties(2).Axis = com.sun.star.chart.ChartAxisAssign.SECONDARY_Y 
		.SecondaryYAxis.Max = 200
		.SecondYAxisTitle.String = "secondary axis"
    end with
    oEmbed.setDiagram(oDiagram) 

closeProjChartDialog    
	Exit Sub
	Erro: MsgBox "ERRO " & Error & Chr(10) & "na linha " & Erl
End Sub

CRDF · August 16, 2026, 6:41pm

You should approach these things stepwise.

Install the MRI Object Inspector.
Upload a Spreadsheet with the real data in simplified ranges:
D1:D150G1:G150N1:N150
And just this chart of course.

Stargazer2050 · August 16, 2026, 7:11pm

Install error on the latest LO:

(com.sun.star.lang.WrappedTargetRuntimeException) { { { Message = “Bad Zip File, ZipException: Zip END signature not found!”…

I’m getting the same error but thankyou

Stargazer2050 · August 16, 2026, 7:15pm

oh wait i just installed mri 1.3.5 now to figure out how to use it

Stargazer2050 · August 16, 2026, 7:39pm

how would I set the path where is this Tool - Options?

Access Configuration Parameters: Open the settings in LibreOffice.
Locate the Path Setting: Look for the system.openoffice.path parameter.

Stargazer2050 · August 16, 2026, 10:45pm

[quote=“Stargazer2050, post:7, topic:136973”]
system.openoffice.path parameter
[/quote].

system.openoffice.path parameter doesn’t exist in paths
thankyou though, but I’m not this much of a programmer. researcher

CRDF · August 16, 2026, 11:10pm

fpy · August 17, 2026, 3:20am

Debugging a Basic Program

Development Tools

Stargazer2050 · August 17, 2026, 3:37am

oh boy another language. the girls at dunkin’ are trying to teach me spanish

ms777 · August 17, 2026, 3:42pm

Open the attached chart.ods and run the macro … install xray or MRI

bseesing · August 22, 2026, 6:53am

Please see example codes below. The only question I have no example code for is how to turn the label 90 degrees, I have never needed that option.

’ I assume you have set an oDiagram Object like this:
oCharts = wshC.Charts
oChart = oCharts.getByName("").embeddedObject
oChart.Diagram = oChart.createInstance(“com.sun.star.chart.BarDiagram”)
oDiagram = oChart.getDiagram()

’ Adding a Secondary Yaxis
oDiagram.HasSecondaryYAxis = True
oDiagram.HasSecondaryYAxisTitle = True
oYaxis2 = oDiagram.getSecondaryYAxis()
’ After this, you can use all the formatting option for the first axis on the second axis.

’ For assigning a data series to the second axis use something like below in the section where you define the series:
oSeries1.AttachedAxisIndex = 1
’ Use 0 for the primary axis and 1 for the secondary axis

’ Example setting a data series for a line chart
'SERIES 3 Net Worth
oSeries3 = CreateDataSeries_Chart(oDataProvider, Xrange, Y3range, “$Indicators.$A$13”) ’ Example code for CreateDataSeries routine can be found in my answer above. $Indicators.$A$13 refers to a cel in my spreadsheet where the Series Name can be found.
oSeries3.AttachedAxisIndex = 1
oSeries3.Color = RGB(255, 0, 0) ’ Color the Line
oSeries3.LineWidth = 80 ’ Line thickness
oSeries3.LineDashName = “Long Dash” 'Example Dash style
oSeries3.LineStyle = com.sun.star.drawing.LineStyle.DASH 'Example Dash style. Use CONTINUOUS for continuous line.
’ Setting data labels for the series
If ThisComponent.NamedRanges.getByName(“boxDataLabels”).getReferredCells.String = “TRUE” Then
oSeries3.Label = oLabelNumber
oSeries3.LabelPlacement = 2 ’ See LibreOffice: com::sun::star::chart::DataLabelPlacement Constant Group Reference
oSeries3.CharFontName = xLabelFontName
oSeries3.CharHeight = xFontHeight
oSeries3.CharWeight = 0 ’ Bold in %
oSeries3.CharPosture = 0 ’ Italics = 1
oSeries3.CharColor = cBlack
End If
oNewDataSeriesList(2) = oSeries3

Stargazer2050 · August 23, 2026, 9:37am

hi,
getting syntax error on “oChart.createInstance()”

Stargazer2050 · August 23, 2026, 9:41am

oCharts = wshC.Charts

getting “object variable not set”

bseesing · August 23, 2026, 10:10am

OK, we are back to basic(s) here…

WshC is my Charts worksheet. So earlier in my code I have declared

wshC = ThisComponent.Sheets.getByName(“Charts”)

Replace Charts with the name of the worksheet where you want your chart to be.

Stargazer2050 · August 23, 2026, 12:20pm

getting “syntax error”
doesn’t seem to fit in with the posting that works but just need the 2nd Y axis
(I think that’s where we are now)

Sub CreateProjChart()
'GlobalScope.BasicLibraries.loadLibrary("MRILib")
   	Dim Sheet As Object, Data_Range As Object, Chart As Object
	'Dim Chart as object
	Dim oCharts as object
	Dim pos_size As New com.sun.star.awt.Rectangle
    'Dim oDiagram as object
   	Dim rangos(2)
   	Dim oDiagram as object
   	'wshC = ThisComponent.Sheets.getByName(“ProjChart”)
   	 
''''' suggested  
    'oCharts = wshC.Charts
	'oChart = oCharts.getByName("ProjChart").embeddedObject
	'oChart.Diagram = oChart.createInstance(“com.sun.star.chart.BarDiagram”)
	'oDiagram = oChart.getDiagram()
    
	On Error GoTo Erro
	
	'Sheet = ThisComponent.CurrentController.ActiveSheet
	 Sheet = ThisComponent.Sheets.getByName("Projection")

	rangos(0) = Sheet.getCellRangeByName("D9747:D9897").RangeAddress   	'Label Range, Date Range
	rangos(1) = Sheet.getCellRangeByName("G9747:G9897").RangeAddress		
	rangos(2) = Sheet.getCellRangeByName("N9747:N9897").RangeAddress	
	'rangos(3) = Sheet.getCellRangeByName("N9747:N9897").RangeAddress
		
	Charts = Sheet.Charts
	If Charts.hasByName("ProjChart") Then
        MsgBox "Chart exists"
        Exit Sub
    End If	
	With pos_size
    	.X = 90000
        .Y = 3000         
        .Width = 25000
        .Height = 15000 
    End With  
    
    Charts.addNewByName("ProjChart", pos_size, rangos, True, True)
	chart = charts.getByName("ProjChart").EmbeddedObject    
    Chart.setDiagram(Chart.createInstance("com.sun.star.chart.LineDiagram")) 
    'Chart.setDiagram(Chart.createInstance("com.sun.star.chart.XYDiagram"))
   
    Chart.HasLegend = True
 
 'Title and Subtitle of Chart
    Chart.HasMainTitle = True
    Chart.Title.String = "S&P & Mer88"
    Chart.Title.CharColor = cBlack
    Chart.Title.CharFontName = "Liberation Sans"
    Chart.Title.CharHeight = 16    ' Font Size
    Chart.Title.CharWeight = 0     ' Bold in %
	Chart.Title.CharPosture = 0    ' Italics = 1

'''' bseesing
    'oEmbed	= oChart1.EmbeddedObject
    'oDiagram = oEmbed.createInstance("com.sun.star.chart.LineDiagram")
	'	oDiagram.HasSecondaryYAxis = True
	'	oDiagram.HasSecondaryYAxisTitle = True
	'	oYaxis2 = oDiagram.getSecondaryYAxis()
	
goto Jumper1	
	 'wshC = ThisComponent.Sheets.getByName(“Projection”)   	
    'oEmbed	= oChart1.EmbeddedObject
    'oDiagram = oEmbed.createInstance("com.sun.star.chart.LineDiagram")
    with oDiagram
    	Chart.HasSecondaryYAxis = True   
		.HasSecondaryYAxisDescription = True   
		.HasSecondaryYAxisTitle = False   
		.getDataRowProperties(2).Axis = com.sun.star.chart.ChartAxisAssign.SECONDARY_Y 
		.SecondaryYAxis.Max = 200
		.SecondYAxisTitle.String = "secondary axis"
    end with
    oEmbed.setDiagram(oDiagram) 
Jumper1:

closeProjChartDialog  
'formatAxisProjChart  
	Exit Sub
	Erro: MsgBox "ERRO " & Error & Chr(10) & "na linha " & Erl
End Sub

CRDF · August 23, 2026, 1:13pm

As I said above, you should do it stepwise. Start in a spreadsheet with only 1 chart, but the real ranges & data you want in it.
After you achieve all desired formatting without errors, insert the code into the real “production” one.
With MRI you check the Properties and Methods availabes for each object (axis, etc…).
Here is an adaptation of @ms777’s with some formatting in it. Use it with your real ranges
D9747:D9897
G9747:G9897
N9747:N9897
and data:
SimpleChartFormatting.ods (20.6 KB)

Stargazer2050 · August 23, 2026, 9:50pm

Yes 1 chart only
this is well written

tnx
st2050