libreofficelibreoffice-calclibreoffice-basic

Cannot get my chart ‘stacked’ using a Calc macro


Somehow I cannot get my chart to ‘stack’. For reasons explained below I first prepare a standard chart in a macro called ‘ChartsGeneral’. Based on some settings on my ‘Charts’ worksheet, it then calls a specific second macro, e.g. ‘ChartBuildStatus’. In this second macro I generate the category and data-series, and adjust the settings of the standard chart as necessary.

When I try to get a stacked chart via ‘createInstance(“com.sun.star.chart.StackableDiagram”)’ nothing changes. I have studied the documentation and several forums, but I have no clue what I am missing. Code below.

Configuration details Version: 7.4.3.2 (x64) / LibreOffice Community

CPU threads: 12; OS: Windows 10.0 Build 19045; Locale: nl-NL (nl_NL); UI: en-US

OK, here it goes… Please have some mercy on a poor old non-programmer nerd.

Background Over the years I collected a rather large set of Excel spreadsheets with VBA macros for my personal finances. This year I decided to migrate them all to LibreOffice, as part of my wider migration from Windows to Linux. I am not a programmer, so I decided to use BASIC as it would be closest to VBA.

One of my spreadsheets tracks progress on our home mortgage. I keep track of all payments on interest and principal. As we are paying down extra with a fixed monthly payment and a ‘snowball’ (all savings go back as payments to the principal) the spreadsheet also calculates the expected next snowball payment, as well as a prognosis for the remainder of the mortgage. That all works fine.

The key worksheets in my Mortgage spreadsheet are ‘Months’, where I store all the data of past payments, and ‘Prognosis’, which holds a copy of ‘Months’ but also a prognosis for every month in the future.

I am now down to the last block of functionality: reporting. This is mainly a collection of charts that show the history, current status and prognosis for my mortgage based on the data in Months and Prognosis. For this I use a dedicated worksheet called ‘Charts’ in my spreadsheet. Here I can select a Chart Type from a dropdown-list, as well as some date or period related settings (I use named fields for this). See a partial screenshot below.

Partial Screenshot of Charts worksheet

When I press the ‘Update Chart’ button on my Charts worksheet, a macro called ‘ChartsGeneral’ is triggered. In this macro:

  1. I use the date or period related settings to calculate the starting row and ending row for the data series
  2. I then generate an empty standard linechart called ‘AdminChart’ on the Charts worksheet, with settings for the axes, the formatting, and everything
  3. I call a second macro based on the ChartType value on Charts. In the above example the chart is called ‘Opbouw Stand van Zaken’, so I call a second macro called ChartBuildStatus.

The code for ChartsGeneral can be found below

