PDA

View Full Version : Number of xvalues on a chart is locked ??



circaa
08-03-2006, 11:16 AM
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 :



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()


any ideas how to unlock that ?

Joey

JonPeltier
08-11-2006, 09:16 PM
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/ChartsHowTo/DelinkChartData.html

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

BlueCactus
08-18-2006, 07:34 PM
What Jon says. Try it like this:

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)

JonPeltier
08-19-2006, 07:43 AM
Minor improvement, so the range is only defined once:

Dim rngChartXData As Range

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