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.sstatic.net/UpOZm.png i.sstatic.net/8H5Qu.png — Kevin M
– Kevin M, Commented May 23, 2016 at 21:04

Community · Accepted Answer · 2017-05-23 12:22:37Z

0

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

edited May 23, 2017 at 12:22

CommunityBot

11 silver badge

answered May 23, 2016 at 21:38

poppertech

1,2942 gold badges9 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kevin M Over a year ago

I appreciate the response, but unfortunately this solution requires using a range of pre-filled cells to make the chart. I was hoping to create the chart directly from an array.

poppertech Over a year ago

If you replace "s.Values = myData" with "s.Values = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)", the solution still works.

Kevin M Over a year ago

Thanks bwyn, it did help me get to the final solution. I ended up needing an additional line to extract each column from my array but then your code helped from then on. Thanks again!

Collectives™ on Stack Overflow

Excel VBA Add Series to Radar Chart from Array

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related