Create Funnel Chart with Excel VBA

To create a Funnel Chart in Excel using VBA, we will utilize a combination of Excel’s built-in charting functionality and VBA to automate the process. Below is a detailed code and explanation for generating a Funnel Chart dynamically in Excel.

Steps for Creating a Funnel Chart Using VBA:

1. Prepare Your Data: Before we start writing the VBA code, you should have data in a tabular form. Funnel charts typically represent stages in a process, so your data will have a series of stages and values that decrease (or increase) as the stages progress.

Example Data Layout:

2. VBA Code to Create Funnel Chart: This VBA code will create a Funnel Chart using the data in your Excel worksheet.

Sub CreateFunnelChart()
    ' Define variables
    Dim ws As Worksheet
    Dim chartObj As ChartObject
    Dim rng As Range   
    ' Set worksheet where data is located (adjust as needed)
    Set ws = ThisWorkbook.Sheets("Sheet1")   
    ' Define the data range (assumes data is in columns A and B)
    Set rng = ws.Range("A1:B6")   
    ' Create a new ChartObject
    Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225)   
    ' Set the chart type to Funnel (Excel 2016 and later supports this chart type)
    With chartObj.Chart
        .SetSourceData Source:=rng
        .ChartType = xlFunnel       
        ' Customize the chart title
        .HasTitle = True
        .ChartTitle.Text = "Funnel Chart Example"       
        ' Customize the data labels
        .ApplyDataLabels Type:=xlDataLabelsShowValue, AutoText:=True, LegendKey:=False       
        ' Set the appearance of the funnel chart
        With .SeriesCollection(1)
            .Format.Fill.ForeColor.RGB = RGB(0, 102, 204) ' Color of the funnel sections
            .Format.Line.Visible = msoFalse ' Remove lines around the sections
        End With
    End With   
    ' Optional: Auto fit the chart to the available space
    chartObj.ShapeRange.LockAspectRatio = msoFalse
    chartObj.Width = 500
    chartObj.Height = 300 
End Sub

Detailed Explanation of the Code:

1. Setting up variables:

• Dim ws As Worksheet
• Dim chartObj As ChartObject
• Dim rng As Range

1. • ws is the worksheet where the data is located.
   • chartObj is the chart object that will be created.
   • rng is the range of data that will be used to generate the chart.
2. Setting the data range:

• Set ws = ThisWorkbook.Sheets(« Sheet1 »)
• Set rng = ws.Range(« A1:B6 »)

• • The ws variable refers to « Sheet1 » (adjust as needed).
  • The rng variable is set to the range containing the data (adjust this range according to your actual data location).

3. Creating the Chart:

• Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225)

• • ChartObjects.Add creates a new chart on the worksheet with the specified position and size.

4. Setting the chart type:

• ChartType = xlFunnel

• • xlFunnel specifies the funnel chart type. This chart type is available in Excel 2016 and later.

5. Customizing the chart title:

• .HasTitle = True
• .ChartTitle.Text = « Funnel Chart Example »

• • The chart title is enabled, and we specify the title text.

6. Adding data labels:

• .ApplyDataLabels Type:=xlDataLabelsShowValue, AutoText:=True, LegendKey:=False

• • This line applies data labels to show the values on the chart, making it easier to read the numbers associated with each stage.

7. Customizing the appearance:

• With .SeriesCollection(1)
• .Format.Fill.ForeColor.RGB = RGB(0, 102, 204)
• .Format.Line.Visible = msoFalse
• End With

• • The funnel chart sections are colored blue (using the RGB color value), and the lines around the sections are removed for a cleaner look.

8. Optional: Auto-fitting the chart:

• ShapeRange.LockAspectRatio = msoFalse
• Width = 500
• Height = 300

1. • This part of the code ensures that the chart is resized to fit within a specified width and height.

Important Notes:

• Excel Versions: The funnel chart type is available only in Excel 2016 and later versions. If you are using an older version, you may need to create a custom funnel chart by using a stacked column chart and adjusting the data and formatting.
• Data Format: Ensure your data is in a two-column format where the first column contains the stages (or categories) and the second column contains the values associated with those stages.
• Chart Customization: You can further customize the chart by adjusting colors, labels, or adding a trendline, depending on your needs.