Excel VBA Add Series to Radar Chart from Array

Question

I'm having trouble with VBA adding series to make a filled radar chart. There may be a better way to do this but to build the desired chart (without VBA), I create a table that is (120,12) in a worksheet, add a "filled radar" chart in the range A1:L120, then manually update the formula bar as such:

=SERIES("name1",,'Sheet1'!$A$1:$A$120,1)
=SERIES("name2",,'Sheet1'!$B$1:$B$120,2)
etc for 12 more series

The idea is to only have 11 values in each column as nonzero, making a chart that looks like this:

Here's a bad example of the first 3 columns of the Range I normally use to make the chart:

I'd like to be able to do this in VBA, but instead of taking the values from a range in the worksheet, the values would come from a (120,12) array. I've read several different pages that have similar intentions but I've yet to see one that addresses this specific issue. One big problem is each SERIES needs to come from a single column in my array, and many pages say it's not possible to pull a single column from an array to put into the =SERIES function.

Overall though, the big problem I'm wrestling with is populating a chart with values from an array. Any help in this matter is greatly appreciated.

Thanks!

I'm not sure why my images didn't get added, here they are in order: i.stack.imgur.com/UpOZm.png i.stack.imgur.com/8H5Qu.png — Kevin M

bwyn bwyn · Accepted Answer · 2016-05-23T21:38:55

This post partially answers the question.

I set up the data as shown in the image below.

Then, the following code creates the chart with multiple series.

Sub CreateChartFromArray()

Dim c As Chart
Dim s As Series
Dim myData As Variant

ActiveSheet.Shapes.AddChart.Select
Set c = ActiveChart
ActiveChart.ChartType = xlRadar

For i = 1 To 12
myData = Range(Cells(1, i), Cells(101, i)).Value
    Set s = c.SeriesCollection.NewSeries()
    s.Values = myData
Next

End Sub

Excel VBA Add Series to Radar Chart from Array

1 Answers