How to correctly automate Radar Chart creation process in VBA?

caesarion

New Member
Joined
Jun 24, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi everybody!

So I'm a total noob and have never used VBA before but I'm trying to automate the creation of 'radar charts' for work. I have a table that looks like this:
Example Table 1.png

And I'm trying to automate the process of creating radar charts that look like this:
Example Wanted Graph 1.png

So far I've written this:

VBA Code:
Sub AddCharts()

Dim i As Integer 'Rows
Dim j As Integer 'Columns

i = Cells(Rows.Count, 1).End(xlUp).Row

For j = 2 To i
    With ActiveSheet.Shapes.AddChart.Chart
    .ChartType = xlRadar
    .SeriesCollection.NewSeries
        With .SeriesCollection(1)
        .Name = "=" & ActiveSheet.Name & "!" & _
        Cells(j, 1).Address
        .Values = "=" & ActiveSheet.Name & "!" & _
        Range(Cells(2, 2), Cells(2, 11)).Address
        End With
    End With
Next j


End Sub

And I've gotten here:
Example Current Chart 1.png


A few things:
1. I've been trying to figure out how to change the x axis labels so that it will display the "skill" instead of a number, but can't figure out how to do it!
2. All the skills are out of 5, and some people don't have a 5 -- but how do I standardize it so 5 appears on all charts?
3. How do I get rid of the "person 5" description and line?

Any help would be appreciated. Thank you so much!
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Hi, and welcome to the Board!

The following code assumes that the workbook running the code contains the source data, and that Sheet1 contains the source data. So change the sheet name accordingly. Note that the charts are placed on your worksheet with one following the another.

*EDIT*

The code has been amended to include the line formatting for the value axis. Change the color as desired.


VBA Code:
Option Explicit

Sub AddCharts()

    Dim sourceWorksheet As Worksheet
    Set sourceWorksheet = ThisWorkbook.Worksheets("Sheet1") 'change the sheet name accordingly

    With sourceWorksheet
     
        Dim lastRow As Long
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
     
        Dim lastColumn As Long
        lastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
     
    End With
 
    'left position for all charts
    Dim leftPos As Long
    leftPos = 30
 
    'starting top position for charts
    Dim topPos As Long
    topPos = 15
 
    Dim i As Long
    For i = 2 To lastRow
 
        Dim newChart As Chart
        Set newChart = sourceWorksheet.Shapes.AddChart2(XlChartType:=xlRadar, Left:=leftPos, Top:=topPos).Chart
 
        'remove extra series, if any
        With newChart
            Do While .SeriesCollection.Count > 0
                .SeriesCollection(1).Delete
            Loop
        End With
     
        Dim newSeries As Series
        Set newSeries = newChart.SeriesCollection.newSeries
     
        With sourceWorksheet
            newSeries.Name = "=" & .Cells(i, 1).Address(external:=True)
            newSeries.XValues = .Range(.Cells(1, 2), .Cells(1, lastColumn))
            newSeries.Values = .Range(.Cells(i, 2), .Cells(i, lastColumn))
        End With
     
        
        With newChart
            With .Axes(Type:=xlValue)
                .MinimumScale = 0
                .MaximumScale = 5
                With .Format.Line
                    .Visible = msoTrue
                    .ForeColor.RGB = RGB(0, 0, 0) 'black
                End With
            End With
        End With
     
        With newChart
            'If .HasTitle Then .ChartTitle.Delete
            If .HasLegend Then .Legend.Delete
        End With
 
        topPos = topPos + newChart.Parent.Height + 25 'top position of next chart
     
    Next i
 
End Sub

Hope this helps!
 
Last edited:
Upvote 0
Solution
Hey, thanks so much for the help, I super appreciate it! I plugged the code in, and I got this:

Example Current Chart 2.png



How would I include the radial/spoke lines? I assumed it would be something like With .Axes & format/line, but I can't seem to figure which axis object property it is!

Thanks so much again.
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,190
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top