Create Pyramid Chart with Excel VBA

To create a Pyramid Chart using Excel VBA, here’s a detailed explanation and the VBA code to achieve it. A pyramid chart is typically a stacked bar chart with the data arranged in descending order, creating a visual that looks like a pyramid.

Steps to Create a Pyramid Chart in Excel VBA:

1. Prepare the Data: Before writing the VBA code, you need to have data in Excel. Let’s assume you have two columns of data: one with categories and the other with corresponding values (e.g., age groups and population).
2. VBA Code Overview:
   • We’ll create a new chart.
   • We’ll use the data range you provide.
   • We’ll format the chart to resemble a pyramid.
   • We’ll reverse the order of categories and adjust the chart layout.

VBA Code for Creating a Pyramid Chart:

Sub CreatePyramidChart()
    ' Declare variables
    Dim ws As Worksheet
    Dim chartObj As ChartObject
    Dim dataRange As Range
    Dim categoriesRange As Range
    Dim valuesRange As Range
    Dim chart As Chart
    ' Set the worksheet object
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Adjust sheet name as needed
    ' Set the data range (adjust according to your data)
    Set dataRange = ws.Range("A1:B6") ' Assume your data is in A1:B6 (categories in column A and values in column B)
    Set categoriesRange = ws.Range("A2:A6") ' Categories
    Set valuesRange = ws.Range("B2:B6") ' Values
    ' Create a new chart
    Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=400, Top:=100, Height:=300)
    Set chart = chartObj.Chart
    ' Set chart data source
    chart.SetSourceData Source:=dataRange
    ' Set chart type to a bar chart
    chart.ChartType = xlBarStacked
    ' Reverse the order of categories to make it look like a pyramid
    chart.Axes(xlCategory).CategoryNames = categoriesRange
    chart.Axes(xlCategory).ReversePlotOrder = True
    ' Format chart to resemble a pyramid
    chart.PlotArea.Format.Fill.ForeColor.RGB = RGB(255, 255, 255) ' Set background to white
    chart.ChartArea.Format.Fill.ForeColor.RGB = RGB(255, 255, 255) ' Set chart area background to white
    ' Adjust series formatting (e.g., colors, spacing)
    Dim series As Series
    For Each series In chart.SeriesCollection
        series.Format.Fill.ForeColor.RGB = RGB(0, 102, 204) ' Set series color to blue (adjust as needed)
        series.Format.Line.Visible = msoFalse ' Remove borders
    Next series
    ' Adjust axis formatting
    With chart.Axes(xlCategory)
        .TickLabelPosition = xlLow
        .TickLabels.Font.Size = 12
    End With
    ' Adjust the title of the chart
    chart.HasTitle = True
    chart.ChartTitle.Text = "Pyramid Chart Example"
    ' Optional: Add Data Labels for clarity
    chart.ApplyDataLabels Type:=xlDataLabelsShowValue, LegendKey:=False
    ' Final chart formatting
    chartObj.Height = 300
    chartObj.Width = 500
    chartObj.Left = 100
    chartObj.Top = 100
End Sub

Explanation of the Code:

1. Setting up the Worksheet and Data Range:
   • ws = ThisWorkbook.Sheets(« Sheet1 ») sets the worksheet where your data is located.
   • dataRange = ws.Range(« A1:B6 ») defines the range for your data (categories in column A and values in column B).
   • categoriesRange and valuesRange are used to specify the ranges for category labels and values.
2. Creating the Chart:
   • Set chartObj = ws.ChartObjects.Add creates a new chart in the worksheet.
   • The chart type is set to xlBarStacked to create a stacked bar chart, which we will manipulate to appear like a pyramid.
3. Reversing the Category Order:
   • chart.Axes(xlCategory).ReversePlotOrder = True reverses the category order, making the largest value appear at the bottom of the pyramid, giving it the classic inverted pyramid shape.
4. Chart Formatting:
   • chart.PlotArea.Format.Fill.ForeColor.RGB = RGB(255, 255, 255) sets the background color of the plot area to white.
   • Inside the loop, series.Format.Fill.ForeColor.RGB sets the color of each series (bar in the chart). You can change the color to any other RGB value you prefer.
   • series.Format.Line.Visible = msoFalse removes the border around the bars.
5. Axis Formatting:
   • The axis formatting adjusts the appearance of the category labels. You can customize the size and positioning of the labels.
6. Adding Data Labels:
   • chart.ApplyDataLabels adds data labels on each bar to display the values directly on the pyramid.
7. Final Adjustments:
   • chartObj.Height and chartObj.Width set the size of the chart.
   • chartObj.Top and chartObj.Left adjust the position of the chart on the worksheet.

How to Run the Code:

1. Press Alt + F11 to open the VBA editor in Excel.
2. Insert a new module by clicking Insert > Module.
3. Paste the provided code into the module.
4. Close the VBA editor and run the macro by pressing Alt + F8, selecting CreatePyramidChart, and clicking Run.

This will generate a pyramid chart in your worksheet with the data from the specified range.