Hiding some Charts' data points not working

Hello,

My aim is to create a chart showing the number of kilometres cycled per week and comparing the figures for the current year with those for the previous years. Nothing too complicated. In Excel, I have a macro that populates the chart using arrays rather than cell ranges.

So I tried converting my Excel file to a Calc file and rewriting the macros. Initially, everything went fine. I created a chart in Calc, copied it to Writer, and then copied it back to Calc in order to get an editable Data Table. See this post

The problem I’m encountering is the following: the line representing the current year should not show data points that lie in the future, whereas the other lines should show all weeks.

After creating the chart as described above, I manually cleared the cells in the second column, from row 44 to row 51, of the Data Table.

I then retrieved the data using:

aData = oChart.EmbeddedObject.Data.getData()

Now, the array elements aData(43)(0) through aData(50)(0) all contain the value 2.2250738585072E-308.

So I concluded that, in order to hide the future data points, I simply had to assign this value to the corresponding array elements. Right? Well, it turned out that wasn’t the case. When I write the array back to the Data Table, these elements are kind of converted (?). As a result, the data points are not hidden; instead, the line drops to zero. And now I’m stuck.

Here’s the code, stripped to the bare minimum:

Option Explicit

Sub TryToHideSomeDataPoints()
Dim oChart As Object, aData() As Variant, i As Integer
 
oChart = Thiscomponent.Sheets.getByName("Graph").Charts.getByIndex(0)
 
 'Retrieve the data from the DataTable.'
aData = oChart.EmbeddedObject.Data.getData()
 
'Put the data back without modification'
oChart.EmbeddedObject.Data.setData(aData)
 
msgbox "The graph has not changed"
 
'Give the elements for datapoints to hide the value (which they already have !?) '
For i = 43 To 50
     'aData(i)(0) = 2.2250738585072E-308 'Throws a runtime error.
     aData(i)(0) = 2.2250738585072 * (10 ^ -308)
Next
 
'Put the array back into the DataTable.'
oChart.EmbeddedObject.Data.setData(aData)

msgbox "The graph has changed, due to putting back the same values (?)"
 
End Sub

TestChart.ods (38.2 KB)

Can anybody offer any suggestions?

Your Chart looks fine WITHOUT any Macro!

with

Version: 26.8.1.1 (AARCH64)
Build ID: bf6ef2662401c7d6dce671ab4deb26ee0af3a7c1
CPU threads: 4; OS: Linux 6.18; UI render: default; VCL: gtk3
Locale: de-DE (de_DE.UTF-8); UI: de-DE
Calc: threaded

Of course it looks fine. It had better be. I created it manually. :slightly_smiling_face:

I think you misunderstood my problem or, more likely, I failed to explain it properly. As I already mentioned, I stripped it down to the bare minimum to focus solely on the essentials of my problem, which is: What can I do with the DataArray to hide certain data points?

And yes, it looks fine without any macro, but the data on which the chart is based may change. What if I want to compare 2026 with 2020, or 2026 with 2016? Or what if I want to compare my data with someone else’s? There are several possible combinations, and I’m not keen on having to modify the chart manually for every possible combination.

So I do need a macro, which actually works fine. I’ve simply left out those parts for the sake of simplicity and clarity. The only thing I’m stuck on is how to hide certain “future” data points.

By the way, I have a working project based on a chart that references a range of cells in a worksheet. In that case, I can hide the necessary data points by calling the ClearContents() method on the relevant cells. However, this involves using a temporary worksheet, which I then have to hide or otherwise prevent its data from being displayed, so I thought using arrays would be a more elegant solution. Hence my question…