Q: Best way to take data from VBA into graphs without writing data to worksheets? (Can a named range refer to an array in memory only?)
 

I have a large array in VBA memory (1 to 3, 1 to 55, 1 to 4, 1 to 18, 1 to
212) filled with various data, some of which I need to dump into graphs.

In the ideal world, I would assign sections of this array to named ranges,
and set the DataSource on my graphs to the appropriate named ranges
(probably 6 at a time, print, update those 6 graphs to the next 6 named
ranges, etc.).

I'd like to avoid dumping the data into a worksheet, because that adds a
layer of complexity trying to manage all the different ranges and then
getting the information into graphs.

Is there an (easy) way to populate graphs directly from numbers in a VBA
array (through named ranges or otherwise), or am I out of luck?

Thanks,
Keith
Using Excel XP/ Windows 2000



--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

Q: Best way to take data from VBA into graphs without writing data
 

You are not completely out of luck: You can use an array to set the "Values"
or "XValues" property of the series on the chart. I don't know how familiar
you are with the object model for Excel charts - it is worth getting to know
it and you can get most of the info you need from the object browser and the
help files. Basically, to set the values for any series, you would reference
it as below (written out in long form):

Workbook(Bookname).Sheets(SheetName).ChartObjects( Index1).Chart.SeriesCollection(Index2).Values = YourArray

The difficulty: the array can only be a one-dimensional array, so you still
need to manipulate your multi-dimensional array and extract the proper values
for the particular series. But it can all be done in VBA.

"KR" wrote:

I have a large array in VBA memory (1 to 3, 1 to 55, 1 to 4, 1 to 18, 1 to
212) filled with various data, some of which I need to dump into graphs.

In the ideal world, I would assign sections of this array to named ranges,
and set the DataSource on my graphs to the appropriate named ranges
(probably 6 at a time, print, update those 6 graphs to the next 6 named
ranges, etc.).

I'd like to avoid dumping the data into a worksheet, because that adds a
layer of complexity trying to manage all the different ranges and then
getting the information into graphs.

Is there an (easy) way to populate graphs directly from numbers in a VBA
array (through named ranges or otherwise), or am I out of luck?

Thanks,
Keith
Using Excel XP/ Windows 2000



--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

Q: Best way to take data from VBA into graphs without writingdata
 

There's also a limit to how many points can be dumped via an array. The XValues
string and YValues string are each limited to about 255 characters. When you dump an
array, it writes out the array like this:

{1,2,3,4}

If you have a short array or integers, it's not a problem. If you have a lot of
points with 15 digits of pseudo precision, you fill up 255 in less than 20 points.
I've written a little about it he

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

Another approach is to dump the arrays into named ranges in the workbook, and refer
to these names in the XValues and Values of the series.

Finally, I'm never reluctant to dump the data into a sheet somewhere, then build my
chart from this range. Charts are more stable if you don't rely on them to do lot of
calculations.

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

K Dales wrote:

You are not completely out of luck: You can use an array to set the "Values"
or "XValues" property of the series on the chart. I don't know how familiar
you are with the object model for Excel charts - it is worth getting to know
it and you can get most of the info you need from the object browser and the
help files. Basically, to set the values for any series, you would reference
it as below (written out in long form):

Workbook(Bookname).Sheets(SheetName).ChartObjects( Index1).Chart.SeriesCollection(Index2).Values = YourArray

The difficulty: the array can only be a one-dimensional array, so you still
need to manipulate your multi-dimensional array and extract the proper values
for the particular series. But it can all be done in VBA.

"KR" wrote:


I have a large array in VBA memory (1 to 3, 1 to 55, 1 to 4, 1 to 18, 1 to
212) filled with various data, some of which I need to dump into graphs.

In the ideal world, I would assign sections of this array to named ranges,
and set the DataSource on my graphs to the appropriate named ranges
(probably 6 at a time, print, update those 6 graphs to the next 6 named
ranges, etc.).

I'd like to avoid dumping the data into a worksheet, because that adds a
layer of complexity trying to manage all the different ranges and then
getting the information into graphs.

Is there an (easy) way to populate graphs directly from numbers in a VBA
array (through named ranges or otherwise), or am I out of luck?

Thanks,
Keith
Using Excel XP/ Windows 2000



--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.