How to Use a Visual Basic Macro to Create a Bubble Chart

This article was previously published under Q139662
This article has been archived. It is offered "as is" and will no longer be updated.
In Microsoft Excel, you can write a macro to create a bubble chart. Bubblecharts are similar to xy (scatter) charts; however, instead of havinguniform markers where the pair of x and y values intersect, these pointsare marked with circles that indicate the relative magnitude of the valuesin a third series.
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. Enter the following code on a module sheet and follow the instructionsbelow:
   Sub BubbleChart()      'define variables for workbook, chart, and chart series      MyBook = ActiveWorkbook.Name      MyChart = ActiveChart.Name      MySeries = ActiveChart.SeriesCollection(1).Formula      'define variables for worksheet and chart series reference      StartVal = InStr(InStr(1, MySeries, "(") + 1, MySeries, ",") + 1      EndSheetVal = InStr(StartVal, MySeries, "!")      mysheet = Mid(MySeries, StartVal, EndSheetVal - StartVal)      EndVal = InStr(StartVal, MySeries, ",")      mysource = Mid(MySeries, StartVal, EndVal - StartVal)      If InStr(mysheet,"'") Then                     'strip out apostrophe         mysheet = Mid(mysheet, 2, Len(mysheet) - 2) 'if sheet name has a      End If                                         'space      'begin loop to add data labels to chart      Counter = 1      For Each xItem In Range(mysource)         xLabel = xItem.Offset(0, -1).Value         ActiveChart.SeriesCollection(1).Points(Counter).HasDataLabel _            =True         ActiveChart.SeriesCollection(1).Points(Counter).DataLabel.Text _            =xLabel         Counter = Counter + 1      Next xItem      'create oval on worksheet (used for chart bubbles)      Workbooks(MyBook).Sheets(mysheet).Activate      ActiveSheet.Ovals.Add(335.25, 12.75, 52.5, 52.5).Select      Application.ScreenUpdating = False      MyOval = ActiveSheet.DrawingObjects.Name      'get values from worksheet to compute bubble sizes      Set MyBubbleRange = Range(mysource).Offset(0, 3)      'begin loop to compute bubble size and add to chart data point      For Counter = 1 To MyBubbleRange.Count         BubbleValue = MyBubbleRange(Counter) * 50         ActiveSheet.DrawingObjects(MyOval).Select         With Selection            .Width = BubbleValue            .Height = BubbleValue         End With         Selection.Copy         Workbooks(MyBook).Sheets(MyChart).Activate         ActiveChart.SeriesCollection(1).Points(Counter).Select         Selection.Paste         'select worksheet         MyBubbleRange.Parent.Activate      Next Counter      'activate chartsheet      ActiveWorkbook.Sheets(MyChart).Activate      'remove oval from worksheet      ActiveWorkbook.Sheets(mysheet).DrawingObjects(MyOval).Delete   End Sub				

To use the macro

  1. In a new worksheet in Microsoft Excel, enter the following values:
          A1:        B1: Gross Revenues  C1: Net Income  D1: # of Plants      A2: East   B2: 831191          C2: 35427       D2: 26      A3: West   B3: 622199          C3: 54263       D3: 13      A4: North  B4: 153794          C4: 80881       D4: 40      A5: South  B5: 711327          C5: 33872       D5: 35						
  2. Select the range B1:C5. From the Insert menu, choose Chart, As New Sheet. In the chart wizard choose the following:

    1. Step 2: choose XY (Scatter)
    2. Step 3: choose Type 1
    3. Step 4: choose Data Series in Columns; Use first 1 column(s) for X data; Use first 1 row(s) for legend text.
    4. Step 5: choose No for Add a legend; enter titles is optional.
  3. Activate the worksheet and type the following formula in cell E2:
  4. Select cells E2:E5 and click Fill Down on the Edit menu. This formula calculates the number of plants for each region relative to the total number of plants in all four regions.
  5. Select the chart sheet and run the macro. Click Macro on the Tools menu, click BubbleChart, and click Run.
For additional information on creating Bubble Charts using the MicrosoftExcel 4.0 Macro language, please see the following article(s) in theMicrosoft Knowledge Base:
107729 Excel: Macro to Create Bubble Chart
bubbles circles XL

Article ID: 139662 - Last Review: 12/04/2015 12:24:03 - Revision: 2.3

Microsoft Excel 95 Standard Edition, Microsoft Excel 5.0 Standard Edition, Microsoft Excel 5.0 Standard Edition, Microsoft Excel 5.0 for Macintosh

  • kbnosurvey kbarchive kbcode kbhowto kbprogramming KB139662