Creating a Chart in Excel using VBA

[ghild]

I'm trying to create a pie chart in MS Excel 2000 using VB 6. However, I need to do some computations on the data before sending it into the chart, and I can't create new fields on my worksheet to store the results. So my problem is that as far as I can figure out, I can't use
ActiveChart.SetSourceData because that only takes a range, and my data is currently stored as a dictionary (although I could change that to a two dimensional array). In VB, I found the MSChart control and it's ChartData Property, but I can't figure out how to make it work or begin to see how it'll work in Excel.

Can anyone help me figure out how to show my values in a Pie Chart? Thanks in advance...

Greg

 

[cactus13]

I don't exactly know what the problem seems to be.. but sometimes you can get some nice insights by just recording the steps you would like to happen... and then generalize strip the code of the trash you don't need.

In my opinion it is possible to add data to the Excel worksheet and afterwards add the range to your graph.

Good luck!

 

[tomatge]

I would have thought you can still perform the computations in VBA, directing the results back to the worksheet, then have a predefined chart (of whatever type you want) to display the results.
You can assign a computation output value into a cell using a statement like :
Sheets("Sheetname&quot.Range("A1&quot.Value = ResultofCalculation

 

[ghild]

Well the thing is... by specification, I can't put data back onto the spreadsheet to use for the chart. I had some success with the MSChart control, but have since been redirected to creating a PivotTable in VBA and creating a chart from that- only now I can't seem to get the VBA that creates the PivotTable to work, even when based on a recorded macro that did work.

Thanks for your input...