Chart error

[Rasm]

If you run the code below - it generates a chart - if you then click on one of the datapoints on the chart - you get MS Excel message - text too long - If I only chart 1000 datapoints - I get now error - but with 2K I do - any idea?

Public Sub AddChartX1()
    Dim NumCharts As Long
    Dim PlotCounter As Long
    Dim DataPtsInChart As Long
    Dim TempValues() As Single
    DataPtsInChart = 2000
    ReDim TempValues(1 To DataPtsInChart)
    For i = 1 To DataPtsInChart
        TempValues(i) = i
    Next i
    ActiveSheet.ChartObjects.Add Left:=100, Top:=50, Width:=800, Height:=300
    NumCharts = ActiveSheet.ChartObjects.Count
    
    ActiveSheet.ChartObjects(NumCharts).Activate
    PlotCounter = 0
    Dim Achart As ChartObject
    Set Achart = ActiveSheet.ChartObjects(NumCharts)
   
    With Achart.Chart
        .ChartType = xlLineMarkers
        .SeriesCollection.NewSeries
        PlotCounter = PlotCounter + 1
         
        .Axes(xlCategory).TickMarkSpacing = UBound(TempValues) / 60
        .Axes(xlCategory).TickLabelSpacing = UBound(TempValues) / 60
        
        .SeriesCollection(PlotCounter).Values = Array(TempValues)
          
        .SeriesCollection(PlotCounter).Border.LineStyle = xlHairline
        .SeriesCollection(PlotCounter).MarkerStyle = xlMarkerStyleNone
       
        .HasLegend = False
        
  
    End With
    Set Achart = Nothing
    
End Sub

 

[GlennUK]

Can I ask why you aren't charting from a worksheet range?

 

[Rasm]

I cannot use a range statement as my string would be more than 255 characters long - I have appox 100K rows in my sheet and these are process data - so often I have to skip rows - say I only want to only chart product X - and we make that 6 times a day in runs of 20 minutes - but make other products in between - or we had a problem and I have to ignore 10 minuttes of data.

The 100K rows represents months of data - so I may have 200 Blocks of data - each with its own range - so I use an array to get them into a single SeriesCollection

 

[Rasm]

ohhh - the code example here is simply to show and troubleshoot the problem

 

[GlennUK]

ohhh - the code example here is simply to show and troubleshoot the problem

Great.

Anyway, if you want to chart a subset of data, I would think that a simple Data Filter would give you what you want. Why exactly can't you do that?

 

[Rasm]

Glenn
I appreciate you inputs - but as I said - I may have 200 subsets of data - so it is no simple matter to create a filter - the 200 subsets are strung togeteher in one long array - so I can chart them as a single seriesCollection. For these 200 sets I also plot various parameters - some of which have cells <> vbNullString for all obersvations - some do not.

I definitely have to use arrays for this purpose - but as you can see arrays has some problems or what I perceive as a bug - but I am hoping I am wrong. The code here is simply to illustrate and reproduce the problem.

 

[GlennUK]

You seem determined that you must use an array. I still think that putting your data into a worksheet range will solve all your problems, regardless of how it gets there. Will you even entertain the idea that this may be a solution? If so, then it's just a matter of populating a relevant range of cells ... maybe advanced filtering could do ... or even write them from your array.

 

[Rasm]

Glenn
Your persistence is much appreciated - but I am simply providing a XLAM file that is then used by my coworkers around the world. They make the worksheets manipulated by the XLAM file - the data stream is approx 17K measuremnts (rows) per week - the data has to be in chronological order as this is data representing productline data - my measurements is for 5-8 parameters (not including Date/time stamp, Measurement identifier, Status string, position number, Product code and production line) - that is before I do any moving averages and other manipulations to the data - which significantly expands the number of columns in my sheet. So sorry - But I need to use arrays.