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).DataLa bel
DataLabel.Left = DataLabel.Left – 40
DataLabel.ShowSeriesName = True
lngSeriesCount = lngSeriesCount + 1
Next rngCell
.HasLegend = False
End With

End Sub

http://excelpoweruser.wordpress.com/2012/09/03/bubble-chart/

Thanks for Reading
Rajan.

[Admin]

See another post here:

http://www.excelfox.com/forum/f12/create-bubble-chart-automatically-59/