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

ID: Q139662

The information in this article applies to:

SUMMARY

In Microsoft Excel, you can write a macro to create a bubble chart. Bubble charts are similar to xy (scatter) charts; however, instead of having uniform markers where the pair of x and y values intersect, these points are marked with circles that indicate the relative magnitude of the values in a third series.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web: 

   http://www.microsoft.com/support/supportnet/refguide/
Enter the following code on a module sheet and follow the instructions below: 

   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:

      a. Step 2: choose XY (Scatter)

      b. Step 3: choose Type 1

      c. Step 4: choose Data Series in Columns; Use first 1 column(s) for
         X data; Use first 1 row(s) for legend text.

      d. Step 5: choose No for Add a legend; enter titles is optional.
3. Activate the worksheet and type the following formula in cell E2: 

      =D2/MAX($D$2:$D$5)
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 Microsoft Excel 4.0 Macro language, please see the following article(s) in the Microsoft Knowledge Base: 

   ARTICLE-ID: Q107729
   TITLE     : Excel: Macro to Create Bubble Chart
Additional query words: 5.00 7.00 bubbles circles 
Keywords          : kbcode kbprg PgmHowto xlchart 
Version           : WINDOWS:5.0,7.0; MACINTOSH:5.0
Platform          : MACINTOSH WINDOWS
Issue type        : kbhowto

Last Reviewed: May 17, 1999