Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I've got a dataset that has 4 dimensions - lets say they're the following:

+------------------------------+
| Type  | Amnt |Price| Quality |
+-------|------|-----|---------+
|  A    |  15  | 4.0 |   3     |
|  B    |  32  | 4.5 |   4     |
|  C    |  35  | 3.8 |   7     |
+------------------------------+

What I'm looking to do is draw a bubble chart of this data in Excel. I can happily draw a chart showing Price against Quality, and I can then size the bubble according to Amount. However, I can't seem to find a way of being able to label the bubbles using the Type dimension. The default labels seem to just be one of the existing three dimensions used on the graph, which isn't that useful to me.

I know I can add data labels and then manually rename each one, but is there a way of using a 4th dimension for the labels automagically?

share|improve this question

2 Answers 2

up vote 1 down vote accepted

Once you've setup the chart with each bubble set to being series named by the Type column (which is a big pain by itself), just run this code, and it will label all of them based on that series name:

Sub BubbleLabel_Click()

Dim BC As ChartObject
Dim i As Integer

Set BC = ActiveSheet.ChartObjects(1)

For i = 1 To BC.Chart.SeriesCollection.Count

  With BC.Chart.SeriesCollection(i)
    .ApplyDataLabels
    .DataLabels.ShowSeriesName = True
    .DataLabels.ShowValue = False
  End With

Next i

End Sub

OR use this code based on a named range "MakeMeAChart" (change as you'd like) on all the data, but not the headers. It will create the chart, with each row a series, and then label them all the first column:

Sub BubbleLabel_Click()

Dim i As Integer

With ActiveSheet.ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225)
  For i = 1 To Range("MakeMeAChart").Rows.Count
    .Chart.SeriesCollection.NewSeries
    .Chart.SeriesCollection(i).Name = Range("MakeMeAChart").Rows(i).Cells(1, 1)
    .Chart.SeriesCollection(i).XValues = Range("MakeMeAChart").Rows(i).Cells(1, 3)
    .Chart.SeriesCollection(i).Values = Range("MakeMeAChart").Rows(i).Cells(1, 4)
    If i = 1 Then .Chart.ChartType = xlBubble3DEffect
    .Chart.SeriesCollection(i).BubbleSizes = "="& Range("MakeMeAChart").Parent.Name _
                & "!" & Range("MakeMeAChart").Cells(1, 2).Address(1, 1, xlR1C1)
  Next i

  For i = 1 To .Chart.SeriesCollection.Count
    With .Chart.SeriesCollection(i)
      .ApplyDataLabels
      .DataLabels.ShowSeriesName = True
      .DataLabels.ShowValue = False
    End With
  Next i
  .Chart.Legend.Delete
End With

End Sub

Note: There are a lot of bugs and tricks to doing bubble charts in VBA.

share|improve this answer

@Lance's code works well. I just had a problem with setting the ChartType, and there's a missing .Rows(i) when setting the BubbleSize

This is what I came up with:

Sub BubbleLabel_Click()

Dim r As Range

With ActiveSheet.ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225).Chart
  'Sacrifical rows that will be lost
  .SeriesCollection.NewSeries
  .SeriesCollection.NewSeries
  .ChartType = xlBubble3DEffect
  .Legend.Delete

  For Each r In [MakeMeAChart].Rows
    With .SeriesCollection.NewSeries
      .Name = r.Cells(1, 1)
      .XValues = r.Cells(1, 3)
      .Values = r.Cells(1, 4)
      .BubbleSizes = "=" & [MakeMeAChart].Parent.Name & "!" & r.Cells(1, 2).Address(1, 1, xlR1C1)
      .ApplyDataLabels
      .DataLabels.ShowSeriesName = True
      .DataLabels.ShowValue = False
    End With
  Next
End With

End Sub
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.