Enhance Data Visualization with Advanced Charts with Excel VBA

This VBA code will create multiple advanced charts, such as combo charts (line + column), radar charts, and a dynamic dashboard with custom formatting.

Sub EnhancedDataVisualization()
    ' Define the worksheet and data range
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    ' Clear any existing charts
    ws.ChartObjects.Delete
    ' Prepare data for visualization
    ws.Range("A1:F10").Value = _
        Array( _
            Array("Category", "Series1", "Series2", "Series3", "Series4", "Series5"), _
            Array("A", 10, 15, 30, 20, 25), _
            Array("B", 20, 35, 40, 25, 30), _
            Array("C", 30, 45, 60, 35, 50), _
            Array("D", 40, 55, 70, 45, 60), _
            Array("E", 50, 65, 80, 55, 70), _
            Array("F", 60, 75, 90, 65, 80), _
            Array("G", 70, 85, 100, 75, 90), _
            Array("H", 80, 95, 110, 85, 100), _
            Array("I", 90, 105, 120, 95, 110), _
            Array("J", 100, 115, 130, 105, 120) _
        )
    ' Create a combo chart (column + line)
    Dim comboChart As ChartObject
    Set comboChart = ws.ChartObjects.Add(Left:=100, Width:=500, Top:=100, Height:=300)
    With comboChart.Chart
        .SetSourceData Source:=ws.Range("A1:F10")
        .ChartType = xlColumnClustered
        ' Add a secondary axis for Series 4 and Series 5 (Line Chart)
        .SeriesCollection.NewSeries
        .SeriesCollection(2).ChartType = xlLine
        .SeriesCollection(2).AxisGroup = xlSecondary
        .SeriesCollection(3).ChartType = xlLine
        .SeriesCollection(3).AxisGroup = xlSecondary
        ' Formatting the chart
        .HasTitle = True
        .ChartTitle.Text = "Sales Data by Category"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Text = "Category"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Text = "Sales Volume (Primary)"
        .Axes(xlValue, xlSecondary).HasTitle = True
        .Axes(xlValue, xlSecondary).AxisTitle.Text = "Sales Volume (Secondary)"
        .Legend.Position = xlLegendPositionBottom
    End With   
    ' Create a radar chart
    Dim radarChart As ChartObject
    Set radarChart = ws.ChartObjects.Add(Left:=100, Width:=500, Top:=450, Height:=300)
    With radarChart.Chart
        .SetSourceData Source:=ws.Range("A1:F6")
        .ChartType = xlRadar
        .HasTitle = True
        .ChartTitle.Text = "Sales Distribution by Category"
        .Axes(xlCategory).HasTitle = True
        .Axes(xlCategory).AxisTitle.Text = "Categories"
    End With
    ' Add a pie chart for Series 1 to Series 3
    Dim pieChart As ChartObject
    Set pieChart = ws.ChartObjects.Add(Left:=650, Width:=400, Top:=100, Height:=300)
    With pieChart.Chart
        .SetSourceData Source:=ws.Range("A2:D2")
        .ChartType = xlPie
        .HasTitle = True
        .ChartTitle.Text = "Category A Breakdown"
        .Legend.Position = xlLegendPositionBottom
    End With   
    ' Create a stacked bar chart for Series 4 and Series 5
    Dim barChart As ChartObject
    Set barChart = ws.ChartObjects.Add(Left:=650, Width:=500, Top:=450, Height:=300)
    With barChart.Chart
        .SetSourceData Source:=ws.Range("A1:F6")
        .ChartType = xlBarStacked
        .HasTitle = True
        .ChartTitle.Text = "Stacked Bar Chart for Series 4 and Series 5"
        .Axes(xlCategory).HasTitle = True
        .Axes(xlCategory).AxisTitle.Text = "Category"
        .Axes(xlValue).HasTitle = True
        .Axes(xlValue).AxisTitle.Text = "Values"
    End With   
    ' Displaying a message to inform the user
    MsgBox "Charts created successfully!", vbInformation, "Data Visualization"
End Sub

Step-by-Step Explanation:

1. Data Preparation:
   • This section of the code prepares a small set of data for demonstration purposes. You can modify this part to refer to your actual data range.
   • The data consists of categories (A-J) and several series that represent different data points.
2. Clear Existing Charts:
   • Before creating new charts, the code removes any previously created charts from the worksheet using ws.ChartObjects.Delete.
3. Combo Chart (Column + Line):
   • A combination chart (Column + Line) is created using the xlColumnClustered chart type for the first three series and xlLine for Series 4 and Series 5.
   • A secondary axis is applied to the line series, which helps visualize the differences in data scale.
   • Titles for the chart and axes are added to make the chart more informative.
4. Radar Chart:
   • A radar chart is created using xlRadar, which is useful for showing multi-dimensional data, particularly when you want to compare values across categories.
   • The chart is limited to the first six data points for this example.
5. Pie Chart:
   • A pie chart is created for the first few values (Series 1 to Series 3) from the data range.
   • Pie charts are useful for showing the percentage breakdown of a particular series.
6. Stacked Bar Chart:
   • A stacked bar chart is used to show how the individual parts (Series 4 and Series 5) contribute to the total for each category.
   • The xlBarStacked chart type displays the data as stacked horizontal bars.
7. Formatting:
   • Each chart has its own formatting for titles and axis labels, which enhances readability.
   • The legends are positioned at the bottom to avoid overlapping with the chart area.
8. Completion Message:
   • After the charts are generated, a message box appears notifying the user that the charts were successfully created.

How to Use This Code:

1. Open Excel and press Alt + F11 to open the VBA editor.
2. In the editor, go to Insert > Module to create a new module.
3. Paste the code into the module and close the VBA editor.
4. Run the macro by pressing Alt + F8, selecting EnhancedDataVisualization, and clicking « Run. »

This code can be customized in various ways:

• Modify the data range to use actual data from your worksheet.
• Adjust the chart types to match your specific visualization needs.
• Add more series or charts based on your data.

Conclusion:

This VBA code provides a comprehensive way to create multiple advanced chart types and a dynamic dashboard in Excel. It leverages the flexibility of Excel’s charting capabilities to enhance data visualization, allowing for better decision-making and presentations.