Using other than Range as SetSourceData method argument

[CKa]

Hi.

I have been trying to find another way to populate a chart in some VBA code
than using a range as a argument for the SetSourceData method of the Chart
object.

I get a lot of external data (from a database) into an array of 2 dimensions
(x and y). Is it possible to use this array to as a datasource without first
having to populate some cells with the array and then use these cells as a
Range property for populating a chart?

Thanks in advance,

CKa.

 

[Jon Peltier]

It takes a little more work. In VBA, set up the X and Y value arrays for
each series. Then add the series one by one:

Dim Srs as Series
With ActiveChart.SeriesCollection
Set Srs = .NewSeries
With Srs
.Values = Yarray
.XValues = Xarray
End With
End With

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[CKa]

Thank you for your help.

Your VBA code example did exactly what I was looking for.

Best regards,

CKa.

 

[Jon Peltier]

Glad to have helped.

- Jon

Thank you for your help.

Your VBA code example did exactly what I was looking for.

Best regards,

CKa.

 

[micr1]

I have similar problem

Hi ther Jon,

I have use the method you describes to allocate Arrays to SeriesCollections values but the chart will not accept more than 256 values....I'm kinda stuck here, 2 days trying to get around this... Here is my VBA code, I use Excel 2003 and 2007, on XP Home Sp3. I checked and the csv file I load to the Arrays works fine.

Thanks for the help!

______________________________________________________________

Public StkLastRow, StkLastColumn As Long
Public DIR As String
Public STOCK As String
Public CsvStockTable() As Variant


Sub ADD_Graphique()

Dim ArClose(), ArVolume(), ArOpen(), ArHigh(), ArLow(), ArAdjClose() As Double
Dim SVolume, SClose, SOpen, SHigh, SLow, SAdj As Variant
Dim ArDate() As Variant

Dim p As Variant
Dim i, Index As Integer

DIR = ActiveWorkbook.Path & "\"

STOCK = "SSS.V"

Load_CSV

'Nbr_Jours = 25

NRows = UBound(CsvStockTable, 1)
NColumns = UBound(CsvStockTable, 2)

If IsEmpty(Nbr_Jours) Then Nbr_Jours = NRows - 1
i = NRows - Nbr_Jours


Index = 0
ArrayDim = Nbr_Jours - 1

ReDim ArDate(ArrayDim)
ReDim ArClose(ArrayDim)
ReDim ArVolume(ArrayDim)
'ReDim ArOpen(ArrayDim)
'ReDim ArLow(ArrayDim)
'ReDim ArHigh(ArrayDim)
'ReDim ArAdjClose(ArrayDim)

Do
ArDate(Index) = CsvStockTable(i, 0)
'ArOpen(Index) = CsvStockTable(i, 1) * 100 / 100 ' I can't get the decimals to appear in the values of the graphic unless I x100/100??
'ArHigh(Index) = CsvStockTable(i, 2) * 100 / 100
'ArLow(Index) = CsvStockTable(i, 3) * 100 / 100
ArClose(Index) = CsvStockTable(i, 4) * 100 / 100
ArVolume(Index) = CsvStockTable(i, 5)
'ArAdjClose(Index) = CsvStockTable(i, 6) * 100 / 100


'**************************************************************************`
' Vérification de l'intégration des tableaux Ar
'Worksheets("AR").Cells(Index + 1, 1) = i
'Worksheets("AR").Cells(Index + 1, 2) = ArDate(Index)
'Worksheets("AR").Cells(Index + 1, 3) = ArOpen(Index)
'Worksheets("AR").Cells(Index + 1, 4) = ArHigh(Index)
'Worksheets("AR").Cells(Index + 1, 5) = ArLow(Index)
'Worksheets("AR").Cells(Index + 1, 6) = ArClose(Index)
'Worksheets("AR").Cells(Index + 1, 7) = ArVolume(Index)
'Worksheets("AR").Cells(Index + 1, 8) = ArAdjClose(Index)
'Worksheets("AR").Cells(Index + 1, 9) = CsvStockTable(i, 0)
'**************************************************************************

Index = Index + 1
i = i + 1
Loop Until (i > Nbr_Jours)


Set Graphe = Charts.Add
'ActiveSheet.Name = STOCK



Set SerieClose = ActiveChart.SeriesCollection.NewSeries
Set SerieVolume = ActiveChart.SeriesCollection.NewSeries


SerieClose.ChartType = xlLine 'xlLine

SerieClose.Name = "Prix"
ActiveChart.SetSourceData Source = ArClose
SerieClose.XValues = ArDate


SerieVolume.Name = "Volume"
SerieVolume.Values = ArVolume
SerieVolume.ChartType = xlColumnClustered
SerieVolume.AxisGroup = 2

End Sub
________________________________________________________________