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

Image

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
Download  .xlsm from here
Thanks for Reading
Rajan.

9 Comments Add yours

  1. dharmendra says:

    code is not working… DataLabel.Left = DataLabel.Left – 40…….. syntax error on this line

    1. there is a problem in wordpress font , you can type again this line, will work ok
      thanks

  2. Also put DoEvents before that line
    For example
    DoEvents: DoEvents
    DataLabel.Left = DataLabel.Left – 40

    Thanks

  3. Jorge says:

    Hi, my code stops at

    “Set rngSource = Intersect(Range(“rngRange”).CurrentRegion, Range(“rngRange”).CurrentRegion.Offset(1))”

    I´ve named the first cell rngRange put it gives me error always

  4. please remove Double Quotes and Put it again

    1. Jorge says:

      Hi did that and still doesn´t work, don´t know what i´m doing wrong 😦

  5. Hi Jorge,
    I have attached a link in the post , you can download the .xlsm file from there

    thanks
    Rajan.

    1. Jorge says:

      Perfect, weird that by copy paste didn´t work. Thank you very much Rajan 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s