XL: How to Use a Visual Basic Macro to Create a Bubble Chart
ID: Q139662
|
The information in this article applies to:
-
Microsoft Excel for Windows, version 5.0
-
Microsoft Excel for Windows NT, version 5.0
-
Microsoft Excel for Windows 95, version 7.0
-
Microsoft Excel for the Macintosh, version 5.0
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/overview/overview.asp
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
- 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
- Select the range B1:C5. From the Insert menu, choose Chart, As New
Sheet. In the chart wizard choose the following:
- Step 2: choose XY (Scatter)
- Step 3: choose Type 1
- Step 4: choose Data Series in Columns; Use first 1 column(s) for
X data; Use first 1 row(s) for legend text.
- Step 5: choose No for Add a legend; enter titles is optional.
- Activate the worksheet and type the following formula in cell E2:
=D2/MAX($D$2:$D$5)
- 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.
- 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:
Q107729 Excel: Macro to Create Bubble Chart
Additional query words:
bubbles circles
Keywords : kbcode kbprg PgmHowto xlchart
Version : MACINTOSH:5.0; WINDOWS:5.0,7.0; winnt:5.0
Platform : MACINTOSH WINDOWS winnt
Issue type : kbhowto