Sub ChartsGeneral()

    'Manages the Charts worksheet
    
    'Set Worksheets
    wshC = ThisComponent.Sheets.getByName("Charts")
    wshM = ThisComponent.Sheets.getByName("Months")
    wshP = ThisComponent.Sheets.getByName("Prognosis")
    wshS = ThisComponent.Sheets.getByName("Settings")
    
    'Define Colours
    cBlack           = RGB(0, 0, 0)
    cGray01          = RGB(50, 50, 50)
    cGray02          = RGB(191, 191, 191)
    cWhite           = RGB(255, 255, 255) 
    
    cDarkRed         = RGB(192, 0, 0)
    cPinkiDEAL       = RGB(204, 0, 102)  'iDeal
    cGreenCash       = RGB(112, 173, 71) 'Cash Green
    cBluePin         = RGB(41, 65, 113)  'PIN Blue
    cBlueContactless = RGB(58, 125, 193) 'Contactloos Light Blue
    cOrangeVisa      = RGB(246, 155, 14) 'Visa Orange

    'Set Parameters
    'Prognosis
    ColumnOriginal = 18 'Original Rest Mortgage
    ColumnRest = 17 'Actual Rest Mortgae
    ColumnPaid = 16 'Reeds afgelost
    ColumnPrognosis = 21 'Prognosis Rest Mortgage
    ColumnInterestSavedPrognosis = 23 'Bespaarde rente prognose
    ColumnInterestSaved = 26 'Bespaarde rente
    ColumnExtraTotal = 14 'Column total extra cumulative
    ColumnPercent = 24
    ColumnPercentPrognosis = 25
    'Months
    XASColumn = 2
    ColumnExtra = 13 'Extra repayment column
    ColumnRegular = 12 'Regular repayment column
    ColumnInterest = 11 'Interest payment column
    ColumnMonthInterest = 3 'Interest payment column
    ColumnMonthRegular = 4 'Regular repayment column
    ColumnMonthTotal = 9 'Total Monthly repayment column
    ColumnSnowball = 7 'Snowball per month column
    ColumnWOZ = 17 'WOZ column
    ColumnMortgage = 16 'Rest of mortgage column
    ColumnVasteLasten = 5 'Regular Interest and Depreciation
    ColumnLTV = 21 'LTV Ratio
    ColumnPercentageRest = 24 'Percentage Rest Mortgage Paid This Month
    ColumnWOZpercentage = 22 'WOZ percentage column
    ColumnMortgagePercentage = 26 'Rest of mortgage percentage column
    
    'X-As Values
    LastMonthRow = getLastContentIndex("Months", 3)
    If wshM.getCellByPosition(1, LastMonthRow).Value = (ThisComponent.NamedRanges.getByName("ChartMonth").getReferredCells.Value + 1) Then
        LastMonthRow = LastMonthRow - 1
    End If
    
    LastMonth = wshM.getCellByPosition(1, LastMonthRow).Value
    LastYear = wshM.getCellByPosition(0, LastMonthRow).Value
    
    xStartRow = 3
    xEndRow = LastMonthRow
    
    If ThisComponent.NamedRanges.getByName("ChartPeriod").getReferredCells.String = "1Y" Then
        StartYear = LastYear - 1
        StartMonth = LastMonth + 1
        If StartMonth = 13 Then
            StartYear = StartYear + 1
            StartMonth = 1
        End If
        For i = xStartRow To LastMonthRow
            If wshM.getCellByPosition(0, i).Value = StartYear And wshM.getCellByPosition(1, i).Value = StartMonth Then
                xStartRow = i
                Exit For
            End If
        Next i
    End If
    
    If ThisComponent.NamedRanges.getByName("ChartPeriod").getReferredCells.String = "2Y" Then
        StartYear = LastYear - 2
        StartMonth = LastMonth + 1
        If StartMonth = 13 Then
            StartYear = StartYear + 1
            StartMonth = 1
        End If
        For i = xStartRow To LastMonthRow
            If wshM.getCellByPosition(0, i).Value = StartYear And wshM.getCellByPosition(1, i).Value = StartMonth Then
                xStartRow = i
                Exit For
            End If
        Next i
    End If
    
    If ThisComponent.NamedRanges.getByName("ChartPeriod").getReferredCells.String = "3Y" Then
        StartYear = LastYear - 3
        StartMonth = LastMonth + 1
        If StartMonth = 13 Then
            StartYear = StartYear + 1
            StartMonth = 1
        End If
        For i = xStartRow To LastMonthRow
            If wshM.getCellByPosition(0, i).Value = StartYear And wshM.getCellByPosition(1, i).Value = StartMonth Then
                xStartRow = i
                Exit For
            End If
        Next i
    End If
    
    If ThisComponent.NamedRanges.getByName("ChartPeriod").getReferredCells.String = "5Y" Then
        StartYear = LastYear - 5
        StartMonth = LastMonth + 1
        If StartMonth = 13 Then
            StartYear = StartYear + 1
            StartMonth = 1
        End If
        For i = xStartRow To LastMonthRow
            If wshM.getCellByPosition(0, i).Value = StartYear And wshM.getCellByPosition(1, i).Value = StartMonth Then
                xStartRow = i
                Exit For
            End If
        Next i
    End If
    
    If ThisComponent.NamedRanges.getByName("ChartPeriod").getReferredCells.String = "10Y" Then
        StartYear = LastYear - 10
        StartMonth = LastMonth + 1
        If StartMonth = 13 Then
            StartYear = StartYear + 1
            StartMonth = 1
        End If
        For i = xStartRow To LastMonthRow
            If wshM.getCellByPosition(0, i).Value = StartYear And wshM.getCellByPosition(1, i).Value = StartMonth Then
                xStartRow = i
                Exit For
            End If
        Next i
    End If
    
    If ThisComponent.NamedRanges.getByName("ChartPeriod").getReferredCells.String = "15Y" Then
        StartYear = LastYear - 15
        StartMonth = LastMonth + 1
        If StartMonth = 13 Then
            StartYear = StartYear + 1
            StartMonth = 1
        End If
        For i = xStartRow To LastMonthRow
            If wshM.getCellByPosition(0, i).Value = StartYear And wshM.getCellByPosition(1, i).Value = StartMonth Then
                xStartRow = i
                Exit For
            End If
        Next i
    End If
    
    If ThisComponent.NamedRanges.getByName("ChartPeriod").getReferredCells.String = "20Y" Then
        StartYear = LastYear - 20
        StartMonth = LastMonth + 1
        If StartMonth = 13 Then
            StartYear = StartYear + 1
            StartMonth = 1
        End If
        For i = xStartRow To LastMonthRow
            If wshM.getCellByPosition(0, i).Value = StartYear And wshM.getCellByPosition(1, i).Value = StartMonth Then
                xStartRow = i
                Exit For
            End If
        Next i
    End If
    
    'Global Variable for BlogMarkers
    ChartStartRow = StartRow
    
    'Generate New Chart
    oCharts = wshC.Charts
    If oCharts.Count <> 0 then
        For i = 0 to oCharts.Count-1
            oChart = oCharts.getByIndex(i)
            If oChart.name = "AdminChart" then 
                oCharts.removeByName("AdminChart")
            End If
        Next i
    End If 
    
    Dim oRange as Object
    Dim oRangeAddress(1) As New com.sun.star.table.CellRangeAddress
    Dim oRect As New com.sun.star.awt.Rectangle
    Dim cTitle as String
    
    '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

    oRange = thisComponent.getCurrentSelection.getRangeAddress
    oRect.Width = 34000
    oRect.Height = 19500
    oRect.X = 8650
    oRect.Y = 20
    
    cTitle = "AdminChart"
    oCharts.addNewByName(cTitle,oRect,oRangeAddress(), TRUE, TRUE)
    oChart = oCharts.getByName(cTitle).embeddedObject
    oChart.Diagram = oChart.createInstance("com.sun.star.chart.LineDiagram") 'LineDiagram
    oDiagram = oChart.Diagram
    
    'Change position and size of chart area in rectangle
    oChart.RefreshAddInAllowed    = True
    Pos_Chart.X                   = 700 
    Pos_Chart.Y                   = 1600
    Size_Chart.Width              = oRect.Width - 1500
    Size_Chart.Height             = oRect.Height - 2500
    oDiagram.setPosition( Pos_Chart )
    oDiagram.setSize( Size_Chart )
    
    'Title and Subtitle of Chart
    'oChart.SplineType = 0
    oChart.HasMainTitle = True
    oChart.Title.String = cTitle
    oChart.Title.CharColor = cBlack
    oChart.Title.CharFontName = "Liberation Sans"
    oChart.Title.CharHeight = 16    ' Font Size
    oChart.Title.CharWeight = 0     ' Bold in %
    oChart.Title.CharPosture = 0    ' Italics = 1

    oChart.HasSubTitle = False
    'oChart.SubTitle.String = "Testing the waters"
    'oChart.SubTitle.CharColor = cBlue
    'oChart.SubTitle.CharFontName = "Liberation Sans"
    'oChart.SubTitle.CharHeight = 12  ' Font Size
    'oChart.SubTitle.CharWeight = 100  ' Bold in %
    'oChart.SubTitle.CharPosture = 0  ' Italics
    
    'Chart Area colours
    oDiagram.Wall.FillStyle = com.sun.star.drawing.FillStyle.SOLID
    oDiagram.Wall.FillColor = cWhite 'Chart Area Colour
    oDiagram.Wall.LineStyle = com.sun.star.drawing.FillStyle.SOLID
    oDiagram.Wall.LineColor = cBlack 'Chart Area Border Colour
    oDiagram.Wall.LineWidth = 20
    
    oChart.Area.FillStyle = com.sun.star.drawing.FillStyle.SOLID
    oChart.Area.FillColor = cWhite 'Full Area Colour
    
    'Horizontal Major Grid Lines
    oDiagram.HasYAxisGrid = True
    oDiagram.YMainGrid.LineStyle = com.sun.star.drawing.FillStyle.SOLID
    oDiagram.YMainGrid.LineColor = cGray02
    oDiagram.YMainGrid.LineWidth = 20
    
    'Vertical Major Grid Lines
    oDiagram.HasXAxisGrid = False
    oDiagram.XMainGrid.LineStyle = com.sun.star.drawing.FillStyle.SOLID
    oDiagram.XMainGrid.LineColor = cGray02
    oDiagram.XMainGrid.LineWidth = 20

    'X-Axis
    oDiagram.HasXAxis             = True
    oDiagram.HasXAxisDescription  = True
    oDiagram.HasXAxisTitle        = True
    oXaxis                        = oDiagram.getXAxis()
    oXaxis.AxisTitle.String       = "X-Values"
    oXaxis.AxisTitle.CharFontName = "Liberation Sans"
    oXaxis.AxisTitle.CharColor    = cBlack
    oXaxis.AxisTitle.CharHeight   = 11    ' Font Size
    oXaxis.AxisTitle.CharWeight   = 0     ' Bold in %
    oXaxis.AxisTitle.CharPosture  = 0     ' Italics = 1
    oXaxis.AutoMin                = True
    oXaxis.AutoMax                = True
    'oXaxis.Min                    = 1
    'oXaxis.Max                    = 5
    oXaxis.CharColor              = cBlack
    oXaxis.CharFontName           = "Liberation Sans"
    oXaxis.CharHeight             = 10              ' Font Size
    oXaxis.CharWeight             = 100             ' Bold in %
    oXaxis.CharPosture            = 0               ' Italics
    oXaxis.LineColor              = cBlack
    oXaxis.LineWidth              = 20
    
    'Primary Y-Axis
    oDiagram.HasYAxis             = True
    oDiagram.HasYAxisDescription  = True
    oDiagram.HasYAxisTitle        = False
    oDiagram.HasYAxis             = True
    oYaxis                        = oDiagram.getYAxis()
    oYaxis.AxisTitle.String       = "Y-Values"
    oYaxis.AxisTitle.CharFontName = "Liberation Sans"
    oYaxis.AxisTitle.CharColor    = cBlack
    oYaxis.AxisTitle.CharHeight   = 11    ' Font Size
    oYaxis.AxisTitle.CharWeight   = 0     ' Bold in %
    oYaxis.AxisTitle.CharPosture  = 0     ' Italics = 1
    oYaxis.AutoMin                = False
    oYaxis.AutoMax                = False
    oYaxis.Min                    = 0
    oYaxis.Max                    = 2000
    oYaxis.StepMain               = 250
    oYaxis.CharColor              = cBlack
    oYaxis.CharFontName           = "Liberation Sans"
    oYaxis.CharHeight             = 10              ' Font Size
    oYaxis.CharWeight             = 0               ' Bold in %
    oYaxis.CharPosture            = 0               ' Italics
    oYaxis.LineColor              = cBlack
    oYaxis.LineWidth              = 20
    oYaxis.LinkNumberFormatToSource = False
    oYaxis.NumberFormat           = "114"
    
    oChart.HasLegend              = 1
    oLegend                       = oChart.getLegend()  
    oLegend.AutomaticPosition     = True
    oLegend.Alignment             = com.sun.star.chart.ChartLegendPosition.BOTTOM
    oLegend.CharHeight            = 10           ' Font Size
    oLegend.CharWeight            = 0            ' Bold in %
    oLegend.CharFontName          = "Liberation Sans"
    oLegend.FillColor             = cWhite
    oLegend.LineColor             = cWhite
    oLegend.CharColor             = cBlack
    oLegend.CharPosture           = 0            'Italics

    oDiagram.Lines = True
    oDiagram.LineColor = cDarkRed
    oDiagram.LineWidth = 20
    
    'oChart.DataSourceLabelsInFirstColumn = True
    'oChart.DataSourceLabelsInFirstRow = False
    
    'Call Chart Procedure
    If ThisComponent.NamedRanges.getByName("ChartType").getReferredCells.String = "Opbouw Stand van Zaken" Then
        cTitle = "Opbouw Eigen Woning " & ThisComponent.NamedRanges.getByName("ChartPeriod").getReferredCells.String
        Call ChartBuildStatus(xStartRow, xEndRow, cTitle)
    End If

