1

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
Community
  • 1
  • 1
MrBaracuda
  • 13
  • 3

1 Answers1

0

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.

Rory
  • 32,730
  • 5
  • 32
  • 35
  • I don't undestand what you mean by the SERIES formula. However, I thought the advantage of using arrays instead of ranges is that I don't need the datapoints in a worksheet, which saves on calculation speed. Especially if large numbers of datapoints are used (>100.000). – MrBaracuda Nov 01 '17 at 13:34
  • Every series plotted on a chart is done via a SERIES formula - you will see it in the formula bar when you select the series, except when you use large arrays - then you'll see an error message. If you use arrays, the literal values have to be put into an array constant in the formula. If you try and plot that much data on a chart, I think you will always have performance problems at the very least. – Rory Nov 01 '17 at 14:14