Bubble Chart

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.

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.

See another post here:

http://www.excelfox.com/forum/f12/cr...omatically-59/