i'm writing data to an excel chart object by declaring a new series object (MySeriesClass) and then setting the MySeriesClass.Value property to an array of values gathered from an xml file. when the array size exceeds 42 items, the Series class throws an error, "Unable to set the Value property of the Series class." do i have to write the data to a worksheet? i'm trying to avoid this if possible. please advise.
below is some sample code. set loop counter n to 43 to test max array size.
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 3/4/2005 by BJB
'
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SeriesCollection.NewSeries
Dim y(), x()
For n = 0 To 42 'SET n TO 43 TO BREAK ROUTINE. 42 IS OK, 43 OR GREATER NOT.
ReDim Preserve y(n), x(n)
y(n) = n
x(n) = "x" & CStr(n)
ActiveChart.SeriesCollection(1).XValues = x
ActiveChart.SeriesCollection(1).Values = y
Next
ActiveChart.SeriesCollection(1).Name = "My Series"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "My Series"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub
Bookmarks