Create a bubble chart in Excel VBA.

Steps to Follow:

1. Open the VBA Editor:
   • In Excel, press Alt + F11 to open the VBA editor.
   • In the editor, go to Insert and then click Module to create a new module.
2. Insert the following code into the new module:

Sub CreateBubbleChart()
    ' Declare variables
    Dim ws As Worksheet
    Dim chartObj As ChartObject
    Dim dataRange As Range
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace "Sheet1" with your sheet name
    ' Define the data range for the chart (e.g., A1:C10)
    Set dataRange = ws.Range("A1:C10") ' Replace with the range of your data
    ' Add a bubble chart
    Set chartObj = ws.ChartObjects.Add(Left:=100, Top:=100, Width:=500, Height:=300
    ' Set the chart type to bubble chart
    chartObj.Chart.ChartType = xlBubble
    ' Set the data source for the chart
    chartObj.Chart.SetSourceData Source:=dataRange
    ' Add axis titles and chart title
    With chartObj.Chart
        .HasTitle = True
        .ChartTitle.Text = "Bubble Chart Example"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Text = "X Axis (Value 1)"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Text = "Y Axis (Value 2)"
        .Axes(xlBubbleSize, xlPrimary).HasTitle = True
        .Axes(xlBubbleSize, xlPrimary).AxisTitle.Text = "Bubble Size (Value 3)"
    End With
    ' Customize the legend (optional)
    chartObj.Chart.HasLegend = True
    chartObj.Chart.Legend.Position = xlLegendPositionBottom
    ' Modify bubble colors (optional)
    Dim series As Series
    Set series = chartObj.Chart.SeriesCollection(1)
    series.Format.Fill.ForeColor.RGB = RGB(0, 255, 0) ' Set bubble color to green
End Sub

Explanation of the Code:

1. Variable Declaration:
   • ws: A reference to the worksheet containing your data.
   • chartObj: A reference to the chart object (the bubble chart).
   • dataRange: The range of data containing the X values, Y values, and bubble sizes.
2. Setting the Worksheet:
   • The ws variable refers to the specified worksheet (here, « Sheet1 »). Replace « Sheet1 » with your actual sheet name.
3. Defining the Data Range:
   • The dataRange is defined for the range that contains the values for the X axis (horizontal), Y axis (vertical), and the bubble sizes.
4. Creating the Bubble Chart:
   • A new chart object is added to the worksheet using ChartObjects.Add.
   • The chart type is set to xlBubble, which creates a bubble chart.
5. Setting Titles for Axes and the Chart:
   • Titles for the X axis, Y axis, and the size of the bubbles are added using .HasTitle and .AxisTitle.Text.
6. Customizing the Legend:
   • The legend is enabled and positioned at the bottom of the chart using .Legend.Position = xlLegendPositionBottom.
7. Customizing the Bubble Color:
   • The color of the bubbles is customized using series.Format.Fill.ForeColor.RGB. In this case, the bubbles are colored green (RGB(0, 255, 0)).

Sample Data for the Bubble Chart:

To use this code, your data should be structured like this in your worksheet:

Each row represents one « bubble » on the chart, where:

• X Value: Determines the position on the X-axis (horizontal).
• Y Value: Determines the position on the Y-axis (vertical).
• Bubble Size: Determines the size of the bubble.

Running the Code:

1. After inserting the code into the VBA editor, press Alt + F8 to open the Macro dialog.
2. Select CreateBubbleChart and click Run.

This will generate a bubble chart with the data specified in the range A1:C10 on your worksheet. You can adjust the code to match your own data layout or further customize the appearance of the chart.