Using 2D arrays to create charts.

[Mightyginger]

Mastered using a 1D array to generate my chart but I'm now trying to ammend it to use a 2D array now because now I'd like to create a multiple plot chart instead.

1D: Code I had originally to create chart

Sheet1.ChartObjects.Delete
Set Cht = Charts.Add
Set Cht = Cht.Location(Where:=xlLocationAsObject, Name:="Main&quot
With Cht
.ChartType = xlLine
'This just fills the graph with something (just anything so I can replace data in code below)
.SetSourceData Source:=Sheet2.Cells(1, 1), PlotBy _
:=xlRows
.HasLegend = False
.HasDataTable = False
With .Parent
.Top = Range("Y2&quot.Top
.Left = Range("Y2&quot.Left
End With
End With

'This bit here fills the chart with the data stored in the arrays earlier.
For Each Ser In Cht.SeriesCollection
Ser.Values = Values
Ser.XValues = Dates
Next Ser

**End of code.

Values and Dates are the original 1D arrays. I just need to know the correct syntax for referring to 2D arrays. My new 2D arrays work and are filled with data but how do I chart Values(1,allthevalues),Values(2,andallthese),..etc. The X-axis is fine - I don't need to use the 2-D dates array for that.

Proposed new code (which doesn't work)

For Each Ser In Cht.SeriesCollection
Do Until Var = Sheet1.cboMain.Value + 1
ActiveChart.SeriesCollection(Var).Values = Values(Var)
Var = Var + 1
Loop
Ser.XValues = Dates
Next Ser

Exit Sub

A little explanation. cboMain is a combobox the value of which is the number of instruments I need to graph. Based on this I need to loop the part for the SeriesCollection. Main line I need help with is
ActiveChart.SeriesCollection(Var).Values = Values(Var)
how do I correctly address Values(1,x),Values(2,x) etc.


Thanks for your help and I hope my post makes sense.


Neil.

 

[SkipVought]

Hi,

Try this...

    For j = LBound(vResults, 1) To UBound(vResults, 1)
        For Each ser In cht.SeriesCollection(j)
            ser.Value = vResults(j, i)
        Next
    Next j

where vResults is the array. You may have to switch the indeces.

hope this helps Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[RobBroekhuis]

I know of no one-step way to extract a 1D array from a 2D array, which is what you need (because each series.values is a 1D object). You can use the following sub to populate a 1D array:

Sub Get1DArray(a, icol As Integer, c() As Double)
Dim i As Integer
ReDim c(UBound(a, 2) - LBound(a, 2))
For i = LBound(a, 2) To UBound(a, 2)
c(i) = a(icol, i)
Next i
End Sub

where a is your 2D array, icol is the first dimension which you hold fixed, and c is a reference to your 1D array.
Rob

 

[Mightyginger]

Thanks for your help but I still have a problem.

Firstly - why I used 2 array in the first place. I've got prices for several instruments and I need to graph these prices against each other. I didn't know how to use a loop to create a series of 1D arrays called Array1,Array2 etc. where each Array is filled with the price for that particular. That's why I used a 2D array so that I could just increment the first index up for each of the instruments. So mapping the 2D array to a series of 1D arrays leaves me with the same problem - how do I then create a series of 1D arrays called Array1,Array2 etc.

Does this all make sense?

Tried looking at your code Skip but can't get that to do the job for me either although that approach would be preferable. Wasn't taking the For Each Ser In Cht.SeriesCollection(j) statement.

Don't mean to be a pain - any help would be appreciated. Most of the books I have (Microsoft & Wrox) don't really cover manipulating charts with 2D arrays (they do mention 1D though to be fair).

Neil.

 

[RobBroekhuis]

Have you tried using the sub I suggested? Here's how you might go about it:

dim TempArray() as double, i as integer
...
i=0
For Each Ser In Cht.SeriesCollection
Ser.XValues = Dates
i=i+1
Get1DArray Values, i, TempArray
Ser.Values=TempArray
Next Ser

Rob

 

[Mightyginger]

okay, I'll give that shot. Just to bounce ideas though - just to answer my question about mapping 2D to 1D I could just name the array? That way I could set the array name equal to some arbitary name + some running variable (so just concatenate the two together).

 

[RobBroekhuis]

No, you can't build variable names from string manipulation in VBA. Of course you can use a select case statement to do something similar, e.g. (if i is your running variable):

select case i
case 1: MyArray1(j)=range("A1&quot
case 2: MyArray2(j)=range("A1&quot
...etc...
end select

But that gets tedious. I don't know of a way in VBA (unlike many more sophisticated programming environments) to overlay one variable over the structure of another, to capture just part (say, one dimension) of the larger variable.
Rob

 

[Mightyginger]

Rob that code is great and works a treat BUT it's only charting one series. Sorry to get spoon-fed on this but how to set it up to add more series to it?

I've used your sub, thanks, and here's the code you gave me

For Each Ser In Cht.SeriesCollection
'Rob, on the above line, do you think it see it as only having 1 series? When I step through it the program just goes through it once. Is the trick to define the number of series somewhere?
Ser.XValues = Dates
i = i + 1
Module1.Get1DArray Values, i, TempArray
Ser.Values = TempArray
Next Ser

In step into mode I've gone back over than section of code once it's created the chart for the first time and what it does is chart the second arrays data on the chart as it is now TempArray. How can I set up multiple Ser.Values?

Thanks a million for your help,


Neil.

 

[Mightyginger]

Rob, yet again, thought I'd got a bit further with this problem and changed the line
Ser.Values = TempArray
to
ActiveChart.SeriesCollection(i).Values = TempArray
this works fine for 1 but after that fails saying the object is undefined. Any idea how I might go about defining more series?

 

[RobBroekhuis]

ActiveChart.SeriesCollection.add

is one way to do it. Look up the add method in help to understand the parameters you need to use. Another way to do it is to use the SetSourceData method (which you already have used) with a range that has as many columns as you need data series, plus one (for the x-value column).
Rob

 

[Mightyginger]

cheers for your help Rob you've been a star. Should have it sorted now.



Neil.