Bubble Chart

[Rajan_Verma]

Bubble chart represent Data in form of Bubble on X or Y Axis , every point of Bubble accept 3 argument X, Y and Size,

you can refer below table to create a bubble chart.

Name X Y Size
A 70 40 1256
B 46 74 1433
C 67 94 1266
D 58 80 884
E 37 69 1286
F 65 67 1196
G 68 22 1218
H 10 84 577
I 56 14 1342
J 16 28 518
K 39 47 1609


Here is Code to create bubble chart on single click. Name the first cell of Table as “rngRange” to refer that in code.

Sub CreateBubbleChart()
    
    Dim chtBubble   As Chart
    Dim rngSource   As Range
    Dim rngCell     As Range
    Dim lngSeriesCount As Long
    Dim DataLabel       As Object
 
    lngSeriesCount = 1
    Set rngSource = Intersect(Range(“rngRange”).CurrentRegion, Range(“rngRange”).CurrentRegion.Offset(1))
    Set chtBubble = Sheet1.Shapes.AddChart(xlBubble, 200, 100, 700, 300).Chart
    
    
    With chtBubble
        For Each rngCell In rngSource.Rows
            .SeriesCollection.NewSeries
            .SeriesCollection(lngSeriesCount).Name = rngCell.Cells(1).Value
            .SeriesCollection(lngSeriesCount).XValues = rngCell.Cells(2).Value
            .SeriesCollection(lngSeriesCount).Values = rngCell.Cells(3).Value
            .SeriesCollection(lngSeriesCount).BubbleSizes = rngCell.Cells(4).Value
            .SeriesCollection(lngSeriesCount).ChartType = xlBubble3DEffect
            ‘working with DataLabels
            .SeriesCollection(lngSeriesCount).ApplyDataLabels
            Set DataLabel = .SeriesCollection(lngSeriesCount).Points(1).DataLabel
            DataLabel.Left = DataLabel.Left – 40
            DataLabel.ShowSeriesName = True
            lngSeriesCount = lngSeriesCount + 1
        Next rngCell
          .HasLegend = False
    End With
    
End Sub

http://excelpoweruser.wordpress.com/.../bubble-chart/

Thanks for Reading
Rajan.