Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Named-range source-data for pie charts on copied worksheets | Charts and Charting in Excel | |||
Can a cell refer to range name/array of data previously set? | Excel Discussion (Misc queries) | |||
How does one refer to the n-1 element of a named range? | Excel Discussion (Misc queries) | |||
does Excel have memory limit of data Array? | Excel Programming | |||
How do you refer to a dynamic named range? | Excel Programming |