I'm creating a chart where the ".Values" are based on an array called 'ReliabilityNumber', which comes from another module. Everything works fine in getting the right datapoints. But whenever the number of elements ('timesteps' in chart) in the array is more than 16384, the datapoints above this number are not displayed in the chart.
Furthermore, if the number of elements exceeds 65536, the chart isn't displayed anymore.
My take is that it has something to do with maximum number of columns (which is 16384 for Excel 2007 and newer) and max number of rows (which is 65536 in excel version 2003 and older). But I can't figure out what it is. My code is below (I use Excel 2010 at work and 2016 at home, the problem is with 2010. Haven't controlled this problem for 2016 yet):
Dim ReliabilityChart As Object
Dim ReliabilitySeries As Series
Set ReliabilityChart = ActiveSheet.ChartObjects.Add(Left:=300, Width:=500, Top:=10, Height:=300)
With ReliabilityChart
.Chart.Type = xlLine
.Left = 600
.Width = 800
.Top = 50
.Height = 300
.Name = "ReliabilityChart1"
Set ReliabilitySeries = .Chart.SeriesCollection.NewSeries
With ReliabilitySeries
.Values = ReliabilityNumber
.Name = "Reliability on timestep #"
End With
End With
ActiveSheet.ChartObjects("ReliabilityChart1").Activate
With ActiveChart
.ChartType = xlLine
.ChartTitle.Text = "Reliability over time"
.Axes(xlCategory).TickMarkSpacing = 1600
.Axes(xlCategory).TickLabelSpacing = 1600
.Axes(xlValue).MaximumScale = 1
.Axes(xlValue).TickLabels.NumberFormat = "0%"
.Axes(xlCategory).HasTitle = True
.Axes(xlCategory).AxisTitle.Text = "Timestep (#)"
.Axes(xlValue).HasTitle = True
.Axes(xlValue).AxisTitle.Text = "Reliability (%)"
End With
Sheets(1).Range("E10").Select
I think your issue is to do with the length of the SERIES formula that is used to plot the chart series. If you load the array into a worksheet and use that as the source for the series, it should work fine.