Number of xvalues on a chart is locked ??

[circaa]

Hi

I get the xvalues on my chart from an array. But when the number of xvalues exceeds 20, I get an error. Is it a default setting ? here's my code :

[vba]

ReDim myxvalues(1 To (numberofyears + 1))

j = 1

For i = myform.ComboBox2.Value To myform.ComboBox4.Value
myxvalues(j) = i
j = j + 1
Next i

ActiveChart.SeriesCollection(1).XValues = myxvalues()
[/vba]

any ideas how to unlock that ?

Joey

[JonPeltier]

The problem is with the limitation on the length of the series formula and its components. When you generate an array, it is written into the series formula like

{12.3456,23.4567,43.2101}

etc. Generally there are excessive digits which increase formula length without improving the charted data. You could truncate the excess digits, but you'll still never get more than around 30 or 40 points. Anyway, there's some information about this here:

http://peltiertech.com/Excel/ChartsH...ChartData.html

I generally dump the array into a worksheet range somewhere, so I don't have to deal with such limitations.

[BlueCactus]

What Jon says. Try it like this:

[VBA]ReDim myxvalues(1 To (numberofyears + 1), 1 To 1)

j = 1

For i = myform.ComboBox2.Value To myform.ComboBox4.Value
myxvalues(j, 1) = i
j = j + 1
Next i

Sheets(1).Cells(1,1).resize(ubound(myxvalues,1),1) = myxvalues
ActiveChart.SeriesCollection(1).XValues = sheets(1).Cells(1,1).resize(ubound(myxvalues,1),1) [/VBA]

[JonPeltier]

Minor improvement, so the range is only defined once:

[vba]Dim rngChartXData As Range

Set rngChartXValues = Sheets(1).Cells(1, 1).Resize(UBound(myXValues, 1), 1)
rngChartXValues.Value = myXValues
ActiveChart.SeriesCollection(1).XValues = rngChartXValues[/vba]