End Sub

Once the macro ChartBuildStatus is called, that macro contains the following actions:

  1. I change the chart type of AdminChart to AreaDiagram. That shows up correctly on my screen.
  2. I then try to make it a stacked diagram. But it won’t…
  3. After that, I change some formatting characteristics of the chart, like axis titles and number formats
  4. And I generate the category and data series calling on two other macro’s, CreateDataSequence() and CreateDataSeries_Chart(). I don’think they are the problem, they work correctly with other non-stacked line charts and the series

I have tried a number of things, including moving the stacking code to the end of the macro. But I cannot get it to work correctly. I must be overlooking something or doing something very stupid. So after literally days of trying I am pinning my hopes on the community. Anybody who can shed some light on this?

Sub ChartBuildStatus(xStartRow As Long, xEndRow As Long, cTitle As String)

    'Define Colours
    cBlack           = RGB(0, 0, 0)
    cGray01          = RGB(50, 50, 50)
    cGray02          = RGB(191, 191, 191)
    cWhite           = RGB(255, 255, 255) 
    
    wshC = ThisComponent.Sheets.getByName("Charts")
    wshM = ThisComponent.Sheets.getByName("Months")
    
    oCharts = wshC.Charts
    oChart = oCharts.getByName("AdminChart").embeddedObject
    oChart.Diagram = oChart.createInstance("com.sun.star.chart.AreaDiagram")
    
    'Stackable Diagram
    oChart.Diagram = oChart.createInstance("com.sun.star.chart.StackableDiagram")
    oDiagram = oChart.getDiagram()
    oChart.setDiagram(oDiagram)
    oDiagram = oChart.getDiagram()
    
    With oDiagram
        .Stacked  = True
        .Percent  = False
        .Vertical = False
    End With
        
    ChartUnit = 50000
    
    
    'Specific Chart Adjustments
    oXaxis = oDiagram.getXAxis()
    oYaxis = oDiagram.getYAxis()
    
    'Ymax And Ymin
    oYaxis.AutoMax                = True
    oYaxis.Min                    = 0
    
    'Chart details
    oChart.Title.String           = cTitle
    oDiagram.HasXAxisTitle        = False
    oYaxis.AxisTitle.String       = "EUR"
    oYaxis.StepMain               = ChartUnit
    oYaxis.NumberFormat           = "115" ' #,##0
    
    
    'Data Series Generator
    oDataProvider = oChart.getDataProvider()
    oDiagram = oChart.getFirstDiagram()
    oCooSys = oDiagram.getCoordinateSystems()
    oCoods = oCooSys(0) ' this chart has only a coordinate system
    oChartTypes = oCoods.getChartTypes() ' chart type one by one
    oChartType = oChartTypes(0)
 
    'Data Ranges
    xEndRow = getLastContentIndex ("Months", 3 )
    Xrange = "Months.C" & (xStartRow + 1) & ":C" & (xEndRow + 1)  'yyyymm
    Y1range = "Months.S" & (xStartRow + 1) & ":S" & (xEndRow + 1) 'Overwaarde
    Y2range = "Months.T" & (xStartRow + 1) & ":T" & (xEndRow + 1) 'Eigen Geld
    Y3range = "Months.M" & (xStartRow + 1) & ":M" & (xEndRow + 1) 'Reguliere Aflossing
    Y4range = "Months.N" & (xStartRow + 1) & ":N" & (xEndRow + 1) 'Extra Aflossing
    Y5range = "Months.Q" & (xStartRow + 1) & ":Q" & (xEndRow + 1) 'Restant Hypotheek
    
    'Prepare for Data Series
    oDataProvider = oChart.getDataProvider()
    oDiagram = oChart.getFirstDiagram()
    oCooSys = oDiagram.getCoordinateSystems()
    oCoods = oCooSys(0) ' this chart has only a coordinate system
    oChartTypes = oCoods.getChartTypes() ' chart type one by one
    oChartType = oChartTypes(0)
    
    'X-Axis Data series
    dim categorySequence as object, categoryRange as string
    dim coordinateSystem as object, axis as object
    dim scaleData as new com.sun.star.chart2.ScaleData
    categorySequence = CreateUnoService("com.sun.star.chart2.data.LabeledDataSequence")
    categorySequence.setValues(CreateDataSequence(oDataProvider, Xrange, "categories"))
    coordinateSystem = oDiagram.getCoordinateSystems()(0)
    axis = coordinateSystem.getAxisByDimension(0, 0)
    scaleData = axis.getScaleData()
    scaleData.Categories = categorySequence
    axis.setScaleData(scaleData)

    'Y-Axis Data series
    oDataSeriesList = oChartType.getDataSeries()  
    Dim oNewDataSeriesList(4) As Object ' new data series
    
    oSeries1 = CreateDataSeries_Chart(oDataProvider, Xrange, Y1range, "Overwaarde")
    oSeries1.Color = RGB(197, 224, 180)
    oSeries1.LineWidth = 60
    oNewDataSeriesList(0) = oSeries1
    
    oSeries2 = CreateDataSeries_Chart(oDataProvider, Xrange, Y2range, "EigenGeld")
    oSeries2.Color = RGB(0, 176, 80)
    oSeries2.LineWidth = 60
    oNewDataSeriesList(1) = oSeries2
    
    oSeries3 = CreateDataSeries_Chart(oDataProvider, Xrange, Y3range, "Aflossing")
    oSeries3.Color = RGB(146, 208, 80)
    oSeries3.LineWidth = 60
    oNewDataSeriesList(2) = oSeries3
    
    oSeries4 = CreateDataSeries_Chart(oDataProvider, Xrange, Y4range, "AflossingExtra")
    oSeries4.Color = RGB(255, 215, 0)
    oSeries4.LineWidth = 60
    oNewDataSeriesList(3) = oSeries4
    
    oSeries5 = CreateDataSeries_Chart(oDataProvider, Xrange, Y5range, "RestHypotheek")
    oSeries5.Color = RGB(192, 0, 0)
    oSeries5.LineWidth = 60
    oNewDataSeriesList(4) = oSeries5
    
    'Update chart (only the charttype is updated)
    oChartType.setDataSeries(oNewDataSeriesList)

