Creating a bubble chart with a variable size in Excel using VBA

A bubble chart is a type of chart where each data point is represented by a bubble, whose position on the X and Y axes is determined by values from those axes, and its size is determined by a third variable.

Objectives:

• Create a bubble chart.
• Add data with X, Y values, and bubble sizes.
• Customize chart properties.

Example Data:

Detailed VBA Code:

Sub CreateBubbleChart()
    ' Declare variables
    Dim ws As Worksheet
    Dim chartObj As ChartObject
    Dim chart As Chart
    Dim dataRange As Range   
    ' Assign the active worksheet to the ws variable
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace with your sheet name   
    ' Define the data range for the chart (for example, A1 to C5)
    Set dataRange = ws.Range("A1:C5") ' Adjust the range to your data   
    ' Create a chart object
    Set chartObj = ws.ChartObjects.Add(Left:=100, Top:=100, Width:=500, Height:=300)   
    ' Assign the created chart to the chart variable
    Set chart = chartObj.Chart   
    ' Set the chart type to "Bubble"
    chart.ChartType = xlBubble   
    ' Assign the data to the chart
    chart.SetSourceData Source:=dataRange   
    ' Configure the chart series
    With chart.SeriesCollection(1)
        ' Set the X, Y values and bubble size
        .XValues = ws.Range("A2:A5") ' X values
        .Values = ws.Range("B2:B5") ' Y values
        .BubbleSizes = ws.Range("C2:C5") ' Bubble size
    End With   
    ' Customize the chart (example)
    With chart
        ' Add a chart title
        .HasTitle = True
        .ChartTitle.Text = "Bubble Chart"       
        ' Add titles to the X and Y axes
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Text = "X Value"       
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Text = "Y Value"      
        ' Customize bubble colors
        .SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(0, 255, 0) ' Bubble color is green
    End With   
    ' Display the chart
    chartObj.Visible = True
End Sub

Detailed Explanation of the Code:

1. Variable Declaration:
   • ws: A variable that refers to the worksheet containing the data.
   • chartObj: The chart object that will be created in the worksheet.
   • chart: The chart object that allows manipulation of the chart.
   • dataRange: The range of data containing X values, Y values, and bubble sizes.
2. Defining the Data Range:
   • The code refers to a data range in the worksheet (A1:C5), which contains X, Y, and bubble size values.
3. Creating the Chart:
   • The ChartObjects.Add method creates a chart as an object.
   • Then, the chart type is set to a bubble chart using chart.ChartType = xlBubble.
4. Configuring the Chart Data:
   • XValues: The data range for the X-axis values.
   • Values: The data range for the Y-axis values.
   • BubbleSizes: The data range for the size of the bubbles.
5. Customizing the Chart:
   • Add a title to the chart and axis titles.
   • The color of the bubbles is customized (in this case, set to green).
   • You can further adjust the chart (e.g., change colors, titles, labels, etc.).
6. Displaying the Chart:
   • chartObj.Visible = True ensures the chart is visible after creation.

Customization:

You can adjust:

• The bubble sizes by modifying the values in the « Bubble Size » column.
• The appearance of the chart, bubble colors, axis labels, and other visual properties.
• The data range can be adjusted based on the position and size of your dataset.

Note:

To run this code, you need to open the VBA editor in Excel (Alt + F11), create a new module, and paste this code there. Then, you can run it by pressing F5 or calling it through a button on your worksheet.