Excel Chart directly from VBA?

[navyguysrighthandman]

Does anyone know how to create an Excel chart directly from VBA without first putting the source data on a spreadsheet? I am trying to do a short animation by refreshing the chart a number of times. The problem is that the extra step of displaying the data on the spreadsheet slows down the program significantly. I can get around this by creating a "Chart data" spreadsheet dedicated to storing numbers and then doing the chart on the active sheet but I would rather not have to do this.

Any ideas?

Thanks,
Ben

 

[PHV]

To speed up the data refresh you can insert this before:

Application.ScreenUpdating = False

and this after:

Application.ScreenUpdating = True

Hope This Help
PH.

 

[navyguysrighthandman]

Doesn't this keep the chart from updating also?

 

[PHV]

If it doesn't, execute the Refresh method of the Chart object before restoring the ScreenUpdating.

Hope This Help
PH.

 

[navyguysrighthandman]

So there's no way to keep from dedicating a sheet to store data? My ideal solution would be to have a way to go directly from an array in VBA to an Excel Chart without dumping the array in Excel first.

Ben

 

[jaseti]

I found this earlier today while searching for something else.
I hope it helps


Sub ArrayChart()

With Worksheets(1)

.Select


' Add a chart object. Note: the 200's are coordinates and sizing
' for Left, Top, Width, and Height

ActiveSheet.ChartObjects.Add(200, 200, 200, 200).Select


' This produces the chart with a temporary source data range

ActiveChart.ChartWizard .Cells(1), xlPie


' Provide an array of values to plot as the chart's first series

Selection.Chart.SeriesCollection(1).Values = _
Array(34, 33, 16, 10, 3, 1)


End With
End Sub



jaseti

 

[SkipVought]

Ben,

You do not have to display the data on the sheet. Using the Activate and Select methods can also greatly increase processing time.

faq707-4105 How Can I Make My Code Run Faster?

If your series data ranges are also changing, you can use Dynamically Named Ranges using the OFFSET function in Insert/Name/Define.

faq68-1331 How can I rename a table as it changes size



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884