End Sub

On the left the chart as it looks now in my LibreOffice Calc spreadsheet, on the right the chart as I want it to look based on my current Excel sheet.

Left: LibreOffice / Right: Excel (how I want it)

For the sake of completeness, please find the additional macro’s CreateDataSequence() and CreateDataSeries_Chart() below.

CreateDataSequence()

Function CreateDataSequence( oDataProvider As Object, sRangeRepresentation As String, sRole As String ) As Object
  
  Dim oDataSequence As Object
  On Error GoTo Handler
  ' create .chart2.data.DataSequence from range representation
  oDataSequence = oDataProvider.createDataSequenceByRangeRepresentation(sRangeRepresentation)
  If NOT IsNull(oDataSequence) Then
    oDataSequence.Role = sRole
  End If
  
  Handler:
    CreateDataSequence = oDataSequence

End Function

CreateDataSeries_Chart()

Function CreateDataSeries_Chart( oDataProvider As Object, sXRangeRepresentation As String, sYRangeRepresentation As String, sLabelRangeRepresentation As String ) As Object

  Dim oNewDataSeries As Object

  oNewDataSeries = CreateUnoService("com.sun.star.chart2.DataSeries")
  
  Dim oData(1) As Object ' x and y: .chart2.data.XLabeledDataSequence
  
  ' Y
  oDataY = CreateUnoService("com.sun.star.chart2.data.LabeledDataSequence")
  oSequenceY = CreateDataSequence(oDataProvider, sYRangeRepresentation, "values-y")
  
  If NOT IsNull(oSequenceY) Then
    
    oDataY.setValues(oSequenceY)
    
    If NOT ((IsMissing(sLabelRangeRepresentation)) AND (sLabelRangeRepresentation <> "")) Then
      oSequenceLabel = CreateDataSequence(oDataProvider, sLabelRangeRepresentation, "label")  ' ""
      oDataY.setLabel(oSequenceLabel) ' oSequenceLabel label is used as name
      
    End If
  
  End If
  
  ' X
  oDataX = CreateUnoService("com.sun.star.chart2.data.LabeledDataSequence")
  oSequenceX = CreateDataSequence(oDataProvider, sXRangeRepresentation, "values-x")
  If NOT IsNull(oSequenceX) Then
    oDataX.setValues(oSequenceX)
  End If
  
  ' set x and y data to series
  aData = Array(oDataY, oDataX)
  oNewDataSeries.setData(aData)
  
  CreateDataSeries_Chart = oNewDataSeries

End Function

Solution

  • Answering my own question... To maybe help someone else. After studying the documentation I realised that I was calling the Stackable code too early. I removed all code related to the stackable diagram from Sub ChartsGeneral. And after the code snippet:

    'Update chart (only the charttype is updated)
    oChartType.setDataSeries(oNewDataSeriesList)
    

    I added this code:

    'Make the Chart Stacked...
    oChart.Diagram = oChart.createInstance("com.sun.star.chart.StackableDiagram")
    oChart.Diagram.Stacked = False
    oChart.Diagram.Percent = True
    oChart.Diagram.Vertical = False
    

    And then it worked.