In Excel, via VBA, is it possible to set chart data from data that is held in memory? For example, if you created an array in VBA [1,2,3,4,5,6...], can you have a chart in the workbook display that?
Thanks - KC
Add a comment
|
1 Answer
Yes, here's a simple example...
Sub test()
Dim vXVals As Variant
Dim vVals As Variant
vXVals = Array("Wk1", "Wk2", "Wk3")
vVals = Array(100, 175, 150)
With ActiveSheet.ChartObjects.Add(Left:=Range("B2").Left, Top:=Range("B2").Top, Width:=360, Height:=210).Chart
With .SeriesCollection.NewSeries
.Name = "Series Name"
.XValues = vXVals
.Values = vVals
End With
End With
End Sub
Hope this helps!
6 Comments
keynesiancross
Immensely! I was worried it would be some super complicated memory / keeping data objects in scope thing.... Thanks!
keynesiancross
One follow up question - do you know if there is a way to append data to that chart later on without providing it with the entire array again? The array that I'm initially setting it to will take some time to run, but would like to append incoming data as it comes in.
keynesiancross
Got it - test = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Values
keynesiancross
Sorry, I guess that wasn't really a complete answer eh! I figured I'd just get the existing values from the chart using that line above, add a row to the array, and set the Chart.Values property again
|