Series values from an array variable

[Guest]

I am encountering a limit of < 25 array elements when using the following
assignment method:

ActiveChart.SeriesCollection(1).Values = thisDataSet

where thisDataSet is a declared array variable of type variant. When the
array variable has more than 24 elements the above assignment crashes with
the message "unable to set the values property of the series class". This
array variable always contains numeric data in every element with no empty
elements. The assignment works perfectly for thisDataSet of 24 or fewer
elements. Is there a better way to assign series values from an array
variable? Or, do I have to live with the < 25 array element limit?

 

[Tushar Mehta]

When one sets the Values to an array, XL actually sets the Values
property to the numbers in each array element. Unfortunately, the
Values property has a limit of about 250 characters. So, if the numbers
are like 100.7755790, 200.9876554091, a lot of characters are being
wasted in data that doesn't really provide any value. Consider rounding
off the array values so that they use only some number of digits.

The number of digits don't necessarily have to be on the decimal side.
If one were plotting numbers in the millions, there is little need for
accuracy down to the units (or even hundreds) place. 10,000,109 and 12,
108,987 are practically indistinguisable from 10,000,000 and 12,108,000
respectively. I would just plot 10.0 and 12.1 and label the axis as 'In
millions.'



--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005

 

[Gerardo]

hello,

I'm working with the same problem, is there another way around? Because
sometimes you will like to plot values smaller than one and some times you
will have millons or thousands of millions. I'm assuming you will need some
more lines to validate imput data before rounding them to have less
characters, and what happens if i need to plot 251 values?

Thank you
Kindest regards

 

[Jon Peltier]

It's not 251 values, it's 251 characters. Each value has multiple
characters. See the issue now? It can be as few as 15 or so values.

The best approach is to actually put the data into the worksheet, and plot
from that range of values. That is how charts were designed, and that's how
they work best.

- Jon

 

[Gerardo]

Thank you, I meant to say what if I have 251 integer values.
I'll take your advice on using ranges, the thing is, I didn't want to print
a series calculated within the macro, so that I don't have to modify the
source workbook.
And as far as I understand there aren't "virtual" ranges.

Regards

 

[Jon Peltier]

It's more like 125 integer values, each between 0 and 9, because you have to
include commas.

Use a range outside of the print area for the data, or put the data onto
another sheet.

There are in fact virtual ranges you could use, called Names. You can
populate one with an array. They become difficult to debug, since you see
them only indirectly. This simple procedure should give you an idea how to
implement them:

Sub PlotArray()
Dim iArray As Long
Dim aArray(1 To 1000) As Long

For iArray = LBound(aArray) To UBound(aArray)
aArray(iArray) = iArray
Next
ActiveSheet.Names.Add "MyArray", WorksheetFunction.Transpose(aArray)

ActiveSheet.ChartObjects("ChartArray").Chart.SeriesCollection(1).Values =
_
"='" & ActiveSheet.Name & "'!MyArray"
End Sub

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