CODEWITHSUNDEEP
excelvbadynamic-arrays
MrYuchiang
MrYuchiang

Reputation: 23

Storing SeriesCollection Values in dynamical array

I have the following issue: I create a chart from values that a user will insert in specific cells of a spreadsheet and the chart displays what I expect (The series are collected in the right way). The Series are created consecutively with 2 data points only. I want to store the Xvalues (And YValues) of the series in dynamic arrays for performing calculations later (outside the series collection loop). I tried the following but the arrays only store the last values of the Series (despite using redim preserve everywhere).

Is it because the array is not redimensioned correctly?

Thanks for your help!

i = 1
SeriesAddedA = 1
RangeOccupiedA = Range("A2").End(xlDown).Row
RangeOccupiedB = Range("B2").End(xlDown).Row
MaxRangeOccupied = WorksheetFunction.Max(RangeOccupiedA, RangeOccupiedB)
Dim XValuesA()
Dim YValuesA()
ReDim XValues(RangeOccupiedA + 2)
ReDim YValues(RangeOccupiedA + 2)


Do While i < MaxRangeOccupied
    FilledCompA = NextFilled(Cells(i, 1))
    PrevCell = PrevFilled(Cells(i + 1, 1))
    rowdiff = FilledCompA - PrevCell
    
    If rowdiff < 2 And PrevCell <> 0 And FilledCompA <> 0 Then
        ReDim Preserve XValuesA(1 To RangeOccupiedA + 2)
        chart.SeriesCollection.NewSeries
        chart.FullSeriesCollection(SeriesAddedA).Name = "Reaction Step A " & SeriesAddedA - 1
        chart.FullSeriesCollection(SeriesAddedA).XValues = "=" & "'" & ActiveSheet.Name & "'" & "!$E$" & FilledCompA - 1 & ":$E$" & FilledCompA
        chart.FullSeriesCollection(SeriesAddedA).Values = "=" & "'" & ActiveSheet.Name & "'" & "!$M$" & FilledCompA - 1 & ":$M$" & FilledCompA
        XValuesA() = chart.SeriesCollection(SeriesAddedA).XValues
        SeriesAddedA = chart.SeriesCollection.Count + 1
    ElseIf PrevCell <> 0 And rowdiff > 0 Then
        ReDim Preserve XValuesA(1 To RangeOccupiedA + 2)
        chart.SeriesCollection.NewSeries
        chart.FullSeriesCollection(SeriesAddedA).Name = "Reaction Step prev A " & SeriesAddedA - 1
        chart.FullSeriesCollection(SeriesAddedA).XValues = "=" & "(" & "'" & ActiveSheet.Name & "'" & "!$E$" & FilledCompA - rowdiff & "," & "'" & ActiveSheet.Name & "'" & "!$E$" & FilledCompA & ")"
        chart.FullSeriesCollection(SeriesAddedA).Values = "=" & "(" & "'" & ActiveSheet.Name & "'" & "!$M$" & FilledCompA - rowdiff & "," & "'" & ActiveSheet.Name & "'" & "!$M$" & FilledCompA & ")"
        XValuesA() = chart.SeriesCollection(SeriesAddedA).XValues
        SeriesAddedA = chart.SeriesCollection.Count + 1
    End If
loop

'the debug print says: 
XvaluesA(1) 310.52 
XvaluesA(2) 408.58

Upvotes: 0

Views: 101

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57743

With XValuesA() = chart.SeriesCollection(SeriesAddedA).XValues you overwrite the data in XValuesA everytime you call it in your loop. That is why it only contains the last values.

Use XValuesA(i) = chart.SeriesCollection(SeriesAddedA).XValues instead and then

Debug.Print XvaluesA(1)(1)
Debug.Print XvaluesA(1)(2)

gives the first values and

Debug.Print XvaluesA(2)(1)
Debug.Print XvaluesA(2)(2)

the second …

Upvotes: 1

Related Questions