Is it possible to plot a chart strictly from VBA memory w/out linking it to data in cells?

[luv2glyd]

I have a rather large workbook with bunch of worksheets data from which need to be charted. The charts remain stationary on the first page, but which workbook they chart changes on a constant basis, so rather than using the "indirect" function which takes up a lot of memory would like to see if I can simply absorb the data into VBA memory, and without importing it somewhere on the first sheet, chart it strictly from memory. Is that possible?

Thanks

[Richard Buttrey]

Are you saying that you have a completely separate workbook containing the chart data. If so have you considered creating a Data Connection to that workbook and have VBA change the connection link when necessary?

[luv2glyd]

Actually, everything is in the same workbook - so sheet 1 would have the charts on it and sheets 1, 2, 3, 100, etc. will have the data. Yes, I've done what you are proposing before with separate CSV files containing data, but in this case the data is being written continuously (every few mins) to the individual worksheets and I need to regularly retrieve it into charts.

[MrShorty]

Possible? I expect that it is. You can use the .values property (and .xvalues property, if you need to change both x and y data) of a data series (https://msdn.microsoft.com/EN-US/lib.../ff840677.aspx has a list of available properties and methods for the series object). You can assign an array constant to these properties, or assign a range reference, as explained in the help file.

Whether it would really use less resources to 1) read the data into VBA then 2) write the data to the chart series than using the INDIRECT() function in a worksheet, I cannot say. You would have to try it and see.

[luv2glyd]

Ok, thank you. I'm just learning about VBA memory arrays and am totally unfamiliar with the jargon. Would you be able to give an example? How would you plot the following in a stock chart for example? This would be the open-high-low-close option under excel "Stock" in Excel stock chart type, requiring 4 columns of data. I'd have the chart already sitting in an excel workbook, so there's no need to create it with VB, just populate it with data.

Here's an example of data in 4 columns that I am trying to plot:

116.17 116.40 116.17 116.30
116.49 116.79 116.48 116.54
116.53 116.60 116.30 116.36
116.32 116.48 116.21 116.30
116.29 116.35 116.04 116.08
116.07 116.12 115.95 116.12
116.12 116.12 115.98 116.00
116.00 116.08 115.96 116.02
116.01 116.08 115.96 116.02

Thank you

[MrShorty]

This is not the kind of programming that I do, so I probably do not have any better examples than the ones in the help files.

What jargon are you having trouble understanding? If it is about understanding how properties and methods work, this is probably one key to learning VBA (and other object oriented languages) -- learning how to navigate Excel's object model.

As near as I can tell, an open-high-low-close chart should be like any other chart with 4 data series in it, at least as far as changing the values of the data series. I would expect the procedure to:
1) Read the data -- it is not clear to me if you will want to read the data into a VBA array, or simply "copy" the data into the dashboard sheet. It sounds like you are asking to read it into a VBA array. From your other threads, I assume you already know how to do this.
2) Loop through each data series in the series collection and assign the appropriate array to each data series .values property.

Maybe something like (untested and incomplete):

Please Login or Register  to view this content.

I don't really know if this is more efficient than the INDIRECT() function, but that is kind of how I would expect it to work.

[luv2glyd]

Ok, thank you so much for your help and the example. I had to tweak it a bit, but works well. For future ref.:

Please Login or Register  to view this content.